6 Replies Latest reply on Mar 16, 2010 1:58 PM by Melinda

    Secondary Global Fields?

    Melinda

      Title

      Secondary Global Fields?

      Post

      Beginner

      Mac OS X

      FM Pro 9 on a shared server

       

      I have global date fields in a layout that I use to collect data from any specific time period -- works beautifully.  I need to be able to collect data, however, from more than one time period in one layout/report and I am having trouble with this...  I need to collect data totals for nine specific fields, by month as well as in cumulative totals for the year to date.  The dates are imported into a date field as m/d/yyyy.  I've tried a variety of summaries, calculations, etc, but I am missing something...  it doesn't seem like it should be that difficult to grab monthly data and tally cumulative data at the same time...  Help, please?

       

      Thanks,

       

      Melinda

        • 1. Re: Secondary Global Fields?
          philmodjunk

          This sounds like something you should be able to do with a summary report after performing a find to pull up just the records that you want.

           

          This seems to be the key sticking point: "I need to be able to collect data, however, from more than one time period in one layout/report"

           

          The following method should produce what you need unless there are details missing from your post that keep it from working for you:

           

          Enter find mode and specify each range of dates on a separate find request.

           

          Enter find mode and enter a date range such as 4/1/2010...4/5/2010 into your date field.

          Select New Request

          Enter a second such date range in the same field.

          Repeat until you've specified all your time periods.

           

          Perform the find once all criteria has been entered.

           

          The above method can be scripted.

           

          Once you've found and sorted your records, summary fields that may include subsummary layout parts should be able to compute the totals you need.

           

          Here's a link to a simple tutorial on setting up summary reports that you may find useful:

          Creating Filemaker Pro summary reports--Tutorial

           

           

          • 2. Re: Secondary Global Fields?
            Melinda

            I hate to post this because I read what you sent me, and the link, and I'm still having trouble with this...  Our employees turn in a monthly timecard detailing what kind of work they were doing each day (billable, home office, personal, travel, etc.) in 1/2 day increments.  A record is created for each day the employee entered work done on their time card -- the original layout created to collect the timecard data is a list that records the data/count for each employee in the body and generates a cumulative total in the footer...  I determine the find parameters using a global start date field and a global end date field.  I am able to prepare reports showing one or more employees and for one month or multiple months, but since cumulative totals are also affected by the dates entered in the global date fields, it is giving me trouble in the new layout.  I am trying to create a layout for individual employees that will show monthly totals AND the cumulative (year-to-date) totals.  The cumulative date on this layout should always be September 1, 20XX - August 31, 20XX (our fiscal year)...  I have written a script to find the dates within the range 9/1/2009...9/30/2009, for example, and when I click the associated button, it finds the correct data, but it drops the information into the monthly totals AND the cumulative totals...  I tried extending the find to include the fiscal year, but I am surely doing this incorrectly because it drops the fiscal year data into all fields, overriding the initial find request... 

             

            Basically, I want to tell Filemaker to find ALL cumulative data for each employee, and then break that down (sort) by month -- I need to be able to select one month at a time for reports, however,  AND I always need to be able to include the year-to-date totals in the reports...

             

            • 3. Re: Secondary Global Fields?
              philmodjunk

              Since all the records for the year aren't in your found set, summary fields can't correctly compute a yearly total.

               

              Sounds like a combination of a summary field for monthly totals can be combined with a calculation field that uses a relationship and the Sum() function can be used to compute year to date totals.

               

              You first need a key field that correctly identifies a record by the fiscal year:

               

              Just posted this yesterday in another thread (modified for different month here) : Let ( d = YourDateField ; Year ( d ) + Month ( d ) > 8 ) )

               

              This will return the number 2011 for dates from 9/1/2010 through 8/31/2011.

               

              Let's name this field "FiscalYearKey".

               

              You can create a relationship like this:

              TimeCards::FiscalYearKey = TimeCardsSameYear::FiscalYearKey AND

              TimeCards::EmployeeID = TImeCardsSameYear::EmployeeID

              (TimeCardsSameYear is a second table occurence of TimeCards)

               

              Now a sum function like this: Sum (TImeCardsSameYear::Hours)

              will compute the total hours for the same fiscal year for the current employee.

               

              The other thread on this subject: http://forum-en.filemaker.com/t5/Using-FileMaker-Pro/Sum-Field-between-Date-Range-w-o-Sorting/td-p/57676

              • 4. Re: Secondary Global Fields?
                Melinda

                Wow.  I printed that out last night, because it seemed to be what I was trying to do, but I had no confidence in my ability to apply it to my own project!  BUT, after you linked it, I tried it!  I am not sure I fully understand it, but it appears to be working exactly as needed -- THANKS!

                 

                Just a couple of questions:

                 

                The sum and calculations only work in the header or the footer -- which is fine, but I am just wondering why I can't put this in the body?   I think it has something to do with the records from which the data is being collected...  I don't need to see those, so at present I just have everything in the header -- will this become problematic?

                 

                Also, I have scripted a button to find the monthly data for each employee and am wondering if there is a way to ask/prompt the user (me) to enter the employee name and month for which I want to collect the data?  My very basic script right now is:

                 

                Action                            Criteria

                Find Records                 ADMIN DATA:: EmployeeName : [Smith] AND ADMIN DATA:: Date [9/1/2009...9/30/2009]

                 

                This will require a button for each employee for each month -- which is doable since the number of employees is small, but it hardly seems efficient.

                 

                And, finally, I am not sure what the "FiscalYearKey" is doing for me...  I created it, and related it as you suggested...  but I don't see what it does...  I just use the fiscal year as my range of dates for the Year-to Date totals and will need to restart next fiscal year...  Which means additional buttons down the road, right? 

                 

                Thanks again!

                • 5. Re: Secondary Global Fields?
                  philmodjunk

                  Let's start with FiscalYear first. It's used to match to all other records from the same fiscal year. When you start a new fiscal year the record's dates will compute in this field to a new year number and thus will match by the new year. There's no need for extra buttons each year. Including a second pair of fields, EmployeeID, narrows the match down to just the records with the same employee ID number. (If you don't have serial number field to uniquely identify each employee, you need one and not just for this report!) You can also perform searches on this field to find all records for a given fiscal year.

                   

                  If you want a script to use user input to search your records, you can use either a custom dialog with input fields that store the user inputs in global fields or just place the global fields on a layout intended for starting these searches. I preferr the latter as you can then format your fields with value lists and other features to make specifying search criteria easier.

                   

                  Here's one script example using the global fields, gName and gMonth (a date field) to find all records of a given name and date range( all dates in the same month, year as entered in the gMonth date):

                   

                  Enter Find Mode []

                  Set FIeld [YourTable::EmployeeName ; YourTable::gName ]

                  Set Field [YourTable::YourDateField ; let ( [ d =  YourTable::gMonth ; d1 = d - day ( d ) + 1  ; d2 = date ( Month (d) + 1 ; 1 ;Year (d) ) - 1 ] ; d1 & "..." & d2 ) 

                  Set Error Capture [on]

                  Perform Find[]

                  • 6. Re: Secondary Global Fields?
                    Melinda

                    Thanks!