Suggested if values based on parent relations

[paullynun]

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.

[Steve]

Try:

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

[paullynun]

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

[Steve]

paullynun:

I have used this similar expression in an similar case. The expression test is positive with the expected results. Unfortunately the form seems to ignore the suggested results.

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

[paullynun]

Hi @Steve

My bad! Disregard this last one.

[paullynun]

Hi @Steve

Steve:

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

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?

[Steve · Accepted solution]

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.

paullynun:

My actual understanding is that the expression runs from a CHILD table

Correct.

paullynun:

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.

[_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.

[paullynun]

Hi @Steve.

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