Data Column Type Enum Update

[Flaman_Mechanic]

I have a table that is generated from a Google Sheet. In the “Work Order sheet” there is a “Part No.” column that uses data validation to pick from a list of parts in the “Parts Inventory Sheet”. As I add parts to the “Parts Inventory Sheet”, my selection in the “Work Order Sheet” is updated via the data validation rule in Google Sheets.

Now the problem is that AppSheet defines the “Part No” column in the “Work Order” table as Enum and builds the list of available choices at the time the table is defined/generated. However, as I add new parts to inventory (i.e. new entry in Parts Inventory Sheet), the Enum list is NOT updated automatically without manually regenerating the table in AppSheet.

Is there anyway around this so my Enum selection is updated automatically???

[Jonathan_S]

Welcome,

I believe the way you have it set up, it will update, but only after every sync/ReGeneration. Its best to not use formulas in the sheets themselves unless necessary as it can cause some strange issues down the road depending on how complicated you get.

Best best would be to reference the parts from the Parts inventory sheet into your work order sheet.

Do you currently have both sheets setup in appsheet?

[Flaman_Mechanic]

Yes both Google sheets are defined in AppSheet as “Work Order Parts” and “Parts Inventory” respectively. I either add parts to “Parts Inventory” through Google Sheets or through AppSheet; however, these NEW parts do no show up in “Work Order Parts” drop-down selection for the Enum “Part No” field unless I regenerate the table. When I look at the column definition for “Part No” in the “Work Order Parts” table the field “Part No” has a list of available values that does not include the NEW part numbers… I regenerate the table and the list is then updated with the NEW part numbers.

[Jonathon]

Hi @Flaman_Mechanic,

Do you have Server caching enabled in the offline/sync options? This setting tells appsheet to persist some of your read-only data on their servers to speed up loading times. One drawback is updates to the data will take longer to show up in your application, however its usually a worthwhile tradeoff for read-only datasets. This is one possible cause to your problem.

Interesting to see the Flaman logo on here. Are you from Sask?

[Jonathan_S]

Just syncing it should update it than.

Maybe select sync at app start. Sometimes it takes a minute or 2 before the app pulls the data. But adding a new part directly in the app should make the part available right away as long as it is the same device.

[Flaman_Mechanic]

I got around the problem by using the Data Validity option with the “Valid If” setting. It seems to pull the list of Part No’s with the most current version of google sheet. We are one of the Flaman Rental dealers in Alberta, and I am the mechanic for the fleet of equipment. I developed a Work Order, Parts and Equipment inventory in Google Sheets, and I am now using AppSheet as an interface for the data. Thanks for your help with this issue!

[Steve]

Flaman_Mechanic:

Is there anyway around this so my Enum selection is updated automatically???

In short: no.

When a spreadsheet column uses data validation to offer a dropdown to select values, AppSheet will automatically configure the corresponding table column as an Enum type and define the allowed values as those defined by the data validation rule. However, AppSheet only synchronizes the allowed values when the column is first added to the table, and when the columns are regenerated; the allowed values are not synchronized under any other conditions.

It sounds like you’re intention is to continue using the spreadsheet as a spreadsheet even as your app uses it as its data source. This approach is not a good one, as it can result in a number of problems, as you’re experiencing. Ideally, the spreadsheet should only be used by and for the app.

To provide a dropdown menu of choices for a column within you app and still allow users to add new values, you will need to remove data validation from the spreadsheet column. Doing so will not interfere with the existing configuration of the column in the app. Then, within the app column’s configuration, set both Allow other values and Auto-complete other values to ON.

[catfooder]

Just to confirm, @Steve 's answer worked for me:

• remove data validity from the column original google sheet
• in AppSheet, edit your table's column
• set the column type to enum and remove any default entries
• set both Allow other values and Auto-complete other values to ON