Photo by Manuel Will on Unsplash | Coding Look-Ups in Coda as a Pro

Coding Look-Ups in Coda as a Pro

Using the filter option via a formula

One of my early Coda aha moments was this thread . I did not yet fully understand the list and item logic, but I noticed something of great value. Before we dive into this, first a few words about the Lookup.

A Lookup brings in the values living in the display column of a table into an other column, mostly in an other table. However you can create a lookup in the same table. This trick is sometimes used on a list of people to tell who is boss of who.

Here we focus on a lookup that brings in the values into an other table. By now you might know that a Lookup permits for chaining. This is relating columns via the famous dot, like in the example below. The colors indicate values living in separate and thus different tables.

the _CVS part is a canvas formula chained to various properties

Chaining is the main reason to use a Lookup. Chaining makes it rather easy to get values by jumping from table to table. Before we explore a solution that enables us to show each time the right set of items per row, we have look at the solution of Joseph Winke we started with. I take the freedom to paste the logic in this blog post because it is well written:

There is a way to configure ‘Lookup Settings’ when you set a column to be a lookup type. Click into the column settings, then scroll to Item Settings. Then add a filter, and then a formula.

Now think of it this way — your formula is going to be looking at the display column from the table that you’ve selected as the lookup. So you need to limit the that list to not include any item that has already been selected. So how do you determine which ones have been selected?

In this example, we have the States table where each state can have a govenor selected. We want to get the list of selected govenors, and we can do that by getting the list States.Govenor.

Now to tie this back to the lookup filter, you use the currentvalue variable to tell the filter to check each item, and we want to see if the currentvalue is in() that list. This can be written as CurrentValue.In(States.governor).

OK but you don’t want it to show if its already been selected. We now reverse the logic by adding not() to the formula:

not(CurrentValue.In(States.governor))

In short: you can only select the items you did not use yet.

Do you notice something strange?

Well, I thought I did. I missed the filter function. Normally when you see the formula editor and you want to filter you go with something like this:

TableName.Filter(columnName.contains(thisrow.columnname).something

Not here and this is because you edit the inside of a filter or put differently you write an expression that outputs ‘true’. In the article about true & false, we explained how a filter works. You look for a match. In the above the match is set up by using In(). I would have used Contains(), but that would not make a difference. Both functions can be read as ‘are equal to’ and the Not() filters-out the not yet used items in the list.

Coda is not explicit about it, but if you open the item settings and you go for the filter option, you edit the inside of a filter.

By the way, this implicit logic you find also elsewhere inside Coda, like the IF when you set up automations.

This logic requires formulas that output something true()

Show only allocated values

I was setting up a CRM and noticed that the small company had marketing wise three entities, each with their own visual identity and products. Clients came in for one of these entities and got offered the services linked to these entities.

When it comes to invoicing, the first manual selection made is the client. Next you select the product(s) you invoice for. And here comes the tricky part. You only want to show the products & services linked to this client. Long story short, below the solution:

expression that filters for only related products & services

The providers are the entities inside the small company, via chaining I related them to the clients and thus the filter shows only products related to this client via the — in between step — of provider.

This part is only comprehensible when you see that an expression is required that outputs True() or False() . The moment you start writing a filter formula inside a filter, it goes wrong for obvious reasons.

One of the beneficial outcomes is that we can link VAT types to the product, since the product is a lookup. Without A lookup the below formula would be longer and harder to understand, likely also slower when dealing with larger tables. The formula below also takes into account the VAT logic by checking for the country of the client.

Conclusion

This blog was about the filter function inside a lookup. The lookup provides the user with a list of items and shows the values from the name column of the table selected. The filter function — as part of item section — deals with this list in which each item is referenced by as currentvalue. We have shown two methods to make better use of this option. One by filtering on this list of CurrentValues and second by chaining these currentvalues to get a relevant list of items.

I hope you enjoyed this article. If you have questions feel free to reach out. Though this article is for free, my work (including advice) won’t be, but there is always room for a chat to see what can be done. Besides you find my (for free) contributions to the Coda Community and on Twitter

My name is Christiaan Huizer and I am the owner of Huizer Automation. A company specialized in serving SME in harvesting data and keeping it aligned. I am a Coda Consultant & Expert and rely mainly on Coda, Mailjet, Zapier & Paperform to get the job done.

Huizer Automation — Coda Expert and Consultant on “Coding Look-Ups in Coda as a Pro