Choice field and formula
Hi,
I'm trying to work out the right formula to calculate my commision on my invoice.
In my parent table 'Invoice', I have created a choice field called 'Com %' where I can select the % of my commission to applied. Choice 1 = 10%, choice 2 = 9% and choice 3 = 8%.
I also have created a formula field to sum up the total amount of fees called 'Total Fees'.
What I'm trying to do now is to create a formula field 'Com' to calculate the % of my commission on the 'Total Fees' according to the choice field selected.
I have tried this formula but it doesn't work:
if 'Com. %':= 1 then
'Com'='Total Fees'* 0.1
else
if 'Com. %':= 2 then
'Com'='Total Fees'* 0.09
else
if 'Com. %':= 3 then
'Com'='Total Fees'* 0.08
end
end
end
Any idea please?
13 replies

Yes, the use of := and = is reversed, switch those two....

You have the assignment ":=" and comparison "=" operators reversed. It should be...
if 'Com. %' = 1
and
Com := 'Total Fees'*0.1
The switch statement also works here...
switch 'Com %' do
case 1:
'Total Fees' * 0.1
case 2:
'Total Fees' * 0.09
case 3:
'Total Fees' * 0.08
end

@Steven, I guess I need to refresh the page before I post


Hi everybody, is it possibile to have a formula that change automatically a choice field ?
example, I need to change a choice field depending the result of another formula field:
if "the result in the formula field is" <" a number" then "change the value of the choice field in: "x"
thanks

@MP... Not following 100% ... So.. you have a choice field that has choices of RED, WHITE, BLUE .. and if the formula yields a number less than 100 .. you want the choices to be A, B, C instead?

Hi,thank you for your reply!
Sorry, I wasn't clear enough.
Well, I have a table called TO DO LIST with a choice field called STATUS, with 3 options: URGENT  NON URGENT  IN PROGRESS. I have 2 dates field also: BEGIN DATE  END DATE.
I would like to find a formula that, for example, if today is between Begin Date and End Date, changes the value in the choice field in IN PROGRESS or if today is Begin Date1day change the field choice in URGENT. etc.
I don't know if is it possible.. I tried but it returns me an error like: the formula does not change the value.. or something like that.

Oh.. Yeah.. sure .. that is possible... you can just do something like ...
let td := today();
if 'Begin Date' <= td and 'End Date">= td then
Status := 3;
end;
That is assuming that 3 is your in progress value ..

Sorry .. I see a typeo .. 'End Date" .. should be 'End Date'... disclaimer.. I did not test the code.. :)

Yeah, i saw it..don't worry. Thank you.
unfortunately It doesn't work.. it returns the error message (word for word traduced): this formula could not change the values.

Yeah, a formula field doesn't change values, only triggers, so if you put this in a button or a fields property trigger after update it'll work....

Yep.. sorry .. I should have been more clear .. evaluate the result of the formula in an appropriate trigger.

Thank you very much guys for your help! As you can see i'm not very expert!;) I'll try !
Content aside
 4 yrs agoLast active
 13Replies
 2673Views