11 Replies Latest reply on Dec 3, 2013 11:51 PM by moj8588

    Sum of portal field within a specified month/year

    c.w.

      Title

      Sum of portal field within a specified month/year

      Post

      Hi,

      I have a database that lists all of our projects (each as a different record). I created a portal within each record to keep track of all individual activities (design, programming, etc) and the amount of time spent on each activity each day by date. I also created a field in the record to keep track of the total amount of time "to date", using a Calculation in the field:

      Unstored, = Sum(Line Items::Hours Billed)

      Now I want to create a similar field to show the total amount of time for the project/record for a specified month. I created two globals, one for "Month Choice" and "Year Choice", that I can change to specify which month and year I want to show. I want to set parameters for the Sum() of the field to only add together the numbers of the line item within the portal that match the month and year.

      Something like this:
      Unstored, = Sum(Line Items::Hours Billed if "Date = Month Choice" and "Date = Year Choice)

      How do I do this (I know the syntax of this is wrong, but this is what I want it to do)? If you need more info, please let me know. Thank you!

        • 1. Re: Sum of portal field within a specified month/year
          philmodjunk

          Sum used in this fashion is controlled by the relationship involved. Your portal's relationship matches a given project to all the related Line Items records. Thus, Sum ( Line Items::Hours Billed ) gives you the total of all hours billed for the current project record like you wanted. To get a total for the current project, but just for a selected month, you'll need an additional relationship that matches only to those records. You can use separate fields for month and year, but you don't really need to.

          Define a calculation field, cMonth in Line Items as: DateField - day ( DateField ) + 1 and select "Date" as the calculation's return type. This calculation returns the date for the first day of the month for all dates from that month.

          Define a date field, SelectedMonth, in Projects and give it the same expression for as an auto-entered calculation and clear the "do not replace existing values..." option.

          Now add a new relationship:

          Projects::ProjectID = LineItemsByMonth::ProjectID AND
          Projects::SelectedMonth = LineItemsByMonth::cMonth

          LinItemsByMonth is a new table occurrence of Line Items, created by clicking the box in Manage | Database | Relationships and then clicking the button with two green plus signs.

          Sum ( LineItemsByMonth::Hours Billed )

          Will now compute the desired monthly amount and you can format SelectedMonth with a pop up calendar if you wish. Any date you select  or enter will automatically update to the first of the month when you exit the field.

          Note: you can use the same method as above for separate fields for month and year if you want, you just need two calculation fields to match in Line Items and both pairs of fields will be included in your relationship.

          Note, you can also use a layout based on Line Items with summary fields and sub summary parts to give you a report of selected line items, grouped by month with both sub totals for each month and also a grand total. This method does not require any additional relationships and table occurrences.

          • 2. Re: Sum of portal field within a specified month/year
            c.w.

            My database is set up as "Project" linked to the "Time Billing" linked  to the "Line Items". "Project" and "Time Billed" are linked by the  "Project ID", and "Time Billing" and "Line Items" are linked by the  "Billing ID". How would I adjust this to make the relationships work?

            • 3. Re: Sum of portal field within a specified month/year
              c.w.

              Everything appears to work, but when I put in a date to show the time worked for that month, nothing appears in the field for the Sum.

              • 4. Re: Sum of portal field within a specified month/year
                philmodjunk

                I take it you have this:

                Projects----<TimeBilling----<LineItems

                Projects::ProjectID = TimeBilling::ProjectID
                TimeBilling::BillingID = LineItems::BillingID

                It depends on where the date is that you want to filter on. If it's a date in TimeBilling (the date of the bill), you can use:

                Projects::ProjectID = TimeBillingByMonth::ProjectID AND
                Projects::SelectedMonth = TimeBillingByMonth::cMonth

                TimeBillingByMonth::BillingID = LineItemsByMonth::BillingID

                And Sum is still defined in projects.

                If the date is a date in LineItems (date work is logged), then you'd need:

                TimeBilling::BillingID = LineItemsByMonth::BillingID AND
                TimeBilling::gSelectedMonth = LineItemsByMonth::cMonth

                gSelectedMonth is a field with global storage specified and is defined in the TimeBilling table, but you can place it on your projects layout.

                All of this added complexity argues for the other option: A summary report based on LineItems.

                • 5. Re: Sum of portal field within a specified month/year
                  c.w.

                  I think I almost have it working (with your amazing help, of course!)!

                  The gSelectMonth doesn't automatically change to the first day of the month, so the final Sum only shows up if I manually select the first day of the month. It works for the calculations in cMonth, but not the date for gSelectMonth.  Is there a way to get it to automatically change to the first day of the month, no matter what day in the month is chosen?

                  • 6. Re: Sum of portal field within a specified month/year
                    c.w.

                    Nevermind, I figured it out (just had the wrong field specified).

                    Thank you so much!!!!!!!

                    • 7. Re: Sum of portal field within a specified month/year
                      philmodjunk

                      When defining auto enter expressions, it's often useful to use "self" in place of the field's actual name Wink.

                      • 8. Re: Sum of portal field within a specified month/year
                        c.w.

                        You are awesome, thank you so much!!!!!!

                        • 9. Re: Sum of portal field within a specified month/year
                          c.w.

                          Ok, now is there a way to "sum" all of the billing time for this found set? I have the total amount of hours that will be billed for the selected month for each project for this client, but now I want to add the totals of this set together for a grand total of hours for this client's projects for the month. How do I "sum" the field of a found set?

                          • 10. Re: Sum of portal field within a specified month/year
                            philmodjunk

                            To sum the field for all records in your found set use a summary field defined to compute the "total of" the field you want to sum.

                            You may want to investigate creating a summary report with summary fields and sub summary parts created on a layout based on LineItems. You can add fields from Time Billing, Projects and a related Clients table as needed to provide all the info needed for the report, but you can use summary fields and sub summary parts with sorting to get totals and sub totals broken down in quite a number of different ways.

                            Creating Filemaker Pro summary reports--Tutorial

                            • 11. Re: Sum of portal field within a specified month/year
                              moj8588

                                   Hi, Can u send a small sample for this problem? thank you.