Filter Catches some, but not all matches

HellO!!!

Am trying to create a filter pulling information from another table by matching emails. Everything was going GREAT, and then for some reason the filter recognizes some emails as matches and others as not. Same code, same email tests, but because of the letter it is not recognizing the match. Any help would be awesome.

I first made a new column in two tables to make the email lowercase and totext()
email linked → email
thisRow.[Email Linked].ToText().Lower()

filter:

[Lead Assignment].filter(Email.ToText().Lower()=thisRow.Email).[Phone Number]

So it’s weird that b@gmail.com = b@gmail.com → true ; but g@gmail.com = g@gmail.com → false

Hey @Carson_Townsend

See example document and explanations below! Hopefully that helps

Also, take a look at the yellow column below, it is another way you could write your look-up column if you really don’t want to run into the bug again.

1 Like

Hey @Scott_Collier-Weir THANK YOU SO MUCH!

Such a rookie mistake, thank you for catching that so quickly.

Follow up question, I did consider the lookup() because we are looking at pretty large datasets in the future, but I noticed on the Coda formulas they recommend filter (read last sentence at output:

This also might be a rookie question, but why rec filter over lookup?

Hey @Carson_Townsend ,

I might have been misleading calling my column a Lookup column because it is actually not using a lookup() formula, it is using the table.filter() formula.

My guess would be that table.filter() accomplishes the same function but processes more quickly and does not slow down your document as much? Honestly not completely sure though!

1 Like

You don’t need relatively expensive Regex replacement/matching — your problem was extra spaces after the email. Using Trim() removes these. This works:

[Lead Assignment].Filter(Email.Trim()=thisRow.Email.Trim()).First()

Oh, and don’t forget to .First() your filter result, otherwise you’ll get a list-of-one instead of just one item, which will result in issues down the road. That’s another mistake everyone makes. See here:

3 Likes

Amazing!! Thank you so much!! This is going to be so helpful moving forward