thank you!!
Date
Votes
4 comments
-
Jet Reports Historic Posts Official comment Hi Nacho,
I'm not positive about the actual correctness of your function, but this will be MUCH faster if you use Link= instead of NL(Filter). Link= is almost always faster, especially with a big table like the Value Entry. Try this:=-NL("Sum";"Item Ledger Entry";"Quantity;"Item Category Code";$F24;"Entry Type";"Sale";"Posting Date";$E$4;"Link=";"Value Entry";"item ledger entry no.";"=Entry No.";"salespers./Purch. code";H$8;"Connection=";$E$2;"Company=";$E$3)
Does this help?
Regards,
Hughes -
Jet Reports Historic Posts Hi everyone!
I am preparing a report of sales by item category code and salesperson. Salesperson code does not appear in the item ledger entry table but it appears at value entry table and in the table sales header shipment. The problem i think to use this table is that some shipments could be deleted but they appears in item ledger entry.
Which will be the correct nl function to get this?
I am using this one but I am not sure if there will be a better one:
=-NL("Sum";"Item Ledger Entry";"Quantity;"Item Category Code";$F24;"Entry Type";"Sale";"Posting Date";$E$4;"Entry No.";NL("filter";"Value Entry";"item ledger entry no.";"salespers./Purch. code";H$8;"Connection=";$E$2;"Company=";$E$3);";"Connection=";$E$2;"Company=";$E$3)
Thank you for all your help!
Rgds,
NACHO -
Jet Reports Historic Posts thank you Hughes! I tried that function first but i think i did it with some mistake because it didnt run ok.
Now it is running but it continues being very slow. could it be because of the Salesperson code is not only one?
What it would be better, to repeat the sentence with each salesperson and sum them or as I am doing, to do it just once for all the salesperson.
Example: The results are in a table in where H$8 could be just one salesperson code: 001 or some of them 002|003|007|SER
=-NL("Sum";"Item Ledger Entry";"Quantity;"Item Category Code";$F24;"Entry Type";"Sale";"Posting Date";$E$4;"Link=";"Value Entry";"item ledger entry no.";"=Entry No.";"salespers./Purch. code";H$8;"Connection=";$E$2;"Company=";$E$3)
In the case of being some salesperson what it is better? to write in h$8 002|003|007|SER or to write:
=-NL("Sum";"Item Ledger Entry";"Quantity;"Item Category Code";$F24;"Entry Type";"Sale";"Posting Date";$E$4;"Link=";"Value Entry";"item ledger entry no.";"=Entry No.";"salespers./Purch. code";"002";"Connection=";$E$2;"Company=";$E$3) -=-NL("Sum";"Item Ledger Entry";"Quantity;"Item Category Code";$F24;"Entry Type";"Sale";"Posting Date";$E$4;"Link=";"Value Entry";"item ledger entry no.";"=Entry No.";"salespers./Purch. code";"003";"Connection=";$E$2;"Company=";$E$3)+=-NL("Sum";"Item Ledger Entry";"Quantity;"Item Category Code";$F24;"Entry Type";"Sale";"Posting Date";$E$4;"Link=";"Value Entry";"item ledger entry no.";"=Entry No.";"salespers./Purch. code";H$8;"Connection=";"007";"Company=";$E$3) + =-NL("Sum";"Item Ledger Entry";"Quantity;"Item Category Code";$F24;"Entry Type";"Sale";"Posting Date";$E$4;"Link=";"Value Entry";"item ledger entry no.";"=Entry No.";"salespers./Purch. code";"SER";"Connection=";$E$2;"Company=";$E$3)
Thanks again for your comments.
Rgds, -
Jet Reports Historic Posts Hi Nacho,
It should be better to use the filter "002|003|007|SER" rather than creating multiple sum functions.
It is probably slow because you are summing a lot of entries out of the Item Ledger Entry table. Since you are using Link=, there is no chance for Jet to use a NAV sum key to perform the sum, so it has to spin through all the records, executing the link to the Value Entry table on each record, and summing them. If there are a lot of records that match your filters then this operation will be slow. There is no getting around that in this case, unless you were able to eliminate the Link= and create the appropriate sum key in NAV. It should still be faster to use the Link= than to use NL(Filter) but the overall operation is probably going to be slow anyway.
Regards,
Hughes
Please sign in to leave a comment.