Filter compare question

Hi all,

I have a formula with a filter like this:
[Volledig Adresboek].Filter([Full Name] = thisRow.Contactpersoon).Email

[Full Name] = a text field in the Volledig Adresboek Table
thisrow.Contactpersoon= is a lookup value that looksup the Full name column mention before.

For some reason the formula does not see these values as the same but for someone like me they are.

Anyone know whats up?

Okay so I figured out I can use turntotext function and then it works!

3 Likes

You can most definitely use .toText() to transform your lookup value to a text value. In the long run though, it can be a bit more efficient to reference the text column itself rather than using .toText() which is a bit more of an expensive formula.

So instead of doing this: thisRow.ContactPerson.toText()
Try this: thisRow.ContactPerson.FullName

Let me know if you have any questions!

3 Likes

I will try this out asap!

This works perfectly but I am struggling to understand why as there is no defined function in there.
Could you explain why .Fullname works like that?

Working formula without totext =
[Volledig Adresboek].Filter([Full Name]=thisRow.Contactpersoon.[Full Name]).Email

Hey there,

Essentially, it is working because it is doing the same thing as .toText() does. This is because it is referencing an already stored text value within the row.

Here’s some further explanation
Notice that this here returns a blank value. Its not finding any matches. That is due to the fact that you are comparing two different data types. The data types of text and row value
image
In order to compare data as = they need to be of the same type (number to number, text to text, row to row). This was the first issue we addressed.

The solution is to match your data values. There are two ways to do that.

  1. Inefficient way - .toText(): This is inefficient because it is causing Coda to run another operation, the transformation of a value to another type. Notice though that it works, because it does return a value
    image

  2. Better way - Referencing an already stored text value. This is better because the value you are after is already stored in your document as text, no need to do an extra operation to transform it
    image

The key to understanding this is in the row value type seen here
image
That value is unique in that inside of it is stored all the data from every column of that row. So you can take the Bob row and reference any of the data within the row using your dot operator

For example, I can find his age or color, or any other data in that column
image

Hopefully that helps! Let me know if you have any other questions!

Also - So @Paul_Danyliuk doesnt roast to me to smithereens I should also say when you are using filter(), add .first() to the end so you end up with a single value, not a list of one value :wink:

3 Likes

Hi Scott,

Thank you for the great explanation! I must say that the way I get answers in this community is amazing and I hope to be able to contribute in the future when I get more proficient.

The first option is nice I will try and remember that one !

I still am kind of confused how the formula now knows it needs to find the row with Bob in it. I will understand it a bit better after reading it a few more time probably haha.

1 Like

Hey @Remco_de_Louw!

I’d strongly advise not to go with the first (.ToText()) solution, and generally not to use .ToText() with the single goal of getting values to match.

You should understand what row.ToText() does: it takes what’s currently a Display Column for that row and renders it to plain text. There’s many problems to this approach (e.g. you may want to change or reassign your display column and all matches will then break) but the worst one is that you quickly get into habit of doing this and each time you lose resolution into your data. E.g. someone adds the second Bob into your system and your filter formula will return the wrong one — but by that time you’ll have forgotten about this and you’ll catch problems with your data throughout the system after the damage is already done (e.g. the first Bob gets two paychecks).

The best way overall is not to filter by Name at all. Either already make a Lookup column to pick a row from table Names, or precautionarily implement your matching in such way that there’s as little room for error as possible — e.g. by full name at least, and add some indication that multiple people matched, to catch those situations as soon as possible.

@Scott_Collier-Weir — glad you remembered about .First() :slight_smile: For the ToText(), the problem is not efficiency but the fact that we a) depend on Display column value, which means we cannot then change it or everything breaks, and b) can get false matches if text values match, and risk not catching these until errors have already plagued the data.

1 Like

Hi Paul,

Not matching names is not an option and I struggle to see why you would suggest not matching something in the first place.

We have two tables where we want to lookup things in and the data that is the same and can be used to link data from one to the data of the other is the person’s name. I really so not understand why you would not use one of the most useful functions.

Not to use to text I can understand of course.

Could you elaborate more on why you suggest not to match names ?

Oh, if you know precisely what you’re doing, what are the implications, and there’s no other way — go for it. E.g. if you have some external automations writing to two tables and you are sure that the data is the same and unique (i.e. there’s not going to be two John Does in the system) then of course look up records by that same, unique and unchanging values.

But generally speaking, it’s best if you can design such a workflow from the ground up where you already know exactly which record is selected/linked.

Yeah - I want to echo what @Paul_Danyliuk said. Your lookup column really should contain unique values for the reasons he already stated, and just from a user perspective it makes it much simpler.

Say you are referencing your name table in a second table as a select list. This is an issue you might come up with:
image

Notice that I am searching for “B” and already come up with 2 bobs, and 2 Bills. Confusing to user. Of course, last names will help, but depending on your data set sometimes people have the same names.

I work with students a lot who are already assigned “Student ID Numbers” so sometimes I use that as an identifier. But thats also not easily searchable (lets be real, no one knows people by numbers). So sometimes I will make a new column called “Tag” or something similar that combines two columns information to ensure unique values and allow the user to easily locate.

Notice how I created a “Tag” column which uses concatenate() to bring in their age. Now when searching from a Lookup column its easier to distinguish. There can be other small drawbacks to this, but not going to go into all that detail now. Hoping this gives you enough direction to start thinking about UI, filters(), lookups and more!

1 Like

Ah yes I understand! The full name should be quite unique but you never know. I will see if I can think of something.

Yea I understand! We do use full names but we can combine it with something else to make it unique! Thanks for all the feedback this will surely help!