Limiting user's table editing options

[Anton_Rybak]

Hello,

I’m trying to make only John Smith can edit/delete the record of this company (Apple Inc.), since he is “App Manager”.

In Companies table I set the following formula:
IF([Company App Manager] = LOOKUP(USEREMAIL(), Users, User EMail, User ID), “ALL_CHANGES”, “READ_ONLY”)

When tested, it show that returned value is ALL_CHANGES, but when saving, it gives an error:
Table ‘Companies’ has an invalid update mode expression ‘=IF([Company App Manager] = LOOKUP(USEREMAIL(), Users, User EMail, User ID), “ALL_CHANGES”, “READ_ONLY”)’. Unable to find column ‘Company App Manager’

What is the problem?
Thank you!

[Markus_Malessa]

Table permissions cannot be set at a table column level unfortunately. I found out the hard way also.

[Steve · Accepted solution]

A tables Are updates allowed? setting applies to the table as a whole, not to individual rows. To control access to individual rows, you’ll need to configure the Only if this condition is true setting for the Add, Delete, and/or Edit system-generated actions as appropriate.

[Anton_Rybak]

It worked! Thanks a lot, Steve!

Another related question. Any app should have a list of all users (Users table), but when a user logged-in, he is already in the system. How can I automatically link current user to a unique row in the Users table (assuming that anyone can log-in)? I’m thinking of some kind of trigger, that on log-in checks if current user’s email is in Users table, and if not, it creates a new row. But what would be the best (recommended) practice here?
And also, would you recommend to use user email as a unique key, insted of UNIQUEID()?

[Steve]

Anton_Rybak:

I’m thinking of some kind of trigger, that on log-in checks if current user’s email is in Users table, and if not, it creates a new row.

Unfortunately, there’s no way for the app to make data changes before the user has interacted with the app, so you have to find a way to get the user to click something that then adds the user.

This might be helpful:

FAQ: How do I do something automatically the first time someone uses my app? Tips & Tricks 💡

How do I setup/register a new app user automatically? How can I do something automatically the first time a user uses the app? This example assumes your app requires sign-in, and that you have a table named Users, each row of which describes a single user of your app. Such a table is a common way to store information about your app users and as an alternative to User Settings to store their individual app settings. The Users table is assumed to have a column named Email to store the email addr…

Anton_Rybak:

And also, would you recommend to use user email as a unique key, insted of UNIQUEID()?

Either is fine, though you might consider the possibility that a user’s email may change in the future. If that could happen, best to use an independent ID, such as one generated with UNIQUEID()

[Anton_Rybak]

Thank you, Steve!