How to check if a reference exists in a referenced table

Ask for Help Formulas
1

Hi!

I have a main table with PATIENTS, and I have sub-tables for each medical diagnosis, for example, ASMA.

I want to create a column in the main patients’ table with, such as isAsma, and assign 1 if the patient exists in the ASMA table, or 0 if it doesn’t.

Support asked me to use this formula:
IF(ASMA.lookupOne(Patient=$id), 1, 0)

Where Patient is the column from the Asma table, and $id is the column I’m using as a key in the main table.

But it doesn’t work, it results in 0 for all the 29k records (I have 800 records of patients with ASMA)

I hope this is understandable, feels kind of difficult to explain, any help is highly appreciated!

TIA!

2 Likes
2

Here is an example implementing this: Grist

The formula is as in the question.

Note that:

  1. The table Patients doesn’t have any visible column named id, because that’s a special column that all tables have. If you try creating one, you’ll find it actually gets assigned another identifier, although the label may still show id.
  2. The Patient column in the ASMA table is of type Reference, referring to the Patients table. Internally each cell contains a row ID of the Patients table, but for readability it’s been set to show the Name column.
3

Hi @alexmojaki

Thanks! I see what was the problem in my formula; instead of using $id i was using the actual name of the referenced column $referenced_column and that didn’t work. I just changed it to $id and it worked.

I guess this is a Python thing, which I’m 0% familiar with.

Will have to keep digging to failirize with these new formulas, thanks!

1 Like
4

Nice seeing you on the forum, David! :slight_smile:

5

To clarify, ASMA.lookupOne(Patient=$id) means "Find one record of the ASMA table where the Patient column has value $id". More generally, it’s trying to say “Find one record of the ASMA table where the Patient column refers to this record” and maybe a clearer way to say that would be ASMA.lookupOne(Patient=rec) which should also work. rec is a Record object, and $id means rec.id, the unique ID of that record.