I'm trying to create a Jet Report which will produce a list of invoices which have been paid.
The slight issue is that I need to filter down through different tables before my NL(Rows) can start. Let me explain.
I need to create a list of PO numbers from the Purchase Line table where the GL code is XXX|YYY. That's easy enough. So I've got this in cell D6:
=@NL("Filter","Purchase Line","Document No.","Filters=",$C$4:$D$5)
Now I need a list of posted invoices which match their own criteria. But once I've matched those criteria (defined in C9:D11, which includes a date filter for 'last month'), I need to restrict the returned list to the PO numbers which were returned in the first function. So this is D12:
=@NL("Filter","Purch. Inv. Header","Purchase Order No.","Filters=",$C$9:$D$11,"Purchase Order No.",$D$6)
Finally, I need to start my NL(Rows) from the Purchase Order Matching table:
=@NL("Rows","Purchase Order Matching",,"Purchase Doc. No.",$D$12)
And this is where I run into difficulty. One of the POs which has filtered through has multiple lines. And the payment identified on the Posted Invoice table was from line 5 of the PO. But the NL(Rows) is returning a payment from 2018 (which is correct for this particular PO). So the Purchase Order Matching table needs to have an extra filter where the PO Matching table's field "Purchase Doc Line No." = Purchase Line table's "Line No."
And this is where I get stuck. I wonder if I need to be able to draw a filter from Purchase Line to show {"Document No.","Line No."} and whilst this NL(Filter) doesn't give me an error, I don't know how to extract both of these fields from my NL(Rows) in the PO Matching table.
Can this be done? I think it's an interesting challenge but just can't find a way.
Thanks in advance