1 2 3 Previous Next 30 Replies Latest reply on May 8, 2017 5:35 AM by fmpdude

    Simulated tree handling

    mbeck65

      Hello to everybody,

      I'm developing a solution that needs a grouping facility useful to dinamically summarize totals in base of the groups definition.

      What I'm trying to do is implement something similar to a tree management system based on multiple tables themselves linked.

      More easy to explain by using an example instead of try to do it by talking about that...

      Attached to this discussion I send an fmp12 file that describes what I mean; inside you can find the following tables:

      - data, to store value and fields for summarization

      - groupData, to define groups of data

      - groupGroup, to define groups of groups (this table contains a field named "level" which describe the tree's level of a node)

      - gd_lnk_data, to link groups of data to data

      - g_lnk_gd, to link groups to groups

      I've also populated all the tables with example data so to create a tree similar to the following:

       

                                 

                        gdg level=1             3

                                                      /   \

                        gdg level=0          1     2

                                                   /  \       \

                        gdd                   1    2       3

                                               / / \   / \ \     / \ \

                        data              1 2 3 4 5 6  7 8 9

       

      What I would like to do is create a report able to show summarizations per levels but... I am able to do it only for the level 0 (see the report's example inside the solution...)

       

      Have someone ideas on how to do that by knowing also that I would like to have the possibility of go up in the tree's levels definition?

       

      Thanks a lot for your suggestions and don't hesitate to ask in case my explanation in not clear enough.

       

      Best

       

      Max

        • 1. Re: Simulated tree handling
          TomHays

          By using the table gd_lnk_data, you have configured your model to allow one record in the data table to exist in more than one group.

           

          Since you are doing your summarizing from the data table via the "sum" field (Summary = Total of value), it looks like you are assuming that each record in the data table belongs in only one group.

           

          If you do want to have only one group per data record, it may be a good idea to remove the gd_lnk_data table and put the idGroup field directly into the data table.  That would remove the need to enforce strict data entry rules in the gd_lnk_data table and eliminate a pitfall.

           

          If want a record in the data table to exist in more than one group, then you probably need to do the summary reporting from the vantage point of the gd_lnk_data table instead of the data table.

           

           

          -Tom

          • 2. Re: Simulated tree handling
            mbeck65

            Yes, Tom, the model foresees that one record in the data table exists in more than one group.

             

            Actually I don't understand what you mean in your last sentence: yes, I can make the summary reporting from the gd_lnk_data table instead of the data table but my goal can't be anyway achieved because the summarization of groups higher to level 1 doesn't work.

             

            Let me know if I miss anything in what you want to say and, in any case, thanks a lot for your answer.

             

            Best

             

            Max

            • 3. Re: Simulated tree handling
              fmpdude

              Hey,


              There was a similar posting like this a few months ago. In that case, the OP wanted to roll up numbers much as you're wanting to do, but in that OP's case, it was for multi-level marketing. You might want to do some searching here on the forum if you haven't already.

               

              To solve that problem, I found FMP was going to be too difficult to use (though, full disclosure, I'm not a full-time FMP person and I didn't have unlimited time). Therefore, I whipped up a quick Java service that I could call from FMP passing data.

               

              That Java service used actual tree data structures (Node(s)) to implement what that OP wanted.

               

              My primary data structure for each Node, was this:

              The data in that case was M:M, naturally.

               

              Running the code I wrote, my console output, given the OP's example, was this:

               

              Now Visiting Node...root with value: 0

              *** child nodes sum: 3

              Now Visiting Node...Record B with value: 3

              *** child nodes sum: 2

              Now Visiting Node...Record D with value: 2

              *** child nodes sum: 9

              Now Visiting Node...Record E with value: 7

              *** child nodes sum: 9

              Now Visiting Node...Record C with value: 6

              *** child nodes sum: 7

              Now Visiting Node...Record E with value: 7

              *** child nodes sum: 11

              Now Visiting Node...Record F with value: 4

              **** Final sums**** : 9

              **** Final sums**** : 20

               

              -------

               

              The OP's Diagram from that posting is below:

               

              • 4. Re: Simulated tree handling
                jbante

                Look into the literature on approaches for materializing OLAP cubes. This paper gives a good overview of some different approaches to the logic of it.

                 

                I'm experimenting with how to implement some of those ideas well in FileMaker. I'm not settled on a final conclusion, and I'm working on a more general solution; but here's what my thoughts are now, such as they are:

                • Stored fields, rather than calculation fields based on related data. This becomes a big deal for making reporting usably fast with larger amounts of data.
                • Simpler structure: one table where each record is a different level of aggregation of data (or different combinations of fields being grouped-by), and one child table for all the summarized measures at those levels of aggregation. If you're clever about building the keys to indicate which records in the child table correspond to which combination of group-by fields, the parent records are mainly for holding metadata used to be smarter about how summaries get calculated.
                • Populate summary records by doing group-by exports of more detailed data, and importing to another set of summary-detail records.

                When base records can contribute to more than one group summary (at the same level of aggregation), things get a little complicated. Avoid that if you can help it.

                1 of 1 people found this helpful
                • 5. Re: Simulated tree handling
                  TomHays

                  When reporting from the data table you are listing records in the data table.  Your sum field is totaling the records being displayed in the data table.  If a record can appear in more than one group, then you want that record to contribute more than once in your total.  The gd_lnk_data table has the record appearing once for each membership in the group which is consistent with your usage of sum.

                   

                  But if you do some other form of data aggregation aside from using sub-summaries in the data table, you can probably achieve what you want.

                  I am thinking that you may need to use a script to do the aggregation and present the results using the virtual list technique.

                   

                  At this point I'm not convinced yet that the tables and relationships you are using are the best fit for your model, but I don't fully understand the rules.

                   

                  Can a group be a member of more than one group of groups?

                   

                  -Tom

                  • 6. Re: Simulated tree handling
                    mbeck65

                    Hello Jbante,

                    I hoped it was easier the solution that I'm looking for but probably an OLAP approach could be harder but more effective... Unfortunatelly I'm not an expert of OLAP but I'll try to invest time to learn something that will be usefull to solve my troubles...

                     

                    In any case thanks a lot for your comment: your think has been a lighting...

                     

                    Best

                     

                    Max

                    • 7. Re: Simulated tree handling
                      mbeck65

                      When reporting from the data table you are listing records in the data table.  Your sum field is totaling the records being displayed in the data table.  If a record can appear in more than one group, then you want that record to contribute more than once in your total.  The gd_lnk_data table has the record appearing once for each membership in the group which is consistent with your usage of sum.

                      This is not the behaviour that I would like... Actually I would prefere that each record contributes one time in every total...

                       

                      But if you do some other form of data aggregation aside from using sub-summaries in the data table, you can probably achieve what you want.

                      I am thinking that you may need to use a script to do the aggregation and present the results using the virtual list technique.

                      I don't know the "virtual list technique" : could you please tell me something about that?

                       

                      At this point I'm not convinced yet that the tables and relationships you are using are the best fit for your model, but I don't fully understand the rules.

                       

                      I'm not convinced too... That's why I'm looking for ideas here

                       

                      Can a group be a member of more than one group of groups?

                       

                      No, it can't do: every group could have more child groups but only 1 parent.

                      • 8. Re: Simulated tree handling
                        fmpdude

                        An OLAP cube is really a fancy way of saying you have multi-dimensional data set often stored in a multi-dimensional array. Since FMP doesn't offer arrays as a native type, you'd need to use clever hacks.

                         

                        For a project like this I would seriously be asking myself if FMP was the right tool for this effort. For computer science-type projects, you really want a flexible programming language that will let you create data structures and do whatever you need.

                         

                        OK, so what I would do in your case, is model the heck of what I was trying to first do rather than figure out how to implement something I don't have completely defined. Perhaps you do have it totally defined, I can't be sure.

                         

                        When I coded the tree multi-level marketing example for the other OP's posting I mentioned, I spent a good hour with paper and pen making sure I understood how the totals would bubble up. The computer was "off". Once I understood what I needed to do, the code was quite fast: about 30 minutes.

                         

                        As I have seen often when mentoring junior developers (not referring to you here) jumping in and trying to code something they really haven't though through only gets them so far and they start over (process repeats...). Sometimes that bottom-up approach is needed, but you clearly have all the specs.

                         

                        A good example would be to try to write the Solitaire game (again, NOT in FMP). Without knowing all the rules first, how would you do it? Sure you can understand linked lists (and you would need to), but implementation details come later in my view.

                         

                        So, assuming you haven't done this, step back from the computer and really do a top down approach to your design.

                         

                        Counter-intuitively, it's not the case that writing a line of code gets you one line closer to completion when the design really isn't there in the first place.

                         

                        Coding is supposed to be the easy part...

                         

                        HOPE THIS HELPS.

                        • 9. Re: Simulated tree handling
                          TomHays

                          mbeck65 wrote:

                           

                          If a record can appear in more than one group, then you want that record to contribute more than once in your total. The gd_lnk_data table has the record appearing once for each membership in the group which is consistent with your usage of sum.

                          This is not the behaviour that I would like... Actually I would prefere that each record contributes one time in every total...

                           

                           

                          That's rather unusual especially considering the report format in layout "repo2".

                           

                          GROUP OF GROUPS 1 (Sub-total 210)

                          GROUP1 (Sub-total 60)

                          ID=1 Value 10

                          ID=2 Value 20

                          ID=3 Value 30

                          GROUP2 (Sub-total 150)

                          ID=4 Value 40

                          ID=5 Value 50

                          ID=6 Value 60

                           

                          If I understand correctly record ID=4 can exist in multiple groups which is why you have a gd_lnk_data table instead of adding the idGroup field directly to your data table.  Thus with the addition of one record in gd_lnk_data, this report would look like the following.

                          (Note that this cannot be expressed simply with the Data table since Record #4 appears twice.)

                           

                          GROUP OF GROUPS 1 (Sub-total ?)

                          GROUP1 (Sub-total ?)

                          ID=1 Value 10

                          ID=2 Value 20

                          ID=3 Value 30

                          ID=4 Value 40

                          GROUP2 (Sub-total ?)

                          ID=4 Value 40

                          ID=5 Value 50

                          ID=6 Value 60

                           

                          And you want each data record to contribute only once to the total.

                          What should these subtotals be?

                          If you do

                          GROUP1 (Sub-total 100)

                          GROUP2 (Sub-total 150)

                          you are adding directly the records in each group which is what the reader of the report expects.

                          Similarly

                          GROUP OF GROUPS 1 (Sub-total 250)

                          is expected as the sum of the individual groups.

                          But that doesn't satisfy the requirement that a record only contributes once to the total.

                           

                          You could do GROUP OF GROUPS1 = (210)

                          and ignore the fact that it is less than the sum of GROUP1 and GROUP2.

                           

                          What if ID=4 is in GROUP3 instead (or in addition to) GROUP1?

                          Now it contributes to GROUP OF GROUPS 2.

                           

                           

                          Are you sure that a record in the data table can be in more than one group?

                           

                           

                          No, it can't do: every group could have more child groups but only 1 parent.

                           

                          That indicates that you don't need the g_lnk_gd table and can add idParent directly to the groupData table.

                           

                          I don't know the "virtual list technique" : could you please tell me something about that?

                           

                          This is a technique where you assemble the information you want to report into FileMaker global script variables (the List) and then display the information using a dedicated table of calculated fields (the Virtual table) that pull information from those script variables.  Once it is set up, this method can be used to display spontaneously calculated information that might otherwise be difficult to present from one table.

                           

                          https://filemakerhacks.com/tag/virtual-list/

                           

                          http://www.soliantconsulting.com/blog/2015/11/using-virtual-list-technique-part-1

                           

                          http://mightydata.com/virtual-list-in-3-easy-steps/

                          • 10. Re: Simulated tree handling
                            mbeck65

                            An OLAP cube is really a fancy way of saying you have multi-dimensional data set often stored in a multi-dimensional array. Since FMP doesn't offer arrays as a native type, you'd need to use clever hacks.

                            we need always clever hacks, not only using FMP

                             

                            For a project like this I would seriously be asking myself if FMP was the right tool for this effort. For computer science-type projects, you really want a flexible programming language that will let you create data structures and do whatever you need.

                            sometime you can't choice the sw infrastructure you've to use but... sometime customers, sws already used/installed, solutions already working (not necessarly ok but effective), company's policies and processes (etc. etc. etc...) decide instead of you

                             

                            OK, so what I would do in your case, is model the heck of what I was trying to first do rather than figure out how to implement something I don't have completely defined. Perhaps you do have it totally defined, I can't be sure.

                             

                            When I coded the tree multi-level marketing example for the other OP's posting I mentioned, I spent a good hour with paper and pen making sure I understood how the totals would bubble up. The computer was "off". Once I understood what I needed to do, the code was quite fast: about 30 minutes.

                             

                            As I have seen often when mentoring junior developers (not referring to you here) jumping in and trying to code something they really haven't though through only gets them so far and they start over (process repeats...). Sometimes that bottom-up approach is needed, but you clearly have all the specs.

                             

                            A good example would be to try to write the Solitaire game (again, NOT in FMP). Without knowing all the rules first, how would you do it? Sure you can understand linked lists (and you would need to), but implementation details come later in my view.

                             

                            So, assuming you haven't done this, step back from the computer and really do a top down approach to your design.

                             

                            Counter-intuitively, it's not the case that writing a line of code gets you one line closer to completion when the design really isn't there in the first place.

                             

                            Coding is supposed to be the easy part...

                             

                            HOPE THIS HELPS.

                            I'm not sure my model is the right one: probably you are right... of course, what you see represents just a very simple sample of the complex system on which I'm working  because I thought it was necessary simplify the context...

                             

                            But, just to understand us, what I'm searching with my post is not the solution of a problem: I'm only sharing with the community my doubts about the approach that I'm following in the project on which I'm working...

                             

                            I absolutely agree with you: coding is the easier part and I can assure you that I've spent a lot of time by designing my environment... now I'm in a closed road and I'm looking for some new advice to make progress in implementing my solution

                             

                            In any case, thanks for your time and your contribution: I'll try to revise my model by hoping I'll able to find something useful to renew it

                            • 11. Re: Simulated tree handling
                              fmpdude

                              Glad to offer comments. Some projects, like yours, are more difficult in a "forum" environment.

                               

                              If it were me, considering the complexity of what you're doing, I would seriously consider tacking this in a real programming language. Using JDBC where you could extract the data you need via SQL, or using a micro-service where you do GETs or POSTs, you could still use FMP and have real-time results and use a real programming language.

                               

                              Good luck!

                              • 12. Re: Simulated tree handling
                                mbeck65

                                That's rather unusual especially considering the report format in layout "repo2".

                                 

                                GROUP OF GROUPS 1 (Sub-total 210)

                                GROUP1 (Sub-total 60)

                                ID=1 Value 10

                                ID=2 Value 20

                                ID=3 Value 30

                                GROUP2 (Sub-total 150)

                                ID=4 Value 40

                                ID=5 Value 50

                                ID=6 Value 60

                                 

                                If I understand correctly record ID=4 can exist in multiple groups which is why you have a gd_lnk_data table instead of adding the idGroup field directly to your data table.  Thus with the addition of one record in gd_lnk_data, this report would look like the following.

                                (Note that this cannot be expressed simply with the Data table since Record #4 appears twice.)

                                 

                                GROUP OF GROUPS 1 (Sub-total ?)

                                GROUP1 (Sub-total ?)

                                ID=1 Value 10

                                ID=2 Value 20

                                ID=3 Value 30

                                ID=4 Value 40

                                GROUP2 (Sub-total ?)

                                ID=4 Value 40

                                ID=5 Value 50

                                ID=6 Value 60

                                 

                                And you want each data record to contribute only once to the total.

                                What should these subtotals be?

                                If you do

                                GROUP1 (Sub-total 100)

                                GROUP2 (Sub-total 150)

                                you are adding directly the records in each group which is what the reader of the report expects.

                                Similarly

                                GROUP OF GROUPS 1 (Sub-total 250)

                                is expected as the sum of the individual groups.

                                But that doesn't satisfy the requirement that a record only contributes once to the total.

                                 

                                You could do GROUP OF GROUPS1 = (210)

                                and ignore the fact that it is less than the sum of GROUP1 and GROUP2.

                                 

                                What if ID=4 is in GROUP3 instead (or in addition to) GROUP1?

                                Now it contributes to GROUP OF GROUPS 2.

                                 

                                 

                                Are you sure that a record in the data table can be in more than one group?

                                I understand it sounds strange...

                                I try to explain my need with an example:

                                GROUP1 = VEGETABLES

                                GROUP2 = FRUIT

                                 

                                GROUP OF GROUPS 1 = GREENGROCER'S WAREHOUSE

                                 

                                APPLE = 4

                                ORANGE = 3

                                LEMON = 2

                                TOMATO = 10

                                EGGPLANT = 1

                                 

                                VEGETABLES = APPLE+TOMATO+EGGPLANT (sub-total = 15)

                                FRUIT = APPLE+ORANGE+LEMON (sub-total = 9)

                                 

                                GREENGROCER'S WAREHOUSE = VEGETABLES+FRUIT (sub-total=20)

                                 

                                I would need of these totalizations: strange but necessary

                                 

                                That indicates that you don't need the g_lnk_gd table and can add idParent directly to the groupData table.

                                You're right: my mistake in the answer... Actually I don't need g_lnk_gd table ( it's a refusal of a model I'm no longer using)

                                 

                                This is a technique where you assemble the information you want to report into FileMaker global script variables (the List) and then display the information using a dedicated table of calculated fields (the Virtual table) that pull information from those script variables.  Once it is set up, this method can be used to display spontaneously calculated information that might otherwise be difficult to present from one table.

                                 

                                https://filemakerhacks.com/tag/virtual-list/

                                 

                                http://www.soliantconsulting.com/blog/2015/11/using-virtual-list-technique-part-1

                                 

                                http://mightydata.com/virtual-list-in-3-easy-steps/

                                I'll study it to learn all possible !!! thanks a lot for the suggestion

                                • 13. Re: Simulated tree handling
                                  mbeck65

                                  I understand it's not easy to explain and more to explain in english that's not my mother tongue

                                   

                                  Thx you so much

                                   

                                  Max

                                  • 14. Re: Simulated tree handling
                                    fmpdude

                                    Wouldn't an ERD help immensely here understand (or communicate) what's going on?

                                    1 2 3 Previous Next