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

[Suvrutt_Gurjar]

Hi @pdorenberg,

If I have correctly understood your requirement, could you please try following expression in the Valid_If constraint setting of the of the CompanyContactID column of the JobTask table

SELECT(CompanyContact[CompanyContact], [CompanyID]=[_THISROW].[CompanyID])

The expression assumes that the user first selects the Company Name in the JobsTask table before selecting the Company Contacts.

[pdorenberg]

Hi Suvrutt,

Thanks for your response. Apologies for delay as I was trying couple of things as well.

So I just updated the CompanyContactID (in the Valid_if constraint) and although it tests and runs successfully, it does not seem to pick up just contacts for respective company.

Pardon typo on my part re: Company vs Contact name but I updated accordingly.

SELECT(CompanyContacts[ContactName], [CompanyID]=[_THISROW].[CompanyID])
The list of values of column ‘ContactName’
…from rows of table ‘COMPANYCONTACTS’
…where this condition is true: ((The value of column ‘CompanyID’) is equal to (The value of ‘CompanyID’ from the row referenced by ‘CompanyContactID’))

However I still see all contacts from Jobs table->Company Contacts

I think i need to step away for a few, I’m going in circles.

Any other suggestion would be gratefully appreciated.

Thanks,
Paul

[Suvrutt_Gurjar]

Hi @pdorenberg,

My quick testing shows that it works as suggested with Valid_if constraint. However there could be mismatch in what you mention as primary key, foreign key and my understanding in terms of standard relevant AppSheet terms of key, reference and reverse reference columns.

Is it feasible that you could mention relationship between three tables in terms of AppSheet terms, that could help us ?

[pdorenberg]

Good day Suvrutt,

Thank you for the update. Sure I can provide more info.

I have a primary key for each of the of the 3 tables based on their function. For example, JobTask has JobID, Company has CompanyID and CompanyContacts has CompanyContactID. (as you can create Companies and Contacts separately from the Job form)

Regarding relationships, JobTask table has JobID as primary key but also contains CompanyID and CompanyContactID. So the relationships in summary are as follows:

JobTask (Parent) - Company (Child)
JobTask (Parent) - CompanyContact (Child)
Company (Parent) - CompanyContact (Child)

Using those 3 tables, as mentioned above, you can create a Job, a Company or Company Contact. JobTask is the main “landing” page/workflow that lets you create a job and then associate that task to a Company and associated Contact as you’re creating job.

From the Job table/form, you enter in details, and choose from drop down lists for Company and Company Contact. Choosing Company drop down shows existing Companies or lets you create a new Company (all good). Choosing Company Contact drop down lets you either create a new Contact (to associate their Company) or pick from the list of existing users (which displays all users for all companies - this is my issue here - as I only want to display contacts from the company I’ve selected or create a new contact for that company).

Desired behaviour would be if I select an existing Company (Company1), then choose a Contact drop down (post Company1 selection), I would have the ability to either select from the existing contacts of Company1 or create a new Contact (basically the same way Company behaves (however we’re pulling in contact info from another table.).

Regarding your question re: mismatch of keys, that is possible as JobID is the primary key of JobTask table, however I also store/use CompanyID and CompanyContactID as foreign keys in Jobs table to be able to associate Companies and Contacts.

This mismatch may be that it is picking up the JobTaskID as the key (as the parent) to associate with Company and Contact instead of just having Company and CompanyContact being associated (via CompanyID in Jobs and CompanyID in CompanyContacts).

Let me see if I can alter your statement you provided yesterday to see if I can switch it to use just CompanyID as the link between JobTask and CompanyContacts tables (instead of it defaulting to JobID).

Let me try that and get back to you.

Thanks again

[Suvrutt_Gurjar]

Hi Paul

Thank you for all the details.I appreciate.

Is it correct understanding that Company ID ( referencing Company table) and CompanyContact ID ( referencing CompanyContact table) are Reference type columns in the JobTask table?

Also Company ID (referencing Company ID) is a Reference column in CompanyContacts table?

The above is from a quickly stitched test app.

[pdorenberg]

Hi Suvrutt,

Yes, you are correct. Here is my screenshot of the Jobs table.

Company references Company Table
Company Contact references CompanyContacts table.

I tried updating the statement to this:
SELECT(CompanyContacts[ContactName], [_THISROW].[CompanyID]=[CompanyContactID].[CompanyID])

but no affect as I still see all company contacts for all companies.

[Suvrutt_Gurjar]

HiPaul,

Ok, thank you. In the meantime , based on your earlier posts and picture shared , I suggest you toplease try the expression mentioned below.

I believe your Valid_if expression of column Company Contacts in Jobs table should contain following

SELECT(CompanyContacts[Key column Name of CompanyContacts], [Column name in CompanyContacts table that references Company Table]=[_THISROW].[Company Name])

Please replace thw column names ofhighlighted text with actual column names you have.

[pdorenberg]

Hi Suvrutt,

Based on your post, I updated new statement to be this (from Contact Name field - in the Jobs table).

SELECT(CompanyContacts[CompanyContactID], [Company Name]=[_THISROW].[Company Name]).

BINGO!!!

I think we have a winner

I’m just doing some testing to validate but I think you have sorted it out for me! Thank you very much.

This is definitely good experience with AppSheet, I have learned a lot over the last week or since joining onto AppSheet platform and team, you guys rock.

I’ll provide another update after i finish my testing, then I’ll be rolling out to production.

Once again, thank you very much your time in helping me through this.

Onwards and upwards!

Paul

[Suvrutt_Gurjar]

Hi Paul,

Thank you for the update. Please do test it well as per your requirement.

[pdorenberg]

Hi Guvrutt,

Actually, I just noticed that I don’t think I actually do have an FK for CompanyContacts (CompanyContactID) in the Jobs table!

I think that may be the issue and my early miss when I was mapping out the tables.

Let me try adding that to see what happens, please stand by.

[pdorenberg]

Hi Suvrutt,

Quick update, I noticed when using the statement from yesterday and looking at the data it’s pulling, I think the key mismatch may be the issue.

Here is the statement that I see when running the statement:
SELECT(COMPANYCONTACTS[ContactName],(CompanyID]=[JobID].[CompanyID]))

I’m still coming up to speed on things within AppSheet so may not have fully picked up on this, that _thisrow inherently picks up the rowid in use - which makes total sense if I was looking to have Job and CompanyContact as the link but I’m using CompanyID as the link between Company and CompanyContacts. I’m going to update statement to reference CompanyID instead of JobID.

Please stand by.