I’m trying to modify a BAQ condition to add some constraints. In SQL the additional constraints would look something like this:
<Original condition> OR (ShipHead.ReadyToInvoice = 0 AND ShipDtl.PackNum > 0 AND ShipDtl.RequestDate = '1/1/2021')
But when I go to modify the criteria, it says, “Criteria applied on ShipHead (Erp.ShipHead) table data retrieval” and I cannot access the ShipDtl table to get the terms I need there. These two tables are directly joined so I could access the individual criteria from each separate table but this doesn’t seem like it will work because I need a more complicate OR/AND relationship between the terms.
I feel like I’m missing something simple, how do I create a criteria from fields from both tables in the join?
Not really, but it depends on the joins. Using CTE, pivots and the like can be a pain. Usually, you can find a way by putting the subqueries in parenthesis in the Subquery List for sub-subqueries.
Or as above, create a Innersubquery for 2 tables, set ShipHead.ReadyToInvoice = 0 - Table01 . Create new TopLevel Query and pull Table01 to as a single table and set the condition for it (ShipDtl.xx = ). But IMPORTANT: I cannot find the ShipDtl.RequestDate on ShipDtl. I only know it on OrderRelease.RequestDate.
If you get this into Dashboard, only set criteria ShipHead.ReadyToInvoice = 0; and on Dashboard, create a tracker with ShipDtl.RequestDate = promt. No need ShipDtl.PackNum > 0 because PackNum is always > 0.
