I want to design a report to have sales data with the following fields.
Item No. Description Quantity Rate Excise Sales TAX Total
I have designed a report using Sales Invoice Header and Line table and its working also but,
in case, if we post any Sales return then also the report is showing the Sales return data.
now i want to modify this report in such a way, so that the report contains only those quantity for which there is no sales return, i.e Quantity should be Quantity- Sales Return Quantity
i am trying the following code but this is showing some negative values for all the fields, is is the correct way or i should try some other technique
code i have written is:
SalesCreditMemoHeader.RESET;
SalesCreditMemoHeader.SETRANGE(SalesCreditMemoHeader.“Applies-to Doc. No.”,“Document No.”);
IF SalesCreditMemoHeader.FINDFIRST THEN
SalesCreditMemoLine.RESET;
SalesCreditMemoLine.SETRANGE(SalesCreditMemoLine.“Document No.”,SalesCreditMemoHeader.“No.”);
IF SalesCreditMemoLine.FINDFIRST THEN
SalesCreditMemoLine.SETRANGE(SalesCreditMemoLine.“No.”,“No.”);
IF SalesCreditMemoLine.Quantity=Quantity THEN
CurrReport.SKIP;
IF SalesCreditMemoLine.FINDFIRST THEN
BEGIN
IF Quantity <> SalesCreditMemoLine.Quantity THEN
FinalQty:=Quantity-SalesCreditMemoLine.Quantity;
FinalAmount:=Amount-SalesCreditMemoLine.Amount ;
END
ELSE
BEGIN
FinalQty:=Quantity;
FinalAmount:=Amount;
END;
Here i am trying to show the quantity and rate, if for any item there is sales return then the Quantity=Quantity-Sales Return Quantity, otherwise
Quantity=Quantity
Usually you want to do this type of reporting from the ledger entries, not documents. In this case, I would use Customer Ledger Entry.
Your way, the code is a bit off. Set all of your filters on the credit memo line before you find the record. Also, what happens if you have a credit memo with multiple lines of the same item number? In this case there is a chance that your code will not retrieve the correct record.
Thanx for the reply, i forgot to mention that, in our case Sales documents contains a single item, either it is sales invoice or sales Return. there cannot be more than one line.
I would put it even stronger as Matt suggested - NEVER report from Document tables - only corresponding Ledger entries! Doc tables DOES NOT contain full and correct info, as additional charges may apply, plus costing info is grabbed from Item card at the moment of creating the doc, and never updated afterwards.
Reporting from Doc tables is considered to be bad design from good programming practice view, you may use them for reprinting posted docs only.
Modris is correct. I should have said it more strongly to begin with. It’s really important to do it this way from the start.
Right now, your company might only have one line per order. And they might only use one costing method. They might not even know what a Charge Item is or have plans to use it. But what about tomorrow? What about when the process changes? NAV is made to bend, but it can be modified in such a way that new process changes do not require new code changes.
Haven’t ever seen Indian localisation, but I know you have a terrible tax system in India…
So, I do not know the structure of these tax-relevant tables, but I assume you should be able to link these to Ledger table thru TransactionNo, EntryNo etc etc fields. It’s a guess, of course, but you can do some investigation in this direction.
Some of the newer versions have tables that link the two, but a lot of NAV still relies on the old stand by (like Navigate). Linking is done via Document No., Posting Date combination.
Ideally there is some linking table, like table 5823, G/L Item Ledger Relation