Is there a way to combine drop-down options from multiple custom fields into one column in a report?

[OliviaMi]

Scenario: We are building a process for Supply Chain. They have multiple regions, and different locations under each region. Because there are so many locations, in the custom form we want to build in display logic - if they select Region "SE", the following field will display these 8 locations to choose from. If they select Region "SW", the following field will display the 5 locations in that region, etc....

In a report, I want just one "Location" column that will display the location chosen no matter which region it is. Can I do that?

Otherwise, in the report, I will have to have the "Location" column shown for each region, and there will be a lot of blanks in those columns.

Thanks in advance for the help!

[AnthonyIm]

Hi - So for the form, definitely build out region and then the different location fields. Then use display logic so the right location drop down shows up. The final step is to create a Calculated field for location that you will use for reporting that combines all of them.

So in a sense, you are creating an easy UX for the requester, but then you will also create a way to make it easy for reporting.

For my example, I'm doing Region and Countries.. Here are my different Country fields (note: I used the same label, but had different names.)

After this, I did a calculated field called Countries and the formula was

IF(TEST REGION="North America",TEST North America Countries,IF(TEST REGION="Europe",TEST Europe Countries,TEST Asia Countries))

Hope that helps