Simplicty and flexibility!

Calculate Discount Fields

Started by Handles

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.

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)

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

Re:Re:Calculate Discount Fields

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 :

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 .

Regards….

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

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

http://www.dataease.com/dg3_HelpView/?PageID=10079...

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
DG3_ForumList