Data structure

Ask for Help
1

Hi,

I think my brain can’t handle the data-structure I want :frowning:

Suppose I want to build something like the Glide Pages template “Company CRM”, but where a company can have more than one “Client Manager”. How should I change the field “Client Manager” in the company-table? Or somewhere else?

I could try with an array, but there you have to “predefine” the max. items (one column for each); I’m looking for a solution with undefined max. items,

Wim

2

I’m not familiar with the template, but when you have this sort of scenario you generally have 3 options:

  • Option 1 (worst option)
    – Add extra Client Manager columns to the Companies table, ie. “Client Manager 1”, “Client Manager 2”, “Client Manager 3”, etc. This is a really bad approach, as it’s difficult to manage and doesn’t scale. Don’t do this.

  • Option 2 (better option)
    – Create another table and call it something like Client Managers - Companies
    – This table would have two columns - one for the Company ID and one for the Client Manager ID
    – There would be one row for every combination of Company/Client Manager
    – This is the traditional way this would be done in a relational database. It’s solid, but can be expensive in terms of row count, which can be an issue with Glide.

  • Option 3 (the Glide way)
    – Leave it as it is, but make your Client Manager column accept a comma separated list of Client Manager ID’s
    – Assuming that you’re using a choice component to assign Client Managers to Companies, then all you have to do is configure the choice component to allow multiple selections.
    – Use a Split Text column to turn the joined list into an array, and then use that array to create a multiple relation column that links to the Client Managers table (or whatever it’s called)
    – This is a really nice option, as it’s quite easy to setup and doesn’t require any additional rows.

Either of Option 2 or Option 3 are generally fine. It depends on the scenario. Option 1 is almost never a good idea.

5 Likes
3

I think you need to consider row owners when building a CRM. What data should the client manager access? Does only one person have access to the data or does a team or a management structure or does everyone? Should all data be downloaded to all devices and let filtering sort it out or are you trying to limit physical access to the rows the client manager(s) has/have the privilege to look at?

Access + Security = :exploding_head:

4

Hi,
thanks for this detailed solution and now I’m trying to work with Option 3. I have the split text column and the array with the multiple relation column.
But in the Glide Page itself, I can’t find how to add field from the related table.

Table 1 (see image) has a relation with Table 2 and I want - in a collection - add these fields :

  • from Table 1 : name and car
  • from Table 2 : color of the car.

So the result should be :
record 1 in the Glide Page collection = wim - renault - green
record 2 in the Glide Page collection= chantal - mercedes - yellow

Is this possible and how? Also with multiple matches?

Wim

table_1
table_11058×460 33.2 KB

table_2

5

I’m finding it a little bit difficult to follow what you are doing there.

What is the general relationship between people and cars?
Is it a 1 to 1 relation, or can each person have many cars?
Also, I assume that each car model can come in different colours, yes?

What you have at the moment doesn’t really look anything like option 3, but it might be that option 2 is better for your use case. I just need to understand it a bit better.

6

I thought my simple screenshots would make clear what I want, but… not at all.
Also, it are dummy tables (not my real data), just to have an example.

Here I go with my “real” problem :wink:
I want - to help the community - to build a Glide Page that groups all CSS-design-codes that are spread in this community and in Notion.so

  • I will have a table with all the components that are available in Glide Apps (title, button, …) having the fields RowID - component_name - component_icon
  • I will have another table with the places where information can be found “everywhere” with fields RowID - component_name - location - title - URL

So the first table can have this records:

  • 999zzz - floating button - image
  • 888www - inline list - image

And the second table can have records like

  • 12abv - floating button - COMMUNITY - [Floating Buttons, Tooltip, CSS] - https://community.glideapps.com/t/floating-buttons-tooltip-css/26814
  • 24zsx - inline list - COMMUNITY - [CSS - inline list for chat] - https://community.glideapps.com/t/css-inline-list-for-chat/34541
  • 36 pqr - floating button - NOTION.SO - Change color of floating button - https://www.notion.so/Change-color-of-floating-button-245f19a1281e4f689bad1b67790e2d12

So one component-item from Table 1 can have multiple relations with Table 2 (in previous case the first and the third), BUT each record from Table 2 can (sometimes) have a relation with multiple component_names from Table 1.

And then in my Glide Page, when someone searches

  1. for “floating button”, then 2 records (1ste and 3th have a relation with the name “floating button”) with their information (location, title, URL) have to be displayed
  2. for “inline list”, then 1 records (2nd with the name “inline list”) with the information (location, title, URL) have to be displayed

I read the explication “things in things”, but don’t understand this quite well.

So thanks a lot for helping !

Wim

7

Okay, gotcha. Nice project :slight_smile:

Let’s call your first table Components, and your second table Component Tips

All you really need to do is create a multiple relation column in your Components table that matches the component name to the component name in the Component Tips table.

Then in your Glide page, add a Collection that uses the Components table as the source.
The default action for a Collection is to Show Details Screen. Don’t change that for now.
Now on the Components details screen (the one you get to when you click on one of the collection items), add another Collection, but this one should use the multiple relation column that you created above. If you did it correctly, this one will contain a list of all the Component Tips for the selected component.

This should be enough to get you started. Give that a go and see how you get on.

1 Like
8

Waauuw, that easy… but you have to know it.

Thanks a lot and now I can start. But probably I will need some more help later (I think…)

9

Everything works fine with your help (thanks). But I want to make it a little better (more complex…)

For the moment, I can add multiple component-tips to each component; that was my goal and is OK.

Table Components (with comma-separated column for the tips) :

  • Component1 / BUTTON / Tip1, Tip2
  • Component2 / INLINE LIST / Tip3

BUT NOW…
Suppose I have a Tip4 for both Component1 and Component2, it’s no problem to store this (same instructions as you gave me) in the Component Tips Table
(in my project: if a community-post mentions both CSS-code for a button and a inline list, then in the Component Tips Table, this one post can be linked to both component Button and component InlineList.

Table Component Tips (with comma-separated column for the components)

  • Tip1 / Component1
  • Tip2 / Component1
  • Tip3 / Component2
  • Tip4 / Component1, Component2

But then… table Components should be updated automatically with Tip4 like this :

  • Component1 / BUTTON / Tip1, Tip2, Tip4
  • Component2 / INLINE LIST / Tip3, Tip4

Can this be done in Glide Pages?

10

yeah, but it gets a little complicated. Tried my best to explain it here…

1 Like
11

At least, I know it’s possible ;). THANKS A LOT for showing this in the video. Very good detailed !!

I’ll have to watch it several times to fully understand the logic and try to do it myself… :thinking:

12

I think this is what MANY people want to do when creating APPs, relate various information in unique ways and automatically keeping relationships up-to-date.

Lots to think about with this example…thanks

Can I upvote this!

1 Like