How to capture respective/related contact name from list of contacts (Parent, Child and Subchild) relationship

[pdorenberg]

Good day all,

Ok, so i have my app around 98% complete and everything works great with exception of one thing that I can’t seem to figure out (and would make things more intuitive for user)

I have 3 tables, and what I am trying to achieve is:
From the JobTask (Prime) table, capture (in jobtask table contactname field) one of the Contacts from the Company table (for just that respective Company).

Schema as follows:

1. JobTask Table with keys for JobTaskID(PK), CompanyID(FK), CompanyContactID(FK).
2. Company Table with CompanyID (PK), CompanyContactID (FK) and JobTaskID(FK)
3. CompanyContact with CompanyContact(PK), CompanyID(FK)

All tables have respective supporting name fields e.g., JobTask Name, Company Name, Company Contact etc.

Currently, from JobTask I can create the jobtask, select a Company Name from dropdown list (Ref to Company Table) and select CompanyContact Name field from dropdown list of Company Contacts (Ref to CompanyContacts Table).

What I would like to have happen is when I select Company Contact field (from Jobtask record) I only want to see the people (company contacts) from that respective company. Currently I see all names in the CompanyContact table.

I believe all my relationships from Parent->Child and Child->Subchild are fine however I am obtaining the Company field (from child Company table) and Contact Name (from child CompanyContact table). As well, I have virtual columns as List type setup for CompanyNames (in Company table) and CompanyContacts (in CompanyContacts table) which is required to be able to list company names or contacts.

My Challenge: From the JobTask->Company Contact field, I suspect I need an expression that references the Child-Subchild relation e.g., Company->CompanyContacts to pull back only the names from that company however I can’t seem to figure that out.

Any assistance would be most welcome.

Let me know if something is unclear.

Thanks again,
Paul