How should I calculate the value of a field using the values of some other fields in the same table

Business Central/NAV Developers Forum
1

Hi,

I wanted to add another field in the Posted Purchase Invoices form. I added the field in the source table of the form.

What I want is that this field can show the value calculated from two other fields in the same table.(the table that is the source table of the form). Should I enter code in the OnValidate Trigger of the field that I added in the table? Is the VALIDATE function that I should use to perform this calculation? Can anyone help me.

Thanx a lot

2

Hi Magdalena,

You don’t need to add a field for this, you can add a field to the form and in the SourceExpr add the two fields

e.g.

Value1+Value2

3

Thanx a lot Dave, I’ll try it right now

4

I tried and it had the problem of division by 0.

I have to perform this:

If the value of “Currency Factor” is 0 I want the “amount incl VAT in LCY” to be equal to “Amount including VAT”.

If it is not equal to 0 “amount incl VAT in LCY” = “Amount including VAT”/“Currency Factor”.

Can this be done in the sourceExp of the field in the form or I should add a field in the table and perform the calculation of values in the OnValidate trigger of the field that I should add in the source table of the form using the VALIDATE function

Thanx in advance

5

You have to decide on one thing up front: do you need to filter on this field on or not.

If you do not then you can just add a global variable to the form, calculate the value in the OnAfterGetRecord trigger, and display it in a text box.

If you do need to filter, though, then you need to add a field to the table. I usually add it to both the posted and unposted tables (Purchase Header / Purchase Invoice Header) and do the calculations up front in the OnValidate fields of the unposted table. Then TRANSFERFIELDS will handle moving it to the posted table automatically.

6

Thanks a lot,

Should I write the code using the VALIDATE function in the OnValidate trigger of the field that I added or it can be done using the If else in the OnValidate trigger of this field( I am a beginner in programming in Microsoft Dynamic nav 5.0)

Thanks again

7

In that case I recommend you read up on the validate trigger. It is called when a value is changed in the text box on the form, or through code using the VALIDATE(Field, Value) structure. You’re trying to create a calculated field, one that will not be entered by the user. So you don’t want to put it in the OnValidate trigger of your new field.

Think about it this way: When should the value in your calculated field change? When one of the inputs to your calculation changes. So in the OnValidate trigger of those fields you will want to calculate your value. Or better yet, call a function that calculates your value. That way, if the formula ever changes, you only have to change it in one place.