Making tables on an appsheet app private the correct way

[Danielsntgd]

I am making an inventory app. I had everything set to shared tables (as is the default apparently). I want this app to be private per user, so I'm assuming I should turn off "Shared". I'm using different sheets in one big google sheets file for all info. When I uncheck "Shared" I got the error below. Does this mean I need completely separate google sheet files for each table?

 

[dbaum · Accepted solution]

If you haven't already, review Private tables - AppSheet Help.

The specific warning you shared refers to the fact that, as noted in that article, "A private table must be the only worksheet in the workbook. Your app can have a number of workbooks and worksheets, but each private table must be the only worksheet in its own workbook.".

You may or may not actually need private tables. There are other ways to limit the data that specific users of the app can see.

[Ferret · Accepted solution]

As for your second question;

on the table's Email field, enter USEREMAIL() into the initial value section. The user creating the record will have their email stored in this field upon record creation now.

[dbaum · Accepted solution]

If you haven't already, review Private tables - AppSheet Help.

The specific warning you shared refers to the fact that, as noted in that article, "A private table must be the only worksheet in the workbook. Your app can have a number of workbooks and worksheets, but each private table must be the only worksheet in its own workbook.".

You may or may not actually need private tables. There are other ways to limit the data that specific users of the app can see.

[MultiTech]

@dbaum nailed it right there.

In actuality - private tables bring a host of problems for ongoing app development

If you think about:

• Each of the users has their own copy of that private table....
• So how is THAT table, the one on user X's Google Drive, supposed to get the new columns you've added to YOUR table???
• The app definition will update sure... but how is the private table supposed to?
• Did you go in and make your changes to ALL of THEIR sheets?
• Did you get THEM to make these updates to their sheets???

[Danielsntgd]

So how would I affectively privetize a shared sheet?

Thanks for all of your help!

[Steve]

Limit users to their own data - AppSheet Help

[Danielsntgd]

So I would manually add the user email function to all rows in every table? And would this work for manual input rows?

[dbaum]

The specifics of what you mean by "privetize a shared sheet" are unclear. A general pattern would be to include a column that is not shown/editable in a form and captures USEREMAIL() as its initial value, and then define the table's security filter to include only rows where that column matches the current user's email. Approaches along these lines are very common. To see lots more nuances of the possibilities, see  Current User (Slice) | How to conform your app a... - Google Cloud Community . 

[Danielsntgd]

I've made a user table 'User' and a slice 'Current User' which has the USEREMAIL() = [Email] as the filter.

I tried making a Security filter for my tables using USEREMAIL() = Current User[Email], but this got me an error. What is the correct formula to do this? Additionally, do I need to add user emails to the User table manually? 

[Ferret]

[Email] = USEREMAIL()

A slice is not needed in this usage. You would only need a slice if you are storing the User record's ID on the other table instead of the email (only needed if user emails might change and you want to use the existing user record instead of creating a new one). In that case, build the slice as you did and then reference it as so:

[CreatedUser] = ANY(Current User[ID])

The usage of the ANY() function here takes the returned list of records (because that is what a slice is) and randomly picks one value, which in this case is the ONLY value, and returns it as a single value.

[Ferret · Accepted solution]

As for your second question;

on the table's Email field, enter USEREMAIL() into the initial value section. The user creating the record will have their email stored in this field upon record creation now.

[Danielsntgd]

It doesn't seem to be working. I tried USEREMAIL()=ANY(Current User[Email]), but all values added are still showing for all users

[Ferret]

replace "USEREMAIL" with the field on that table that stored the user's ID.

[Danielsntgd]

I'm not sure what you mean. I tried User[Email], but that didn't work. 

[Ferret · Accepted solution]

If you have a table of data (not the User's table) and you would like to show only records that were created  by the app's current user, then the table of data must have a field called something like CreatorID, which would be populated with data using the initial value condition that has the formula:
ANY(Current User[IDUser])

In this case, your User's table must have a field called IDUser that is set as the key for that table in Appsheet, and you must have a slice like you have already set up (named Current User).


Unless you need to account for users changing their emails, I think a much easier method would be to have a field on your data table named CreatorEmail (with an initial value of USEREMAIL() )and set the table's security filter with a formula of :

[CreatorEmail] = USEREMAIL()

No slice or user table would be needed then.

[Danielsntgd]

Thanks for your explanation and taking the time to answer. I now understand this method of adding a column to each table containing a userid/email. Would the more complicated version of adding a user table along with a current user slice also need a column added to each table with a security filter in a similar fashion? Thanks!

[Ferret]

You are welcome.

Yes it would.