Hi there,
Iโm struggling with an expression
I have a virtual column that generates a list from another table that is somehow associated with the original entry. Itโs not a parent-child relationships but more like finding the other parent for a child entry and this virtual column generates a list of all other parents that this first parent is associated with through a shared child. I have this list
I then have a Y/n variable on the second parent table and I would like to create a y/n variable on the first parent table to say whether the first parent is associated through their children to any the โYโ type of second parents. Think of it as a table of men with children with multiple different women and we would like to know as a y/n if this is a man who has a child with a women who lives in London
Contains() seems to only work if it is on the same table, and wont let me use the virtual column of list of women as a ref to the right column on the mother table
thoughts?
Olivia
Solved! Go to Solution.
The expressions I gave were for use in your app, but #VALUE!
is typically indicative of a spreadsheet problem. Can you provide a screenshot?
Whatever the case, your clarification is very helpful.
Instead of what I proposed above, try this as the app formula for a virtual column in the Fathers table:
ISNOTBLANK(
SELECT(
[Related Childs][_ROWNUMBER],
[Mother].[Lives in London?]
)
)
Why not use
select(table[virtual_column], [_thisrow].[key]=[key])
and then use contains on that list?
thanks - I tried that and it doesnt work on its own since you need a filter on the other parent table to only select the rows associated with this rowโฆ
CONTAINS() answers the question, โdoes this text contain that text snippet?โ For instance, CONTAINS("My name is Fred.", "Fred")
is TRUE because the text, Fred
, contains the text, My name is Fred.
IN() answers the question, โdoes this item occur in that list of items?โ
CONTAINS() is for looking for a fragment of text in a larger text value. IN() is for looking for a single value in a list of values.
Sounds like youโve got men and women split into separate tables, right? On the children table, is there a ref to one or both of the parent tables?
Yes its separate tables for the men and women so to speak. The child table has ref to both parents
Steve - how can you combine an IN() with a ref? I dont want to look in the key values for the mothers, but in the y/n variable of whether they live in London
Itโs tough to give you a concrete answer because my understanding of your data organization is so murky, but Iโll give it a tryโฆ
Assuming:
To answer the question, โWith which women who live in London does this father share at least one child?โ:
FILTER(
"Mothers",
AND(
[Lives in London?],
(
COUNT([Children] - LIST())
<> COUNT([Children] - [_THISROW].[Children])
)
)
)
If you already have a column (named Mothers) in the fathers table that contains the list of women with whom the father has a shared child, and want to know which of those mothers live in London:
SELECT(
[Mothers][RowKey],
[Lives in London?]
)
replacing RowKey
with the name of the key column of the Mothers table.
hmmm it gives an error #VALUE! when I set that up.
Maybe I can explain the data structure better:
On the Fathers table, Iโd like to create a y/n answering โDoes any of this manโs associated mothers live in London?โ
does that help?
The expressions I gave were for use in your app, but #VALUE!
is typically indicative of a spreadsheet problem. Can you provide a screenshot?
Whatever the case, your clarification is very helpful.
Instead of what I proposed above, try this as the app formula for a virtual column in the Fathers table:
ISNOTBLANK(
SELECT(
[Related Childs][_ROWNUMBER],
[Mother].[Lives in London?]
)
)
Awesome! this worked. I also had an error with one of the references that wasnt linking properly to the mothers table.
thanks for the help
User | Count |
---|---|
26 | |
25 | |
25 | |
20 | |
19 |