Reporting Help

Hey all,

I have my DataItems setup and linked together as follows:

  • Customer
    • Sales Invoice Header
      • Sales Invoice Line

Now I’m trying to count the number of Sales Invoice Headers for each Customer, based off a field in Sales Invoice Line.

I currently have a flowfield setup in the Customer table to count the number of Invoice Headers, but I’m confused on how to filter based on a field in Invoice Line. I’ve tried creating a boolean within Header that changed based on Line, but couldn’t get it to work properly.

If anyone could point me to an example of this being done or help me out I’d appreciate it! Thanks!

Hi, what are you trying to achieve with this?

I’m trying to create a report that shows the number of Sales Invoice Headers a Customer has, filtered by a field on the Sales Invoice Line.

In Customer Table there are already flowfield available which shows No. of Pstd. Invoices,Credit Memo , Sales Order.

You can show those field in the Customer Card if report is not that important.

You can only base a FlowField on one table, so if you need info from the Sales Invoice Line (SIL) your FlowField should be build on this. As the SIL contains a field “Sell-to Customer No.” you can filter out all SILs that belong to one customer. But then you have the problem that Sales Invoice Headers (SIH) can have multiple SILs. So if you would count the number of SILs through your FlowField it will at least be equal to the number of SIHs but generallty be above that.

So I guess to best way to do this write your own code (function) that does the calculations.

hi

create one view in sql server as per your requirement and view result will be

custmer no, number of document

create one linked table from this view and one flowfield in customer table from then new table

update the look trigger in customer card for showing the filtered sales invoice documents [;)]

This is what I first tried but, like you said, my numbers were too high because it was counting the multiple SILs for each SIH.

Could you point me in the right direction to get started with this? I can’t quite figure out how to only count unique SIHs. Thanks!

Something like (just writing it hear, so haven’t compiled it):

NumberOfHeaders := 0;
SalesInvoiceHeader.SETRANGE(“Sell-to Customer No.”,number_of_customer);
IF SalesInvoiceHeader.FIND(’-’) THEN
REPEAT
SalesInvoiceLine.SETRANGE(,specific_field_value);
IF NOT SalesInvoiceLine.ISEMPTY THEN
NumberOfHeaders := NumberOfHeaders + 1;
UNTIL SalesInvoiceHeader.NEXT = 0;

Depending on the size of the expect number of headers and the version of NAV you are running on the SalesInvoiceHeader.FIND(’-’) could be SalesInvoiceHeader.FINDSET.

Thanks Luc, that’s a perfect starting point for me!

You’re welcome, Dan.