Set a checkbox true if the name of the row is present in another table

1

Hi everyone. I’m kinda ashamed because this looks simple, but I still have not figured out how to do this.

So, I’m a nomad, and recently I started to create such a structure to track this lifestyle.

I have this table (called Footprint ) who tracks where I’ve been, which house was used and, which spots was visited among other stuff:

image
image2290×1720 418 KB

Houses and Spots are saved in this another table called Places.
At Places , I have this checkbox collumn so I can track wich ones I’ve visited and I want check this automatically if this Place (House or other) is present at Footprint.

I was trying do this using this formula:

Footprint.[Houses Used].Contains(thisRow.Name)

But as you can see below, it’s just checks if the column Houses Used have a single value. I tried using listCombine() in some ways but I never got the expected result.

image
image1920×994 139 KB

PS.: I was using the Houses Used column just for figuring out, but it supposed to consider both collumns Houses Used and Spots Visited.
I tried to abstract something from this topic, but no success at all.

Thank you!

2

You might try creating a new (hidden) column in Places “Referenced By Footprint” (should be an automatic option). This will create a formula that shows you which Footprint rows reference Places in Houses Used.

Then, for your checkbox, you can use [Referenced By Footprint].IsNotBlank()

Otherwise, you might use Footprint.filter([Houses Used].Contains(thisRow.Name) OR [Spots Visited].Contains(thisRow.Name)).isNotBlank which has the added benefit of also looking at the Spots Visited column.

3

Hi @Billy_Jackson, thank you for your time!

I don’t know if I’m missing some detail here, but those options unfortunately doesn’t worked for me.

The first option
As you can see below the “Referenced By” option doesn’t appears when I create a new column:

image
image2438×1398 374 KB

I tried to “fake it” using the common formula coda uses to do that:
Footprint.Filter([Houses Used].Contains(thisRow))
But no success as well.

I din’t mentioned before and, I don’t if it is the root of the problem, but I’m getting [Houses Used] using a formula:

image
image2468×1540 484 KB

The second option
Using the formula
Footprint.filter([Houses Used].Contains(thisRow.Name) OR [Spots Visited].Contains(thisRow.Name)).IsNotBlank()
Sadly, all I got is false for all rows.

image
image2372×1456 511 KB

4

Maybe I found a solution…

I got inspired and tried using this formula:
If(Footprint.filter([Houses Used].ContainsText(thisRow.Name)),True() ,False() ) that worked correctly for almost all rows, but I got an error for some specific rows.

image
image1920×1239 173 KB

After that I tried to simplify the formula and I got this one:
If(ListCombine(Footprint.[Houses Used]).ContainsText(thisRow.Name),True() ,False() )
And it’s working considering only the [Houses Used].

image
image2346×1392 446 KB

Now, I’ll try to append the [Spots Visited] column and see what happens.

Updates soon.

5

Hmm. It seems like there’s another table in the mix here that’s potentially causing some trouble (House Useage). It seems like maybe the entities inside of the list “Houses Used” are NOT rows from the table Places, which is why it’s returning false until you change it ToText. They are - in fact - rows from House Usage. Can you show me the House Usage table?

My suspicion is that the filter we’re looking for matches [House Usage].[The Column Referencing Places Inside House Useage].contains(thisRow)

6

Yeah, you right.

The structure is actually based on three tables:

  1. Places: Where I save houses and spots
  2. House usage: Where I track the utilization of the houses saved in [Places]
  3. Footprint: Where I track my movement, the cities where I’ve been (or planning to).

And yes, the “Referenced By” option is available in [House Usage]

You can see all here:

7

About the checkboxes;

I wrote this formula:
If(ListCombine(Footprint.[Houses Used]).ContainsText(thisRow.Name) OR ListCombine(Footprint.[Spots Visited].ContainsText(thisRow.Name)),True() ,False() )

And it’s working. Now, if the row appears in [Footprint] as House or Spot the checkbox becomes true.

8

Great!
Slightly cleaner might be:

Footprint.Filter([Houses Used].Name.contains(thisRow) OR [Spots Visited].Name.Contains(thisRow)).isNotBlank()

I’m slightly unsure of the [Spots Visited] Part, because I’m not quite sure what that column references, but this formula basically finds all the entries in Footprint that include that row in Places via reference from the House Useage Table

You could also just go direct to House Useage:

HouseUseage.filter(Name.contains(thisRow)).isNotBlank

(or something similar) might also work, depending on database construction

9

On second though, I think maybe this should be:
Footprint.Filter([Houses Used].Name.contains(thisRow) OR [Spots Visited].Contains(thisRow)).isNotBlank()

If the [Spots Visited] column references Places, this update is correct. if it references House Useage, my initial formula is likely correct.

10

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.