Summarizing Grandchildren

[SurferNate]

Hi All,

I have a new project, and I find that I am stuck.

I have this issue basically solved in a spreadsheet, using pivot tables, but it is not suitable for the "Data Entry Personnel" interface I really need.

I have basically 5 source tables related so:

PARENT = JOINTABLE1 = CHILD = JOINTABLE2 = GRANDCHILD

Where for problem solving purposes, PARENT is a list of "Events", JOINTABLE joins those events to CHILD which is "People", JOINTABLE2 joins those people to GRANDCHILD which is "Materials".

I have the summary for Child set up easily, using a self join of JOINTABLE1 to get the summary of CHILD for each PARENT. (Where I summarize all records where PARENT=PARENT and CHILD=CHILD)

I carried this same logic to summarize CHILD=CHILD and GRANDCHILD=GRANDCHILD in JOINTABLE 2

I worked around the problem by creating a field in JOINTABLE2 to relate GRANDCHILD temporarily to PARENT, but it is slow, requires manual update, and is not reliable in producing complete matching results. I need to troubleshoot my calcs still (as they are working in theory but still not returning consistent correct answers.

This is important because one low level employee will enter people, events, and materials, and another low level employee needs a simple report of events=people, and most importantly, a simple report of events=materials.

I basically need to summarize Granchild=Parent so the end user can hand a materials requisition sheet to the purchasing desk, without having to do anything except enter who is going where, and what they need.

If necessary, I'll generate an example file to play with. :-) I just wonder if I am asking the wrong question. I really want to do this with rock solid relationships and "network fast" calculation updates. This seems so obvious a scenario to me that I hope someone has already solved it.

Help?

Thanks!!

Nathan

[comment]

Sounds very similar to:

http://www.fmforums.com/forum/showtopic.php?tid/191460/

[SurferNate]

Comment,

Yes, that is VERY similar to my needs. I will need to study the entire conversation now to see how well it applies.

Thanks mucho !! :-)

[SurferNate]

Hi Comment, (and anyone else that might also be a lot smarter than me)

I think I'm missing a crucial part of the pie here. In playing with the structure, I see vaguely the error of my thinking.

In both of my Jointables, I have many-many relationships, and each join record also contains things about the relationship like quantities and dates etc.

I used another technique with a self join in each of my join tables, to get a summary of similar children for each parent, and then also a summary of similar grandchildren for each child, but I can't seem to carry the calc's up and down the relationship chain.

Here's what I want to get:

Where I might have:

Picnic A: (parent)

Salad: 3 (child summary)

Sandwiches: 2

and

Salad: (child)

Lettuce: 2 (grandchild summary)

Tomato: 1

Dressing: 1

Sandwich:

Bread: 2

Lettuce: 1

Tomato: 1

Turkey: 1

I want to see from the Picnic A record:

Picnic A: (parent)

Tomato: 5 (grandchild summary)

Lettuce: 8

Dressing: 2

Bread: 4

Turkey: 2

What logic am I missing here to make this work? My brain is fried, and I'm thinking I might need to go recursive in my calc's, except right now I still only understand recursion the way a blind person understands the ocean...

[The Shadow]

This looks like a "bill of materials" issue. You might try searching the forum using that term.

Here's an example bill-of-materials file I put together, maybe it can help you.

BilloM.fp7.zip

[comment]

Couldn't you have picked something easier on a Friday A.M.? I'm a bit rusty on the subject, but let me give it a shot - at least enough to get you started.

First, let's name your main tables. We'll have:

• Products (e.g. a picnic)

• Assemblies (Salad; Sandwich)

• Materials (Bread; Lettuce; Tomato, etc.)

And of course, two join tables where the quantities are, ProductAssemblies and AssemblyMaterials.

Now, basically you want the bill of materials for a product. To do this in the simplest way I know, we will add a global field gProductID to the AssemblyMaterials table. We will produce a report for one product at a time, and we will populate this global with the current product's ProductID.

Next, we'll define a relationship between AssemblyMaterials and a new TO of ProductAssemblies:

AssemblyMaterials::gProductID = ProductAssemblies 2::ProductID

AND

AssemblyMaterials::AssemblyID = ProductAssemblies 2::AssemblyID

and define a calculation field cQty in AssemblyMaterials as =

Quantity * ProductAssemblies 2::Quantity

Now we can produce a report from the AssemblyMaterials table, sub-summarized by MaterialID, using a summary field to total the cQty field (after finding the relevant records, of course, which is easily accomplished by GTRR from Product).

If you want to see this in a portal, you can apply something like Ugo's method to summarize the portal (as you have been started to do, by your description). But keep in mind that the global in AssemblyMaterials needs to be repopulated each time you switch to another Product.

Note also that this assumes a rigid (i.e. non-recursive) structure. This means each product has at least one assembly and nothing but assemblies (no material goes directly into a product), and each assembly has at least one material. IOW, in order to sell just lettuce you need to create an assembly that contains lettuce as the material, and a product that contains that assembly.

Shawn's file, OTOH, uses a recursive structure - but I don't see an easy way to get a list of unique materials and their quantities this way.

[SurferNate]

Comment,

Thanks,yes you do seem to understand exactly. I am trying to maintain a rigid structure, where if the user wants to have a relationship "directly" from Product to Materials (like you said a single material added to a product), then one would create an assembly containing only that material, and only one of it. I like this rigidity of structure, as it translates across platforms and processes nicely.

Anyhow, I think hit upon the gist of your answer in my efforts so far. I have the gProductID field in AssembliesMaterials already, as that made some sense, and I can easily script the refresh of that field when Product is changed. I'll review your outline and see if I can fill in the blanks. I'm 80% there in structure already.

Can you link me to Ugo's solution for summarizing portals you mentioned?

I was concerned that I might have to go recursive in the way of GetNthRecord(AssembliesMaterials;$LastRecord+1) or something, and I love the idea of recursion, but lack the mathematical background to be comfortable swimming in those waters.

Question: This seems like such a ubiquitous need to me that I would think the answer is well documented. Am I putting business logic ahead of database logic? I thought that the relationship chain I have is exactly the point working within of a database...

EDIT: I realize a challenge in my thinking here. We're talking as if there is only one instance of Parent::Child for each unique Child. In this case, the user expects to add Children to Parents at will, and with multiple occurrences of a similar Child::Parent relationship, that may have different relationship parameters in the join table. The same can happen with Grandchildren. My users don't want to summarize anything. They want to add the same child to a parent a dozen times if need be, and in different quantities at different times. This requires that the join table be able to self join and summarize these "similar" records, in order to get a total of each unique Child::Parent relationship, where the same Parent::Child relationship may occur many times with differing parameters.

Since I've done this so many times now, I just hacked together and attached a file that gets me I think 70% of the way to the answer, and then leaves me hanging...

Summarize_Grandchildren.zip

Edited March 21, 2008 by Guest

[comment]

You can search the forums for "Ugo's method" - I have posted several files showing different implementations of the basic principle.

However...

I am quite confident the method I have outlined above will work with a sub-summarized report. I added the part about viewing this in a portal as an afterthought only - now that I think of it, I am not at all convinced it can be done.

The problem here is to find some "common ground" between children of different Assemblies, sharing the same ancestor Product. This seems to be quite a challenge, so I'm afraid I will have to put it off until I can devote more time to it.

[SurferNate]

Thanks,

I was hasty and actually had the Summarize Portals file on my drive already. (I love Spotlight!)

I guess I'm somewhat relieved that I'm probably not a complete idiot. This has stymied me for a while now. The shift in technique from portal base reporting to "report" based reporting might be my only good option...

Anyhow, I can't say how much I appreciate the help. I try to do what I can to pass it on, considering I'm basically running a small company and at the same time trying to start my own, and still at night trying to develop these "enterprise" solutions to hopefully make it all easier someday.

[SurferNate]

Shadow,

I wanted to thank you for your input, in reviewing your file, we do come to a similar result, except I don't use recursion in mine. I don't know, either in your recursive structure, or my rigid structure, how to aggregate the totals of each given Material in the Super-Assembly. (ie more than one relationship away on the graph)

Also, I know there are ways to control recursion (such as limit the number of levels and also to prevent a record from recurring within itself), but I wanted simplicity of relationship. So that's why I have three Prime Tables, and one multipurpose Join Table

At this point I would be happy go recursive if it allowed me to pull a portal to totals of each of the lowest level records, but I'm still missing a piece of the calculation/relationship required to do that.

Just thinking by typing here, but...

I need to first determine whether a record is a child with no children. Then I determine whether that child is related to the parent or super-parent from which I want a summary. Then I need to determine and summarize all recursions of that child::parent relationship...again, I'm missing that last part. Pulling simple summaries from one relationship away is easy, pulling grand summaries from N relationships away seems to become exponentially more difficult...

[comment]

I have thought about this during lunch, and I am close to concluding it cannot be done - at least not through merely de-duping a portal. I am afraid you're up against one of the con sides of relational databases.

It's quite easy to isolate the base materials used in a product, and to get their quantities in their parent assemblies. It's also very easy to know how many of each assembly go into a product. But to combine the lettuce that goes into a picnic in a sandwich with the same lettuce that comes via a salad... when you are down at the assembly-material level, they don't have a common product (at least not stored). As you go up to the product-assembly level, they're not the same lettuce anymore.

Perhaps it CAN be done by adding more and more TO's in Ugo's fashion - but it doesn't seem very cost-effective, esp. considering that SOME scripting (setting the global gProductID) is required anyway.

If you must have this in Browse mode, I would make it something you obtain by demand, and run a script to perform a Quick Summary and put the result in a global text field.

Another option would be to have a custom function sum and de-dupe the list on its way "up from the ranks" (similar to the way Shawn's demo gets its hierarchical listing). This would be applicable to both the rigid structure and the recursive data models (I think).

[SurferNate]

Comment,

Again I can't say how much I appreciate it. Take a look under Tips & Tricks. I actually solved it, although you could smell the wood burning while my brain tried to wrap around some of the structural flow. I did it basically by treating the tables as algebraic functions, and then determining what unique info I could squeeze out of each function. Relationship sorting was the critical tipping point. I realized I could isolate a single summary value of each similar Parent|Child, and then each similar Child|Grandchild and relate the two through ChildID and gParentID. Since I now only have one summary for each similar relationship, I just multiply the two together where gParentID=ParentID and ChildID=ChildID.

One of the key tricks was to have a separate relationship that determines if a record is the "prime" or first similar record, and then only if that test passes, I would summarize all similar records through another relationship. So, only the "prime" or first record contains the summary of all others, and by creating these "prime" records in the join table relationship, I could wash out duplicates and work with one unique set of summary values for both Child and Grandchild.

P.S. - The thought about TO's is correct. I suspect if I wanted to deal with great-grandchildren, my graph might get totally out of control...

Edited March 22, 2008 by Guest

[comment]

Re:

http://fmforums.com/forum/showtopic.php?tid/194171/

Sorry for not replying sooner, I was too busy to give this the attention it requires.

I didn't go over your file too carefully, but IMHO you are already past a reasonable amount of TO's relative to the task. I believe using custom functions would be a better solution.

Roughly, I would use one custom function in ProductAssemblies, to multiply Quantity by a list of quantities from AssemblyMaterials. Another CF would be used in Products to summarize the child lists.

This requires no additional TO's, only a couple of calculation fields - and it would work "live" on any product (no setting of global field required). OTOH, the result would be a text field, not a portal. I don't think it matters much, but it's worth pointing out.

Edited March 27, 2008 by Guest
added link

[SurferNate]

Thanks,

One reason I felt I had to do this is that I can now GTRR from my portal. I'm sure there is a way to do so with a custom function too, but again I'm still struggling with truly understanding and writing recursive calcs.

I do worry that this structure will "break" with a FileMaker update. If nothing else though it is proof of concept, and gets me what I need right now.

I definitely would be excited to see another way to accomplish this goal. Somehow relating Grandchild to Parent in a reliable way, such thaI can recur, summarize, filter and aggregate Grandchild records "live" would be a very good thing indeed.

[comment]

I can now GTRR from my portal.

This doesn't make much sense to me. You can GTRR from the portal to ... to where exactly? If your portal says "5 x Tomato", which record is the logical target for a GTRR? The 2 records that came through a sandwich, or the 3 records that originate in a salad?

[SurferNate]

I may have put that wrong. I can GTRR Tomato. I know I can do that anyway though. What I want to see is "Tomato 5 $2.00" and have that info live in front of the user.

It's entirely possible that I'll change my mind about this, when records multiply and speed becomes more apparent, but for now, adding another Sandwich to Picnic produces an apparently instant result of

"Tomato 7 $2.80"

Soren is trying to send me over to the "report" camp on this issue, but I don't see how I can do that still without a recursive multikey.

[SurferNate]

Re:

http://fmforums.com/forum/showtopic.php?tid/194171/

Roughly, I would use one custom function in ProductAssemblies, to multiply Quantity by a list of quantities from AssemblyMaterials. Another CF would be used in Products to summarize the child lists.

I agree that makes sense for data summary viewing, which I could potentially decide is really the only important issue. For now, I wanted the option of interactivity with the data in a portal, including the ability to see the current aggregated summary of grandchild records through all current recursive relationships.

I remember reading a post from Soren, where he remarks on newbies feeling accomplished at making FM do something it is not designed to do. Maybe that is what I have done, but if so, I am frustrated that I could not accomplish the same goal in another more robust manner.

If there is another piece of the puzzle that I am missing, I certainly wish I had it. Am I alone in wanting to do this? Again, it seems to me that the question has been asked and considered by at least a few in the past, but so far I understand none of the answers to be direct solutions...shoot, my workaround is not even remotely direct, except it does use relationships and calcs, which are understood AFAIK to be the most reliable means to an end here.

[comment]

I don't think there is a "direct" solution, simply because from the point-of-view of a relational database, you are asking for something that is not.

Basically, you want a portal to Materials. That is easy. But then you also want this portal to show the quantities relevant to the currently viewed Product. How is the Material record supposed to know that? The quantities are stored in two join tables along the way.

There is a good reason for this - it's the most efficient arrangement in terms of eliminating redundancy. But it also means that there is no "direct" way, relationships-wise, to get the result you want - because it needs to be computed from raw data. The quantities need to be traced through the two join tables along the way and multiplied.

Perhaps it would be different if Filemaker supported more types of joins. I don't know much about SQL, but I think in SQL you could get this by constructing the proper query. OTOH, in SQL there is no difference between list view, a portal, or a scripted report - everything is just a result returned by the submitted query (if I understand correctly). So perhaps this difficulty is just another price to pay for Filemaker's ease-of-use.

it does use relationships and calcs, which are understood AFAIK to be the most reliable means to an end here

Not necessarily - because this is not about manipulating data. This is merely a display issue, so a scripted solution, for example, seems perfectly acceptable to me (in terms of reliability).

[SurferNate]

So, having been over to that other thread, where you and Sopren discuss efficiency of find operations on stored and unstored calcs. Is it reasonable to think that a scripted solution could be faster than unstored calcs?

I'm studying up on recursion, and more than once I played with the idea of creating a dedicated Reports table, to contain only the calculated info, that could "bridge" multiple tables and perform recursive summaries on a RecordNumber = RecordNumber basis. This way I can have one actual unique record for each recursive summary, and could also use that as a temporary join table from Product to Materials.

I guess the hangup that brought me thus far is that I don't want to just display a summary from Product, I want to be able to also quickly interact with root values stored in Materials at the same time. This has to do more with business logic than DB logic. In business, if I am working on Product, I can quickly see if one of my Materials is out of date or incorrect, and make the change "live". I also have calculations/logic that converts partial "lots" of material to whole "lots" for ordering purposes. Even though a Sandwich contains two slices of Tomato, I still need to buy a whole Tomato. Not that any of that constitutes requisite conditions for the structure I finally came to, just that considering those conditions, I have not come up with any other more elegant solution.

Oh, and knowing very little about SQL, and learning a cursory amount about the differences between transactional and relational DB structure, I'm way over on the side of relational in this case.

[SurferNate]

And so I feel compelled to rant.

/rant/ Why is it that the more "normal" I try to make my data and structure, the more difficult it becomes to aggregate and analyze?/*rant/

[comment]

Why is it that the more "normal" I try to make my data and structure, the more difficult it becomes to aggregate and analyze?

Like everything else, the relational model has its advantages and its weak points. This just happens to be one of those. Another example is when a comparison between siblings has some significance - because the model assumes siblings are independent of each other, and unordered*. OTOH, the relational model provides a very efficient way to store data, even in your example.

BTW, I don't think 'relational' is the opposite of 'transactional'. SQL itself follows the relational model. See:

http://en.wikipedia.org/wiki/Relational_model

Is it reasonable to think that a scripted solution could be faster than unstored calcs?

It rather depends on what the calculations are and what does the script do. I don't think you can formulate any rigid rules here.

I don't want to just display a summary from Product, I want to be able to also quickly interact with root values stored in Materials at the same time.

Assuming interaction means GTRR, not modifying data directly in a portal, it should be still possible, even if using a calculated result. Suppose the result is presented in a repeating field. You could click on a repetition, have the script pick the MaterialID from that repetition, put it in a global and GTRR from there.

---

(*) LOL, speak of the devil: http://www.fmforums.com/forum/showtopic.php?tid/194263/

Edited March 27, 2008 by Guest

[Søren Dyhr]

Remind of the reasons why the recursive structure was rejected, it has indeed some "normal" 'ish virtues:

http://www.fmforums.com/forum/showpost.php?post/282444/

...he talks about "Big Blue" of all!

--sd

[comment]

1. I am guessing a recursive structure was rejected because (a) the attributes for each level are different, and ( because the real-life situation is NOT recursive, i.e. the same element is ALWAYS used at the same level.

2. I don't see that a recursive structure is any more or less normalized than a rigid structure.

3. What advantages would a recursive structure have here? If you use custom functions to gather the base materials, the problem is the same. If you try to use relationships, it's impossible.

[Søren Dyhr]

I do not quite get this argument, sets and items has absolutely nothing in common except a primary key and that they belongs to the same table, items have a stored price sets do not, but are dependent on quantity and the sub items selected.

Where do Picknic A differ from Sandwich B they're both descriptions of sets?

--sd

[comment]

Where do Picknic A differ from Sandwich B they're both descriptions of sets?

That is not for me to answer. I am guessing SurferNate does not actually organize picnics with sandwiches and salads... In any case, the option to go recursive has been presented early in the thread. And you haven't answered my question: what advantages would a recursive structure have here?

items have a stored price sets do not

Perhaps you know more about SurferNate's solution than I do. This is certainly not true in general: sets can have either a discount or a value-added surcharge (not that that's any reason to choose or reject a recursive structure).

sets and items has absolutely nothing in common except ... that they belongs to the same table

Isn't that a contradiction?

[SurferNate]

In answer to the last few replies, no of course I don't plan picnics for a living. The Product-Assembly_Material structure is practical to me business wise, and so used a fairly simple picnic as a metaphor.

I did spend a little time playing with Matt Petrowsky's recursive "Infinite Hierarchies" solution, understood it pretty well, and realized I wanted to force a more explicit set of rules and levels. Yes recursion can be limited pretty easily. I just failed to see a substantial benefit.

I realize that at some time I might want to add optional Super-Assemblies, but that's not terribly concerning to me right now. One big part of this is the point of knowing exactly how and why this structure works, as I can foresee using it again.

Comment you mentioned using a repeating field, except I understand that to have it's own limits. What if Assembly eventually contains more than 100 different materials?

Also, reporting on the join table only works if all of the record joins are similar in nature, Comment nailed the point down pretty well in recognizing that one has separate join records from Parent to Child, and from Child to Grandchild. Even though I use a multipurpose join table, there is a subset of Parent=Child records and another subset of Child=Grandchild records, where the only commonality is Child. Grandchild never actually equals Parent.

[Søren Dyhr]

And you haven't answered my question: what advantages would a recursive structure have here?

Get rid of one of the entity duplications, parent and child has the same set of attributes and does therefore not need to be split in two tables ... what I have in mind here is a join table'less many2many recursive structure each layer or recursion is linked by a multiline key, and actually be one and same table?

I would have to admit not having read the thread thoroughly enough to be absolutely sure.

---------------------------

No the join can't be avoided since the quantity is required

--sd

[comment]

parent and child has the same set of attributes

I am afraid I know no such thing. Only if SurferNate decides to tell us what this solution is about, will I be in a position to make such judgement (maybe).

And what about the grandchild, which in a recursive solution would be forced into the same table - or are you proposing a hybrid?

[comment]

used a fairly simple picnic as a metaphor.

Note that such metaphors can often be very misleading and generate less than optimal advice:

http://www.fmforums.com/forum/showtopic.php?tid/191864/

you mentioned using a repeating field, except I understand that to have it's own limits. What if Assembly eventually contains more than 100 different materials?

Well, a repeating field can have 32,767 repetitions, so that's hardly a limitation. But you might have a display problem here, since - unlike a portal - you cannot scroll through the repetitions of a repeating field. Though it is possible to build a 'paging' system of sorts, like 'show next 25'.

Edited March 29, 2008 by Guest

[Søren Dyhr]

And what about the grandchild, which in a recursive solution would be forced into the same table - or are you proposing a hybrid?

I think I am, parent-child a recursive construct, while grandchildren ....

The problem here is that when I hear something like how many picknic's could x leafs of salad produce, am I pavlov'ian way into recursive structures and not the transaction model ... simply because my tool is filemaker, and I have absolutely no idea of how to do the things I do in filemaker in other tools.

At the same time isn't recursive solutions just something I cough up, I do indeed still need an example or two to lean up against, before performing it. One of those is this one:

http://www.jonathanstark.com/downloads/Inventory.fp7.zip

The other one is the one following ISBN: 1-55622-859-7

--sd

[comment]

The problem here is that when I hear something like how many picknic's could x leafs of salad produce, am I pavlov'ian way into recursive structures

I didn't want to say it, but that is more-or-less my complaint here. I think the main advantage of recursive model is ... well the fact that it is recursive. In situations that are naturally recursive, such as genealogy, or when components and sub-assemblies can also be sold as products - going recursive is practically a must.

But recursive has prices to pay: the same table structure is forced upon all elements, and it becomes much more complex for the user entering a new element into the system (or perhaps I should say it becomes much more difficult to make it simple for the user). And Filemaker is not 'natively' recursive. Try telling it to find all of record's grandchildren. Producing reports is also not trivial, as it is with a rigid structure (present example excluded).

These things must be considered and weighted before deciding which is best for a particular solution.

[SurferNate]

Comment,

Yes, metaphors are not necessarily accurate. In this case, it fits the model I have in mind perfectly. I am more than aware that after muddling my way through "forcing" the result I want, I do sometimes revert back to a more simplistic solution. Such is the learning curve.

I still don't think I can go recursive in a practical way, although I did already play with Jonathan Stark's Inventory file when working on this problem.

I'm still more than open to the idea of recursion, It just means AFAIK that I wind up with a whole huge mess of fields in one or two tables, not including the requisite multikeys.

Also I didn't know that repeaters could go to such a high value. I was held back thinking the limit was 100.

[comment]

I was a bit over, quoting from memory - I have edited my post to include the exact number as specified by FMI.