I have a BAQ that returns a list of the parts due to be shipped out for subcontract operations. Part of this BAQ should show the resource groups in the operation sequence. I can list all the resource groups for the operations, but I can’t seem to get them to stay sorted by operation sequence. To complicate things further, I have a similar BAQ that DOES sort the operations correctly.
Here is the BAQ where I want to return the list of resource groups, sorted by operation sequence. In the past when I have done this it was in a subquery, and I just turned on Top 100%, so that I could use sorting. DailyShipList-DownCountry.baq (103.3 KB)
And here is the BAQ where the operation sorting is done correctly. The setups look identical to me. What did I miss? DailyShipList-Orders.baq (85.5 KB)
You should be sorting it in the top level. The reason that it doesn’t let you sort in the sub queries is that it’s not required to keep that sorting, so that sorting is only going to be for selection, not display sorting. In the one that it’s working, you have a sort on the top level, in the one that’s not, you don’t.
In the BAQ where the sort is working, I expect the String_Agged op list to be sorted correctly. This sort is not done at the top level, it is done in a subquery. I just want to do the same thing in this BAQ.
Yes, those two subqueries are intended to return the same results. The list of operations in op sequence order, but instead of showing the operation number it shows the related resource group (or capability if no resource group). I saw that the sort on one had job and the other didn’t I have tried several iterations of putting various fields in the sort, but none seem to be working with this downcountry BAQ. The other BAQ shows my op list perfectly.
I think you’re probably going to be better off doing this as a single sub select rather than trying to join it as a table, for the reason that you’re finding. Do you know how to do that?
No, what is a single sub select? Why is it that my DailyShipList BAQ sorts the operations properly? I know I have done this before and had it work. Did I just get lucky in the past?
So for the single sub select, you just return a single column row, then add the whole sub query as a calculated field. . I’m looking for a post that explains it pretty well.
I am trying to figure this out, but just not getting it. Once I setup the inner subquery (OpList) with Top 1 row, and I set the subquery criteria, how do I pull the (OpList) inner subquery into my top level? There is nothing to match on, since the inner sub only returns the op list. This is so confusing as my other BAQ consistently returns the operation in exactly the right order. There must be something else I am missing that is making my DailyShipList-Orders BAQ work. I don’t buy that I just got lucky.
I think I got it. I still want to know why my other BAQ works. But I got this one working using this single sub select. What a neat trick!
One problem was that I was trying to include my OpList subquery in the top level as a table. Instead of adding the subquery at this point, I just opened the calculated fields and added the expression: {OPList} to a new calculated field. Without any sorting at the subquery or top level it seems to work! DailyShipList-DownCountry.baq (92.0 KB)
You can easily remove the UD fields from the BAQ without majorly impacting the functionality. The only references to UD fields I can see are at the top level JobHead.Date08, and maybe JobHead.Checkoff2. You can delete those without changing the functionality.
