Restricted view in dynamic view. I want to use multiple e-mail addresses to restrict access.

Hilbert Kok

I have created a reference sheet where per account the e-mail addresses are joined i.e. =JOIN(CHILDREN([Primary Column]@row); SUBSTITUTE([carriage return]@row; "-"; "")), In the Carriage return column I have a - CR-.

I have multiple sheets that are using forms to receive data. Based on the entered data, the "account" field in the form is used in a VLOOKUP looking for the account in my reference sheet and use the field that contains the joined e-mail address and copy it in field "Account access e-mail"

a workflow checks in the sheet if an answer is valid and if not copies the row to an action list.

The action list is linked to a dynamic view and the dynamic view is linked to a dashboard. The dynamic view is restricted for users in the "Account access e-mail" field.

So far so good, but for some odd reason the e-mail address that i copy over to the actionlist are not recognized as e-mail address. whatever i use as a separator, space, "-", CR, " " it is not working.

What am I doing wrong?

I must say I have had more problems with e-mail addresses that are not recognized as e-mail address.

Can anybody help me with this challenge?

Thx

Hilbert

Paul Newcome

It is currently not possible to use a formula to output multiple usable contacts in a single cell. Please do Submit a Product Enhancement Request when you have a moment as this would be a really great feature to have.

Additionally, you say that your carriage return is not working. Have you turned on text wrapping in that column? Another note for this... You can get rid of the helper column and use CHAR(10) instead.

=JOIN(CHILDREN([Primary Column]@row); CHAR(10))

Hilbert Kok

solved the problem using HEX 0D as a CR in the formula.

=JOIN(CHILDREN([Primary Column]@row); " ")

Paul Newcome

Which problem exactly? Outputting multiple usable contacts or just the carriage return?

Hilbert Kok

The outputting of the multiple e-mail now works, with the CR the e-mail is recognized as e-mail address in the multiple address field, I'm happy I solved it myself, ;-) thx

Paul Newcome

Is it a single email address, or do you have multiple usable email addresses in the same cell? If the second... Can you please explain more on "HEX 0D" as well as the rest of your solution such as what is in the Primary Column?

This is something that many users have been trying to accomplish for quite some time now, and if you were able to solve this particular riddle it could be quite a game changer...

Hilbert Kok

Hi Paul,

It is multiple email addresses, i had it working but for some odd reason smartsheet is now not recognizing the e-mail address in the multi field. And as such the restricted view function in dynamic view is not working. I need to restrict the users of the report based on which account they are working for. so i created a reference sheet with account and employees, using the above mentioned formula with a 0D separator to add a Carriage return so every e-mail will be on the next line. The column setting is contact and allow multiple contacts in field. The data is then copied using vlookup into multiple sheets. The dynamic view restriction is looking at the same field to determine who can have access to the specific data. I just don't understand why smartsheet is not recognizing the e-mail addresses.

If I toggle the allow multiple contact per cell off, save and then switch it on, Save again it recognizes the e-mail. i need to do that for all the sheets everytime to get the restricted view to work.

Is there a different separator required or is this a bug?

Paul Newcome

This is unfortunately a known issue. Please feel free to report it to Support as a "bug" as well as fill out a Product Enhancement Request letting them know you would like the ability to use a formula to output multiple USABLE contacts in a single cell.

Hilbert Kok

Hi Paul, Where can i do that. I'm really getting frustrated. I have build a smartsheet access granting process, that requires e-mail adresses and then i use that for the dynamic view access, sometime it works and when i refresh the sheet the e-mail adresses are not recognized anymore and as such no access to dynamic view. I need this urgently to be compliant to GDPR. is there a way to speed this up?

Paul Newcome

https://app.smartsheet.com/b/form/739aa75f30ca43a8a22eb53e4da7d409?_ga=2.154822712.1225612972.1629717733-266231573.1626789885

Hilbert Kok

Thx for your support