I have a enum list of employees. when I select multiple employees and go to the excel file it looks like this: Josh , John. I would like to make the formatting Josh, John. Is there a way to do this?
Personally, I think I would let AppSheet record lists in the way it likes to (“Josh , John” etc) and then use SUBSTITUTE() to fix it. SUBSTITUTE() is available in AppSheet and spreadsheets. In Google sheets it would be something like this:
SUBSTITUTE(A2," , “,”, ")
I’m thinking of having two columns: one that AppSheet records in the way it likes and one that you reformat with SUBSTITUTE().
In AppSheet, you would need to convert the list to one long text string with CONCATENATE() before using SUBSTITUTE().
You also have the option of changing the “item separator” for enumlists:
This wouldn’t fix the retroactively, but it would from here on out.
I tried using the item separator in the past but it didn’t work. I think is put in the comma but the no spaces, also it seemed to cause other problems as the list is used as a key. I went the route of substitute for now as it was relatively easy to implement.
Thanks for the responses
I thought I had my head wrapped around this, but I don’t. Can you give me a sample expression of how I would do this in Appsheet with the CONCATENATE() and SUBSTITUTE().
I have messed with the Item separator per @MultiTech_Visions post. It works just fine for me to change it to be a comma space, instead of space comma space. New records follow suit just fine.
I take the information and use a mail merge to put it in to a word document. The people who have to work with the word document are tired of removing the space.
I keep having trouble if I put SUBSTITUTE(), CONCATENATE(), or even just [Employee] as the formula in a new column all it gives me is the first name. Is that normal with ENUMLIST?
Also tried the Item separator route and while that fixes the issue of formatting. it brings a new issue that since the employee column is used as a key, if i go to edit that row when I go to save it cant find the key.
Also thought I would mention even though I gave an exampe of 2 names, it can also be any amount between 1 and 6.
How does this happen? Using a report from the app? Or just accessing the spreadsheet directly?
I think @Kirk_Masden’s suggestion above was a good one:
Suppose app’s EnumList column is stored in spreadsheet column D.
Insert a new spreadsheet column E to the right of the existing spreadsheet column D.
Give the new spreadsheet column E the column name of your choice in cell E1.
Set spreadsheet cell E2 to
=SUBSTITUTE(D2, " , ", ", "). Observe that the cell’s value is the value of D2 with your preferred comma formatting.
Copy and paste spreadsheet cell E2’s formula down into all remaining cells in column E. Observe that all E cells are their corresponding D values with the preferred comma formatting.
From the app editor, regenerate the column structure for the table that uses the spreadsheet. Observe the addition of a new column corresponding to spreadsheet column E.
In the app editor, edit the new column’s configuration. Find the Spreadsheet formula property. Observe that it contains something similar to (but not the same as) the formula in (4), above.
In the emulator of the app editor, add a new row to the table just regenerated.
Allow the app data changes to sync with the spreadsheet.
In the spreadsheet, observe the newly-added row, its E cell value (should have preferred comma formatting), and its E cell formula (should be consistent with preceding E cell formulas).
Ultimately, the way you’re trying to use the spreadsheet for dual purposes is not ideal. Preferably, the spreadsheet should be exclusively for the app’s use, so that it conforms to AppSheet’s expectation and preferences, and to ensure proper and best behavior. Using AppSheet’s report capability might be a better way to achieve your goal.
SUBSITUTE(XX," ,",",") works if I put it directly into excel. the problem is that when Appsheet inserts a row it pushes the formula down. I thought I could put the formula into Appsheet as a initial valve but I dont know how I would write the XX part of that excel formula to make it work correctly. I know I am grasping at straws here…
Thanks for the detailed response. that worked, somehow i was missing the spreadsheet formula option the entire time. I’ll take not of your comment about restructuring so this all happens through reports, but for now this will get me by. thanks for the help
I haven’t worked with Excel as storage for AppSheet; my only experience is with Google sheets. With Google sheets, however, step numbers 6 and 7 of @Steve’s post make AppSheet add the proper formula to any new rows that are added:
- From the app editor, regenerate the column structure for the table that uses the spreadsheet. Observe the addition of a new column corresponding to spreadsheet column E .
- In the app editor, edit the new column’s configuration. Find the Spreadsheet formula property. Observe that it contains something similar to (but not the same as) the formula in (4), above.