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 |
---|---|
43 | |
28 | |
24 | |
21 | |
13 |