Reference to a Parent value, when creating a Child entry

[MMMiles]

I have what I believe is a syntax issue, but it may be another mistake I have made.

• I have a table Task, which has person_id , which is a Ref to table Person[id] (required "primary contact")
• The table Contact, is a part of Task, and each row has a person_id, which is Ref to Person[id] (optional "secondary contacts")
• Everything is working as intended, except when I want to validate the person_id of a child Contact

Task and Contact are created in the same form. person_id for the parent Task is always chosen first, and then child Contact entries can be added.

In Contact, my intention was to make the person_id VALID_IF =  "All Person, except the person_id of the parent Task"

[task_id].[person_id] appears to yield the correct value from the parent Task, but I don't understand how to use it in the valid_id formula. Or I am going about this in the wrong way.

Any help is appreciated

[Markus_Malessa]

Not sure if this will yield the proper result, but try the NOT() function like this:

NOT([task_id].[person_id])

[MMMiles]

/edit possibly solved

VALID_IF = Person[id] - LIST([task_id].[person_id])

Appears to produce the desired result.

------

That is one of the solutions I tried, assuming it would be that simple, but it does not work. In the valid_if it is returning a type Ref, and NOT fails (which I thought would have worked). 

Which leads me to believe I'm doing something else wrong.

But in a virtual column in the child form, [task_id].[person_id] does return the correct value.

Also this mess:

valid_if = NOT(IN([_THIS],LIST([task_id].[person_id])))

Produces a dropdown of all [person_id] and will correctly say "entry invalid" if you select matching [person_id] from [task_id].[person_id]

But I don't understand how to generate a list that excludes it in the first place.

[MMMiles]

Possibly solved?

This has produced the intended effect (all person, excluding the one selected in parent), but I would love to know if I am going about this the right or wrong way.

VALID_IF = Person[id] - LIST([task_id].[person_id])

-----

/edit going further:

Person[id]
-LIST([task_id].[person_id])
-SELECT(Contact[person_id],[task_id]=[_THISROW].[task_id])

Also excludes other child records correctly. (Although I was not concerned about this functionality working, I don't know if this is the right way to do it)

in the parent:

Person[id]
-SELECT([Related Contact][person_id],[task_id]=[_THISROW].[id])

Also correctly excludes any Person added as child entries

Am I using parent/child refs correctly?