lookupRecords where column value IN multiple value

1

Hi,

I’ve a table with a ReferenceList column, and I’d like to have a formula that lookup records from another table that match any of the values of the ReferenceList column.

I’m looking for something like: OtherTable.lookupRecords(col=IN($myreferencelist)) (like we’d use CONTAINS).

In Django world, that would have been something like OtherTable.objects.filter(col__in=$myreferencelist).

Is there such a feature ? Or am I not looking at the problem in the right way ?

Thanks :slight_smile:

2

Hey there!

You can definitely do that. I created an example for you that pulls data in nearly every direction so hopefully one of the examples covers what you need :smiley:

Grist Doc - Community #1656

  1. If you want to pull from the table that the Reference List column points to, you can use the reference list column along with dot notation. The format for this would be $Ref_List_Column.Column_Name. You can see an example of this in the ‘ref_list color’ column of the ‘Reflist Table’. The formula here is:
$Reference_List.Color

We use the reference list column, labeled ‘Reference List’ to pull data from the referenced table (Names Table). Specifically, we want to pull data from the Color column of that table.

image
image1193×951 56.6 KB

  1. If you want to pull data in the other direction (from RefList to Names), you can use a lookupRecords function. You can see an example of this in the ‘Example List #’ column of the Names Table. The formula here is:
RefList_Table.lookupRecords(Reference_List=CONTAINS($id)).Example

This follows the format of [Table_Name].lookupRecords([A]=$[B]) where [Table_Name] is the name of the table you want to lookup data in. In this example, we want to pull data from the RefList Table. [A] is the column in the table being looked up (named at the beginning of the formula). In the example, we want to match a Name listed in the Reference List column of the RefList table. Then [B] is the column in the current table / the table you are entering the formula in. We want to match one of those names to the name listed in this row of the Names table. Last, we use dot notation to specify what value we want from the records found above. We want to pull the value from the Example column of the RefList table.

image
image736×903 41.4 KB

  1. Our last example pulls data into the RefList table from a table where no link exists, Table3. Meaning, there is no reference to Table 3 from RefList or from RefList to Table3. This requires a helper column in Table3. The formula found in the Helper column of Table3 is:
RefList_Table.lookupRecords(Reference_List=CONTAINS($Ref_column))

Using the lookupRecords format I described above, we want to pull data from the RefList Table where the Reference List column contains the value found in the Ref Column of Table3. This will return a list of records in the RefList table where the name in the Ref Column of Table3 appears in the Reference List column of the RefList table.

Next, we can use this to pull data into the RefList table. We pull the project associated with any names in the Reference List column from Table3. The formula found in the ‘Table 3 Project’ column is:

Table3.lookupRecords(Helper=CONTAINS($id)).Project

We want to lookup Records in Table 3 where the Helper column contains the $id from this row. For example, if you look at Table3 record for Anais, she is assigned to Project A. We see in the helper column that she is listed in two records from the RefList table - one with record id = 3 and the other with record id = 4. If you look at the RefList table, the two rows with ID 3 and 4 show Project A listed.

image
image1277×937 73.9 KB

I hope this helps! Please let me know if you have any questions.

3

Hi @Yohan_Boniface. When I implemented CONTAINS, IN was indeed also considered. It didn’t get implemented because there wasn’t a need for it at the time, but it would be nice to have, and I think easier to implement than CONTAINS. In the meantime, you could do something like this:

result = set()
for ref in $myreferencelist:
    result.update(OtherTable.lookupRecords(col=ref))
return result

(Apologies to @natalie-grist since I suggested the more complicated method 3 above)

4

Thanks you both for your answers! And special thanks for @natalie-grist for the detailed answer and document :slight_smile:

I’m indeed in the case 3: I’ve table A with a reference to table B, and table C referencing table A, and I want to lookup in table C from table B :slight_smile:

For now I’ll use a workaround, and I’ll see if I can implement an IN helpers :slight_smile:

5

@alexmojaki Just for information, is there a difference, performance-wise, between those formulas?

A

T_Cours.lookupRecords(Classes=CONTAINS($id))

B

tuple((c for c in T_Cours.all if rec in c.Classes))
6

Yes, lookupRecords uses an index behind the scenes to make things as efficient as possible. Iterating over .all should generally be avoided and can be a big performance problem even for medium-sized tables.

2 Likes