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:
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:
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.
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.
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.
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:
I tried to “fake it” using the common formula coda uses to do that:
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:
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.
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.
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].
Now, I’ll try to append the [Spots Visited] column and see what happens.
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)
Yeah, you right.
The structure is actually based on three tables:
- Places: Where I save houses and spots
- House usage: Where I track the utilization of the houses saved in [Places]
- 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:
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.
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:
(or something similar) might also work, depending on database construction
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.
This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.