Security Expression

[Miranda_Lubarsk]

Hey guys - I would like to limit who can see a certain view in the app using the security in Data>Tables>Security Filter.

I want Homestead Managers to be able to view the rows that have their name in it, but not not other Homestead Manager’s names.

And then I also want Evaluators to be able to see all of the rows.

In the existing rows in this “Garden Feedback Form” spreadsheet, I have both columns for Homestead Manager names and Evaluator names.

In another spreadsheet, titled “Employees”, I have a column of [Employee Names] which would be equal to the “Homestead Manager” names. And I have another Column where I have assigned people the status of “Evaluator”. I also have USEREMAIL() in this spreadsheet which I know I need for this all to work.

So how do I ask app sheet to show the view if the USEREMAIL() equals the name of the person in the spreadsheet? AND if you have status of Evaluator, you can see all rows?

Many thanks!
Miranda

[Steve]

What are the names of all the involved tables?

What are the names of each of the columns in those tables you think will be needed?

Is the “Evaluator” status determined by a name in a column? Or by a column in the Employees table? Your description is unclear.

[Miranda_Lubarsk]

Hi Steve! Thanks for the help!

Here is a screenshot of the columns in the Garden Feedback Form table.

And here is a screenshot of the Employees table columns:

In the [Oversite] column, I have the word “Evaluator” if someone is an evaluator, and blank if they are not.

[Steve]

What is the relation–if any–between the Evaluator column of the Garden Feedback Form table and the occurrence of Evaluator in the Oversite column of the Employees table?

[Miranda_Lubarsk]

Yes, so they are related but I guess they aren’t really linked in any way in AppSheet.

The [Evaluator] Column in the Garden Feedback form is a Ref to the [Employee Name] Column in the Employees table. And I have an initial value expression so that the Evaluator’s name comes up automatically when they add a new row (since they are the ones filling it out). This is my expression for that, which has been working:

LOOKUP(USEREMAIL(), Employees, Useremail, Employee Name)

[Employee Name] is the Key of the Employees table.

[Miranda_Lubarsk]

Now I’m realizing that I probably also need some kind of security feature so that only Evaluators can edit the form??

I guess I would do that Data>Garden Feedback Form>“Are Updates Allowed?”

But then I would need a similar kind of expression that says, based on the USEREMAIL(), only allow people labelled as “Evaluator” in the [Oversite] column of the Employees table to be able to edit, and then everyone else is View only.

Seems like it would be a similar expression for both scenarios, correct? I am rusty on my expressions having not done them since winter…

[Steve]

Miranda_Lubarsky:

Now I’m realizing that I probably also need some kind of security feature so that only Evaluators can edit the form??

How are form rows added? If from the same app, you’ll need to allow adds, too.

Miranda_Lubarsky:

But then I would need a similar kind of expression that says, based on the USEREMAIL(), only allow people labelled as “Evaluator” in the [Oversite] column of the Employees table to be able to edit, and then everyone else is View only.

In the Are updates allowed? expression for the Garden Feedback Form table:

IF(
  ISNOTBLANK(
    FILTER(
      "Employees",
      AND(
        (USEREMAIL() = [Useremail]),
        ("Evaluator" = [Oversite])
      )
    )
  ),
  "UPDATES_ONLY",
  "READ_ONLY"
)

[Miranda_Lubarsk]

Ok, I tried the IF/ISNOTBLANK/FILTER expression, but it is not working. I tried viewing the app as one of the Gardeners (not an Evaluator) and I can still see other people’s Feedback Forms.

Thoughts??

[Steve]

Try this as the security filter expression for the Garden Feedback Form table:

ISNOTBLANK(
  FILTER(
    "Employees",
    AND(
      (USEREMAIL() = [Useremail]),
      OR(
        ("Evaluator" = [Oversite]),
        ([Employee Name] = [_THISROW].[Homestead Manager])
      )
    )
  )
)

[Miranda_Lubarsk]

Thanks!! I got this when I pasted it into the window:

[Steve]

[Miranda_Lubarsk]

Hi Steve,

Ok I corrected those things, but it’s still not working somehow. I still see all of the entries when I view the app as one of the Employees (not an Evaluator).

I think there may be something wrong with the expression even though it should “work” in app sheet.:

I think the thing that isn’t quite right is the part that says “Evaluator” = [Oversite]. Because that isn’t the case. The [Evaluator] column is the Employee’s name, whereas the [Oversite] column is marked as “Evaluator” or blank. So I think in order to connect the Employee’s name in the [Evaluator] column, it would need to be a de-ref to the [Oversite] column and the text “Evaluator” being TRUE. Does that make sense?

Lmk your thoughts on how that would pan out in an expression. Here is a look at the Employees table so you see what I’m talking about…

[Steve]

Miranda_Lubarsky:

I think the thing that isn’t quite right is the part that says “Evaluator” = [Oversite]. Because that isn’t the case. The [Evaluator] column is the Employee’s name, whereas the [Oversite] column is marked as “Evaluator” or blank. So I think in order to connect the Employee’s name in the [Evaluator] column, it would need to be a de-ref to the [Oversite] column and the text “Evaluator” being TRUE. Does that make sense?

Nope, it doesn’t make sense.

In the expression I provided, ("Evaluator" = [Oversite]) is asking whether the Oversite column in a row of the Employee table contains the text value of Evaluator, which aligns with your table structure:

Have you tried the Test button in Expression Assistant?