Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have the following situation where I've got 2 tables:
1. GL entries
Fields:
Document number
Amount
2. Value entries
Fields:
Document number
Cost amount
My issue is that the cost amount in the value entries can consist out of multiple lines. Thus I need to sum the cost amount for each document number.
I can then work out the GP% for each invoice posted.
how can i group the cost of each document number in the value entries table, and only the value entries table?
Yes, you can do that. Same way..
can one have a where statement in a resident load?
Hi,
Yes you can write where condition in Resident Load.
what is wrong with this script?
it seems not to be working...
//GENERAL LEDGER ENTRIES
TempGeneralLedgerEntries:
Load
"G_L Account No_",
Year("Posting Date") as Year,
Month("Posting Date") as Month,
Day("Posting Date") as Day,
"Global Dimension 1 Code" as Department,
"Global Dimension 2 Code" as BU,
"Document No_" as "Document No",
Amount as "Sales Amount";
SQL SELECT
"G_L Account No_",
"Posting Date",
"Global Dimension 1 Code",
"Global Dimension 2 Code",
"Document No_",
Amount
FROM "DynamicsNAV90_Prod".dbo."Ambassador Foods (Pty) Ltd$G_L Entry"
Where "G_L Account No_" in (1110,1120,1130,1140,1150);
//*****************sum of sales amounts****************************
GeneralLedgerEntries:
Load
"G_L Account No_",
Year,
Month,
Day,
Department,
BU,
"Document No",
sum("Sales Amount")
Resident TempGeneralLedgerEntries
Group By "Document No";
DROP Table TempGeneralLedgerEntries;
in Group By you are using Only "Document No" and in Load Script you are using other fields also .
either remove those fields from load script or else add only those fields which required in Group by,
Can I pull the G_L Entry table again at a later stage if I want those fields?
yupp, you can pull from the database and make a different table with the same syntax as above. Please note that do not mention the field name as same as it will create sunthetic key or loops.