How do I make it so that the app writes the Name rather than the key into the spreadsheet

[Jerry_Hill]

My app has a number of columns that are populated from a dropdown (ref). Example: I have an Entitys table, with a unique Key column, and a Name column. In a form view based on another table, a dropdown is populated by the Name column; at that point it’s working perfectly. When it syncs it writes the key into the spreadsheet dropdown, instead of the name, and then in the app it shows a warning triangle.

[Steve]

Don’t use a Ref value.

[Jerry_Hill]

What do I use instead. When I’ve tried Enum it doesn’t use the table after initialized. List does seem to work, or I’m doing something wrong.

[LeventK]

Jerry_Hill:

When I’ve tried Enum it doesn’t use the table after initialized.

What do you mean exactly?

[Jerry_Hill]

In my Entitys table I have a Bank column that I want to populate a dropdown from the “Name” column in my Banks table. If I set Entitys-Bank to Enum, it adds to an Enum list in the editor any Bank names already being used in the Entitys rows. From then on any new Bank name that is added to the Banks table, no longer shows up in the dropdown. I hope that makes sense. BTW Entitys is purposely misspelled .

[LeventK]

You need to use your expression in Suggested Values property of the ENUM column and explicitly mark Allow other values and Auto-complete other values properties.

[Jerry_Hill]

Thanks, so far it works great.

[LeventK]

You’re welcome.

[Jerry_Hill]

Opps, I came across a problem. Because it is explicitly set to allow other values, when I added an the value it shows in the current form, but it does add the value to the source table.

[LeventK]

Sorry @Jerry_Hill, couldn’t understand the problem. Can you elaborate?

[Jerry_Hill]

Yes, I have a JLedger table. I want the column “Bank” to have a drop down (DD) from which a bank is selected. By using your suggestion to use Enum with an expression in Suggested values and explicitly setting both “Allow other values” and “Auto-complete other values’’ to true, it provides a populated DD with the banks that are currently in the Banks table. If the user adds a value for the Bank in using the DD, it populates the Bank column in the JLedger table, but it does not add that value to the Banks table; in other words, it doesn’t add a Bank record.

[LeventK]

To add a missing Bank record, you need to use REF. If the bank is not in that list, the user than create a new Bank record by selecting New from the dropdown.

[Jerry_Hill]

This started when I had a problem using Ref and Steven Coile told me not to use ref, and I asked what I should use instead, and you told me Enum. Here is what I asked Steve:

My app has a number of columns that are populated from a dropdown (ref). Example: I have an Entitys table, with a unique Key column, and a Name column. In a form view based on another table, a dropdown is populated by the Name column; at that point it’s working perfectly. When it syncs it writes the key into the spreadsheet dropdown, and then in the app it shows a warning triangle.

[LeventK]

@Jerry_Hill
A ref type column displays the column value from the table set as the Label, however when it’s recorded to the back-end gSheet, it records the key column value of the referenced table. Provided you notice a yellow triangle next to the ref column in the app, this warning indicates that the key column value of the referenced record does not match with the value in the back-end gSheet.

---

Possible Solutions

---

1.) Provided you have set any initial value expression with a non-matching content to the RDBMS context, please remove away that expression.
2.) Provided you are using a Valid_If expression for the ref column, please pay attention to 2 things:
2.1.) Expression is populating the key column values of the referenced table
2.2.) The Key column and the Label column for the referenced table is properly set

[Jerry_Hill]

Thanks millions Levent for that very thorough explanation. Being new I’m not completely certain of the last statement, i.e., 2.2.) The Key column and the Label column for the referenced table is properly set.

Does that mean I should explicitly set the column I want displayed to be a LABEL column? And/or do they need to be side-by-side in the back-end gSheet?

I have two additional questions that’s a little bit off topic, but for me they are related.

1. Does AppSheet expect the tables in the back-end gSheet to remain in the same order, or does it use the name of the tables, i.e. getSheetByName()?

2. Does AppSheet somehow suppress the GAS (Google App Script) code from triggering during sync?

[LeventK]

Jerry_Hill:

Does that mean I should explicitly set the column I want displayed to be a LABEL column? And/or do they need to be side-by-side in the back-end gSheet?

You can choose whatever column you want to be the label column. This column’s value will be displayed in a REF dropdown.

Jerry_Hill:

I have two additional questions that’s a little bit off topic, but for me they are related.

1. Does AppSheet expect the tables in the back-end gSheet to remain in the same order, or does it use the name of the tables, i.e. getSheetByName()?
2. Does AppSheet somehow suppress the GAS (Google App Script) code from triggering during sync?

1. The sheet order in a spreadsheet is not an issue at all.
2. Sure thing. Provided you check the Tips&Tricks section, I have a bunch of posts regarding the utilization of GAS with an AppSheet App. There a couple of techniques that might be used depending on how you want to interact with the data/back-end.
   https://community.appsheet.com/search?q=Google%20Apps%20Script

[Jerry_Hill]

Great, thanks for that. I’ll leave you alone for a bit, while I digest all of the tips and tricks.

[LeventK]

You’re welcome