is Calcsums the best method

allenman

Hello, I hope someone can help me with this.

I have a simple table and a tabular form based on it.
I have a "Quantity Required" field and a “Completed” field.
Complete will either be 0 or 1
What I what to achieve is a text box at the bottom of my form (not the tabulated section) to display the sum of "Quantity Required" where “Completed” = 0.

My primary key is “Line Number”
I have made this work but I believe in an inefficient way.
I have set up a SumIndexFeild against the primary key..
Then I have added a field to my table “SumOfQuantity”. I have its CalFormula set to
Sum(“This table”.”Required Quantity” WHERE (Completed=CONST(0)))

In the Developers Manual all the SumIndexFeild examples show pulling this data from another table. My information is on the one and same table.

Obviously, my table field “SumOfQuantity” is merely duplicated for each record.
I used a GET to find the first (any) record but found the variable returned 0 from the FlowField, but I was able to retrieve the correct info using the rec variable.

So, I have been looking through the help and came across Calcsums..
Can I duplicate the necessity of the field “SumOfQuantity” in code?


When I get this to work I would then like to do another sum. This time “Quantity Required” compared to another field “Sales Order”.
Would I need another secondary key and SumIndexFeild, ie
Sales Order (with its own SumIndexFeild of ) Quantity Required

Or will my existing SumIndexFeild suffice.

Sorry this has been such a long question, all help is much appreciated.

Regards,
SteveA

Stardust77

You could perhaps add a variable on the form itself (not related to a table field), which should add the values you need based on your criteria. You will need to add some code. Please see in the General Journals form how the Balance and Total Balance are calculated. It can give you an idea.

Tim81

You have a table like this:

Field Name         Data Type
Line No.           Integer
Quantity Required  Decimal
Completed	         Boolean

Add a key with the fields you want to filter, i.e.:

Completed  --> SumIndexFields: Quantity Required

Add a function to the table:
Function SumQuantity with a Return Value with the type decimal.
Add the following code (Test is a record var of the table above):

Test.RESET;
Test.SETCURRENTKEY(Completed);
Test.SETRANGE(Completed,FALSE);
Test.CALCSUMS("Quantity Required");
EXIT(Test."Quantity Required");

Make a new text box in the form and set SourceExpr to SumQuantity. That should do the thing you want and is efficient. Of course you can add the code above to the form instead of creating a function, but with a function you can re-use it.

allenman

Thanks to both of you.

I actually used the code suggestion from Tim81.
It worked perfectly, no unnecessary fields.

Much appreciated.

SteveA