I am getting better a BAQs but I still have a long way to go. I am trying to do a BAQ on all SO Lines not invoiced. e.g.
How I just get SO Lines not invoiced i.e. SO 123 Line 3 and SO 124 Line 1.
Thanks in advance
I am getting better a BAQs but I still have a long way to go. I am trying to do a BAQ on all SO Lines not invoiced. e.g.
How I just get SO Lines not invoiced i.e. SO 123 Line 3 and SO 124 Line 1.
Thanks in advance
Hi Matt,
You can set a sub query criteria for the invoice table. Something like the attached image.
Thanks for the response Nate. I was still poking away at the query when you posted as I though I was close. It seems to work by using a matching join between subqueries and setting the NOT flag. The numbers don’t tie up with my Excel mockup but they correct
Depending on what you are looking for you may need to bring in the OrderRel table. Invoices are generated from the Order/Line/Release. If you have a multiple release line you could have some that are already invoiced and still have an open line.
Nope, I was wrong. Still not getting the 2x rows showing not invoiced. invoiced is a flag on the pack dtl so I’m just using SO DTL & Pack DTL
Just a thought, I remember in a different product, that testing if the key values weren’t null sometimes didn’t work, it’s as if the key values would be filled in whether the optional join worked or not. If there’s another field, Invoice number to check, that might work better. You can bounce Order Detail off Invoice Detail and if the Invoice Number of Invoice Detail isn’t > 0, then consider it not invoiced. I tend to have better luck using fields that aren’t part of the join.
@Matt_Belshaw Releases get shipped and invoiced. Pack lines are not always order lines. So can we go back to what you are trying to solve? If you want shipments not invoiced that is readytoinvoice but not a status of invoiced. If you want releases not invoiced those would also be not shipped or open or closed with no shipped amounts.
Hi Greg,
We are looking to maintain a dollar amount of all open order lines. All open order lines should be summed together if they are not invoiced. Once invoiced Sales do not want to see them in the report.
Thanks