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
Grist Doc - Community #1656
- 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:
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.
- 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:
This follows the format of
[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.
- 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:
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:
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.
I hope this helps! Please let me know if you have any questions.