Adding/Multiplying Choice Values of Custom Fields in a Calculated Field | Community
Skip to main content
S
SSM01
Level 3
May 25, 2023
Question

Adding/Multiplying Choice Values of Custom Fields in a Calculated Field

  • May 25, 2023
  • 1 reply
  • 786 views

Hey all! I've been digging through the archives trying to find an answer to this, but it seems like nothing is quite addressing my issue, so I humbly put forth my question for the great minds here!

 

I've been asked to set up an automatic "LOE Score" field which will basically take a 1-5 scale value from a Level of Effort custom field, then multiply that by various multipliers that will be defined by choices made on other custom fields in the same form. For example, there is another field that will ask if the project is a Rush request, and if "Yes" is selected, it will multiply the LOE Score by 2. There will be several multiplier fields like this, and I need to the calculated field to be able to check and apply whichever multipliers are selected.

 

I know it should be possible because I built something similar a few years back, but sadly I wiped out the sandbox it was on before I realized my code was there, and I've long since forgotten how it was made... can anyone help me out with this? Happy to provide additional info if this was too vague 🙂

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

1 reply

ChrisStephens
Community Advisor
ChrisStephensCommunity Advisor
Community Advisor
May 30, 2023

You would have a couple of options here, in my opinion.

The quickest & dirtiest, in my opinion, is to have the labels of those fields be what they are now, but the values be their numbers, so for example, your LOE score would have human text in the label fields, but then in the value fields it would be 1 - 5, and rush would be 1 (default) or 2 (Yes). Then you could just built your math equation that way.

The other way, which is better, IMO (I don't like having different values/labels, but that's just me), is the exact reason that I made a request to Workfront to add a switch statement, but for now, for every value, you would have to build an IF table to convert the selections to numbers, and then do your math. So for example,

IF({LOE Score}="Text1", 1,IF({LOE Score}="Text2", 2,IF({LOE Score}="Text3",3,IF({"LOE Score}="Text4",4,IF({LOE Score}="Text5",5,0)))))

multiplied by

IF({Rush}="Selected",2,1)

For however many variables you have.

    S
    SSM01Author
    Level 3
    June 1, 2023

    Thank you for this! I'll try to apply it to my report 🙂 It has been so long since I messed with formulas and expressions, I had forgotten even the most basic of basics! 

    Terms & ConditionsAccessibility statement

    Loading footer...