Confused about refs

AppSheet AppSheet Q&A
1

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:

  1. Create a VC in the Sample table, called it PrepRef, and copied exactly the key for the Prep Log table. It wouldn’t work at all otherwise, I tried using Ref_Row with just the Experiment ID and skipping Step 2, but it didn’t display any output at all.
  2. Create a virtual column in the Prep Log table, and the formula is REF_ROWS(“Samples”, “PrepRef”).
    There aren’t any other options on the form at this time, so I save and validate.
    So when I view my Prep Log entry, it will show my list of 2 samples. It’ll open a form from the prep log entry so I can add a new Samples entry. Nice, but it won’t show me details of the samples that already exist for that prep log.

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?

2

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.

3

@TJD, did you find a solution to this as I have a similar issue?

4

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

Simon@1minManager.com