Hi all,
I tried searching for an answer but I don’t even know what terms would point me there. I am making a BAQ for a dashboard to help shipping enter tracking numbers. It has all packing slips that do not have a value in the tracking number field (Packing Slips needed a tracking number and to be checked off as shipped to disappear from the dashboard). That was working great until they figured out how to link BOLs to the PSs. Now they want lines to disappear off the dashboard if they have been marked shipped, and have a PRO number filled in on the BOL. So on the dashboard would be packing slips not linked to a BOL with no tracking number and/or not marked shipped, and PS linked to a BOL with no PRO number and/or not marked shipped.
If you have the Packing Slip and BOL linked correctly in the BAQ already, you can put in Subquery Criteria which will filter based on the full dataset returned by that BAQ. I think this would be the best option given how you need to cover two cases.
Your criteria would be (not using actual table and field names):
PackingSlip.Shipped = false OR (BOL.BOLNum is null AND PackingSlip.TrackingNum = ‘’) OR (BOL.BOLNum is not null AND BOL.PRONum = ‘’)
Or if you don’t want to mess with table relationships, table criteria, and sub-query criteria, you could make a calculated field in the BAQ. It would be based on the logic you described. Then in the Dashboard, add a filter on that newly created field.
If you haven’t already added the BOL table in your BAQ, make sure it is joined with a LEFT OUTER join. That will make sure you get Packers even when there is no related BOL.