Solved: Ref column not looking up existing values from She... - Page 2

parmachris · 04-16-2025 08:14 AM

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

@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!

View solution in original post

Ref column not looking up existing values from Sheet data source