I have been tasked with modifying another person’s BAQ to fit specific criteria (Screenshot at the end of the post).
THE BAQ is supposed to return all records that are in a specific product group. This happens by the Table criteria filter on the table OrderDtl.
They then want to filter out any results that have a RMA assigned to them (UD Field) and have the RMAHead.OpenRMA field equal False.
The problem is that the table that holds this RMAHead.OpenRMA field is joined to the other tables by a left outer join. That seems to keep the filter fom removing the records that fulfill this criteria.
If I turn this join into an inner join, it ONLY shows the records that are RMAHead.OpenRMA equals True.
If I do that, it filters out all the records that do not have an RMA number assigned to them yet. I also need to show the records that have not had an RMA assigned to them.
I am stuck on how to return a BAQ that will show both the records not assigned to an RMA and the records with an RMA that are still open.
I hope this makes sense. If there are specific questions, please let me know and I will answer them all.
First make a copy of the BAQ in question so you can modify it without altering the original. Use Actions > Copy BAQ. Then take the RMA tables out of the top level query. Make the top level an inner subquery. on your new top level query, add in your subquery, and then link your RMA tables. That should give you a bit more control over how the joins and filters act. Using this, you may be able to use a subquery filter instead of a table criteria.
Using subquery criteria is supposed to filter it after the fact, so you should be able to put it there instead of on the table itself. There some caveats that make it not work (with calculated fields and aggregates and whatnot), and in that case, you’ll have to make it a subquery and filter on the next level up, but I would try this first and see if it works.
After some fumbling, I came up with the correct filtering to get both the Unassigned to RMA records and the RMA records that were still open on the same screen!
I appreciate the help both of you have given me on this.
