Enumlist - Sheet with multiple values per cell

[Rory_Forde]

Hello all…

First I want to thank this community for the prompt and helpful responses that I have received to previous questions.

I have a google sheet that I want to create an app for. The main problem with this sheet is that some of the columns have multiple values per cell - they have been populated by a multi-select form and all of the selected responses have been inserted into a single row/column. From a traditional DB perspective this doesn’t work too well. These values are currently separated by a period (.).

As trying to rework the structure of this google sheet would be a particularly tricky exercise I want to see if there are any options to work with the data in Appsheet using the current structure. I am aware of the ENUMLIST data type in Appsheet which would appear to be compatible to the data in the sheet. The question is: Is there any way for Appsheet to recognise the multiple values in these cells? For example if they were separated a a semi-colon ( or a comma (,) would Appsheet recognise these multiple values.

Any suggestions would be gratefully accepted. If you want to tell me to restructure the sheet I am looking at that as a (very) last resort.

Thanks,

RoryF

[Aurelien · Accepted solution]

Hi @Rory_Forde

You can go there:

And in the base type Details Section:
Go to “Item separator”, and set your comma, dot, or any separator you are facing:

[Rory_Forde]

Wow… if that works I will need to buy someone a beer…

One question - do I need to add the values to the ENUMLIST manually - its not a problem just wondering…

RoryF

[Aurelien]

Normally not with this option

[Rory_Forde]

I will try that.

One last question if I may…

I see that one such column uses a “new line” (I think it is new line - 2nd value is on 2nd line within the same cell) - not sure if its CR or LF - as the separator. Would you know how to use that as the separator?

Really appreciate the time you are taking with this,

RoryF

[Aurelien]

@Rory_Forde no idea, unfortunately.
I believe this is a common issue.
I would suggest trying to substitute it directly in the Sheets with an array formula, it could be easier to deal with later.

Just in case, I call on rescue @Steve , the expression magician

[Rory_Forde]

I will test all of that later Aurelien…

Thanks so much for your time. I will let you know how it works out and mark the posts then.

RoryF

[Rory_Forde]

All of that worked…

And as an added bonus is looks like the issue with the CR/LF is a non-issue - Appsheet picked up both lines (maybe it sees the value as a single entry and just includes the CR/LF in the record).

Sooo happy - you saved me so much time…

Roryf

[Aurelien]

Great !
Can you share how it was and how it went out ? (screenshots)