Hi, I don't manage to create what I need may...

(Pierre Marceau) #1


I don’t manage to create what I need maybe someone could help me. I want to create an app where the users can only access their own data. Here is the model https://docs.google.com/spreadsheets/d/1z-IwqpSLBiNP9xDjm78wvCCw-R4T7jMwe5dhp29uwHQ/edit#gid=0 Column A and B are the common columns to each users Column C and D has been fulfilled by the first user Column E and F has been fulfilled by the second user Column G and H has been fulfilled by the third user There is a view where a user can see the 2 common A and B column and their respective 2 columns without seeing the other ones. The model should allow to add easily a new user with new columns.

Is it possible and how to create it?

Thank you

(Reza Raoofi) #2

In order to be able to easily add more users, I would consider only one set of Status and Comment columns, and adding a UserName or UserEmail and then having a separate row for each user; this way you can easily use expressions like FILTER(…) or SELECT(…) and extract the related data to the current user e.g. by the help of USEREMAIL(…) function.

Also recommend reading this article: https://intercom.help/appsheet/security/security-filters/limiting-users-to-their-own-data

(Pierre Marceau) #3

It would mean I have multiple same entries for companies, am I right?

(Reza Raoofi) #4

I just tested it; it does work; and even in the app editor there is a tiny description that says the related records are deleted when Is a part of is set; please make sure the table that contains the related rows allows Delete in the Data >Tables section:

(Reza Raoofi) #5

Can you explain the relationship between Users, and Companies? Can a particular user be in more than one company?

(Pierre Marceau) #6

Sure, to explain a bit more the context, I want to propose a list of companies to a list of sowtware editors (my users) For each of the companies they have to tell me if they want to be put in relation with the companies or not. This information is confidential and not shared throuhg the different users (however 2 users from the same software editor share the same status for companies) My day to day business is to create new relationship with new companies and propose them to the software editors So I can add and remove companies for everybody. The second thing is that I can add new software editors also or remove them. I hope it’s more clear

Thank you

(Reza Raoofi) #7

Ok, so the data model is more complicated that I first thought. Sounds like this is the summary; correct me if I am wrong:

Each company can be assigned to more than one software editor and each software editor can be assigned to more than one company, am I right?

Then again you mentioned: “…2 users from the same software editor…”, so users are yet a third identify in your model, right?

How data are tagged by related company? What is status?

You need to know all these details in order to have a proper relational data model.

It might take some time, but I recommend reading this document if you haven’t read already: https://help.appsheet.com/app-design/app-design-101

And more importantly this one for a better data model: http://help.appsheet.com/data/data-the-essentials

(Pierre Marceau) #8

Each companies are assigned to ALL software editors. It’s the current portfolio I can put the software editor in relation with. “users are yet a third identify in your model, right?” Good question, I was thinking about filtering emails per email domain but maybe we need a third one. If we take an example: I have a list of companies that I want to share with the software editor salesforce. 2 people of salesforce can use the app but will have to fullfill/share the same status information for each comapnies. These information musn’t be seen by another software editor’s user like Jira

The aim for each sofware editor is to give me a status per companies (interested, not interested) in order to know who should I contact to put them in relation.

(Pierre Marceau) #9

In the example below if maurice comes from saleforces and Pierre from Jira you have an example of what it could be docs.google.com - DATABASE APPSHEET DATABASE APPSHEET docs.google.com

(Pierre Marceau) #10

do you find a solution Reza? or anyone else thank you

(Pierre Marceau) #11

I will have to add and withdraw companies each day

(Reza Raoofi) #12

Well, adding/removing rows is always easier in database apps than adding/removing columns; besides if the relationship between users and companies is one-to-many meaning each company could have more than one user, then your database design and column structure should allow that via repeating rows; you do not have to repeat all company related columns, but at least minimum 2 columns are necessary: [CompanyID], [UserID]

So it would allow multiple users by adding new rows in that table.

(Pierre Marceau) #13

Interesting thanks so if you do it like that I will have a table with company name and company ID and the other talbe with company ID and the user ID and user value. In this scheme, if I delete a company it will remove all the lines associated to the company id in the other table?

(Reza Raoofi) #14

Yes, if a particular user can be only in one company, then you would need only 2 tables; one for companies and one for users with a Ref column to Companies table, then in that Ref column you can turn “Is a part of?” property ON which means deleting a company row will delete all associated users rows from users table.

(Pierre Marceau) #15

Hi Guys, The model evolved. Here it is. docs.google.com - DATABASE APPSHEET Now I would like that when I add a new value in the company tab it add as many rows as we have users in the matrix tab. The contrary is also true. If I create a new user it adds as much as companies in the matrix tab.

I have setted the ref but don’t know how to create the above behaviour. Thx DATABASE APPSHEET docs.google.com

(Reza Raoofi) #16

You cannot Automatically add multiple rows to an AppSheet table. What I explained about automatically deleting related rows only works for deleting and when there is a Ref between two parent/child tables.

(Pierre Marceau) #17

That’s why at the begining I wanted to add column for different users. If we deals with column, I can add a compalny by simply adding a line and it will appear for all the users. The problem in this case is to have a common app because the users musn’t see the value set in status and comments between each others. I don’t find a solution if someone can find one please.

(Pierre Marceau) #18

I have set the is part of in the ref but when I delete the company I can only see a warning in the matrix view, it’s not deleting the value