Can I verify each entry in a multi-contact list exists in a different multi-contact list?
I use Dynamic View. Dynamic View, unfortunately, only allows 1 single column to be the list of users who can see data in a specific row. Smartsheet is not able to combine multi-contact lists into a single column that is recognized within Dynamic View. (I can combine multi-contact lists however, e-mail addresses listed in the combined list are treated as text instead of contacts, even though the column type is set to be a Contact List. Therefore, those e-mail addresses, when logging into Dynamic View, do not see the rows they are assigned).
As a result, I have a situation where vendors input data into my sheets using Dynamic View. There is a field where I ask them to include every person who will need access to the data. Unfortunately, not every person who enters data, is aware of who is identified as the primary contacts for their organization. As a result, I want to verify the primary contacts for that vendor, are included in the access list. As mentioned above, I would love to just concatenate the primary contacts with the e-mail of the person creating the row, and use that new multi-contact column as the access list for the Dynamic View, but SmartSheet isn't able to join contact lists and thus, I have to rely on the person creating the new row to enter in all the names of people who need access to that row of data.
Please be aware that not every person who works for the vendor should be accessing every row for that vendor. If that were the case, I would centrally house an access list and just use that for each new entry.
So my question is: is there a way to compare a multi-contact list from one sheet to a multi-contact list in a another sheet and return "True" if every person in list A is found in list B, regardless if additional names are also listed in list B?
Let's pretend Joe Johnson is working on a project with Diane James and is inputting their data into my Dynamic View. They put their names in and include their boss, Jane Smith, however they are unaware that John Doe is identified as a primary point of contact, and therefore, also needs access to their entry. As a result, I want to compare the list on the left and see if each person is included in the list on the right. In this instance, it should return false, and as a result, I could kick off a notification to the people on the left, with the new row's data, informing them that new data was entered that they both cannot access, and that they need to see who does have access and work to ensure the missing name(s) are added.
Using the FIND function, Smartsheet allows comparison of a cell in a multi-contact list with the cell of another multi-contact list, but it does not see each entry as an individual user, it sees the cell as a whole, and only looks to see if the entire cell is found within the target cell.
For example: I would get the following true/false result from comparing the 2 cells using FIND:
The top returns "TRUE" because the 2 names on the left are found in the list on the right exactly as they are listed. The bottom returns "FALSE" because the list on the right does not contain the names exactly as they are found in the list on the left
Is there a way to do this? Also, it should NOT be assumed that a vendor will only have 1 or 2 primary contacts. They need to have the ability to add any number of people they feel is required to access their Dynamic View data