REF_ROWS() doesn't return any rows between primary key and foreign key

As the title suggests, I’m using some REF_ROWS() formulas for one of my tables “Asset_reports”.
In this table I have a column “UID” acting as the key. In a few other tables, I have the same “UID” column which is of type Ref pointing back to this table, acting as a foreign key. Naturally Appsheet automatically creates virtual list columns with a REF_ROWS() formula in the original table, pointing to these tables, but for some reason, the formulas don’t return any rows from these tables.
I double checked the backend data, and am 100% sure, that I have rows in these tables where the content of the “UID” column matches the “UID” column of some rows in the original table “Asset_reports”.

Any ideas to why this is happening? Let me know if you need more information.

Best regards

The REF_ROWS() expressions auto-generated by AppSheet in the virtual columns auto-generated by AppSheet that have not been modified in any way by you are producing no results?

Please provide screenshots of one of the problem REF_ROWS() expressions, its column configuration, and the column list of the table to which that REF_ROWS() expression refers that includes at least the column to which the expression refers.

2 Likes

That would indeed be my impression, although I’m sure the answer is somewhere.

Here is an image of a configuration of one of the REF_ROWS() formulas that doesn’t yield any results:

And here is an image of the table columns that the formula is referring to (not all columns are included here):

According to my backend data, the formula should return one row, as there is one row in “Asset_CPU” that has the same UID as in “Asset_reports”

Let me know if you need more information

1 Like

I would recommend removing the apostrophe (') from the column name, though I don’t suspect that’s the problem here.

What happens when you use the Test button in Expression Assistant for the REF_ROWS() expression?

Removed the apostrophe, but as you said, no difference.

I’m not quite sure how to interpret these test results, but it seems like it doesn’t return anything, when running the formula?

1 Like

Update:

I tried removing the “Read-only” property from all the relevant tables, allowing edits, adds and deletes, and then all the lists showed up. Apparently having them read-only breaks it? The odd thing is though, that after the list appeared, I put the tables back in read-only, and the lists are still there. Not quite sure what’s going on here, but now I wonder if the app will add new rows to these lists when the tables are in read-only? I’m guessing not.

1 Like

That behavior definitely seems odd to me. If this continues to be a problem or becomes a problem again, I recommend engaging support@appsheet.com.

2 Likes

Will do. Thanks for the help!

1 Like