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
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:
2 Likes
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
1 Like
Normally not with this option
2 Likes
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
@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
1 Like
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
2 Likes
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
3 Likes
Great !
Can you share how it was and how it went out ? (screenshots)
3 Likes
