Can Survey123 auto concatenate text and integers/times from other fields?

6019
3
Jump to solution
06-25-2017 06:11 AM
LindsayRaabe
New Contributor III

I have a series of questions asking if a place is open on set days where the answers are either "Closed", "All-day" or "Set Times". If "Set Times" is selected then Open and Closing time fields appear for manual input. I would like to then concatenate all these answers into a single field for use later. This would result with multiple combinations in that field as shown below;

i) "Closed"

ii) "All_day"

iii) "08:00 am - 05:00 pm" or something to this effect if I could get it to work

I've tried numerous combinations of formulas based on the "IF" scenario in the calculate and default fields with the type as hidden and calculate. I manage to get the "Closed" and "All_day" answers to work but not the concatenate part if "Set Times" is selected. I don't know if this is possible so hopefully someone can set me on the right path (if it exists!). 

I've uploaded some sample data. 

Thanks!

1 Solution

Accepted Solutions
CarmelConnolly
Esri Regular Contributor

Hi Lindsay, 

Take a look at the attached spreadsheet. Looking at Monday, I've added in 2 new rows (rows 4 & 6) to covert the recorded opening and closing times to text using help from here in the Calculate column. I had to convert them to text, otherwise the Epoch version of the time would be filled in.

For the combined field, I've used this to concatenate the times:

if(${Monday}='Closed', 'Closed', if(${Monday}='All_Day', 'All Day', if(${Monday}='Set_Hours', ${Monday_Open_Text} + '-' + ${Monday_Closed_Text}, ${Monday_Open})))

Rows 4,6,11 and 13 are currently text but changing them to hidden the process should still work! 

Carmel

View solution in original post

3 Replies
CarmelConnolly
Esri Regular Contributor

Hi Lindsay, 

Take a look at the attached spreadsheet. Looking at Monday, I've added in 2 new rows (rows 4 & 6) to covert the recorded opening and closing times to text using help from here in the Calculate column. I had to convert them to text, otherwise the Epoch version of the time would be filled in.

For the combined field, I've used this to concatenate the times:

if(${Monday}='Closed', 'Closed', if(${Monday}='All_Day', 'All Day', if(${Monday}='Set_Hours', ${Monday_Open_Text} + '-' + ${Monday_Closed_Text}, ${Monday_Open})))

Rows 4,6,11 and 13 are currently text but changing them to hidden the process should still work! 

Carmel

LindsayRaabe
New Contributor III

Hi Carmel. Your answer looks promising. I'll give it a try and see how I go. Thank you!

0 Kudos
LindsayRaabe
New Contributor III

Gave it a whirl and came up a treat. Thank you. Still getting my head around how the scripting works. Only problem with being self taught!