Looking to build a BAQ that shows last time a part was invoiced by customer. I got the last invoice to customer with a subquery (max invoice date). How do I get the BAQ to get that data by part? Do I need another subquery for the parts?
Make your sub-query with columns
- Company (Grouped)
- CustID (Grouped)
- PartNum (Grouped)
- Calc filed of
MAX(InvoiceDate)(not grouped)
This query will give you one row for each CustID and partnum combo, with the date of the most recent invoice the part was on.
Now in your Top Level query, you can add that sub-query like it was a table. Link the subquery’s displayed fields to your top level query’s tables, to limit the subqueries records to only the ones your top level needs.
I was using the InvcHead and InvcDtl for the tables in the subquery. There is only CustNum not CustID in those tables. Are those the correct tables to be using for the subquery?
You’re correct, it should be CustNum.
Thanks @ckrusen, that worked. My next question is if I want to add the part description for the listed parts to the query, where would I add the part table so I can add that field?
The InvcDtl should have a LineDesc field that you can add (Grouped).
Like @Doug.C said, the InvcDtl will have the LineDesc.
But an issue you might run into is that the LineDesc may be different on different invoices.
So if you had invoices with the same PartNum, but non-identical LineDesc’s, these will show up as two different rows in the output - and would appear as two different “last dates”.
Make the BAQ and Report sort by Customer, then PartNum, and look for rows of duplicated Customer and Partnum
edit
here’s an example:
One of the invoice to Custnum 1959 had a different line description, so that Partnum shows up again for the customer.
Yes, if this is the case for you, then I would link the Part table to the InvcDtl table and get the current description from there.
Assuming you don’t want OTF parts to be included in the BAQ 
What is an OTF part? I did have different descriptions on invoices for the same part so I did join the part table to the InvcDtl table and it gave me the correct part description.
“On the Fly” Its the term for a PartNum that doesn’t exist in the part table. This allows you to use it as kind of a temporary identifier to be used by that quote or orders, jobs for that order, shipments for that order’s jobs, and invoices for that shipment.
edit
And other than the duplicates (for different LineDesc), did you end up with the same number of records?
Make the join between InvcDtl and Part be “All InvcDtl rows …”, and then see if the PartDesc is ever blank. Those would be OTF parts.
The amount of records were the same. I did the join for the InvDtl and Part to be "All InvcDtl rows and there were no blanks. We don’t do the OTF parts.
1 Like