1 2 Previous Next 23 Replies Latest reply on Aug 10, 2011 10:25 AM by philmodjunk

    Sum of unstored field for multiple related records

    c.w.

      Title

      Sum of unstored field for multiple related records

      Post

      How do I sum an unstored field for multiple related records?

      I have a database for all of my work's projects (each is one record), and a portal in each project that allows us to record the date, activity and total time for every activity for the project. For each of these projects, I have a field with the sum of both the total time into the project, and the amount of time into the project for the current month. For one client (with whom we have muiltiple projects every month), I would like to keep a running total of the time put into all of their projects each month. This would be, essentially, finding all of the projects for this client, then adding together the numbers in unstored field that gives the current total time for the month. (This number would change as more time is put into the various project for this client, it would be an unstored total visible on every project/record.)

      For the love of dog, how do I do this?

        • 1. Re: Sum of unstored field for multiple related records
          philmodjunk

          I take it that you have a relationship similar to this:

          Project::ProjectID = Activity::ProjectID

          What method do you use to compute the total time for a given project for a given month?

          If you use a calculation field defined in project for this, a summary field that computes the total of it will report the total for all projects. If you perform a find to find just projects for a specific client, the total will be for that client. If you sort your project records by client, then put the summary field in a sub summary layout part "when sorted by" your client field, you can put the summary field in this sub summary part to display the sub total for each client.

          But keep in mind that I'm assuming a lot of details about yoru database set up here.

          Do you have a clients table? That can also be used to compute a total for each client.

          • 2. Re: Sum of unstored field for multiple related records
            c.w.

            What method do you use to compute the total time for a given project for a given month?

            I created a field for the viewer to select the month for which they want to know the total time for that project.

            I then created another field (unstored) with a calculation to total the line items in the portal for that month (Sum ( LineItemsByMonth::House Billed )

            All projects have a company name attached to them, and I'd like to create another unstored field for just one particular company that would total ALL of the projects' "time for the month" for this company in a single field visible on all records. This is so, no matter what project we are working on, I can see where we are for hours for the month for this particular company.

            I do not have a clients table at this time, but I would like to incorporate one in the future to streamline the project-entering process.

            • 3. Re: Sum of unstored field for multiple related records
              philmodjunk

              That suggests you have this relationship:

              Projects::Month = LineItemsByMonth::Month

              (And there are a number of ways to set up the values in "month" on both sides to include month and year, but the basic relationship structure will be the same.)

              So if you set up this relationship:

              Projects::Month = CustLineItemsByMonth::Month AND
              Projects::ClientID = CustLineItemsByMonth::ClientID

              The same type of sum function can compute the monthly total for a specified client. Projects::ClientID can be a field that identifies the current client for the current project record or you can define a global field with a value list for selecting different client's.

              If you Have Filemaker 11, you can use single row filtered portals with a summary field defined in LineItems to display the two monthly totals without needing to define the extra table occurrences.

              • 4. Re: Sum of unstored field for multiple related records
                c.w.

                Thank you. I'm pretty much a notice when is comes to Filemaker, just learning enough to get our different databases to work. So I'm not sure I understand how to use this to ge it to show the current time for the month for this client.

                Would there be an easier way to run a report with a script for all the current company projects? This report shows a list of all ofhte projects, and one of the fields shows the total time for the month for each project. Is there a way to just add the totals of this field for each of found projects into one total?

                • 5. Re: Sum of unstored field for multiple related records
                  philmodjunk

                  You were able to set this up to get a monthly total. This method is just an extension of the same approach.

                  Can you produce the relationship I suggested? (I'm looking for a starting point from which to walk you through the steps to do this.)

                  What version of FileMaker do you have?

                  It's also possible to set up a summary report in LineItems with summary fields and a sub summary part so that you get a list of all subtotals broken down by Client and Month. I can't say that this is any easier and you can use both methods as each can be useful in different circumstances. Have you ever created such a summary report?

                  If not, you may want to play around with this tutorial first: Creating Filemaker Pro summary reports--Tutorial

                  This is an old thread and posts to it no longer put it back in Recent Items, so feel free to post any questions about it here in this thread where it will be much easier for me to find and to respond to.

                  • 6. Re: Sum of unstored field for multiple related records
                    c.w.

                    Filemaker 9 (I also have 10, but no one else in the office does so I use 9)

                    I'm not sure if my relationships are set up to allow for me to do this. Here is a screen capture of our projects database where I am trying to create the relationship you mentioned, however Company is only included in the main project table (Projects Database), which links to the Time Billing, which links to the Line Items. How would I create the relationship between the Company and the Line Items?

                    ProjectList_FINAL_relationships2011-08-09

                    • 7. Re: Sum of unstored field for multiple related records
                      philmodjunk

                      I see that I've assumed something silly in my prior post. Sorry about that.

                      A client table would make this much easier as this would give you a single record for a given client as the starting point. Then we could select the month and see the monthly total for that client record. It looks like you already have that table. At least Company is being used as a foreign key for some table not included in your screen shot here.

                      Companies::CompanyID = Projects Database::CompanyID

                      Then, the sum calculation for a monthly total is in a field named Total Hours Billed For Month, you can set up this calculation in Companies:

                      Sum ( Time Billing::Total Hours Billed For Month )

                      And you can put the global gSelectedMonth field on your Companies layout so that you can specify the month on a Companies record. (You may need to script a Refresh window or Commit record step to get this field to update each time you select/enter a different value in gMonth.)

                      • 8. Re: Sum of unstored field for multiple related records
                        c.w.

                        Ok, I created a Customers table with Company ID and Company Name, and added in the sum calculation (see relationships in image attached). I entered a few of our customers into the Customers, and created a value list from the Customers field (so when more customers are entered, they show up as options). I then went into a few active projects and chose the same customer's company from the drop down list to test if it shows the sum of the current time for the month. And it didn't work.

                        What am I doind wrong? (if you need more info, please let me know, thank you!)

                         

                        relationships for project list

                        • 9. Re: Sum of unstored field for multiple related records
                          philmodjunk

                          What value was entered in gSelectedMonth?

                          • 10. Re: Sum of unstored field for multiple related records
                            c.w.

                            What value was entered in gSelectedMonth?

                            Dropdown calendar of 8/1/2011

                            • 11. Re: Sum of unstored field for multiple related records
                              philmodjunk

                              How exactly did you do this? "I then went into a few active projects and chose the same customer's company from the drop down list..."

                              From the client's layout, how do you "go into a few active projects"? What layout were you on?

                              Just trying to get the full picture here. As I mentioned earlier, we may have an issue where a triggered script is needed to force a window update after you select a different month or different client.

                              • 12. Re: Sum of unstored field for multiple related records
                                c.w.

                                The active projects are in the Project Database, each under it's own Project ID. Each of the projects already have a Company Name entered under the Project Database, but there was no corilation between the Project Database Company Name and the Customers Company Name (when I tried to link these, it ended up doing strang things like deleting companies names). So, to create the link between the Project and the Company, I added a dropdown field in the project layout, allowing me to choose a Company Name based on a few companies I entered to test this. I then selected the corresponding Company Name from the dropdown list for a few of the projects so the tables had the common link. I then went back to the Customers layout to select the gSelectedMonth, but it didn't show anything.

                                I'm sure I'm missing something, I'm just not sure what.

                                • 13. Re: Sum of unstored field for multiple related records
                                  philmodjunk

                                  Is your drop down of company names located on a Customers layout. Is it the Customers::Company ID field or the Customers::company name field?

                                  Since the relationship is based on Company ID, selecting a value for Customers::Company Name will have no effect on the results returned here. So I need to rule that issue out here.

                                  You can place a copy of the global month field on the Customers layout so that you can specify a company and a month on the same layout. (Global fields can be placed on any layout in your database whether or not the layout's table occurrence is related to the table where the global field is defined.)

                                  • 14. Re: Sum of unstored field for multiple related records
                                    c.w.

                                    The values in the drop down menu included the Customers::CompanyID as the main value, but it also displayed the value for the Customers::Company Name. But I just noticed that for those three projects I was testing to apply the Customers::CompanyID to actual projects, it was also creating new records int he Customers table. (I noticed in the relationship between Customers and Projects Database, the "allow creation of records..." was checked, so I unchecked it and now nothing shows up in the dropdown menu to choose a Customers::CompanyID to apply to the projects.)

                                    How do I get the Customers::CompanyID to attach to the individual projects, so the time will be counted under that company?

                                    1 2 Previous Next