lol, it’s fun when you pick up someone else’s “starter” package and build upon it. Yeah for consistency it should always be the Curr Comp but it is only running in one company so I get the same results. As for the LineDesc, that’s a holdover from the previous builder and he was in Accounting so I just left it in place. Not sure exactly what it excludes but hey, I’m not gonna fight inertia in this instance if the output is what is desired.
a HUGE THANKS! Nate. Adding the subquery criteria did the trick. I haven’t actually used that before (only table criteria as I was focused on table linkages for everything) so didn’t even think about it. You’ve made me just that much more dangerous with programming BAQ’s.
Dear Folks
Here is the simplest solution if you just want to show only the distinct values of a field.
Off course you can replace the NULL with actual column or other value you want as in my case its not required.
CASE When COUNT(Distinct(InvoiceRef)) > 1
Then
string_agg(InvcDtl.InvoiceRef,‘,’)
ELSE NULL
END
That won’t work if you have more than one set of duplicates. Edit: actually that won’t work at all, that’s basically just string agg, the count won’t do anything except not do the string agg if you only have one, in which can it would display the one field anyway.
I might be wrong here but I’ll check when I am back in the office tomorrow but off the top of my head I think I managed to get something working for this by doing something along the lines off:
Top level
I think there is a simpler way, I seem to temper doing this before:
Sub1
Add subquery 2 and add YOUR calculated STRING_AGG field.
Sub2
Put the field you want to aggregate in here and change the subquery setting to Distinct. Probs have use some filter expressions from top level for the table filtering.