10 Replies Latest reply on Apr 7, 2010 9:26 AM by Wimmachine

    Too many calculation fields?

    Wimmachine

      Title

      Too many calculation fields?

      Post

      Hello. I am new to the forum but have been reading it for some time now. I have not used FileMaker in almost a decade, but I just inherited a database that needs 'improvement' so I am re-learning a lot of things while I brush away all the cobwebs... I have a pretty extensive Access background however. I am using FileMaker Pro 9 on XP. Anyway on to my question:

       

      I have an Attendance table which stores attendance infraction records. The notable fields in this table (among others) are date and type. A scoring system has been implemented based off of the type.  type values are text and the scoring is numeric. I am creating Calculation and Summary fields for display on a list-view layout, which is sorted by employee. The system I have set up to get the score is a Calculation field (Case function) to translate the type into its associated score, and then a Summary field to Total the Calculation field. The Summary field is displayed on the layout. This works fine to get the score for each employee across all dates.

       

      My question is, I need to calculate the score for various date "blocks." e.g. block1 is the last 35 days, block2 is the 35 days prior to block1, block3 is the 35 days prior to block2, etc. I need to do this for about 14 blocks, which means 28 fields added to the table. Am I going about this wrong? With my Access background I am used to being able to create "unbound" calculations on layouts in order to maintain a clean tables. I know FileMaker doesn't work this way, and have created Calculation fields in this and other tables to suit my needs just fine -- so I am over my "clean table" mentality. I am just hesitant to create 14 calculation fields and 14 summary fields to accomplish essentially one thing. I have already created the fields in a copy of the db and I get the results I want, but it seems "messy"... Am I approaching this wrong?

       

      Thank you for your input.

        • 1. Re: Too many calculation fields?
          philmodjunk

          Do you need to see all 14 results at once or just one at a time?

           

          If one at  a time, you should be able to modify your calculation to include a global field that let's you specify which "date block" you want. THen you can get any 35 day block you need just by entering different values in this one global field.

          • 2. Re: Too many calculation fields?
            Wimmachine

            Yes I need to see the score for all 14 blocks at once on the layout.  It is the type of layout I call a "summary report" (of which I am designing many for various tables).

            • 3. Re: Too many calculation fields?
              philmodjunk

              This tutorial may help: Creating Filemaker Pro summary reports--Tutorial

               

              It demonstrates several key techniques worth knowing how to use when setting up this kind of report.

               

              Are the 35 day periods a rigid preset distinction or are they subject to modification?

               

              If you can devise a field that identifies which records fall in each 35 day period, you should be able to use a single set of calculation and/or summary fields which you can place in a sub-summary part after you've grouped your records by using that field in a sort.

              • 4. Re: Too many calculation fields?
                Wimmachine

                Thanks for the link. I have read that thread many times :)  I have a SubSummary report-generating mechanism on this layout already for reporting on this information based on a specific date range entered into a global field.

                 

                This layout must be viewed in browse mode because it includes navigation elements:  clicking on employee name in body opens employee details layout, navigational buttons in header, etc.

                 

                The 35 day periods are dynamic:

                block1:  infraction_date ≥ Get(CurrentDate) - 35

                block2:  infraction_date ≥ Get(CurrentDate) - 70 and infraction_date < Get(CurrentDate) - 35

                block3:  infraction_date ≥ Get(CurrentDate) - 105 and infraction_date < Get(CurrentDate) - 70

                and on and on

                • 5. Re: Too many calculation fields?
                  philmodjunk

                  What version of FIlemaker are you using?

                  With FMP 10 and later, you can view summary reports in browse mode. Hence you can add buttons to any of the layout parts as needed.

                   

                  If you need to get this working in Browse mode for FMP 9 or older, you might try creating a special table solely for the purpose of grouping your records.

                  The table would have two date fields that would correspond to the first and last months of your  35 day period. You'd use a script to set up one such record for each period. Since this table does not store any actual data, you can create and destroy records in this table as needed to produce the desired reports. You would link this table to your original report with a relationship that uses inequality records:

                   

                  DatePeriods:: Date1 < Attendance:: Date AND

                  DatePeriods:: Date2 > Attendance:: Date

                   

                  Now you can set up a summary report in your attendance table and use DatePeriods:: Date1 as a break field for a sub-summary part or you can define sum, count or other aggregate functions in calculation fields in DatePeriods for a Browse Mode report based on the DatePeriods table.

                  • 6. Re: Too many calculation fields?
                    Wimmachine

                    I am using FMP 9.  I find sub-summary reports almost entirely useless due the fact that they only 'work' when viewed in preview mode, which as far as I can tell only exists as a means to an end for printing, which does not occur in my environment.  It is interesting to hear that these reports work in browse mode in FMP 10.  I'd be interested in reading a feature comparison on FMP 9 and 10, although that is getting a bit off topic.

                     

                    As I said my current layout is a list view displaying a single text field (employee) and about 20 summary fields. These are all "Total" summary fields, that total up values for whatever calculation they are tied to, for each employee. The layout is sorted by employee so you could consider that the break field (although there are no subsummary parts in this layout). Most of the calculation fields have the defined date range in them, as well as the function to translate the infraction type into a numeric value. Essentially if the infraction record is not within the defined date range, the value is 0.  

                     

                    How would moving the defined date periods to another table reduce the number of calculation fields in the attendance table?

                    • 7. Re: Too many calculation fields?
                      philmodjunk

                      "How would moving the defined date periods to another table reduce the number of calculation fields in the attendance table?"

                      Instead of 14 sets of calculation fields for 14 35 day periods, you'd have 14 records with the same set of calculation fields referencing a different 35 day period in the original table.

                       

                      Needing this data for each employee complicates the technique but doesn't make it impossible. You could place an employee's ID number in a global field in this table and include it in the relationship.

                       

                      DatePeriods:: Date1 < Attendance:: Date AND

                      DatePeriods:: Date2 > Attendance:: Date AND

                      DatePeriods::gEmployeeID = Attendance::EmployeeID

                       

                      Using this approach, I'd put calculation fields that use either sum or count in this table that compute totals or counts of matching records in the original table.

                      Sum(attendance::NumberField) would then return the total of NumberField for all records falling in the specified date range for the specified Employee.

                       

                      Essentially, this table is simply a table of pointers that link to different groups of records in your main table. You can reference different groups of records in your original table either by modifying the data in these records, finding a different group of records or by creating a new set of such records.

                      • 8. Re: Too many calculation fields?
                        Wimmachine

                        Thanks for your help. I'm not sure if this is going to work... I might end up going with the 28 fields.  I also might not have explained this clearly enough.

                         

                        The table contains every infraction record for all employees.  I will use myself in the following basic example, and we will say the scoring schema is: absent=2 and tardy=1.

                        - I am absent 2 days and tardy 1 day.

                        - This is 3 infraction records, obviously.

                        - The values "absent" and "tardy" are what are entered into the type field, these are not numbers and must be converted into numeric values via calculation fields for scoring.   

                        - Both of my absences occur in block1 and my tardy occurs in block 2.

                        - My totalscore is "5", block1score is "4", and block2score is "1".

                         

                        The layout I have set up is in List view. The body is like .2 inches.. so it looks like an excel spreadsheet displaying all employees (scrollable) and tons of columns:

                         

                        Wimmachine           5                               4                                  1                                    0                                   0

                        employee2               totalscore               block1score               block2score               block3score               block4score

                        employee3               totalscore               block1score               block2score               block3score               block4score

                        etc

                         

                        - totalscore in the above layout example is a summary field: Total of points

                        - points is a calculation field (line breaks for clarity):

                        Case (

                        type = "absent"; 2;

                        type = "tardy"; 1; 

                        0)

                         

                        - block1score in the above layout example is a summary field: Total of block1points

                        - block1points is a calculation field:  

                        Case (

                        date ≥ Get (CurrentDate) - 35 and type = "absent"; 2;

                        date ≥ Get (CurrentDate) - 35 and type = "tardy"; 1;

                        0)

                         

                        - block2score is a summary field: Total of block2points

                        - block2points is a calculation field:

                        Case (

                        date ≥  Get (CurrentDate ) - 70 and date <  Get (CurrentDate) - 35 and type = "absent"; 2;

                        date ≥  Get (CurrentDate ) - 70 and date <  Get (CurrentDate) - 35 and type = "tardy"; 1;

                        0)

                         

                        The problem is that the date ranges are dynamic, not static.  If I were to create Date1 and Date2 fields in a separate table they would not contain values, they would be calculations.

                        • 9. Re: Too many calculation fields?
                          philmodjunk

                          "The problem is that the date ranges are dynamic, not static.  If I were to create Date1 and Date2 fields in a separate table they would not contain values, they would be calculations."

                          Not really. Since you can script the creation and or update of these records the dates in these can be fully dynamic. They can even be calculation fields that use the current date and a serial number field to compute different 35 day intervals.

                           

                          However,

                           

                          Given the multiple column format you want and a layout that lists all employees, I'm not sure that the extra file as I've described will really be any simpler.

                           

                          • 10. Re: Too many calculation fields?
                            Wimmachine

                            It is still early in the day, but I may have found a solution that will cut the number of fields in half by way of a self-join. I am still experimenting but it may be the way.