7 Replies Latest reply on Oct 8, 2012 4:37 PM by mel_shamamyan@mcmintegrated.co

    Running total IF

    mel_shamamyan@mcmintegrated.co

      Title

      Running total IF

      Post


           Hey Guys,

           I was trying to make a running total of a 5000 record database. I want the running total to be based on the name of the vendor.

            

           So lets say for example the vendor name is Microsoft, for all records that have Microsoft I want a running total, so I can create a report with the total we have spent on that company.

            

           HEre is what I was trying to do.. GetSummary ( Vendor ACS ;if(Vendor="Microsoft" ))

            

           Where am I going wrong?

            

           Thans,

            

           Mel

        • 1. Re: Running total IF
          MarcMcCall

               Then your calculation field  VendorFlag =  If (Vendor="Microsoft";1)

               Summary Field  Count (VendorFlag)

          • 2. Re: Running total IF
            philmodjunk

                 You should describe the context behind what you are trying to do.

                 Simply sorting your records by vendor and using the "restart totals when grouped by vendor" would produce the running totals, but I suspect that there are some additional details that will keep that very simple approach from working for you. I suggest describing the layout design where you want to see this running total.

            • 3. Re: Running total IF
              mel_shamamyan@mcmintegrated.co

                   Let me give you guys a better picture behind what I am trying to do in order to get my running totals correct. Here is the screen shot of how my layout looks currently. I have circled where the name of the vendor is, so I want different counters depending on the vendor. So Again lets say in this example its Zebra Technologies, everytime there is a record present in this database with that name I want it to add the total and give me the final dollar amount I have spent on that company.

                   Hope this clears it up a little please let me know if you have any further information.

                    

                   Thanks,

                   Mel

              • 4. Re: Running total IF
                philmodjunk

                     Technically that's not a running total, but it is one you can easily get with a self join--though I suggest something more rigorous that matching a name field to do it.

                     Let's assume that you have these two tables and this relationship involved:

                     PurchaseOrders-----<PurchaseOrderLineItems

                     PurchaseOrders::__pkPOID = PurchaseOrderLineItems::_fkPOID

                     and Total Cost is defined as: Sum ( PurchaseOrderLineItems::ItemCost )

                     Add a new table occurrence of PurchaseOrders, name it PurchaseOrdersSameVendor and then you can link PurchaseOrders to purchase orders by vendor:

                     PurchaseOrders----<PurchaseOrdersSameVendor

                     PurchaseOrderes::_fkVendorID = PurchaseOrdersSameVendor::_fkVendorID

                     Then Sum ( PurchaseOrdersSameVendor::TotalCost )

                     will produce the total spending for the current vendor.

                     Note:

                     I'd add a table for vendors with a relationship like this:

                     Vendors-----<PurchaseOrders

                     Vendors::__pkVendorID = PurchaseOrders::_fkVendorID

                     If the notation I am using is not familiar to you, see this thread: Common Forum Relationship and Field Notations Explained

                • 5. Re: Running total IF
                  mel_shamamyan@mcmintegrated.co

                       Hey Phil,

                        

                       Thanks for the quick response, I tried the what Marc had posted "VendorFlag =  If (Vendor="Microsoft";1)

                       Summary Field  Count (VendorFlag)" and it worked, except for I changed the VendorFlag=If(Vendor="Microsoft";Total Cost). I looked at what you had responded to with the above post but it looks a little too complex for me for now.

                       With the current way I did it, can I also narrow down which dates it takes the total cost from. Lets say I want to take "Microsoft" and only show the total cost from 01/01/12 to 01/06/12? Or how can a user input the dates as a field and have the calculation done automatically?

                        

                       Thanks for your time!

                  • 6. Re: Running total IF
                    philmodjunk

                         If I understand what you have there, won't you need a separate calculation field for every vendor? And how does the "count" return a total?

                         There are several ways to use the self join relationship to limit the totals by date or a range of dates. Are you asking for help with the calculation field march suggested or the relationship based method I am suggesting?

                         Using variations of my method, you can include a pair of date fields in the relationship, or you can use a filtered portal that filters by date range with a summary field inside the portal.

                         And if you have FileMaker 12, there are ways to use ExecuteSQL to produce the desired total--without adding any additional occurrence boxes to your relationship graph.

                    • 7. Re: Running total IF
                      mel_shamamyan@mcmintegrated.co

                           Hey Phil,

                            

                           I ended up creating a new layout called running total and I used the formula I posted above for a running total. I changed the If statement to If(Vendor!=1;Total Cost). So ofcourse vender will never equal 1 and therefore it will always compute this formula.

                            

                           Now as far as the sorting goes, I used the built in sort feature that lets you search by vendor and specify dates. So if I wanted to show Microsoft from 01/01/2012 to(...) 06/01/2012 I just type that in with the ... and it works.

                            

                           Once again thanks for the help!