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

[Pierre_Marceau]

Hi,

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

[RezaRaoofi]

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]

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

[RezaRaoofi]

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:

[RezaRaoofi]

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

[Pierre_Marceau]

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

[RezaRaoofi]

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]

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]

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]

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

[Pierre_Marceau]

I will have to add and withdraw companies each day

[RezaRaoofi]

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]

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?

[RezaRaoofi]

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]

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

[RezaRaoofi]

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]

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]

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