Struggling with multiple records linking

Hi there

Struggling with some aspects linking multiple records both ways and trying to avoid creating duplicate records.

I have 3 tables.


A user creates many construction sites under his profile.
A user creates many viewers (viewers are people with emails that will receive info from User/Site)
Sites have 1 user but many viewers
Viewers have many Users and many sites
Users have many sites and many viewers

Viewers can be created by different users (as the viewer will have the same email address)

I need to find a way of having a User link created Viewers to created sites, and have a list of all viewers on particular sites.

Do I need a table that only associates Viewers to sites so i can pull out separate rows?

Users: Can create many sites and many viewers

Sites: many Users Create many Sites

Viewers: Many users create many viewers and associate viewers to their many Sites. Other users can create the same viewer (based on the email record) for different sites they are working on.

I need to be able to display on the interface the following on each User profile:

Created Sites
Created Viewers

When I go into a site (from the Profile page), I need to be able to see the Viewers associated with that site

When I go into a viewer (from the profile page), I need to able to see the Sites associated with that viewer. I also need to use a “choose” function to choose a site to associate with that viewer (and by the same token, to disassociate a site from a viewer).

A couple of observations:

  • You have Row Owners applied to the email column in your Users table. With that applied, no user will be able to see any other user in your App. You probably don’t want that.
  • In both your Sites and Viewers tables you have User Specific Columns. Again, you almost certainly don’t want that.

Now, a suggestion.

I would probably dispense with your Viewers table, and instead use a single column in your Sites table that can contain a comma separated list of Viewer email addresses. This column can be managed using a multi-select choice component, using your Users table as the source (assuming that’s where viewers will come from).

To see a list of site viewers:

  • Create an array from the Viewers column using a Split Text column
  • Use a multi-relation column to link that back to your Users table
  • Use that relation column as the source of a collection

To see a list of Sites from a viewer profile:

  • In your users table, create a multi-relation column that matches the user email with the created array column in the Sites table.
  • Use that relation as the source of a collection.
1 Like

Thanks Darren, I’m going to try this and revert.

I think we’re probably missing each other.

The Viewers aren’t necessarily Users. It appears from our explanation that that is assumed.

A User will create Viewers (or batch create) that will be linked to that User.

A User will create Sites.

Now that User will need to select some Viewers they have created, to any Site they have created.


I see. That leads me to some questions:

  • Viewers won’t log into your App at all?
  • If they don’t, how will they “view” the site? (or will that happen outside of your App?)
  • Should User A be able to see (and select) Viewers that were added by User B?

It would have been ideal if “Viewers” could log in yes, so I guess Viewers could be Users. The issue is that Users should have specific Viewers that are linked them, and only them. The other issue is that the same viewers (using the same email address) could be invited by different Users (because they do the same type of work on multiple sites that are controlled by other Users.

Option 1 - Viewers are passive and will get info on mail through automations

Option 2 - Viewers are Users as well

User A should only ever see Viewers and sites that were added by User A.

User B should only ever see Viewers and sites that were added by User B

Viewer A could be linked to User A and User B based on User invited

mmm, okay. The plot thickens :slight_smile:

First thing: if a Viewer will ever need to sign in, then they must also be a User. That is, they must have an entry in the Users table. So it sounds like you have two classes of Viewers - those that are users and those that are not.

This leads me to another question - what are (or will be) the privacy settings of your App? And specifically, will you restrict who can sign in, or will anyone be able to sign in?

Let’s answer that first, and then we can circle back to the other points.

Re Sign-in and privacy.

I was hoping to create a landing page of sorts explaining the solution (simple website type page) but then with a standard signup/signin navigation (as per normal sites/apps)

Anyone can signup (although I’m not too sure about Glide’s options re signup vs signin).

So, re the classes of User, it would probably make sense to have some sort of category of User such as an admin User (ability to create sites and invite Viewers (other basic Users in this case), I guess I can setup a tag function so that when a User Invites another “User” (read Viewer), this Viewer/User is tagged as a basic and can only view but this is something I can being in later on.

It seems like the simplest to get past this roadblock is that Users and Viewers are all Users with logins. It make the relationship easier to manage this way.

Yes, I agree. Everything you’ve outlined there is perfectly doable, but you’re probably better off to keep things as simple as possible to start with.

So if you start by making all Viewers also Users, then I think we’ve come full circle and you can probably use my original suggestion. If you later want to add a second class of “non-user Viewers”, it shouldn’t be too difficult to extend that.

Just one thing to be aware of - you should familiarise yourself with Glide’s pricing (if you haven’t already), particularly with respect to limits on private and public users. And make sure you understand the difference.

1 Like

Thanks Darren, this helps alot, much appreciated. I will give the original a bash now as per the above and once working I will mark as solved.

@beginnerglider - just regarding the above, one thing you will need to do if you go with my solution is apply Row Owners to your Sites table. So you will need a column that contains the email of the User that created the site, and apply Row Owners to that column.

1 Like