Simplicty and flexibility!

Calculate Discount Fields

Started by Handles
You will need to Sign In to be able to add or comment on the forum!

Calculate Discount Fields

DataEease for windows

Version LegEasy 6.53

Build 3158

I am returning to an old program written many years ago and trying to add a Definition to a filed.

What I am trying to do is something like below :

if ( "rStockEntryForm_ON" = "Proforma" SellingCost lookup "rOHeader_ON" "CompanyDiscount" + “ExtraDiscount” , blank )

I have two Fields in a related form to the form I am working in , I want to calulate “CompanyDiscount” and “ExtraDiscount” and need to add both fields together, Then deduct the percentage away to show how much the discounted selling cost will be .

So the “rOheader_ON” has 2.5% discount in the company discount field and 5% in the extra discount field , that is 7.5% total discount , deduct 7.5% from the selling Cost of £100.00 should give you a figure in the selling cost field of £92.50 but only if its a Proforma , other wise the field is blank or 00.00.

Any Help Please

Written by Handles 20/11/18 at 13:16:26 LegEasy 6 Windows

Re:Calculate Discount Fields

These things need to be right or it won't save so without knowing the structure we can only have a go and try to explain.

if ( "rStockEntryForm_ON" = "Proforma" SellingCost lookup "rOHeader_ON" "CompanyDiscount" + ?ExtraDiscount? , blank )

1. any value in another form need to be looked up, you can't reference it directly.
2. Using " " around field names is only necessary and recommended if they have spaces in the name but even then it will only be a problem if not adding them will cause a mismatch.

There are two if elements in DataEase. If Statement (DQL) and if Function (everywhere).

IF function is formatted  If (boolean expression, true, false)

if (rStockEntryForm_On=lookup Proforma SellingCost, lookup rOHeader_On CompanyDoscount+lookup rOHeader_ON ExtraDiscount,blank)

Written by DataEase 22/11/18 at 20:09:24 LegEasy 6 Windows

Re:Re:Calculate Discount Fields

Download: DataEase If Statement.jpg

First may I say thanks you for the explanation , and help with this situation.

Over the last few days I have tried different ways of doing the calculations without any success, I went back and have tried your suggestion

But without any joy still will not allow me to use :

if (rStockEntryForm_ON = lookup Proforma SellingCost, lookup rOHeader_On CompanyDoscount+lookup rOHeader_ON ExtraDiscount,blank)

Trying another approach I have attached a picture of the table involved with the Formulas I have placed in the Derivation box, as you will see they are all working accept the “SellingCost” field I wonder if you would be kind enough to have a look to see what you think I may be doing wrong .

Now what I am trying to say is. If ( the status is = proforma, lookup the rMaterial table “sellingcost” and discount that cost by the amount in the DiscountCalc filed if  status is = anything else just use the selling cost in the rTable Materials.

if (Status= "Proforma" SellingCost = lookup rMaterials “SellingCost” - DiscountCalc , lookup "rMaterialsMC" "SellingCost" )

With regards to your note about “ ” I only use these as I was told years ago that its good practise to do so, but if we don’t need them I can remove them .

Hope the way I have explained it may help solve this issue

Thanks again for your help .


Written by Handles 23/11/18 at 13:05:39 LegEasy 6 Windows

Re:Re:Re:Calculate Discount Fields

Sorry Regards the above JPG  It's the If( part of the statement that's not working, the lookup rMaterials is working fine   

Written by Handles 23/11/18 at 13:11:48 LegEasy 6 Windows

Re:Re:Re:Re:Calculate Discount Fields

Download: Working Not Working.jpg

Just thought I would add another jpg that may help 

Written by Handles 23/11/18 at 16:59:02 LegEasy 6 Windows

Re:Re:Re:Re:Re:Calculate Discount Fields

the most obvious fault is that you don't format it properly.

you have to have the , (comma) in the right places.

if (A=B, Yes, No)

you ave

if (a=b yes,NO) 

That won't work.

You can nest as many times as you like.

If (A=b, if (b=c, if(C=d,True,Fales),False),False) etc

Follow the link below to read up on the if() function.

Written by DataEase 23/11/18 at 19:18:23 LegEasy 6 Windows

Re:Re:Re:Re:Re:Re:Calculate Discount Fields

thank you , that comma done the trick ,how I missed that I don’t know. 

Thanks again 

Written by Handles 23/11/18 at 23:26:48 LegEasy 6 Windows