3 Replies Latest reply on Apr 28, 2015 8:53 AM by Heidelberg

    Summing up results from 4 files in a master file



      Summing up results from 4 files in a master file


      I am just stuck how to relate "many to one". A link to a help page which I should study to solve my issue would be great.


      Wonder if someone could give me a starting point of how to du that:

      I have 4 Files of sales north west east south. I’d like to have those data shown in one masterfile. For example listing 4 lines - each of which shows the sales in one of the 4 regions plus a sum.

      Example: Sales of bread = 4 lines. Total sales of bread = 1 line.

                      Sales of Mile = 4 lines. Total sales of milk = 1 line.

      The design actually does not matter as long as I get an overview

      The above sample is just for easy illustration of my "problem". The real use is for my LEGO collection. I have for each construction set a file with all pieces (number, color,name etc.) and I want to have an overview of how many parts of a certain typ I have in total . that would allow me to judge if I can build something new with the pieces I have already bought.

        • 1. Re: Summing up results from 4 files in a master file

          have 4 Files of sales north west east south.

          This is not ideal. It would be better to store all of this data in the same table of the same file with an added field that identifies the region for each record. You can use Import Records to pull of this data into such a single table.

          Given that you have many more than four files for your lego sets, this is even more the case.

          What you describe is really what manufacturing databases call a BOM, a bill of materials--a list of all items and their quantities needed to assemble/manufacture a single unit of product.

          So you might consider this many to many relationship:


          LegoSets::__pkLegoSetID = BOM::_fkLegoSetID
          LegoParts::__pkLegoPartID = BOM::_fkLegoPartID

          Note that the same part can be found and used in multiple lego sets. The existence of a specific part in a specific lego set is documented in the BOM table where each record links a particular LegoSet to a particular part. A portal to BOM on the LegoSets layout can list every part used in that set. A portal to BOM on the LegoParts layout could list every Set that uses that part. (When setting up a portal to the BOM on the LegoSets layout, you can include fields from LegoParts in the portal row. When setting up a portal to the BOM on the LegoParts layout, you can include fields from the LegoSets table.)

          • 2. Re: Summing up results from 4 files in a master file

            Thanks for your comments. I will do some testing with smaller files containing less records. So far I have mostly used only one table included dummy fields for "internal" relations. The reason for experimenting with individual tables was more of seeking a challenge since FM-design is more of a hobby for me keeping that 70year old brain fit. My other hobby is playing with Lego. Being a retired manager its a great joy now not focus anymore on next quarter's result..

            It may take a while but I will be back - hopefully with some contribution for others to use.


            • 3. Re: Summing up results from 4 files in a master file

              So - finally I’ve found more than one option. You may need some flexibility regarding my use of the english names for menu items etc. since I translate that from German.



              I imported all 4 Tables in one table „ALL SALES“ - adding an additional field to distinguish  the regions south west north east.

              The other field were product data and salesvolume incl. main categories like bread have the same article number

              In the FM-management menu I duplicate the „ALL SALES“ and named it „ARTICLES“ 

              Create a relationship between „ALL SALES“ and „ARTICLES“ based on article numbers

              I created a field sum(ARTICLES::salesvolume)


              If you put the fields from „ARTICLES“ in a portal and sort it by article number you see the individual items like 

                  Products                                   salesvolume           total

                  12345 bread toast                      500                        800

                  12345 bread wheat                    200                        800

                  12345 bread sliced                    100                        800

              The above is in the formula view i.e. you always see only the one related to the article shown in the current window. 

              In the tableview (the 3. viewing option) you see all items of the FM-file.


              For practical purpose useable but what is not so nice … the summary of one particular article shows up next to all lines for that article.



              So I decide to try out FM option of subtotals. I have attempted to use subtotals several times in the past already and gave up because I did not manage to force subtotals in an existing table. I read FM help more carefully and realized that you need to start with a new layout.

              Details can be found here:  http://www.filemaker.com/help/12/fmp/html/non_toc.46.14.html

              That’s exactly what I wanted in the first place

              As I wrote that all is to be used for my LEGO-hobby. My final plan is to add the parts needed for interesting constructions. Using a script it should be possible to change the number of parts needed to a negative value. If my subtotal table shows no negative sums I can build that construction. Otherwise I can easily see what's missing.



              As a side effect I found 2 more ways to links tables in a multilevel way. The relationships are for example  based on article numbers. The top-level uses the plain article number to build a relationship to the „inbetween-level“. In the direction to the base level the inbetween-level uses article number preceeded by the letter of that particular table:

              Top-level     inbetween        inbetween    lowest level A    level B     level C     level D

              1234   …         1234              A1234            A1234              

                                                            B1234                                 B1234

                                                            C1234                                                   C1234

                                                            D1234                                                                 D1234

              If I understand it correctly the whole concept is called „ghost system“ or so


              A similar construction is this one using a virtual table as inbetween




              Finally I found in the same post a neat modification which also employs a script:


              It is from a German guy but the FM coding should translate automatically in your local language.



              3) + 4) are from this German forum-post:



              You can open the above mentioned  .fm7 in FM12 where they are converted without affecting the original.