This seems like it should be easy, but I’m not figuring it out. What I want to do is list Quotes, then put the QSalesRP records on one line for it. The problem is that I need to display the Name and RepSplit for each one, and use the RepSplit for calculated fields. Now, I’m pretty sure I am safe using no more than 3 Reps per QuoteHed (I only need to show ones with a Split > 0) but I can’t figure out how to display or use the RepSplit in calculations. I’ve done stuff where I pivot up just names and use those, but I don’t know how to get 2 values pivoted and referenced.
Turns out I found a work around. Never did figure out the pivot, but I was able to use subqueries to extra the values separately by using one for PrimeRep = 1, one with PrimeRep = 0 and Min(name) and one with PrimeRep = 0 and Max(name). Then link back to get the Split% for each. There’s only 3 quotes with more than 2 reps, but one of those 3 is actually valid and this solved it.
1 Like