Ref column not looking up existing values from Sheet data source

[parmachris]

I want to change a column in a table to a Ref type, but AppSheet is not creating references from the existing values in the column. Adding new rows to this table and manually linking the ref using the Form view works as expected.

Overview:

I have a "Projects" table sourced from Google Sheets. That sheet has a column called "Client Full Name", which is already populated with existing values. These values are just plain text, and not sourced from a formula.

The app also has "Clients" table, sourced from AppSheet DB. The LABEL column of this table is called "Client Full Name" and has a datatype of Text. This column has values that match up exactly with the [Client Full Name] column from the "Projects" table.

When I change the [Client Full Name] column of the "Projects" table to a Ref type, I would expect the app to then look up the existing values in the column and attempt to make references to the table specified in the column type. I have seen this successfully work when using an AppSheet DB as the data source, and to me is the expected behavior.

However, AppSheet does not link the existing value from "Projects"[Client Full Name] to the corresponding row of the "Clients" table. When I try to link the reference manually, I see all the values from the "Clients" table populating a dropdown menu, as I would expect. However, that dropdown menu now has duplicate values for the Client table, one representing the actual Client reference, and one that must be the cell's plain-text value.

Constraints and things I've already tried/checked:
- The values from the "Projects"[Client Full Name] column that I'm trying to look up are correctly formatted and exactly match values in the LABEL column of the "Clients" table. This isn't a case of trailing whitespace characters causing issues.

- I can't use AppSheet DB as the data source for this table. I know that already works for converting plain-text values to references, but it's not possible for this business case.

- I have created a workaround using a separate virtual column in the "Projects" table to create these references.  INDEX(FILTER("Clients", [Client Full Name]=[_THISROW].[Client Full Name]),1)This is super klunky, but shows that AppSheet should be able to create references from the existing values in the "Projects"[Client Full Name] column. 

I think this is a bug, unless others aren't able to reproduce this behavior. When changing the data type of a column to Ref, I expect the data in that column to either be an actual Ref or some form of error or null value. I shouldn't see stray plain-text values that do not correspond to the data type of the column. I'd love to hear any thoughts on this.

[WillowMobileSys · Accepted solution]

---

@parmachris wrote:

I want to change a column in a table to a Ref type, but AppSheet is not creating references from the existing values in the column.

---

When you set a column to REF type, the only thing created for you is the [Related xyz] virtual column in the referenced table.  If in the comment above of "is not creating references" you mean that the REF column dropdown in a Form View is not showing possible values to choose from, that is because you need to implement an expression in the Valid_If property to select the possible rows to be chosen as the referenced row.

---

@parmachris wrote:

When I change the [Client Full Name] column of the "Projects" table to a Ref type, I would expect the app to then look up the existing values in the column and attempt to make references

---

No, this is not what a REF column is.  Think of them as "pointers" to a row in another table - much like Foreign Keys in a database - if you are familiar with that.  For a "pointer" to work you need some way to uniquely identify that particular single row.  Row keys are used for that.  So, a REF column will store a row key for the row it is "pointing to". 

You may also may find you do not need a separate "Client Full Name" column in your Projects table.  If you only ever display the "Client Full Name" for the Project in the app, then you can just use the REF column.  Some things, such as CSV export, might require you to have the separate column.

************************************

If for some reason, I mis-understand your issue, please include an image showing where the problem is.

I hope this helps!

[parmachris]

Thanks for a helpful reply. I do see that AppSheet Ref columns simply store the ID of the referenced object (like a pointer as you described). My confusion about this came from how AppSheet works compared to AppSheet Database. AppSheet DB will automatically "convert" values for you when changing the data type of a column. If you have a column full of strings, and change its data type to Ref, AppSheet DB will try and look up those strings in the LABEL column of the referenced table and replace that string with an ID, creating a reference. I expected AppSheet to work the same way, but I see now that it doesn't. For anyone who may have experienced the same issue, I was able to get around this by using LOOKUP() in order to return the ID column of a row based on its corresponding project name.