Reference list problem

Ask for Help Formulas
#1

Hi Guys,

I am sure there are some easy solutions to this problem. I have three tables clients list, campaigns list, and campaign request form list. I want to lookup records. The problem is that my column type is a reference list. Few days ago, i was stuck with similar problem but found a solution to add contain formula in the lookuprecord formula. However that was mathematical problem. Now I want to show all the client names that are associated with a client request id. here is the formula; $Requisition_Form.Client_Name.Clients
The formula works as soon as I change the type to reference collumn instead of a reference list. Any idea how to do this?

#2

Hi!

I don’t have a clear picture of how your tables are related, but generally if you have, say, a Reference List column “Clients” in a table Forms, then it would contain a list like [Client1, Client2]. In the other direction, it means that a single Client may be associated with multiple Forms (e.g. it may be in the list of both FormA and FormB). To look up which forms include the client in the reference list, you’d use the formula:

Forms.lookupRecords(Clients=CONTAINS(rec))

So if for a single-reference column you have a formula like

Forms.lookupRecords(Clients=rec)

when you change the formula to a reference-list, replace rec with CONTAINS(rec).

If this doesn’t help, maybe you could make an empty copy of your document (using copy as template), share it publicly, and post a link here, so that we can understand better the structure you are going for.

#3

Hi dmitry, thanks for the support.

I have made a public copy. here is the link http://139.180.223.0:8484/o/docs/vqpBMQrvGnSd/Outreach-Demo

I need to lookup client names in the campaign table. client is connected as reference list to the requistion form table which is connected to campaign table as reference list as well.

Your help is appreciated.

#4

Ah, I see the issue. You have a ReferenceList column Requisition_Form, and each referenced record in it has a ReferenceList column Column_Name. So $Requisition_Form.Client_Name is a list of lists.

Grist allows the dot-notation to be applied to a direct ReferenceList, but here it’s a level deeper, so the dot-notation no longer works for $Requisition_Form.Client_Name.Client. The solution is to use Python list comprehensions to flatten the list of lists:

[c.Clients for f in $Requisition_form for c in f.Client_Name]

In your example, such a list may have repeating clients. To make the list unique (remove duplicates), you can use a Python set, constructed like a list comprehension but using curly braces:

list({c.Clients for f in $Requisition_form for c in f.Client_Name})

Flatten into a ReferenceList

Both expressions above give a list of client names. Alternatively, you could flatten the list of client references into an actual ReferenceList like so:

{c for f in $Requisition_form for c in f.Client_Name}

If you then change the column type to a ReferenceList to table Clients showing the column Clients, then you will see the list of client names rendered as a usual reference list.

2 Likes
#5

Thank you very much Dmitry. I am loving grist so much that I have recommended to our CEO that we shift our operation to grist from airtable. However, the filter thing needs some touch to be user friendly for common folks who are not so much good at using different tools.

I am regularly following you guys. Eagerly waiting for the next webinar.

1 Like