Calculate Discount Fields
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
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)
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 :
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 dont need them I can remove them .
Hope the way I have explained it may help solve this issue
Thanks again for your help .
Regards….
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
Re:Re:Re:Re:Calculate Discount Fields
Just thought I would add another jpg that may help
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.
Re:Re:Re:Re:Re:Re:Calculate Discount Fields
thank you , that comma done the trick ,how I missed that I dont know.
Thanks again