EnumList Work Around

[Srikanth_S]

Hi, 

I have 3 tables.

1 ) Site 

2) Contacts

3) Log

When my staff visits a site, he may contact multiple people and puts a single log.  

I am looking for a solution where the Log has a field where multiple contacts can be selected. I know, this is possible with EnumList. 

However, if the contact records are zero at the moment (in case of a fresh site), then the contacts have to be added first in a separate form and then multiselect is possible. In case there are no contacts, the contacts field in Log form is not visible (as there are no records, no select is possible). 

Is there a workaround, where Addition can be possible at the EnumList while using a Ref Column from Contacts table.

Regards, 

Srikanth. 

[Joseph_Seddik · Accepted solution]

Assuming Contacts table has a ref column "siteID" pointing to table Sites, i.e this is how you assign a contact to a site, and the Log table also has a reference to Sites, where the user can select a Site in a form then select from Contacts assigned to this site using the EnumList column "siteContacts", then you can do the following:

• In Suggested values of "siteContacts", use the expression: 
  
  SELECT(Contacts[contactID], [siteID] = [_ThisRow].[siteID])
  
  Here the user will be presented with a list of Contacts assigned to the selected site. If there are none, the user will see an empty list as well as an Add allowing him to create a new contact. 
  
  
• If you still want to hide this field from the form, then you can put the following expression in the Show? filed of the column:
  
  ISNOTBLANK(SELECT(Contacts[contactID], [siteID] = [_ThisRow].[siteID]))

[Srikanth_S]

Thank you. I was using the select statement in "Valid if", instead of in "Suggested Values".

Regards, 

Srikanth.