Ref_rows choosing the wrong row

[mbavishi46]

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]

Hi @mbavishi46 

This may be due to the fact the key-column is incorrect in your table DispatchLines.

Can you check this?

If you don't have a key-column, I suggest strongly that you add one and set its initial value expression with:

UNIQUEID()

For reference:

What is a key? - AppSheet Help

REF_ROWS() - AppSheet Help

References between tables - AppSheet Help

UNIQUEID() - AppSheet Help

[mbavishi46]

Hi,

I have added an ID column to the DispatchLines. however, if I set it as the key for the table, then the link between the Dispatch -> DispatchLines breaks.

[Aurelien]

You would need to adapt the ref_rows expression to catch the id column instead.

[mbavishi46]

I am unsure how to do that. The ID column is a random number so how would I be able to use that to match the other tables? Is there a foreign key to link the two DispatchIDs and StockIDs

The below screenshots are for the DispatchLines Table and the fields that have been added.

The below screenshots are for the other Tables and the fields that have been added.

[Aurelien · Accepted solution]

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.