Suggested if values based on parent relations

Hi,

Is the following impossible?

Let’s says I have a CHILD table with two parent tables: MOM and DAD. When filling a CHILD form I would like after choosing a DAD to suggest MOM records that would insure no child would be from the same MOM and DAD.

Try:

FILTER("MOM", TRUE)
- SELECT(
  CHILD[MOM],
  ([_THISROW].[DAD] = [DAD]),
  TRUE
)

Hi @Steve.

It seems like you do not even break a sweat when you do that!

I could figure this out after a long time on it. This is smart.

Thanks!

For the record, mine looks like this:

FILTER(“Parcours”, TRUE) - SELECT(
Affectations[Parcours ID],
([_THISROW].[Opération ID] = [Opération ID]),
TRUE
)This text will be hidden

1 Like

Please provide a screenshot of the column configuration screen, and of the complete Suggested values expression.

Hi @Steve

My bad! Disregard this last one.

1 Like

Hi @Steve

After a deeper look at your expression, trying to add to it, I realize that I am confused with logic behind the condition of the SELECT.

My actual understanding is that the expression runs from a CHILD table so [_THISROW] = CHILD and [DAD] is a REF column from CHILD table pointing to the DAD record. So [_THISROW].[DAD] = [DAD] appears to be the same thing to me.

Would it be too much asking you to come down at my level of ignorance to enlighten me?

All moms, minus moms of children with this dad, leaves only moms of children with some other dad.

  1. FILTER(“MOM”, TRUE) gives a list of all moms.

  2. SELECT(CHILD[MOM], ..., TRUE) gives a list of the moms of all children who meet the given criteria (...; see (3)).

  3. ([_THISROW].[DAD] = [DAD]) matches only children whose dad is the same as the dad identified in the form.

  4. FILTER(...) - SELECT(...): All moms (from (1)), minus moms of children with this dad (from (2)), leaves only moms of children with some other dad.

Correct.

[_THISROW] refers to the row of the form. Therefore, [_THISROW].[DAD] is the value of the DAD column in the form. The bare [DAD] refers to the DAD column value of each CHILD row the SELECT() function examines.

3 Likes

Hi @Steve.

Thanks for this very detailed explanation. So my interpretation of [_THISROW] was wrong and now it makes sense!

1 Like