How to set up a calculated field

ALopez27

Hello,

I'm trying to add a field into the Sales Line that will be a calculated field of Gross Weight, it will be the current outstanding ship qty * the Item Gross Weight, how can I do this?

Once I put it on a the Sales Line, I will then create a calc field in the Sales Order Header that will sum up this field for all Sales Lines.

Is this possible? If so, please advice how. Thanks.

Savatage

First off if you hit F9 & check the statistics your net & gross weight should be there.

But I understand we added a "total net weight" field to our header too. (You'll just adjust for Gross instead)

Field "Total Net Weight" Type Decimal
Type Flowfield with CalcFormula of Sum("Sales Line"."Line Net Weight" WHERE (Document Type=FIELD(Document Type),Document No.=FIELD(No.)))

So we're summing the new field "Line Net Weight", lets add it to the sales line table.
Field "Line Net Weight" Type Decimal

OnValidate of "Line Net Weight"
"Line Net Weight" := "Net Weight" * "Qty. to ship";

OnValidate of "Qty. to ship" I have (at the end)
VALIDATE("Line Net Weight");

I've also added it ONvalidate - Unit Of Measure Code
under
"gross weight".....
"net weight".....
VALIDATE("Line Net Weight");

incase the uom changes
*PS obviously you'll add the sales line field first else you'll have nothing to sun on the header field

ALopez27

Thanks Harry....

I now understand how that would work, however, is it possible to just have a 'calculated' field in the Sales Line that will always just be Qty * Gross Weight (both of these existing field on the Sales Line table) ?

In other words, not enter any code on the Sales Line table and just add a 'calc' field that will always have this calculated value in real time?

THanks.

ALopez

jversusj

you 'could' add a variable to the order subform and display that variable in a new column in the subform. OnAfterGetRecord trigger of the subform you would add code that defines the variable as (outstanding qty * weight)... This would only show the values in the lines and you could not leverage it in the header as a flowfield (since it isn't a real field). you also could not filter on the value - it would just be seen by the user. similarly, you could add a variable to the header to roll through the lines and build a value for display as well.

not pretty, but it would display what you are after...

otherwise, you would be adding code to the tables as Harry said - adding code to OnValidate triggers as necessary (depending on what approach you take) or creating a function that calculates the values upon a certain action (like a button click). there are probably other ways too...

Savatage

How you would you add a field that calculates without adding code?
You're going to have to add "this field" times "that field" equals a "new field" somewhere

or F9 for stats

Savatage

If you want to see it on the sales line without code.
Add a field to the sales Line called "Total Line Weight" or whatever type decimal.
go to the sales line form 46 and drop in the new field.
Change the sourceexp of the field to
"Net Weight" * Quantity * "Qty. per Unit of Measure"
(in your test you can use Gross Weight if you want)

but when seen in the sales line table - the value will apear as zero since it's only calcing on the form.
And that will not allow you to flowfield it into a sum on the header.

David_Singleton

jversusj wrote:

you 'could' add a variable to the order subform and display that variable in a new column in the subform. OnAfterGetRecord trigger of the subform you would add code that defines the variable as (outstanding qty * weight)... This would only show the values in the lines and you could not leverage it in the header as a flowfield (since it isn't a real field). you also could not filter on the value - it would just be seen by the user. similarly, you could add a variable to the header to roll through the lines and build a value for display as well.

not pretty, but it would display what you are after...

otherwise, you would be adding code to the tables as Harry said - adding code to OnValidate triggers as necessary (depending on what approach you take) or creating a function that calculates the values upon a certain action (like a button click). there are probably other ways too...

Don't do this on the form. Put the function in the table. That way you just place the source to the function in a control on the form and you don't have to much about with all that onaftergetrecord nonsense.

jversusj

David Singleton wrote:

jversusj wrote:

you 'could' add a variable to the order subform and display that variable in a new column in the subform. OnAfterGetRecord trigger of the subform you would add code that defines the variable as (outstanding qty * weight)... This would only show the values in the lines and you could not leverage it in the header as a flowfield (since it isn't a real field). you also could not filter on the value - it would just be seen by the user. similarly, you could add a variable to the header to roll through the lines and build a value for display as well.

not pretty, but it would display what you are after...

otherwise, you would be adding code to the tables as Harry said - adding code to OnValidate triggers as necessary (depending on what approach you take) or creating a function that calculates the values upon a certain action (like a button click). there are probably other ways too...

Don't do this on the form. Put the function in the table. That way you just place the source to the function in a control on the form and you don't have to much about with all that onaftergetrecord nonsense.

right - i was just giving him an option that did not involve him adding code to the table, which he seems to be against (or unable due to license?).

David_Singleton

jversusj wrote:

right - i was just giving him an option that did not involve him adding code to the table, which he seems to be against (or unable due to license?).

OK gotcha, though of course if he can't access code in tables, same applies to forms. In which case put the code in a report instead.

jversusj

David Singleton wrote:

jversusj wrote:

right - i was just giving him an option that did not involve him adding code to the table, which he seems to be against (or unable due to license?).

OK gotcha, though of course if he can't access code in tables, same applies to forms. In which case put the code in a report instead.

ah yes! i forget what the non dev license gets you, now that i have been spoiled with the dev license.

in any case, as Harry said - you can't get something for nothing here. some code will be required.

ALopez27

Thanks to all for your help....

I guess it is kinda of a limitation that we can't simply have a "calculated field" in a table similar to what we can do in the form, but that will allow us to treat it as a real field that we can filter on, sum up, etc....

I will proceed with adding code in the table so that it will be a real field, then I can sum up to the Sales Header, etc....

Thanks again for your prompt response.

ALopez