Can I verify each entry in a multi-contact list exists in a different multi-contact list?

Ryan Michalski

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?

For example:

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

Genevieve P.

Hi Ryan,

I can't think of a way to compare one multi-select cell with another one that has additional values in this way. There's another thread where Paul has discussed the request for a PARSE function in Smartsheet, and we've submitted Enhancement Requests for this (see here), which I think is what we would need in this case... we'd need to break out the contacts in the first column and search for them individually in the second column. Please submit an Enhancement Request for this as well!

The only thing I can think of to ensure that these primary contacts are included in the view would be to create separate views for each vendor. You could set up the default value for the contact column to have the correct Primary contacts for each vendor added in already (selecting different emails to be the default depending on the view/vendor). Then when users go to create new submissions in their view, the first emails will be pre-populated and they only need to add in the additional viewers.

I'd be interested to see if anyone else has other ideas!

Cheers,

Genevieve

Ryan Michalski

Thanks Genevieve. I've thought about building out a 2nd view, the problem is that views cannot be copied/duplicated, enabled/disabled, or spun off from a master view. The sheet the view is built off of contains 150 columns, and the view itself utilizes 105 pieces of logic. Duplicating the view, seems like it would be too time consuming, and allow for a situation where the 2 views can get out of sync. That doesn't seem worth it just to allow for a 2nd group of individuals access to the view. If only Dynamic Views could be shared to multiple contact columns simultaneously...

Genevieve P.

Ah, I see - yes that's a lot of logic to duplicate without a copy function...but I do think it might be the only option to make sure that the correct contacts are always being selected.

Both of these are good ideas, so please do submit your feedback to our Product team with the form above.