Hi again!
I have two tables with the following attributes:
Costumer:
Services
At the “Costumer” table I have those that did services and didn’t.
Thus, I would like to add a “column” at the “Costumer” table that shows if he/she is present at the “Services” table (has done any service), as a “boolean” attribute.
How can I do it, please?
Thanks in advance!
Eduardo
Hi Eduardo!
I created an example for you at the link below:
https://public.getgrist.com/7ZzSevrBhPoY/Community-783/p/1
The formula column I created is “Services” in the Costumer table.
if len(Services.lookupRecords(Name=$id)) > 0:
return "True"
We use the lookupRecords function to find all records in the Services table where the Name and $id match. We use $id here because Name is a reference column in the Services table so it points to the entire record in the Costumer table (not just the Name). We use len() to count the number of items in this list and if it is greater than zero (meaning matches are found), the formula returns “True”.
Perfect, Thanks! GRIST support is the best. Never disappointing! 
Hi Natalie!
Considering again the example above, there is any way that I have a “true / false” for a specific “service” or for a specific column? Is this clear?
Thanks in advance!
Eduardo
Hi Eduardo!
I think I know what you mean - I have updated the example document, linked below.
https://public.getgrist.com/7ZzSevrBhPoY/Community-783/m/fork
On the Costumer page, I have added some other “Services” columns. The three columns with “Services” as the header are using the same formula, just shown with different column types. This is the same formula as before - it notes whether or not a person exists on the Services table.
On the Services page, I updated the Service column to a choice column (“Service (Choice)”). On the Costumer page, I added a column “Service A (Choice)”. If Service A is listed in the “Service (Choice)” column for any records for this person in the Services table, this column will read as True. If you want true/false, you can change the type to toggle and cell format to textbox.
Then, I did the same thing but using a Choice List. If you need to select multiple services for a single record on the Services table, this would be the one to use. Then in the “Service A (Choice List)” column of the Costumer table, it will read True if Service A is selected in any record for this person in the Services table.
Hi Natalie,
Wonderful, thanks!
However, still one more doubt:
In your example, considering that in the “Services” table you have a column for “Premium Services” and other column for “Regular Services”. Each one with a different kind of choices.
I got the formula for the choices in the column. But there is a formula for the column with record. No matter what kind of service chose? For example:
Eduardo | hadPremium (true) | hadRegular (false)
Natalie | hadPremium (true) | hadRegular (true)
Thanks again!
Eduardo
Hi Eduardo!
https://public.getgrist.com/7ZzSevrBhPoY/Community-783/m/fork
I added Premium and Regular toggle-type columns to the Services table.
On the Costumer table, I added new formula columns that pull in data from the new Services columns.
For Premium:
Services.lookupRecords(Name=$id).Premium
This looks up all records in the Services table where the Name column matches the ID of the row in this table. Because Name is a reference column, it points to the entire record, not just the Name - this is why we use ID in the lookup. Then, we pull the value from the Premium column (or for Regular Services, we pull using .Regular).
This will list values for each record so if a person has multiple records in the services table, multiple values will appear as we see in the screenshot below for Peter.
If you’d just like it to say “True” if they have ever provided a Premium (or Regular) Service, use the following formula:
bool(Services.lookupRecords(Name=$id, Premium='true'))
You’ll see this formula in the ‘Any Premium?’ column of the Costumer table. For ‘Any Regular?’, Premium='true' is replaced with Regular='true'.
We use the same lookup as before but now add on the requirement that Premium must be ‘true’. bool() returns either true or false. If a record exists where the ID in the Name column matches the row ID in this table AND Premium is true, the formula will return true. If no record exists that matches both criteria, it returns false.
Going a step further, Premium 2 and Regular 2 in the Services table contain different service choices. Premium 2 is a Choice column and Regular 2 is a Choice List column. The corresponding Premium 2 and Regular 2 columns in the Costumer table show if any choice(s) was selected in the services table.
The formula in the Premium 2 column of the Costumer table is:
list_premium_services = [c.Premium_2 for c in Services.lookupRecords(Name=$id) if c.Premium_2]
bool(list_premium_services)
Because lookupRecords creates a list of records, we end up with a list inside of a list > we are listing all of the choices selected for Premium 2 for each record in our list of records. We use Python list comprehensions to flatten the list of lists.
If you just enter [c.Premium_2 for c in Services.lookupRecords(Name=$id) if c.Premium_2] into a column, you’ll get a list of Premium 2 services for each person (You can see this in the Premium 2 List column of the Costumer table).
I assigned this to the variable list_premium_services. bool() returns true if this list exists, meaning if services were selected in the Premium 2 column, otherwise it returns false.
The formula in the Regular 2 column of the Costumer table is slightly different because Regular 2 (Services table) is a Choice List column. The formula here is:
list_regular_services = [x for c in Services.lookupRecords(Name=$id) for x in c.Regular_2]
bool(list_regular_services)
Again, we flatten our lists to create a single list, list_regular_services then use bool() to return true if items exist in our list.
Hi @natalie-grist ! Thanks for this, but we are not there yet. I’m sorry for not been clear.
In your last example (Grist), at the table “Costumer” you have “Any Premium” and “Any Regular”, which is perfect. However, it replicates a manual checked column at the services table.
What I would like to have is that “Any Premium” and “Any Regular” is an automated filled boolean column at Costumer table, which represents that there is an entry at Services table, for any kind of “Premium 2” or “Regular 2”.
In other words, can “Any Premium” and “Any Regular” at Costumers exists (boolean) without having to check manually the Services columns “Premium” and “Regular”? Automatically from “Premium 2” and “Regular 2”?
Thanks!
Eduardo
Hi Eduardo!
I created a new page with cleaned-up version of our tables but with just the columns I think you’re looking for
https://public.getgrist.com/7ZzSevrBhPoY/Community-783/p/3/m/fork
In the Services table, you could add a Name then what service they did in either Premium or Regular Service columns.
Then, the Costumer table will automatically update to reflect true or false to premium or regular services.
I also created an example where only one column of Services exists on the Services table (Table2).
https://public.getgrist.com/7ZzSevrBhPoY/Community-783/p/5/m/fork
In Table2, you would create a new row for someone then add the specific services they did in the Service column. Then, Table 1 will update with true or false for each premium and regular. For this to work, “Premium” or “Regular” need to be in the service name so it can differentiate.
Hi Natalie! That’s it! I’m very happy! 
Thank you very much indeed. Very elegant and beautiful solution. GRIST is a wonderful tool.
Eduardo
My dear @natalie-grist ,
Now that I got this, How do I transform those IDs in the value at the referenced column:
I used this: “=[c.Taxa for c in Colaboracao.lookupRecords(Nome=$id) if c.Flora]”
The value for “Taxa[n]” is in “Taxa” table.
Thanks in advance!
Change this column type to “Reference List” pointing to that column of the Taxa table
So obvious! Thanks!
Hi @natalie-grist. Something is wrong with my Document…
I´m trying my best to follow your lead. Please be patient with me 
I have this table called “TAXA”, whit this record:
I also have this table called “ESPECIALISTA” (expert) with this recod:
Then, I have a third table where I link the expert with his “taxa”:
Now I want to show a list of the “taxa” at the ESPECIALISTA table, using this formula:
=[c.Taxa for c in Competencia.lookupRecords(Nome=$id) if c.Flora]
And this is what is shown:
It suppose to show the link with the value “Aa” at the third table!
What I am missing, please!
Thanks in advance
Eduardo
Hi Eduardo!
Just change c.Flora to c.Taxa and then you should see ‘Aa’ for Aclebia.
[c.Taxa for c in Competencia.lookupRecords(Nome=$id) if c.Taxa]
I added this in our example as well.
https://public.getgrist.com/7ZzSevrBhPoY/Community-783/p/8m/fork
Yes @natalie-grist . True. But I forgot the details. 
Note: [Table] and {column}
I’m trying to link “experts” to his “competences”. I have a list of “Areas of competence” [TAXA] table, a list of “experts” [ESPECIALISTAS], and the link table [COMPETENCIA]. You got that already.
At the [COMPETENCE] I have two projects: {Flora} and {CNCFlora}.
Someone may have one competence for one project but not the same for both. Like this:
Or have the same competence for both projects:
Thus, I would like to show a list of competence for each project at the [ESPECIALISTAS], like this:
It worked once, following you tips, but I had to update/change the [TAXA] and now the list are not matching.
I’m sorry for ask for that much of attention…
Should I apply for the special program that you guys have that we have time together?
Should I share my doc with the support again?
Eduardo
Can you share the document with support@getgrist.com? I’ll work on a copy so I won’t make any changes to your working document!
Hi Eduardo!
competenciasFlora needs to be updated to pull from the taxa column of the Taxa table. It’s currently pulling from TaxaOld
Once you update, it will pull correctly. Here you see it shows the correct competenciasFlora for Abel.
Be sure to update competenciasCNCFlora as well to pull from the Taxa column of the Taxa table, rather than TaxaOld. Then that column will pull correctly as well.
Let me know if you run into any other issues. Once everything is working as expected, be sure to remove support@getgrist.com from the document.
Thanks!
