So I have a reference to another table that d...

references
(Joshua Giles) #1

So I have a reference to another table that does nothing but act as a referenced list for a drop down in my form. For some reason after every other save those virtual columns revert back to the type “text” instead of “lists” which produces an error. I have to change it back to the list type every time and my error goes away and the app acts as it should. Why does this keep changing from list to text? This has never happened to me before, even though I’ve referenced lists exactly like this multiple times.

(Steven Coile) #2

What is the App formula for the virtual column?

(Joshua Giles) #3

@Steven_Coile

'REF_ROWS("“DLSWHSE” Daily Time Sheet", “Service Request #'s:”

and

'REF_ROWS("“DLSWHSE” Daily Time Sheet", “Activity #2 Work Performed:”)

The specific error that I am receiving is:

“Column Name ‘Related “DLSWHSE” Daily Time Sheets By Activity #2 Work Performed:’ in Schema ‘Work Performed Categories_Schema’ of Column Type ‘Text’ has an invalid app formula 'REF_ROWS(”“DLSWHSE” Daily Time Sheet", “Activity #2 Work Performed:”)’. Unable to find table ‘’"

Like I’ve said, I’ve always been able reference rows like how I’m doing now. None of the settings are different (that I can find, and I’ve definitely looked). The only steps that I’m taking:

1.) Created a data filler tab in my excel file. 2.) Added those tables to my data table pages 3.) Turned the column I wish to turn into a dependent drop down to a “Ref” type. 4.) For the, “ReferencedTableName” drop down, I’ve selected the data filler table 5.) Everything else stays the same. Click “okay” 6.) Save

Upon reload I get the error referenced above. However, I can manually change the virtual column that the reference created from “text” to “list”. Click save, and boom the problem is fixed and the referenced row acts exactly how I want it to (a dependent drop down). However whenever I do anything in the app the virtual column type changes itself back to “text” and then produces that same error.

(Steven Coile) #4

@Joshua_Giles REF_ROWS("“DLSWHSE” Daily Time Sheet", “Service Request #'s:” has too many quotes. Remove the quotes from your worksheet (and then table) name.

Specifically, change:

“DLSWHSE” Daily Time Sheet

to:

DLSWHSE Daily Time Sheet

(Joshua Giles) #5

@Steven_Coile So I thought that, that might be the issue, and went to remove them from my tab name. However, once I did this, all of my existing formula and formatting for the other columns are deleted. Is there any way to not have to reformat everything? (Basically starting from scratch and the app was essentially finished.)

(Joshua Giles) #6

Also, @Steven_Coile that totally fixed it. :’( but now I’m looking at the rest of my day rebuilding what I already built :’’(

(Steven Coile) #7

@Joshua_Giles The best way I’ve found to rename a worksheet without losing AppSheet configuration is this:

  1. In the AppSheet workbench, go to Data / Tables, to the configuration of the table you want to rename.

  2. Change the name of the table in the workbench as desired. Do not (yet) change the name of the worksheet.

  3. SAVE the change. Note that this will result in errors if your configuration otherwise refers to the old table name (likely).

  4. After the save is complete, correct any errors by updating your configuration to use the new table name and get your app working again. Still do not yet change the name of the worksheet.

At this point, you may stop. Changing the name of the worksheet to match the table name used by AppSheet is not strictly required, but you will likely want to change the worksheet name to avoid confusion in the future. To do so:

  1. Rename the worksheet as desired. Doing so is likely to break your app until you complete the steps below.

  2. Again in the AppSheet workbench, return to Data / Tables, to the configuration of the table you’re renaming.

  3. For Worksheet Name/Qualifier, choose the new name of the worksheet.

  4. SAVE the change. When the save is complete, the app should again be working, and using the newly-renamed worksheet.

Note that in the course of this process, the table configuration is likely to be reordered among the list of table configurations in the AppSheet workbench. This reordering is not significant.

In essence, the process above amounts to: (a) renaming an AppSheet table; and (b) connecting an existing AppSheet table to a different worksheet (that happens to be the old worksheet, but with a different name).