1 2 Previous Next 19 Replies Latest reply on Jan 26, 2010 6:14 AM by qwedley

    SUM of fields in related records

    qwedley

      Title

      SUM of fields in related records

      Post

      Greetings. I have built a rudimentary purchase order database that works pretty well. The only thing I've really struggled with is calculating order totals. I have an item info table, a PO table and a PO Lines table (among others). These tables are all related, if not always directly. I often order from more than one vendor and I often order a given item in more than one package size. So for most items, I have more than one PO line. On top of that, I often have multiple orders for the same item in different years.

       

      Here's what I'd like to be able to do:

      1) On the PO Lines layout, I'd like to see the running total for the item for the year (if I'm looking at a 2009 PO line, I'd like to see the 2009 total ordered).

       

      2) On the Item info layout, I'd like to be able to see the total ordered by year: 2007: 15,200; 2008: 11,000; 2009 12,250; etc.

       

      3) On a separate layout (yet to be created), I'd like to see summary information by category and by year: 2007 widgets, nuts, bolts, total--same for 2008 etc.

       

      Help! 

        • 1. Re: SUM of fields in related records
          philmodjunk
            

          On the PO Lines layout, I'd like to see the running total for the item for the year (if I'm looking at a 2009 PO line, I'd like to see the 2009 total ordered).

          If you use the PO Lines layout for other purposes, create a new layout that references the PO Lines table in Layout Setup... Otherwise you can just use the existing layout.

          Define a new field, cOrderYear, as Year(OrderDateField). For faster performance, you'll probably want to auto-enter a date (you have several options here) into a field in your PO Lines table and refer to it in this calculation. You could refer to a date field in the PO table, but that can have some signficant performance issues when you go to set up your report.

          Define a summary field, set up as the Total of your PO Lines::Qty Ordered field. Select the running total option. Also select the restart summar for each sorted group option and select cOrderYear in the "when sorted by" pane.

           

          Now format your layout as a list view report with your PO Lines fields in a row and include the above summary field in the body.

          Enter browse mode and sort your records by part and cOrderYear and you sould see a list of PO Lines records with a running total that restarts with each year.

           

          On the Item info layout, I'd like to be able to see the total ordered by year: 2007: 15,200; 2008: 11,000; 2009 12,250; etc

          This reads like you want columns of summary data summarized by year. This can be done but if you use the following report format, it's easier to setup:

          Part ABC

             2007    15,200

             2008    11,000

             and so forth...

          Part XYZ

             2007     12,000

             2008     12,100

          etc.

          How badly do you need the column format? Let me know and we'll take a swing at it if that's what you truly need here.

           

          On a separate layout (yet to be created), I'd like to see summary information by category and by year: 2007 widgets, nuts, bolts, total--same for 2008 etc.

          This is a classic form of summary report.

           

          Base your layout on PO Lines.

          Create a sub-summary part when sorted by year.

          Create a sub-summary part when sorted by category.

          Place the fields you mention in the "category" sub-summary part--including the summary field I mentioned earlier.

          If you don't want a list of individual PO Lines in your report, delete the body.

          If you wish, you can re-size the Year sub-summary part to be just a few pixels tall and leave it blank or you can place year specific fields in this part depending on how you want to format your report.

          Now your report should work providing you sort first by year and then by category.

           

          Note to users who are using versions of Filemaker older than FMP 10: Such summary reports only show correctly in preview mode or when you print the report.

          • 2. Re: SUM of fields in related records
            qwedley
              

            Sorry for the long silence. I fell into the holiday vortex. I'm back in the saddle--for two or three days...

             

            Thank you for your suggestions on items 1 and 3. I will work to implement them. My experience with summary fields is limited. Need to explore.

             

            As far as item 2 goes, I am indeed envisioning a table. The table would appear on the item info page. User would be able to go to the info page for a given item and see a table. On one side (we'll say the left side), user would see years. There would be columns as follows: Initial Order (data entry), Total POs (calculated from PO Lines), Received (calculated from PO Lines), Sold (data entry), Sold-Out Date (data entry).

             

            Is this feasible?

             

            Thank you for your help! 

            • 3. Re: SUM of fields in related records
              philmodjunk
                

              It looks like we can adapt a method we use where I currently work. In several places, you'll have to use your field names in place of mine.

               

              Create a new table, SummaryByYear, with the following fields:

              YearPurchased (number field)

              gMaterialID (Global field, to match to records in your POLines table)

              Total PO's (Count (PO Lines::MaterialID) )

              Recieved (similar calculation)

               

              (can't tell from your post what kind of data Initial order (invoice number, date, or ?), Sold, and Sold-Out Date refer to. You may be able to refer to a field in a related table or you may have to set up a calculation field)

               

              In your PO Lines table, define a calculation or Auto-enter by calculation number field: YearPurchased as Year(ItemSaleDateField).

               

              Define a relationship with two pairs of fields:

              PO Lines::MaterialID = SummaryByYear::gMaterialID AND

              PO Lines::YearPurchased = SummaryByYear::YearPurchased

               

              To use this setup, create a new record for each year that you want to use in summarizing your data. Then enter an ID value for a specific item in gMaterialID, (You can define a value list for this) to specify the item for which you want to see this data. Note that the same record in this new table can be used for any item in your database, you just find and/or create a record for each desired year and then specify the item in the global field.

               

               

              • 4. Re: SUM of fields in related records
                qwedley
                  

                Thank you for your help. I will endeavor to implement your suggestion.

                 

                As you may have inferred, I am new to databases and to FileMaker (I'm using 9.0 v3). Thank you for taking the time to help bring me along.

                 

                It occurred to me that it might be useful for me to send you a pdf of where I am and maybe to show what I'd like my database to do. Is that possible? Would you like me to do that? Or maybe that would be TMI...

                 

                A general question: I am struggling with the mutability of the data in summary fields. They change according to the way Finds are implemented and results are sorted. I would like a static summary that only changes if an element in the equation changes. Is it possible to write a calculation that does what I want? Example: I would like to have a calculation field that presents the sum of all orders placed in a given year, of all orders in a category placed in a given year, of all orders of an item placed in a given year, etc.

                 

                I understand that a script could be written to do what I want, but a script has to be triggered somehow and would have to be re-run to show any change in any of the elements of the calculation. 

                 

                 

                • 5. Re: SUM of fields in related records
                  philmodjunk
                    

                  "...it might be useful for me to send you a pdf of where I am and maybe to show what I'd like my database to do. Is that possible?"

                  You can upload screen shots to a filesharing web site and then use one of the link tools above to post a link to your shared screen shot, PDF or even a sample file so others can take a closer look.

                   

                   "I am struggling with the mutability of the data in summary fields. "

                  The same summary field can be used to compute very different totals. You can place the same field in different subsummary parts that are based on different sort fields. As you've discovered they summarize the records in the current found set. These issues can be either a "feature" or a "problem" depending on your interface and data designs. You can usually handle these issues by designing an interface that always presents the right records sorted in the correct order whenever the user selects to view/print the summary report. You are correct that this is done via a script, but is pretty easy to set up with buttons, script triggers and/or custom menus to make sure the report reflects correct totals.

                   

                  Another option is to use an aggregate function [sum(), Count()] calculation and a relationship that is used by the function to determine which records should be summarized. That's what I described in my last post. This approach avoids found set and sort order factors but also may require designing additional relationships and tables in order to create the desired report.

                  • 6. Re: SUM of fields in related records
                    qwedley
                      

                    I have reemerged again for a day or two. Thank you for your generous assistance.

                     

                    I am continuing to work on ways of generating the reports I need. Use of aggregate function looks to be the way to go for me, at least for some of my reports.

                     

                    I am trying to implement the SummaryByYear solution you proposed and have encountered a problem. I don't have a YearPurchased field on my PO Lines table. Instead I pick up the related field from the POs table. (PO Lines are created through a portal in the POs layout.) It doesn't appear that I can create an AND relationship using two different tables. Or can I? Here's what I'd like to do:

                     

                    PO Lines::Material ID = SummaryByYear::gMaterialID AND

                    POs::Year Purchased = SummaryByYear::YearPurchased

                     

                    I assume this is impossible. So I created a YearPurchased field on PO Lines as a lookup of the YearPurchased field on the POs table. All well and good, but for some reason, FM won't allow a Relookup Field Contents. Is there another way to get the correct year in a field on the PO Lines table that matches the year on the POs table? You mentioned use of a calculation.

                     

                    Thank you! 

                    • 7. Re: SUM of fields in related records
                      philmodjunk
                        

                      Does the lookup work for new PO Lines records?

                      The relookup should work. If you are doing this manually to update existing records, you can simply place the cursor in your "invoice id field" (use your name for this field) for PO Lines and choose relookup from the records menu.

                      • 8. Re: SUM of fields in related records
                        qwedley
                          

                        I should have mentioned that the lookup DOES work for new PO Lines records. I'm sure I'm not doing something right. In the meantime, I got what I wanted by using Replace Field Contents. I did a search of each year in the PO Lines layout for the PurchaseYear (the related field from POs) and replaced the field contents in the lookup field on the PO Lines table, one year at a time. Seems to have done the trick.

                         

                        I now have to run off to a trade show. I will be back next week. My goal is to get my reports more or less where I want them to be by month's end. OK if we keep this thread open for three or four weeks longer?

                         

                        Thank you again for your help. 

                        • 9. Re: SUM of fields in related records
                          philmodjunk
                            

                          Since this was a task to update past records, you shouldn't have to revisit this part of the task but...

                           

                          If you can look up the values for a new record, the relookup option will work if you specify the details correctly. The catch here is that you have to specify the relookup in terms of the key field that defines the relationship between your tables rather than the data field where you want to see the looked up value and this can confuse new users sometime.

                           

                          Replace Field contents is certainly an effective way to do this. Using the calculated value option, you could even have done the update for all your records in one replace operation.

                          • 10. Re: SUM of fields in related records
                            qwedley
                              

                            I have resurfaced again.

                             

                            Thank you for your help regarding the Relookup function. I am aware of the pitfall you describe--i.e., putting the cursor in the field where you want to see the looked up value instead of the key field that defines the relationship. I had the cursor in the key field. Still couldn't get relookup to work.

                             

                            Thank you also for the tip re using the calculated value option. If I can figure it out, I will employ it next time around...

                             

                            Here now another report presentation question. With your help, I have been able to produce a report that looks like this:

                             

                            YEAR            Ordered        Received

                             

                            Category 1      XXXX            XXXX

                            Category 2      XXXX            XXXX

                            Category 3      XXXX            XXXX

                             

                            Totals             XXXX            XXXX

                             

                            I can also produce such tables in stacks, by which I mean that I can request summaries for the years 2007 through 2009 and receive a report that presents three tables one after another for each year. All well and good.

                             

                            But what if I want to produce an order summary by category with year to year comparisons, as follows:

                             

                                                2007      2008      2009

                             

                            Category 1     XXXX     XXXX     XXXX

                            Category 2     XXXX     XXXX     XXXX

                            Category 3     XXXX     XXXX     XXXX

                             

                            Totals            XXXX    XXXX     XXXX

                             

                            Is there a way to do that? 

                            • 11. Re: SUM of fields in related records
                              philmodjunk
                                

                              That last example looks very similar to the report we have here. We do a 5 year summary with each year in a different column.

                               

                              I have a global field, gYear5 and 4 calculation fields: gYear5 - 1, gYear5 -2, etc. for fields cYear4, cYear3, etc. These then become part of separate relationships and calculation fields (or portals) for each column:

                               

                              PO Lines::Material ID = SummaryByYear 5::gMaterialID AND

                              POs::Year Purchased = SummaryByYear 5::gYear5

                               

                              PO Lines::Material ID = SummaryByYear 4::gMaterialID AND

                              POs::Year Purchased = SummaryByYear 4::cYear4

                               

                              PO Lines::Material ID = SummaryByYear 3::gMaterialID AND

                              POs::Year Purchased = SummaryByYear 3::cYear3

                               

                              Not that these are separate relationships linking PO Lines to separate table occurrences that all point to the same data source table.

                               

                              The calculation, in SummaryByYear, Sum(PO Lines::Qty), evaluated from the context of SummaryByYear 5 will give you a total based on gYear5. Make a copy of this field and change its context to SummaryByYear 4, and you get the total based on cYear4 and so forth for each column.

                               

                               

                              • 12. Re: SUM of fields in related records
                                qwedley
                                  

                                Thank you. I think I understand what you are recommending but am having trouble with implementation and can't figure out why.

                                 

                                Here's where I am. Per your suggestion of December 29, I created a new table called SummaryByYear, with the fields you suggested. Rather than relate this table to PO Lines, I related it to a table occurrence called SummaryByYear_PO Lines, which did the trick. So I can now pull up the total quantity ordered of a given item in a given year, which is great.

                                 

                                To implement your latest suggestion, I created a table occurrence of SummaryByYear (named simply SummaryByYear2 for the time being) and set up a double relationship as you suggested to SummaryByYear_PO Lines. And then I hit a wall. It seems as though the earlier relationship takes precedence somehow over the new double one.

                                 

                                Can you see where I've gone wrong? 

                                • 13. Re: SUM of fields in related records
                                  philmodjunk
                                    

                                  It sounds like the correct first steps. What do you try to do next?

                                   

                                  What I percieve that you've done:

                                   

                                  SummaryByYear_PO Lines::Category = SummaryByYear::Category AND

                                  SummaryByYear_PO Lines::YearPurchased = SummaryByYear::YearPurchased

                                   

                                  Your new relationship looks like this?

                                  SummaryByYear_PO Lines::Category = SummaryByYear 2::Category AND

                                  SummaryByYear_PO Lines::YearPurchased = SummaryByYear 2::gYear

                                   

                                  I'm also assuming that SummaryByYear_PO Lines and PO_Lines have the same data source table.

                                   

                                  The next step is to define a calculation field in SummaryByYear that uses the Sum function, but in the calculation dialog, change the Table Occurrence name in the "reference" drop down at the top from SummaryByYear to SummaryByYear 2.

                                  • 14. Re: SUM of fields in related records
                                    qwedley
                                      

                                    Phil (if I may),

                                     

                                    Still struggling. I'm not sure I've given you enough information to make a diagnosis. Let me try to tell you exactly what I've done. I will use your example field names where appropriate to keep things in line with our earlier discussion.

                                     

                                    At your suggestion, I created a new table called SummaryByYear. It contains the following fields: gYearPurchased, gMaterialID, TotalPOs. I then created a table occurrence of PO Lines called SummaryByYear_PO Lines. The relationship between the new table and the table occurrence are as follows:

                                     

                                    SummaryByYear::gYearPurchased = SummaryByYear_PO Lines::YearPurchased AND

                                    SummaryByYear::gMaterialID = SummaryByYear_PO Lines::MaterialID

                                     

                                    SummaryByYear::TotalPOs is a calculation field: Sum (SummaryByYear_PO Lines::Quantity).

                                     

                                    I also created a table occurrence of my MaterialInfo table and created a relationship (SummaryByYear::MaterialID = MaterialInfo::MaterialID) that allows me to pull up the name of the Material. 

                                     

                                    Next I made a layout based on the SummaryByYear table that consists of the three fields in the table plus MaterialName. There is only one record. When I enter a year and a MaterialID, up pops the name of the item and the total purchased in that year. Works great!

                                     

                                    Now the trouble starts. In an effort to implement the purchase summary by year table I described above, I made a table occurrence of SummaryByYear called (for now at least) SummaryByYear2. I also added a calculation field called cYear2 that consists of: gYearPurchased-1, and a second calculation field called TotalPOs2 that goes like this: Sum (SummaryByYear2::TotalPOs).

                                     

                                    Next I created the following relationship:

                                     

                                    SummaryByYear_PO Lines::MaterialID = SummaryByYear2::gMaterialID AND

                                    SummaryByYear_PO Lines::YearPurchased = SummaryByYear2::cYear2

                                     

                                    I then added the new fields to the layout based on SummaryByYear. I get "<Index Missing>" in the new fields. So I made a new layout based on SummaryByYear2. cYear2 field shows correct year but TotalPOs2 is blank.

                                     

                                    I've obviously run off the road somewhere. Help! 

                                    1 2 Previous Next