Combining contains() with a ref

[Olivia_Bryanne1]

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

[jonas_dont]

Why not use
select(table[virtual_column], [_thisrow].[key]=[key])
and then use contains on that list?

[Olivia_Bryanne1]

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…

[Steve]

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.

[MultiTech]

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?

[Olivia_Bryanne1]

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

[Steve]

Olivia_Bryanne_Zank1:

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:

• The table that describes the mothers is named Mothers.
• The Mothers table has a column named Lives in London? of type Yes/No.
• The Mothers table has a column named Children of type List (or EnumList) containing identifiers of the corresponding woman’s children.
• The fathers table also has a column named Children of type List (or EnumList) containing identifiers of the corresponding father’s children.

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.

[Olivia_Bryanne1]

hmmm it gives an error #VALUE! when I set that up.

Maybe I can explain the data structure better:

• Child table, has ref to mother and father
• Father table, has column of list type which are the mothers of his children, called “associated mothers”
• Mother table, has column of y/n type called [Lives in London?]

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?

[Steve · Accepted solution]

Olivia_Bryanne_Zank1:

it gives an error #VALUE! when I set that up.

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?]
  )
)

[Olivia_Bryanne1]

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