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:
- 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.
- 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?