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


I want to add a column in the Posted Purchase Invoices form (“Amount Incl. VAT in LCY”). The value in this column should be calculated using the values from two other fields in the source table of the form that I want to modify(“Currency Factor”,“Amount Including VAT”). If the “Currency Factor” = 0 then “Amount Incl. VAT in LCY”:= “Amount including VAT”, else “Amount Incl. VAT in LCY”:= “Amount Including VAT”/“Currency Factor”.

I added a field in the source table Purch. Inv. Header and a column in the Posted Purchase Invoices (“Amount Incl. VAT in LCY”) with a SourceExp the column that I added in the Purch. Inv. Header table. I created a function in the Purch. Inv. Header:

CalcAmountInclVATInLCY(“Currency Factor” : Decimal;“Amount Including VAT” : Decimal) : Decimal
IF “Currency Factor” = 0 THEN
EXIT(“Amount Including VAT”);
EXIT(ROUND(“Amount Including VAT”/“Currency Factor”,0.00001));

I also added the same code in the OnValidate trigger of both “Currency Factor” and “Amount Including VAT”

VALIDATE(“Amount incl. VAT in LCY”,CalcAmountInclVATInLCY(“Currency Factor”,“Amount Including VAT”));

It doesn’t work this way.

Can anyone help me.

Thanks a lot

You put it on the posted side. Those fields are never validated when the order posts. You need to put it on the unposted side (Purchase Header) and let it flow to the posted side. I think I talked about this in a post with you yesterday.

Thanx a lot

yes, you are right we talked about this yesterday, I followed the instructions you recommended me. Are they(the code I’ve written and where I’ve written) correct, except the fact that the those should have been done in Purchase Header table and Purchase Invoice form or Purchase Order (I’m not so sure).

Thanks again

The only thing I would change would be that your parameters appear to have the exact same names as the fields in your table. You can either not pass them as parameters at all (the function still knows which table it is in, so it knows the fields in that table and their value), or rename the parameters to something like CurrencyFactorParam, AmountIncludingVATParam. Personally I would go with the first.

Thanks again

I changed the function when I included it in the Purchase Header table, I didn’t include the parameters.

CalcAmountInclVATInLCY() : Decimal
IF “Currency Factor” = 0 THEN
EXIT(“Amount Including VAT”);
EXIT(ROUND(“Amount Including VAT”/“Currency Factor”,0.00001));

I also changed it when calling in the OnValidate trigger of Currency Factor

Currency Factor - OnValidate()
VALIDATE(“Amount incl. VAT in LCY”,CalcAmountInclVATInLCY);

and in the OnValidate trigger of Amount including VAT.

Amount including VAT- OnValidate()
VALIDATE(“Amount incl. VAT in LCY”,CalcAmountInclVATInLCY);

I didn’t change the forms, except the fact that I declared a global variable in the Posted Purchase Invoices form(PurchHeader) and in the sourceExpr of the field that I had added in the form I put PurchHeader.“Amount Incl. VAT in LCY” and it didn’t worked again

What else should I had done?

Anyway thanks a lot

You don’t need any global variables. The data is stored in the field on both tables. When you change one of the values on the Purchase Header document, you should see the new field value change as well. The SourceExpr on both forms should just be the name of that field.

If you want the data to be filled in for all of the invoices that have already been posted you will have to write a script to back fill them all.


I saw the problem again and it seems to me more complicated than I thought it was. I shuold had made changes in the purchase order subform too(except the change that I made in the Posted Purchase Invoices), shouldn’t I? The change should be made refering to the Line Amount Excl. VAT in the Purchase Line table(instead of the Amount including VAT of the Purchase Header table). I saw it again and the value that this field returned (Amount Including VAT) wasn’t what I expected. What confused me was the value that Amount Including VAT in Purch. Invoice Header table. It was the the right one to perform the calculation that I was trying to do.(Sorry again, I didn’t thought twice )

So I think that what I should do is to add a field(Line Amount in LCY) in the Purchase Line table(because Purchase header table cannot contain the amount of the Purchase order) and perform calculation based on the currency factor and Line Amount Excl. VAT fields as I did in the purchase header table with the fields Currency Factor and Amount including VAT. Should this be included in the purchase Line or purchase header table? I should add a column in the Purchase Order subform too(Line Amount in LCY as I wrote at the beginning of this post).Am I right?

Please help me.

Thanks again