0

# 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

## 13replies

• Ninox partner
• RoSoft_Steven.1
• 4 yrs ago
• Reported - view

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

• Sean
• 4 yrs ago
• Reported - view

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`

• Sean
• 4 yrs ago
• Reported - view

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

• Ninox partner
• RoSoft_Steven.1
• 4 yrs ago
• Reported - view

• saffron_car
• 3 yrs ago
• Reported - view

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

• Mconneen
• 3 yrs ago
• Reported - view

@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?

• saffron_car
• 3 yrs ago
• Reported - view

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 Date-1day 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.

• Mconneen
• 3 yrs ago
• Reported - view

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 ..

• Mconneen
• 3 yrs ago
• Reported - view

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

• saffron_car
• 3 yrs ago
• Reported - view

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.

• Ninox partner
• RoSoft_Steven.1
• 3 yrs ago
• Reported - view

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....

• Mconneen
• 3 yrs ago
• Reported - view

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

• saffron_car
• 3 yrs ago
• Reported - view

Thank you very much guys for your help! As you can see i'm not very expert!;) I'll try !

## Content aside

• 3 yrs agoLast active
• 13Replies
• 2632Views