Job and Contact tables both store an AccountN...

[Eric_Schwartz]

Job and Contact tables both store an AccountNo. 1. How to display a virtual column in Jobs, listing all Contacts with matching AccountNo? 2. How to display a drop-down in Jobs to select a designated Contact from among Contacts with matching AccountNo? Thx in advance

[Eric_Schwartz]

Hi @Suvrutt_Gurjar

Thanks for 1, which I’ll check out shortly. 2. On the Job form, will select a single account contact who is responsible for the job, from a list of account contacts. Thx so much.

[Eric_Schwartz]

Hi @Suvrutt_Gurjar, Reg. 1., Is it possible to display a functional table of records meeting the Where clause of the Select, the same as 'Referenced …" fields AppSheet creates?

[Suvrutt_Gurjar]

Hi @Eric Schwartz, Yes, for referenced records display

I believe

you

will need to use references between Job Table and Contact Table.

For example you may need to reference job number to relevant Contact numbers in Contact table ( assuming there are many contacts for a single job) . Then you can pull in all contact related record parameters (name, email, phone etc) into job table

and display as Contact records in tabular form.

Also there is a third parameter of Account Number whose relation with other two parameters

(Contact Number and Job Number) you may need to consider.

[Eric_Schwartz]

Hi @Suvrutt_Gurjar, This brings us back to the initial question, as both Jobs and Contacts reference the Account table. It is this indirect relationship which we wish to use as the basis of the queries, to 1. select in a drop down, and 2. display in a table, the Contact records, on the Jobs Details page.

[Suvrutt_Gurjar]

Hi @Eric Schwartz, Thank you

May we know a bit in more detail how

three tables are related to each other in terms of key ( and may be label fields)

For one account number,

there can be many jobs and for each job there can be many contacts? or is it for one job there can be many accounts and of course many contacts?

[Eric_Schwartz]

Hi @Suvrutt_Gurjar, The code you provided creates a simple list, while we’re manually trying to create the functionality provided by the system generated 'Related …'list fields.

Understood that the reference is via a non-key text column

Contact.AccountID, which may not be possible.

1. Pseudocode might be:

Populate Virtual List type field ‘Related Contacts’ with:

SELECT *

FROM Contacts

WHERE

Account ID=[_THISROW].[Account ID],

Would be a ref type drop-down field field, based on ,

SELECT * FROM Contacts

WHERE

Account ID=[_THISROW].

[Suvrutt_Gurjar]

Hi @Eric Schwartz

I went through the

entire post;s discussion

thread again. Based on my understanding, you may wish to

go through/ try below

1. How to display a virtual column in Jobs, listing all Contacts with matching AccountNo?

Response: We have already discussed that by using expression SELECT(Contact[Contact Name],[Account Number]=[_THISROW].[Account Number]), the contact names can be listed in one field as a list.

As per my understanding , to display contacts as

a referenced table in Jobs table, one would need a referencing between Jobs and Contact table. This referencing is especially necessary ,if one contact can be assigned to more than one jobs.

2. How to display a drop-down in Jobs to select a designated Contact from among Contacts with matching AccountNo?

Response: If the column name is say Responsible Contact, please enter the following expression in the valid_if setting of the column

=SELECT(Contact[Contact Name],[Account ID]=[_THISROW].[Account ID])

hope this helps.

[Suvrutt_Gurjar]

Hi @Eric Schwartz

You may wish to take a look at the following-

you may wish to try in virtual column

SELECT(Contact[Contact Name],[Account Number]=[_THISROW].[Account Number]) This will display list names of the

contact from contact table matching the account number of current row in Job table.

Hope this helps.

2. You may wish to give a few more inputs such as what you mean by “designated contact”? In short what condition qualifies a designated contact?Also does select designated contact mean just one column , say contact name as in 1 above?