Relation in same table

I have a User table wherein Roles are Client, Representative, Admin.
I want to assign a Representative to the Client so I created a Relation column which is linked to same table. I have also added some lookup columns for name and photo.

However, while adding the details in the app, it does not show anything while the details are visible in the table. What can be the reason of not showing the details? @Darren_Murphy @ThinhDinh

Screenshot of the table, please.

PS. You don’t need to tag me in posts. You can assume that I read all posts, and if have something to contribute, I will.


Hi Darren, thank you for reply.

Here is the screenshot.

Sure, sorry if that was bothering.

The user you’re viewing as only has access to the first row in the table. You can tell that, because the other rows are dimmed out. So there is something wrong with your row owners/roles configuration.

Yes, that was an intention. That’s the reason I am using relation to bring second row to first raw. If you see last four columns, which are visible in first row. I just want to show those details in one of the screen. But its not happening.

If that is how it works, how do I achieve it?

Your relation won’t work on a user device, because there will only be one row. Rows 2 & 3 will not exist. If you want to use data from other rows, then the signed in user needs access to those rows.

1 Like

Thank you. that makes sense. What do you think is the best way to achieve this? If you can guide me, that will be helpful.

Well, lets go back to your original question:

I would have an email column to hold the representative email address, and then make that column a row owner. No relations or lookups required.

Thank you. However, in terms of flow, when a new User is added by Admin using Form, he will select which Rep is to be assigned. Now, there is a chance that one Rep can be assigned against more than one Client. How to handle that? So, more than one User (client) will need access to another same row.

Are Client users and Representative users all in the same table?
If they are, then that’s fine. The same Representative email address can appear against multiple clients.

It would only be a problem if you need to assign multiple representatives to the same client. If you need that, then you would either need multiple representative/owner columns, or (better) make use of roles as row owners.

I guess its other way round. If I want a Client User to access the row of Representative User, I need to mention email id of Client User in Rep User’s row. Is that correct? Then how do I do that?

I’m a little confused. Which of the below best describes your use case:

a) One Client has one Rep
b) One Client can have one or more Reps
c) One Rep can have one or more Clients

This is correct one.

Okay, so in that case you just need a secondary row owner column in your Users table, and add the Rep email address in that column, for each of their clients.

Presumably the Rep should also have access to their associated clients data?
If that’s the case, you would also need to do the same as above in any table where you are applying row owners.

I am sorry but I am still confused.

What I want is, Client should access the details of his Representative (who is also User). So in that case, I need to add client’s email id in its Rep’s row. Am I correct?

So if Client 1, Client 2 and Client 3’s Rep is same, I need to add total three email ids in Rep’s row and make it row owner so that all those clients can access Rep’s details.

Yes, that’s correct.

If you could have many clients associated with the same Rep, then it may be better to use the RepID (RowID) as a Role. Then you could assign that as a Role to each associated client and make the RowID an Row Owner Column.

For example, take a look at the below screen shot:

  • Marie is a Rep, and she has two clients (Tamsin & Alan)
  • Email is a Row Owner, and RowID is a Row Owner
  • I am viewing as Tamsin, and I have access to two rows:
    – My own row, because my email address matches the value in the email column
    – And Marie’s row, because my Role matches Marie’s RowID

If I switch that to viewing as Alan, then I see my own row and Marie’s row, but not Tamsin’s:

1 Like

That is a good idea.
I have already set up Role as Client, Representative and Admin. If I change the Roles of Client, how do I configure the actual role for the entire App?

You don’t need to change anything in your User Profiles configuration, but you may need the following changes in your Users table:

  • instead of using the word Client as a Role name, use the RowID of the associated Rep, as shown in my example.
  • for your Admins (assuming that they should see all users), then you will need one more column with Row Owners applied, and put the word Admin in every row.
1 Like

Thank you for the idea and answering my questions. I will try this method. Thank you for continuous support and help, as always.

1 Like