I have 2 tables: Prep Log and Samples
Prep log has one entry per experiment, and the key is:
CONCATENATE([Instrument],": “,[Start Date],”: “,[Experiment ID],”: ",[Equipment])
Samples can have multiple entries per experiment, and the key is:
CONCATENATE([Experiment ID],": “,[Sample ID],”: “,[Sample Holder ID],”: “,[Sample Change],”: "[Position])
I would like to open up the Prep Log entry, and see the referenced samples at the bottom. I know this is possible, I’ve seen examples, I read the Help entry on References, but can’t get it to work for me. Specifically, following the instructions for “User-Added References” resulted in this error: Column Name ‘Related Samples’ in Schema ‘Prep Log_Schema’ is a virtual column without an app formula." This is sensible, of course it needs a formula, but it doesn’t tell me what formulas can work.
I have it partially working, though, here’s what I’ve done:
I switch the type to Ref, but of course then Ref_Rows throws an error, because Ref can’t be type Ref, fine, it has to be a List. The only things I see that say they result in type Ref are Minrow and Maxrow, which don’t seem useful here.
Any idea what I’m missing?
To be clear, the example app that I was looking at was Order Capture. It works obviously with Ref_Rows and type List. But it is clearly operating with single column keys. When it evaluates the expression REF_ROWS(“Orders”, “Customer Name”), the result is:
The list of values of column ‘Order Id’
…from rows of table ‘Orders’
…where this condition is true: ((The value of column ‘Customer Name’) is equal to (The value of column ‘Name’))
For my app, ignoring step 1 in my other attempt, the expression is Ref_Rows(“Samples”,“Experiment ID”) and the result is:
The list of values of column ‘_ComputedKey’
…from rows of table ‘Samples’
…where this condition is true: ((The value of column ‘Experiment ID’) is equal to (The value of column ‘_ComputedKey’))
And the actual result is nothing at all in my app, because my samples need a more unique key than the experiment ID by itself.
If the answer to my question is that Refs don’t work with multi-column keys, then I’m not sure what the point of any of it is.
@TJD, did you find a solution to this as I have a similar issue?
The easiest way to automatically create a Ref is to have the first column of the second table equal the name of the first table. So in your case Column A in Samples should be called [Prep Log]. Then Appsheet will automatically link them.
If needs be, remove the Prep log table, add this column then re-add the table. Once you’ve got this working, then you can try to create your own Refs
Also for your key column, I suggest using an Initial_Value of UniqueID() as this generates a 6char AlphaNum key
User | Count |
---|---|
35 | |
30 | |
30 | |
20 | |
18 |