Row Owners and Roles - follow up

[constantinos_christo]

Hey everyone,

I’ve been trying to understand row owners and roles and found this extremely helpful, so thank you @Darren_Murphy for the amazing explanation!

I have a few followup questions though. Given the company example in the explanation, currently it is being set up to be used by only one company right? My understanding is that in the example there is one company, one CEO, managers and users.

What changes are needed to let’s say have an app where a company can register and then each company has their own CEO, and managers and users, all on the same app.

Another question is, let’s assume that there is another app for clients. A client can register to the app and then see all the companies and become their customer. How would this work? How can each company user see only their clients while their clients can be customers to multiple companies?

Thanks a lot in advance.

[Darren_Murphy]

@constantinos_christo

I have a few followup questions though. Given the company example in the explanation, currently it is being set up to be used by only one company right? My understanding is that in the example there is one company, one CEO, managers and users.

Yes, that’s correct.

@constantinos_christo

What changes are needed to let’s say have an app where a company can register and then each company has their own CEO, and managers and users, all on the same app.

hehe, as it so happens I’m working on an App at the moment that is structured very much like this. It gets a bit tricky, but essentially what you need to do is create role names that are a concatenation of both a company identifier and the role name, and use these as Row Owner values.

So for example lets say we have Company A and Company B, the CEO of Company A would have a role name of Company A - CEO, and the CEO of Company B would have a role name of Company B - CEO. Then you’d need to assign similar role names for each user role in each company.

One thing you will find is that you may wind up with several “Owner” columns in your data tables - depending on the total number of roles - but it’s really the only way to do this and keep each company’s data isolated from all the others.

@constantinos_christo

Another question is, let’s assume that there is another app for clients. A client can register to the app and then see all the companies and become their customer. How would this work? How can each company user see only their clients while their clients can be customers to multiple companies?

Keep in mind that Row Owners & Roles are App specific, so just because one App has row owners applied in a particular table, that won’t have any impact on a second App that’s sharing the same data source. But you can take advantage of this. So in your Client App you might have a Company Profiles table with no row owners applied, that allows Clients to browse all Companies. Then once a Client is associated with a specific Company, you could set an Owner row (or most probably multiple owner rows) in that Clients profile that allows users from that Company to see that Client. And as that Client starts creating data, you’ll also need to be setting owner rows in the appropriate data tables so that the same Company can access that Client’s data.

I won’t pretend that it’s simple, but once you get your head around how it works it’s not too bad

[constantinos_christo]

Thanks for prompt reply!

I played around a bit with what you suggested and I think I got my head around how it works.

So far, I achieved the same result by using an “if then else” column. It basically checked if the row belonged to the signed in user and if yes it would return the row id that I would use then in a relation from the user profile row.

I would also access the user’s data in the tabs using inline lists from those relations, which was convenient but had its limitations.

I’ve now replaced almost all the “if then else” checks with row owners and roles but there are still some cases where I struggled to get the same outcome so I still had to use a relation column and a query column.

A follow up question I have is the following:

How many Row owner columns do I need per role? And, how does it work when each role creates new data?

For example, I may have a CEO, Manager 1 and Manager 2. This means that whenever the CEO creates a new row, the values will be the following:
Row Owner email = CEO’s email
Owner 1 = CEO
Owner 2 = CEO
Role = CEO

When Manager 1 creates a row the values will be:
Row Owner email = Manager 1’s email
Owner 1 = Manager 1
Owner 2 = CEO
Role = Manager 1

When Manager 2 creates a row (assuming that CEO and M1 can see their data) the values will be:
Row Owner email = Manager 2’s email
Owner 1 = Manager 2
Owner 2 = CEO
Role = Manager 2

In the scenario where Manager 2 creates a row (assuming that CEO can see their data but M1 not) the values will be:
Row Owner email = Manager 2’s email
Owner 1 = empty
Owner 2 = CEO
Role = Manager 2

For the different companies I could concat the company name after each role as suggested above, but I could also create a “Company Row Owner” column with just the company right? Or this would not work since the company will not be part of the Role column?

The example above suggests that a neat answer to “How many Row owner columns do I need per role?” is 1 column per role. Is this correct?

Also, for the question “And, how does it work when each role creates new data?” my understanding is that each user, by having in their user profile row the roles defined, they will be able to use them when creating new data. Is this right?

Finally, if my above assumptions are correct, does this mean that it is required to know all the roles from the beginning in order to create enough row owner columns in advance? I guess it would be nice to, but if new roles come up you can always add the later but it will require some extra work

This came out as a “think out loud” post but it would be nice to sense check the above and hear other opinions.

[Darren_Murphy]

Apologies for the delayed reply. The topic was auto closed because my previous reply was marked as a solution, and so this slipped off my radar. I’ve removed the solution and reopened the topic, in case you have follow up questions or comments.

@constantinos_christo

So far, I achieved the same result by using an “if then else” column. It basically checked if the row belonged to the signed in user and if yes it would return the row id that I would use then in a relation from the user profile row.

That if-then-else column is actually redundant. If you have Row Owners applied to a table, then by definition every row that is present on a users device is owned by that user. So your if-then-else column will return true for every row. Note that when viewing the data in the Data Editor it might not appear this way, but that’s because by necessity the Data Editor has to show all rows - both owned and unowned. That’s not what happens on user devices - the unowned rows simply do not exist. In the Data Editor you can identify unowned rows by the fact that they will be “dimmed”.

@constantinos_christo

How many Row owner columns do I need per role?

It depends, and the answer could be different for the Users table, and other tables. The simple answer is if a specific role needs access to data in a table, then you need an owner column in that table for that role.

@constantinos_christo

And, how does it work when each role creates new data?

You need to assign row owners as new rows are created. There is no restriction on users being able to set (any) values into owner columns, so that should be easy enough. But where you will have a challenge is with assigning roles to users, or changing users roles. Users can only assign roles that they themselves have, and once a user has been created, it’s not possible to change their role via the UI. So you either have to manage this manually in the Data Editor, or (much better) by using a combination of Webhook->Make->Glide API.

@constantinos_christo

For the different companies I could concat the company name after each role as suggested above, but I could also create a “Company Row Owner” column with just the company right? Or this would not work since the company will not be part of the Role column?

If you set a value (other than an email address) in a Row Owner column, then any user that has that value as a Role will have access to that row. If no users have a matching role, then no users will have access.

@constantinos_christo

The example above suggests that a neat answer to “How many Row owner columns do I need per role?” is 1 column per role. Is this correct?

In your users table, yes that looks to be correct.

@constantinos_christo

Also, for the question “And, how does it work when each role creates new data?” my understanding is that each user, by having in their user profile row the roles defined, they will be able to use them when creating new data. Is this right?

Roles have no impact on a users ability to create data. They just control which data a user has access to (when used as row owners).

[constantinos_christo]

Hey @Darren_Murphy, thanks for your reply.

I just found some time to play around with it and managed to make it work for the Private App with Roles. However, I it is a bit tricky to achieve something similar with the clients app which is public with required signed-in.

Image that a user from the public app becomes a customer of the Company from the private app. Then the customer invites their colleague to the public app so they sign up and they should be able to have the same access to the Company data as the person who invited them. The number of users that can be invited is not know upfront so this should be dynamic.

I searched the community a bit and found that @ThinhDinh has a neat solution for this. In this the array formula is used in a very smart way and I believe the same logic applies to this example too. I sort of get how the array formula works, but what I am struggling with is where the data is being pulled from.

For example, let’s say that the user’s table there is a row with user-A.

1. User-A invites user-B by zapier or integromat and a new row is created for user-B in the user’s table. Row owner is currently set on Email only so each user has access to their own data only.
2. User-B joins and they have access to their own data too.
3. Assuming now that the row owners array column is set up, how can I tell the array column that user-A and user-B should see the same data?

What else should I do when inviting users to build the relationships so the array column can work as expected? I know that computed columns are being calculated after all the data is downloaded so in this case this won’t work.

[Darren_Murphy]

@constantinos_christo

the array formula is used in a very smart way and I believe the same logic applies to this example too.

I’d not seen that example before. @ThinhDinh is indeed a very clever cookie
Of course that can only work if you are using a Google Sheet as the Users Table - are you?

Assuming yes, then…

@constantinos_christo

1. User-A invites user-B by zapier or integromat and a new row is created for user-B in the user’s table. Row owner is currently set on Email only so each user has access to their own data only.
2. User-B joins and they have access to their own data too.
3. Assuming now that the row owners array column is set up, how can I tell the array column that user-A and user-B should see the same data?

What else should I do when inviting users to build the relationships so the array column can work as expected?

If you look at the example Google Sheet that Thinh provided, you’ll notice that each row in Column A contains a comma separated list of values, which are “exploded” out into individual columns by the array formula (which also takes care of dynamically creating new column headers as required).
So what you would need to do is write a comma separated list of email addresses into that column (from Glide). Then you make the array column a Row Owner, and every email that you add to the list will become a “shared” owner of that row.

I do have to admit, it is a very clever workaround. Hats off to Thinh for that one

[constantinos_christo]

@Darren_Murphy

Of course that can only work if you are using a Google Sheet as the Users Table - are you?

I am currently not but I guess I would have to switch

@Darren_Murphy

So what you would need to do is write a comma separated list of email addresses into that column (from Glide).

This is the part I’m struggling with. How am I going to create this csv list and how do I write it in the google sheet? I think I know how to create it with a relation and a joined list but this won’t show in the google sheet. Could you provide a simple example?

[Darren_Murphy]

I guess it depends on your exact flow, but it could be as simple as a Choice component with multi-select enabled.

If that doesn’t work for your use case, then you may need some variation of the Trebuchet method.

[ThinhDinh]

Can you provide me with the data structure you may have for the app, not taking into account the row owners part?

I also updated the example to have the split happen automatically. Only the red-marked cells contain formula.