I've tried to retreive per product, the tax amount (not VAT) from sale invoice line (and Sale Cr. memo line), tax is associated to a product but not on the same line in Sale invoice line table, but it is a line after. I can retreive tax with the folowing formula, but it is too long (1 minute per tax per product). this formula has to be run for each product (more than 100 product), and the total calculation time is over several hours.
HOW TO IMPROVE RESPONSE TIME IN THIS CASE (another way to retreive this tax amount, indexes, sql formula, …) ?
=NL("sum";"Sales invoice Line";"Amount";"Posting Date";$D$6;"Item Charge Type";"TAX";"Gen. Prod. Posting Group";"TAX-01";"Link=";"Sales Invoice Line";"Document No.";"=Document No.";"Line No.";"=Attached to Line No.";"No.";E10)
in $D$6 the date period (01/01/2011..31/01/2011)
in E10 the product code
Nav database : SQl 2005
Nav client : Nav 5 SP1
How to retreive tax from sale invoice line per product