Hello! I’m trying to find out how this CREATETOTALS function work, but i had no success so far. I read the Aplication Developers Guide but i dodn’t understand which fields does it read to make totals… I got a homework to find out how a report that we use works. It uses CREATETOTALS function. Some of the Code looks like this: --on predataitem (Item ledger Entry) SETRANGE(“Item No.”,Item.“No.”); SETFILTER(“Variant Code”,Item.GETFILTER(“Variant Filter”)); SETFILTER(“Location Code”,Item.GETFILTER(“Location Filter”)); SETFILTER(“Global Dimension 1 Code”,Item.GETFILTER(“Global Dimension 1 Filter”)); SETFILTER(“Global Dimension 2 Code”,Item.GETFILTER(“Global Dimension 2 Filter”)); IF EndDate <> 0D THEN SETRANGE(“Posting Date”,0D,EndDate); CurrReport.CREATETOTALS(QtyOnHand,RcdIncreases,ShipDecreases,Quantity); CurrReport.CREATETOTALS(InvoicedQty, InvIncreases, InvDecreases, “Invoiced Quantity”); CurrReport.CREATETOTALS( ValueOfQtyOnHand,ValueOfRcdIncreases,CostOfShipDecreases,CostPostedToGL,ExpCostPostedToGL); CurrReport.CREATETOTALS( ValueOfInvoicedQty,ValueOfInvIncreases,CostOfInvDecreases, InvoicedValueEntry.“Adjusted Cost”,InvCostPostedToGL); —on post data item QtyOnHand := 0; RcdIncreases := 0; ShipDecreases := 0; IF “Posting Date” < StartDate THEN BEGIN QtyOnHand := Quantity; END ELSE IF (“Entry Type” IN [“Entry Type”::Purchase,“Entry Type”::“Positive Adjmt.”,“Entry Type”::Output]) THEN BEGIN RcdIncreases := Quantity; END ELSE BEGIN ShipDecreases := -Quantity; END ------- in sections these variables are printed:InvoicedQty,ValueOfInvoicedQty,InvIncrease, ValueOfInvIncrease, InvDecrease, CostOfInvDecrease, InvCostPostedToGL If anyone can help me unrattle this code i’d realy appriciate it. I just can’t understand where does it collect the data from. Thanks in advance lp Prosen
i don’t know if i’m right but i think CREATETOTALS sums all entries of a specified field which are SHOWN on the report within a dataitem. The special thing about CREATETOTALS is that you have the total in onPost available without assigning anything to this variable. Example: OnPre: CREATETOTALS(MyVar); OnAfter: [ lets say we have 3 records with values like 5,7,8 – if we make a message box in OnAfterGetRecord we see this 5 7 8 ] MESSAGE(’%1’,MyVar); On Post: MESSAGE(’%1’,MyVar); [ Here you will get 20 as output of MESSAGE BUT ONLY if the section was showed in report. ] As i said i’m not sure exactly i tell you this out of my mind ;-). (((Maybe this logic is also working only on sections???)))
—on post data item QtyOnHand := 0; RcdIncreases := 0; ShipDecreases := 0; IF “Posting Date” < StartDate THEN BEGIN QtyOnHand := Quantity; END ELSE IF (“Entry Type” IN [“Entry Type”::Purchase,“Entry Type”::“Positive Adjmt.”,“Entry Type”::Output]) THEN BEGIN RcdIncreases := Quantity; END ELSE BEGIN ShipDecreases := -Quantity; END -
I would move the code from OnPostDataItem to OnAfterGetRecord.
Thank you guys i’m beginnig to understand this thing however i’m still open for suggestions…
Damn i forgot to ask one more thing. How does CREATOTOTALS know from wich field in the table it has to make totals of?
Hi prosen extract from the online help
CREATETOTALS (Report) Use this function to maintain totals for a variable in the same way as totals are maintained for fields by using the TotalFields property. CREATETOTALS(Var1 [, Var2] ,…) Var1, Var2, … Data type: decimal Comments Just like the TotalFields property, CREATETOTALS causes group and grand totals to be maintained. The totals can be printed by placing controls that have the variable or variables that are the arguments of CREATETOTALS as their source expressions in the appropriate sections: the group totals are printed in GroupFooter sections, and the grand totals are printed in Footer sections. Example To have the system maintain totals for the two variables Amount and Quantity, use CREATETOTALS like this: CurrReport.CREATEOTALS(Amount, Quantity);
It doesn’t have to be variables that are totalled it can be Actual fields from the Table or Tables lower down the Dataitem tree.
Yes now i know the concept of CREATETOTALS function but i still don’t know how to check wich fields in the table are totaled if you use variables instead of actual field names.
Hi Prosen Your last question, Is it which fields are being Totalled?, or is it on Which fields are the Totals being grouped? If it is the grouping then you set the ‘GroupTotalsFields’ property on the relevent dataitem. (The fields you set here must be included in the Sort Key for the dataitem.
Hi, You can also use the CREATETOTALS to any variables even if it is not a Table field, specially when you have your own computations and want to have totals for it. Regarding your original question, I think this is in the Inventory Valuation Report. The meaning of the variables that you mentioned can be seen on the Value Entry data item of the report. Regards,
Yes i figured that out from the Developer Guide, but this report that i’m researching it doesn’t have any fields set in the GroupTotalFields property of the report. All i know is that CurrReport.CREATETOTALS(x,y,z) is beeing used to total some fields. In the sections part, there are all these variables (x,y,z) included in the GroupFooter text boxes, so that createtotals works. I just can’t figure out which fields from the “Item ledger Entry” table are beeing totaled with the CurrReport.CREATETOTALS(x,y,z) or how does CREATETOTALS know which field to total.
The last post was ment for Steve Gault’s last post…
Hi Are there any other DataItems on the report, If so check the code on those dataitems as some of the Variables could be assigned there. The amounts are held in detail on the “Value Entry” table, and from the code you posted earlier this table looks like it has been declared as a variable ‘InvoicedValueEntry’. If you want you can e-mail me a text file of the report so i can look at it StephenGault512@Hotmail.Com
Originally posted by lordslayer
I just can’t figure out which fields from the “Item ledger Entry” table are beeing totaled with the CurrReport.CREATETOTALS(x,y,z) or how does CREATETOTALS know which field to total.
By the look of it I’d say Quantity and “Invoiced Quantity” are from “Item Ledger Entry” (since the lines are in a “Item Ledger Entry” trigger, they don’t need to be qualified); InvoicedValueEntry.“Adjusted Cost” is from the data item named InvoicedValueEntry, the remaining variables should be Global Variables. Which report is it? Anna
Steve, the first row of dataitems is “Item” and it has “Inventory Posting Group” assigned in the GroupTotalFields property. The structure of DataItems is like this: Item Item Ledger Entry Value Entry Value Entry
The report is “Inventory Valuation”
Hi Prosen The Variables you were querying are being assigned in the DataItem “Value Entry” (InvoicedValueEntry);
OnAfterGetRecord() ValueOfQtyOnHand := 0; ValueOfInvoicedQty := 0; InvoicedQty := 0; ValueOfRcdIncreases := 0; ValueOfInvIncreases := 0; InvIncreases := 0; CostOfShipDecreases := 0; CostOfInvDecreases := 0; InvDecreases := 0; IF "Posting Date" < StartDate THEN BEGIN ValueOfQtyOnHand := "Adjusted Cost"; ValueOfInvoicedQty := "Adjusted Cost"; InvoicedQty := "Invoiced Quantity"; END ELSE IF ("Item Ledger Entry Type" IN ["Item Ledger Entry Type"::Purchase,"Item Ledger Entry Type"::"Positive Adjmt.", "Item Ledger Entry Type"::Output]) THEN BEGIN ValueOfRcdIncreases := "Adjusted Cost"; ValueOfInvIncreases := "Adjusted Cost"; InvIncreases := "Invoiced Quantity"; END ELSE BEGIN CostOfShipDecreases := -"Adjusted Cost"; CostOfInvDecreases := -"Adjusted Cost"; InvDecreases := -"Invoiced Quantity"; END; CostPostedToGL := "Cost Posted to G/L"; InvCostPostedToGL := "Cost Posted to G/L"; I hope this should clarify the problem.
I think i finally figured some of this report out. Thanks all of you for all the help!
One additional FYI. Once you have defined CREATETOTALS(MyVar,MyField) on a number of variables or fields, Navision Creates a new total variable for this element, and you can indent, so for example if you have
Table1 table2 then you can place code to add totalas and subtotals. One Gotcha here is what triggers the Totaling function. I like to look at it as something like CALCFIELDS, where if you put the folw field on a report it automatically caclulates, but if you don’t then you have to force a CALCFIELDS. Well with CREATETOTALS, if you invoke the field by putting it on the Report, then it is automatically included in the TOTALING. Also(OR) if you assign a value to that Field or Variable it is also summed. A small trick to force this, and make certain that the value is included in the sum, is to assign it to it self in the section. eg;
**Table1.OnAfterGetRecord** //My code is here ... MyVar := MyVar; MyField := MyField; **Table1.OnPostDataItem** The only way to access the variables is after running through the records, so there is no way to manipulate or view the current totals. (Wouldn’t it be nice to have a command like CurrentValue := Table1.TOTALS.MyVar;). I hope this provides more help than confusion.