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.
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
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:
You’ll see this formula in the ‘Any Premium?’ column of the Costumer table. For ‘Any Regular?’,
Premium='true' is replaced with
We use the same lookup as before but now add on the requirement that Premium must be ‘true’.
bool() returns either
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
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]
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.
true if this list exists, meaning if services were selected in the Premium 2 column, otherwise it returns
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]
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.