Hello! I’ve been developing glideapps for my company for about a year now, but I’ve stumbled on something I can’t figure out a workaround for.
My setup similar to what is described on this page: Subcategories + Hierarchies - Glide Library . I have multiple levels of nested relations. I need each item to know not only its Parent and Child(ren), but also its Parent’s Parents and Child(ren)'s Children. The only way I can think of doing it is results in cyclic dependencies* between columns and Glide won’t let me do that (even though they are referencing different rows).
Does anyone have any ideas? Thanks in advance!
*: Here is an example:
Each row has a lookup (Col 1) and a template column (Col 2). The lookup column looks at its parents’ template (Col 2) and the template column (Col 2) takes its own row’s RowID and its own lookup (Col 1). The result should be that as you descend the levels you get columns 1 and 2 that look like this (using A, B, C, D as the RowIDs for four nested rows):
RowID Col 1 Col 2
A A
B A B
C A B A B C
D A B C A B C D
I’m finding your example a little difficult to follow. That’s probably my fault, not yours.
But, a question: is the relationship between parents and children one to many or many to many?
ie. Presumably a parent record can have zero or more child records, but what about the inverse? Child records can have how many parent records? Exactly one, or many?
Yeah, this might be a case where I need to see screenshots of real data to get a better understanding.
But, just guessing here…does your template have any sort of delimiter between the values? If you make sure that you have a comma or some other delimiter between the values, then you can use a Split Text column to split the values into an array that you can use to create a relation back to the Row ID column. I’m not sure if that’s what you need, but maybe it’s a step in the right direction.
@Darren_Murphy: A row can have any number of children, but only one parent. @Jeff_Hager, yes I have a delimiter. I have also tried using joined lists and arrays, but they end up creating cyclic dependencies. I think that Glide just catches any sort of dependency between two or more column and blocks it- without considering if said columns are pulling data from different rows via relations.
Thank you for your responses! Let me see if I can clarify more:
My use-case is a checklist app for communicating about equipment and supplies between a field team and a quartermaster. If the field team uses something out of a box, they use the checklist app to record that so that the quartermaster can see what needs to be replaced. This is how it (should) work:
The user can create a list, and within that list they can nest any number of lists and items.
Items have a simple true/false condition. Lists are true if all their child lists and items are true.
This is all done in one sheet (i.e. an “item” row is the same data structure as a “list” row, they are distinguished by whether or not they have children)
The two approaches I’ve tried are:
Each row determines its “trueness” based solely on its childrens’ “trueness”; in essence “trueness” is checked one level at a time and “passed up”
This doesn’t work because it leads to a cyclic dependancy unless I use N columns.
Each row has a list of all its parents, or a list of all its “descendants” (children and their children)
This would let me have a multiple relationship connecting each row to each of its descendants, then use a rollup to count if they are all “true” so that I can determine whether or not that row is “true”. Again, this doesn’t work because the only way I can create such a list of all parents or all children ends up as a cyclic dependency.
I could limit the number of layers in a list to some number N and then do it with N columns, but I’d like a more elegant solution that doesn’t limit the list depth.
I think @Miroslav_Madaric has possibly done something similar with his X.500 layout. I wonder if he could offer some thoughts on how he set up his tables.
I’ve definitely ran into the cyclic dependency problems before, but I’d really have to see it happening in from of me to fully comprehend why it’s happening. But I do think I understand the problem.
This is a very high level thought, but rather than dynamically determining the parent record/s for each child, could you just write the parent value/s to a basic column, and then use that with a split text and relation column? Not fully dynamic, but would probably prevent the cyclic dependency.
I’m not a big fan of multiple categories and subcategories in the same table. If you know how many levels you have, then it’s much easier to separate the levels into separate tables. It’s easier to maintain and more flexible.
If you have any screenshots, videos, or a working copyable sample app, I think that would help us to get things set of correctly.
Box - If (ALL TRUE), then TRUE else FALSE
Item - Used (T/F)
Item - Used (T/F)
Box - If (ALL TRUE), then TRUE else FALSE
Item - Used (T/F)
Box - If (ALL TRUE), then TRUE else FALSE
Item - Used (T/F)
Item - Used (T/F)
Box - If (ALL TRUE), then TRUE else FALSE
Item - Used (T/F)
Item - Used (T/F)
Box - If (ALL TRUE), then TRUE else FALSE
Item - Used (T/F)
Item - Used (T/F)
…
An item can be in only one box but a box can hold multiple items and boxes?
Box - If (ALL TRUE), then TRUE else FALSE
Item - Used (T/F)
Item - Used (T/F)
Box - If (ALL TRUE), then TRUE else FALSE
Item - Used (T/F)
Box - If (ALL TRUE), then TRUE else FALSE
Item - Used (T/F)
Item - Used (T/F)
Box - If (ALL TRUE), then TRUE else FALSE
Item - Used (T/F)
Item - Used (T/F)
Box - If (ALL TRUE), then TRUE else FALSE
Item - Used (T/F)
Item - Used (T/F)
…
To determine if any row is true, do you only consider its descendant children, or do you also consider the row itself?
I’m thinking it must be the latter.
I also agree with Jeff, in that you’re probably going to have to write the parent ID to each row, rather than try to dynamically determine it.
This is something I’d like to have a play with, because my gut feeling is that it’s doable. I just want to be sure I’m clear on the criteria before I dive in.
Yes, cyclic dependencies are making some trouble here. But one can make a proper workaround using additional schhet column for temporary storing the structure. 1st you have to define the row structure as follows:
Here you see that you get parrent structure and add time and row of actual entry. In the set column values then you simply copy this value in sheet structure, avoiding thus circular dependency:
When creating next level in he+ierarchy, you simply use “StructureSheet” valu from parent relation.
Of course, this has nothing to do with indentation! This you are making every time when you are creating new entry, smply adding “1” obtaining in that manner the depth. With this this depth you are are multiplyin empty characters, thus obtainng structure as depicted here:
If somethimng unclear, I can publish share this simple template. Now I am struggling with the new challenge: from poure x500 structure I want to develop billboard-like app using Glide. 1st challenge: how to make the list filtered with ONE starting structure. Eg. Topic x wuith all its descendents!?
@Jeff_Hager, your idea to write the list of parent values to a basic column could definitely work. It’ll be a little tricky to make it so that children can be moved from one parent/ancestry to another, but I’ve done that sort of thing in Glide before. It would also definitely be easier if I set a limit to the number of levels, but I’d like to keep it open ended.
@ MattLB that is correct!
@ Miroslave_Madaric, that looks like exactly what I need! I think that this is what Jeff_Hager was describing, and what I will implement unless someone can think of a dynamic way to do this. To get your list filtering, I think you can do it by using a split text column on your StructureSheet column to get an array of all the parents’ rowIDs. Then the parent can have a multiple relation to check their any StructureSheet columns that contain their own rowID.
@Darren_Murphy, there are actually multiple conditions that determine if a row is true. I’ll describe below. I also feel like it’s doable, but I’ve found that often Glide has to sacrifice some functionality you would have in programming otherwise for all the advantages it has as a rapid-development platform. It’s so satisfying to see new features come out that let me get rid of clunky work-arounds though!
Here is a copyable copy of my app. I’m trying to delete all the superfluous stuff so it’s easier to see the mechanic I’m going for (fortunate-song-6841.glideapp.io/). Just so its easier to read, in my actual use case there are four levels of booleans that determine if a row is “true”:
Manual Override. Use-case: someone in the field manually marks the row as needing to be checked by the quartermaster. If override is checked → the row is false
Date: Rows can be set to required regular checks, i.e. every 7 days. If it’s been more than 7 days since the row was edited → the row is false
Children: (Not working) If any child row is false → the row is false
Quantity: The basic “this should have x things” if it doesn’t have x things → the row is false
For this structure (one row per ‘thing’) to work I think a LIST (BOX in my example) always has to have one ITEM in it otherwise the LIST would be considered an “ITEM” in this data structure. This is the CS definition of a un-labeled tree with just the concepts of NODES/LEAF where a LEAF is a NODE without any ‘children’
But in this case it is known what a NODE is since it is labeled as a ‘List’ and can contain 0 or more ‘items’ and the ROOT NODE(s) would also be labeled for the quartermaster.
I think this is a really important use case and FWIW…Glide can immediately differentiate itself by offering a hierarchy “table”. Hierarchies are REALLY important in business, from ORG charts to projects to, well, quartermaster field teams.
Finnaly I have the solution, instead to have only issues!
My X500 template functions perfectly and is vey simple to implement.
Who says " you-can-t-teach-an-old-dog-new-tricks"?
Exactly, Lists are rows with children and Items are rows without. And that is the use-case! I made a copy of the app so everyone can easily see what it looks like: http://fortunate-song-6841.glideapp.io/
If you haven’t seen my answer: you can cheat Glide implementig SheetColumn which is not prone to dependencies, functions perfectly in practice. Here explanation:
If you try to copy the structure from parent, Glide warns about circular dependency, although it is not the case, because you are copyying the same field from another row (Glide bug?). But if one sets the sheet column as “temporary storage”, Glide of course can’t catch this “circulatoiry dependency”.
Here one should be cautious if using datetime in set values. It doesn’t work correctly in two ways:
Glide changes formats arbitrarily (random), making troubles in proper sorting. Workarond: converting date/time into YYMMDDHHMMSS numeric format using formula.
After submission new record, Glide doesn’t recognize RowID yet, so detail has to be displayed upfront, costs nothing with show detail screen.
