Re: Limiting the people that can edit a view/table

Miranda_Lubarsk · 01-30-2021 06:31 AM

Hello!

I am trying to make it so that some employees can edits a form/table, while others can only view it.

I am in the Data>Table view.

I have this expression, but don’t know if it would work.

SWITCH(USEREMAIL(),
“evelubarsky@gmail.com”, “UPDATES_ONLY”,
“evelubarsky@gmail.com”, “ALL_CHANGES”,
“READ_ONLY”)

I want to enable multiple people to be able to edit this table. Thus far I only have my email. How do I add multiple people and is this even the correct expression?

Many thanks!
Miranda

Thiago_Calcagni

You better have a table only for the users first.
In each row, you add the user email and create a column with a category for the user.
Example: Administrator, User, Manager, something like this.

Then you create the rules in the Data>Table view as if it was IF conditions or SWITCH conditions.
For example, bellow i will write the idea not the complete code:

SWITCH( [UserCategory],
“Administrator”,“ALL_CHANGES”,
“User”,“READ_ONLY”,
“Manager”,“UPDATES_ONLY”,
“READ_ONLY”
)

Miranda_Lubarsk

Ok, thanks. Still struggling with the expression though.

My column name is [Oversite]. I want the people labelled as “Evaluator” to be able to edit.

I want people with nothing in that column to be able to Read only.

The table name is “Employee”, where that information is stored. It’s a ref to another table.

How do I get it to look for that info there. The key in that table is Employee name, but we use the useremail to recognize the user…is this my problem?

Thiago_Calcagni

Try reading this topic see you can figure out, otherwise post again, i try helping you.

Steve

The expression would look like this:

IFS(
  IN(
    USEREMAIL(),
    LIST(
      email-of-updater1,
      email-of-updater2,
      ...,
      email-of-updaterN
    )
  ),
    "UPDATES_ONLY",
  IN(
    USEREMAIL(),
    LIST(
      email-of-admin1,
      email-of-admin2,
      ...,
      email-of-adminN
    )
  ),
    "ALL_CHANGES",
  TRUE,
    "READ_ONLY"
)

Miranda_Lubarsk

Ok, so I tried what both Thiago and Steve recommended but still not working, I’m probably doing something wrong.

Including lots of screen shots. First I tried Thiogo’s rec.

Don’t know why it can’t find column Evaluator when it is clearly there, and spelled the correct way in both places.

Then I tried Steve’s way…

But I feel like I’m doing something wrong with that.

I have a Column called [Oversite] in the Employees spreadsheet which qualifies people who I want to be able to edit the view as “Evaluator”. I would prefer to use an expression that just uses this function so in the future if the people change, I don’t have to manually change their emails in the expression.

Cheers!
Miranda

Thank you for your help!

Steve

should be (email addresses obscured for privacy):

IFS(
  IN(
    USEREMAIL(),
    LIST(
      "f***@gmail.com",
      "a***@gmail.com",
      "m***@gmail.com",
      "e***@gmail.com"
    )
  ),
    "ALL_CHANGES",
  TRUE,
    "READ_ONLY"
)

Thiago_Calcagni

I think Steve’s idea is really good in case you wanna type the emails in the formula.
If you wanna have a column that controls the access, and you do, better use SWITCH + LOOKUP.

In the table where you type the formula for “Are updates allowed?”, the result must be one of the values from the ENUM options (ADDS_ONLY, READ_ONLY, UPDATES… etc), otherwise it won’t work.

ANY(SELECT(Employees[Oversite],[Evaluator])=[_THISROW])

will not result in one of the ENUMs, it will result in one of the row values from column Oversite.

LOOKUP([_THISROW],“Employees”,“Oversite”,“Evaluator”)

i really don’t know why it doesn’t find column Evaluator, but i can say it won’t work for the purpose , because the result value must be one of the ENUMs and this will result in a Evaluator.

so… what you need to do is to try the idea from @11179 , supposing your table column Evaluator will contains the mail of people, and column Oversite which can be the access type like (Aministrator, User, Manager) or could be also a X, whatever…

SWITCH(LOOKUP(USEREMAIL(),“Employees”,“Evaluator”,“Oversite”)),
“Administrator”,“ALL_CHANGES”,
“User”,“READ_ONLY”,
“Manager”,“UPDATES_ONLY”,
“READ_ONLY”)

the result being evaluated by the SWITCH is the resultant from the LOOKUP which will be “Oversite”.
that should definetly work.

Miranda_Lubarsk

Ok, Thiago is right, I want something not dependent on email addresses.

This is what I came up with:

SWITCH((LOOKUP(USEREMAIL(),“Employees”,“Useremail”,“Employee Name”)),
“Evaluator”,“ALL_CHANGES”,
“”,“READ_ONLY”,
“Admin”,“UPDATES_ONLY”,
“READ_ONLY”)

And it checked out on the formula helper. I changed some things around from what you had Thiago but not I’m not sure that was right.

Now, when I see the Garden Scorecard view on my phone or computer, there is no plus sign to add a new form. It was appearing before I messed around with all of this, so I’m assuming something is wrong with the expression.

Thoughts??

Miranda_Lubarsk

And here is a screen shot of the row structure of my Employees spreadsheet. Maybe that will be helpful for you.

Thiago_Calcagni

Is it the Oversite column that has the access level? what could be the values for Oversite?

Thiago_Calcagni

Hej,

In your LOOKUP formula , you are returning the Employee Name, you need to return “Oversite” column.
Under SWITCH, you have “”,“READ_ONLY”, this is useless, cause in the end there is a “READ_ONLY” if none of the values are choosen.

Could you please put a screenshot from you table filled with 2 or 3 rows of information?
Maybe this can help to solve your issue.

The formula is now working that is why the add button disappeared, but the parameters might need to be adjusted in the formula.

11179

Try this.

SWITCH(LOOKUP(USEREMAIL(), Employee, here put the email column, Oversite),
“Evaluator” , “ALL_CHANGES”,
“READ_ONLY”)