Solved: Ref_rows choosing the wrong row - Page 2

mbavishi46 · 02-21-2023 01:17 AM

Hi there, I have minimal experience in AppSheet, I am used to MS Access

I have three tables

Table	Important Columns
Stock -	StockId (KEY), SKU
Dispatch -	DispatchId(Key)
DispatchLines -	_RowNumber(Key), DispatchId, StockId

Links

Dispatch -> Dispatch Lines
Stock -> Dispatch Lines
DispatchLines -> Stock (Doesn't Link Correctly)

In the DispatchLines table, the Expression Assistant I have used REF_ROWS("Stock", "StockId"). The note below shows this message:

The list of values of column 'StockId' ....from rows of table 'Stock' ....where this condition is true: ((The value of column 'StockId') is equal to (The value of column '_RowNumber'))

How do I get this to link correctly? Is there a foreign key?

Aurelien

This is not possible.

The key can be either calculated using other real columns, or can be a real column itself.

I think this is the source of your issue.

Moreover, UNIQUEID() must be on INITIAL VALUE column. Otherwise, it is constantly changing and therefore unstable.

I suggest you add this column on your Sheets, click on "regenerate structure" ==> that will turn your virtual column into a real one.

Then, change the location of your expression from FORMULA to INITIAL VALUE.

You may need to artificially create a list of random values to initialize your existing records on this column.

View solution in original post