I have a BAQ that I have made, very basic. I am pulling PO Number, Part Number, Est. Setup hrs, Est prod Hrs, Act. Setup Hrs, Act Prod Hrs and the job number. I am using the Invoice date to get the desired jobs I want. I have run into a minor setback with this BAQ. If for some reason there are multiple invoice activity lines it doubles up the data that I am getting, throwing the totals off. In the example that I uploaded you see three invoice activity lines, the original, the cancellation and the correction lines. I would like to pull only one line if this situation occurs. I would like to get the first invoice activity line if this situation occurs. the total of the invoice doesn’t factor into the report. I think I have to ad an expression in the invoice date but I am not very good with expressions.
If there were two separate invoices (and both legitimate, say you did two shipments and one invoice each), would you want to see two rows in your BAQ?
Filtering out the cancellation invoices isn’t too hard. A cancellation invoice must reference the invoice it cancels. So you might need to add the Invoice header table in again, and then link the original InvoiceNum from the first InvcHead table to the Referenced invoice on the added InvcHead table
EDIT
Might even be easier than adding more tables. There are fields in the InvcHead table: RefCancelled and RefCancelledBy.
When an invoice is cancelled, its RefCancelledBy value is set to the Cancellation invoice number.
When an invoice is a cancellation Invoice, its RefCancelled value is set to the invoice number it cancelled.
So add two criteria to your InvcHead table. RefCancelledBy = 0 AND RefCancelled = 0
That will remove any cancellation invioices, as well as the invoice the cancelled
