useremail

AppSheet AppSheet Q&A
1

Hello everybody!! I need a little help with my APP.
I have a google sheet where all the cars of my company are entered.
The cars are found in different cities around Italy.
the application is used to make a periodic checklist of cars.
Data entry is done every day by 3 people who are in different cities. I have created an account for each employee.
What I would like to do is show each employee only their own list of cars in their city. How can I solve?

Thanks for your help

2

The easiest way is to apply a Security Filter to your Cars table, retrieving only those Cars rows assigned to the logged in user.

If your “Assigned To” column has the user email they use to login in with then the Security Filter expression would simply be:

[Assigned To] = USEREMAIL()

https://help.appsheet.com/en/articles/954497-security-filters-the-essentials

2 Likes
3

Another idea would be to have a UsersTable and on of the fields of each User can be Assigned Cars made using an EnumList column of basetype Ref. I use this approach for multi-company or multi-role apps

4

Thanks everyone for the help !!
so I created a “USER” tab with name, surname, email, and station of belonging.
I did not understand the security filter should I insert it in the “DB” table? where are all the machines in Italy marked ?.
Example if User Francesco has to view only the cars present in ROME.
In the “User” table I entered Francesco’s name, with his email and in the Station column I wrote Rome.
How should I do the formula for the filter?
If you need more information, just ask
Thanks again!!!

5

The article I provided should give you the details you need to understand Security Filters. They are placed in the AppSheet Table. AppSheet will try to apply them at the database level, if appropriate.

Since it appears you are wanting related cars to the people by city/area, then the expression will need to be slightly different.

It’s not clear how you identify what city a User is assigned. I’ll assume that is what “station of belonging” is? Maybe you assign the city here, e.g. Rome. I will also assume only a single city can be assigned.

I will also assume that you have a similar column in your cars table to know what city/are they are in - let’s say it’s called Location.

With above in mind the Security Filter for the Cars table simply needs to find cars that match the city of the logged in user…like this:

[Location] = ANY(SELECT([Station of Belonging], [Email] = USEREMAIL()))

This expression is placed in the Security Filter of the Cars table in AppSheet

2 Likes
6

DB.png
DB.png302×119 2.18 KB

USER.png
USER.png428×119 3.17 KB

Hi, Im sorry but I can’t get there, could you give me a hand?
The sheet of the first image is called “DB” inside there are all types of machines, the station column is where the cars are placed.
In the second image I have created another “user” sheet where I have the data of who has to access and see only their own station.
A thousand thanks

7

Sorry, I missed including the SELECT’s table name in my sample expression which might be confusing you. Here is the modified expression based on your details:

[Station] = ANY(SELECT(User[Station], [Email] = USEREMAIL()))

This expression should be places in the Security Filter of the “DB” table.

2 Likes
8

Thanks a lot it works perfectly !.
One last thing if you can, and what if you want a user to view two stations at the same time?
it’s possible?
I don’t want to steal any more time
Thanks again

9

Hello Practically I would like to give access to a single user to the whole DB.

10

If you change the Station column for the user to an EnumList to allow assignment of one or more locations, then your Security filter expression only needs to change slightly to this:

IN([Station], ANY(SELECT(User[Station], [Email] = USEREMAIL())) 

You still need to use ANY() to retrieve the User’s list of stations they have been assigned.

1 Like
11

Now, taking all of the above one step further. If you wish to allow access to ALL data to certain users, you could just select all locations in the Station column. There is a “Select ALL” option in an EnumList dropdown.

But I think you may be referring to a manager or admin type person so I recommend building into the Security filter a bypass for these certain users that can be used across several tables - not just this one. Using the examples above AND assuming you are identifying users who need ALL access by their email, You could change the Security Filter like this:

OR(
   IN([Email], {"abc@user.com", "def@user.com","ghi@user.com"}),
   IN([Station], ANY(SELECT(User[Station], [Email] = USEREMAIL())) 
)

Eventually you’ll get tired of trying to manage the email lists in the Security Filters. A better way is to assign a Role, a new column, to each user in the Users table. For users who need to see all data maybe you assign them the role of “Manager”. Then the Security Filter could be:

OR(
   IN("Manager", ANY(SELECT(User[Role], [Email] = USEREMAIL())),
   IN([Station], ANY(SELECT(User[Station], [Email] = USEREMAIL())) 
)

I am suggesting a new Role column as opposed to an option within the Station list because you will likely want to give this “All Access” capability in other tables within your app - maybe not now but likely later.

1 Like
12

thanks a lot i tried but i recive this error.

Expression ‘OR( IN(“Manager”, ANY(SELECT(User[Role], [Email] = USEREMAIL())), IN([Station], ANY(SELECT(User[Station], [Email] = USEREMAIL())) )’ was unable to be parsed: Number of opened and closed parentheses does not match.

13

Now, I know you have been around long enough to know how to fix those! :grin: :grin:

Yes sometimes we miss something in the syntax when typing them into the community. But if you must, here is the corrected expression:

OR(
   IN("Manager", ANY(SELECT(User[Role], [Email] = USEREMAIL()))),
   IN([Station], ANY(SELECT(User[Station], [Email] = USEREMAIL()))) 
)

I still haven’t tested it but I THINK it’s right. :thinking: :thinking:

1 Like
14

It’s always best to edit expressions with good indenting to identify such errors, try the following:

OR( 
  IN(
    "Manager", 
    ANY(
      SELECT(
        User[Role], [Email] = USEREMAIL()
      )
    )
  ), 
  IN(
    [Station], 
    ANY(
      SELECT(
        User[Station], 
        [Email] = USEREMAIL()
      )
    ) 
  )
)
1 Like
15

thanks a lot i tryed but recive this erroe

Parameter 2 of function IN is of the wrong type

16

I’ll let @WillowMobileSys respond to that one, I suspect it is because [Station] is a list and so you probably need to use Intersect() rather than In()

1 Like
17

I agree it is probably something with how the [Station] columns are defined. Can you show us how you have defined the [Station] column in both the DB and User tables?

1 Like
18

Cannone_Cannone_0-1648113848971.png
Cannone_Cannone_0-1648113848971.png1920×1200 219 KB

Cannone_Cannone_1-1648114363420.png
Cannone_Cannone_1-1648114363420.png1920×1200 212 KB

I hope it goes well

19

The issue is that in your User table both Station and Role are defined as Text. The Security Filter is assuming these are Lists - EnumLists.

You had said earlier that you wanted to be able to assign multiple Stations to a single User.

I had introduced the idea of Role but I probably failed to mention that it is a good idea to plan for a user being assigned multiple Roles.

If you wish to support these concepts you will need to update the column types and add values to choose. Let me know if you make any of the suggested column changes.

For now, based on your current column definitions, the Security Filter expression should be:

OR(
   "Manager" = ANY(SELECT(User[Role], [Email] = USEREMAIL())),
   [Station] = ANY(SELECT(User[Station], [Email] = USEREMAIL())) 
)
1 Like