Custom Form Calculated Field based on Dropdown selection

[BeksKi1]

Hi all, Wondering if you can assist... I am trying to make a calculated field that will show what is selected across a few dropdowns. Our dropdowns are separated by region so we have Dropdown 1,Dropdown 2, and Dropdown 3 and each is named Type - Region 1, Type Region 2, Type Region 3. In each of those dropdowns they have the option to choose "Client" or "Internal". I am wondering if there is a calculated statement that could just show Type = "Client" or "Internal" based on what is selected across those dropdowns? We have a similar calculated field that pulls the name of the specific client and pulls the name from every regional dropdown so we can report across several regions. For some reason though, I am struggling in doing the calculation on this one. Can anyone help please? Thanks. Beks Kiley Compass

[NRYNR]

Hi Beks, Are you saying you'd like to create a calculated field that interprets the results of Dropdowns 1, 2 and 3 and looks to see if any contain "Client" or "Internal"? A screenshot of the report and an example of what you'd expect in the output might help the community help you! Thanks, Narayan Narayan Raum Workfront Delivery Lead - SunTrust Bank https://wf-pro.com for Text Mode & Solutions

[BeksKi1]

Thanks for the reply! Providing some screenshots here with description: Currently, we do have a calculated field that can pull from across all our different Agent name drop down fields. Because of this, in the screenshot of the prompt report below, I am actually able to see the names of agents from two different (smaller) regions. In the image above you can see I have marked up and left notes about what I would like to have happen if I can create a calculated field to pull from the "Region - Type" field rather than from each "Region - Agent Name" field. Below you can see there are various dropdowns that are separated in a custom form for "Region - Type". They all have fields that are dependent on them and appear or not appear based on what is selected. In these two screenshots below, I wanted to show you the logic between when the Type "Agent" is selected that a dropdown with the list of agents/customers for that region would appear. If Brand is selected, there is no other dropdown to appear. Hope that helps with providing more clarity. Interested to see if anyone may be able to help find a solution! I have been an admin in Workfront for three years but only recently began utilizing this Community feature. Thanks. Beks Kiley Compass

[BeksKi1]

Just bumping this to see if there is anyone who can help with this? Trying to solve it without needing WF consultation hours. Thanks! Beks Kiley Compass

[BarryBu2]

Basically it's a long IF statement. If we take the scenario that someone selects 'Atlanta' for the region, then selects 'Agent'. They then realise they meant to choose Austin all along and change region from 'Atlanta' to 'Austin' and again choose Agent. Both Atlanta and Austin now have 'Agent' selected, although on the interface you can only see (and change) Austin. Put all those custom drop-down fields in a view and you'll see what I mean. So, you have a calculated field at the end that says: IF(Region='Atlanta', Type - Atlanta, IF(Region = 'Austin', Type - Austin, ... If(Region = 'Wyoming', Type - Wyoming ))...) Notes: Each IF line ends in a comma, except for the last one . This is basically Workfront's way of saying ELSE You need a number of closed brackets at the end that match the number of IF lines above. The entry in the region drop down (right after the = sign) needs to have single quotes around it (single straight quotes, not the curled opening and closing apostrophes programs like Word automatically convert it to) The drop-down field name at the end of each line needs to exactly match (case sensitive) the field name already on the form. If you add a new drop-down, you need to save or at least apply changes to the form before you can reference it in the formula. Barry Buchanan - WMA Work Management Australia

[BeksKi1]

Hi Barry, Thank you so much for responding! I tried your suggestion in the below ways but still getting a "Custom Expression Invalid" error. Made sure they matched exactly to the fields due to the case sensitivity too. I've built out much longer calculated fields and did not think this would be complex at all but it's proving to be a head scratcher for sure. See below for the two ways I tried doing this: IF(Request Origin='Austin',Type - Austin,IF(Request Origin='Boston',Type - Boston,IF(Request Origin='Seattle',Type - Seattle))) IF({Request Origin}='Austin',Type - Austin,IF({Request Origin}='Boston',Type - Boston,IF({Request Origin}='Seattle',Type - Seattle))) Maybe my brain is a bit fried or maybe I messed something up or both. Beks Kiley Compass

[BarryBu2]

Try braces around {Type - Austin} etc. Workfront often doesn't like punctuation in field names. Barry Buchanan - WMA Work Management Australia

[MirandaRa1]

I was given this recently to do something similar and I adapted this to suit my needs.... IF(Squad Name="Squad A","Squad A",IF(Squad Name="Squad B","Squad B",IF(Squad Name="Squad C","Squad C",IF(Squad Name="Squad D","Squad D",IF(Squad Name ="", "No Selection","Multiple Squads"))))) I was pulling in choices from a multi-select drop-down - but, it looks similar to the above, you would jut need to adapt it....I had / in my choices, to be honest, I removed them, just in case, as my brain was also getting fried XD Miranda Rais GVC

[BeksKi1]

That worked! Thank you so much for your help Barry :) Beks Kiley Compass