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

Any idea please? 

13 replies

null
    • 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

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

Login to reply

Content aside

  • 3 yrs agoLast active
  • 13Replies
  • 2642Views