I was wondering if it was possible to create a BAQ with conditions to retrieve parent rows based on the child rows existence.
Example, we want to retrieve orders that have all item in stock.
The only solution we have come up with results in all items of an order populating rows but if an item is not in stock it does not show the row but does return the order.
The desired result should perform as such:
Order items if in stock return the order if one item is not in stock for a specified order then do not return the order.
I don’t 100% understand what your desire for the end result is.
You only want orders that every line has inventory in stock? And in the final grid you want just 1 line for the whole header? Or you want all of the detail lines?
You can make a subquery to check the order. Basically, check on each line if there is enough inventory for the line. If there is, than assign a 0 to that line. If there is not, assign a 1. Then bring that subquery up to another level, group by order number and sum the calculated fields. Everything with a sum of greater that 0 does NOT have all of the inventory available. Bring that level into your top level, and filter by sum = 0, and do an inner join to your order table (header or dtl, depending on what you are trying to show) Then only the orders that have adequate inventory will show on your list.
And if you post what you have so far, (screen shots of your BAQ parts) we can coach you on what you need to change.
I have the order number, the individual parts with a 1 on items that are missing. I’m lost as to what to do now to make order 51755 disappear because it has missing items.
So whatever you did to get the 1 or the 0 needs to moved to a subquery, so that you can group and sum by the order number.
the calculated field would look like this
sum(your_Calculated_Field)
Check the group by box on the fields to group by. Make sure you aren’t showing any field that you don’t want to group by.
This then gives you a “Table” than you can join to in your top level. Do an inner join to order number. Then you can filter the subquery (as a normal table filter) to only show rows where your calculated field = 0.
