13 Replies Latest reply on Feb 4, 2013 1:27 PM by FileMakerNovice

    Creating a relationship for date range find among multiple tables

    FileMakerNovice

      Title

      Creating a relationship for date range find among multiple tables

      Post

           Hello all,

           I recently posted http://forums.filemaker.com/posts/31d8108476 and got a great answer but need to find a solution that does not use the summary fields.  Working with several thousand records has made this option take too long.  Plus, I need to work with fields from separate tables.  I am going to simplify the database immensely so apologies if I am not clear.

           Let's say I have:
           3 tables located on an external server, located in a FM database.  On the server, there is:
           Employees table with name and employee ID.
           PhoneSales table with a total of phone sales in a day, the date sold and employee ID.
           OnlineSales table with a total online sales in a day, the date sold and employee ID.

           What I want:
           A sum total for a given employee of how many online and phone sales (combined) they made for a given time period.  For instance, if I enter John Doe and 1/1/12...1/1/13, it would give me all his sales for that year.
           I would also want to see the highest saler and lowest, rank all from high to low, etc.

           I believe if I answer these, this will answer my questions on what I need to do with my other formulas/relationships but I can explain more about the database, if needed.

           Thanks for all the help.  Have a good one.

        • 1. Re: Creating a relationship for date range find among multiple tables
          philmodjunk

               I see no reason why PhoneSales and OnLineSales can't be replaced with a single table storing records for both types of sales. A field in that table can be setup with a value list for identifying the type of sale. If there are significant differences in the fields used in each table, you still may want to use a unified sales table with related tables for managing the "sale type" specific differences. Such a unified table of records makes reporting much simpler.

               With large sets of related records, putting a summary table inside a filtered portal will be pretty slow, but it's the portal filtering that produces the major hit. There are other ways, if you use that unified table, where you can use summary fields to get what you want without such long delays. You may also find that eliminating the portal filter and using relationships that produce the same result as the portal filter may update faster as well.

               On the other hand, sometimes the shear mass of data makes computing the needed aggregate values take too long. This will be true for any method you use in FileMaker to compute such totals and sub totals. This sometimes makes settting up a summary table of denormailzed data worthwhile. Such a table is populated via script with subtotals already computed with one record for each sub total. The key requirement is that the data being summarize cannot be data that frequently changes. Your daily sales data would appear to be a good candidate for a summary table based solution.

               Here's an example that I've had in place for many years:

               We purchase/redeem scrap metal/used beverage containers. We average between 500 to 1000 customers a day. We use the typical line items table on an "invoice" (Really it's a purchase order) layout to itemize the items we purchase or redeem. The invoices are "preloaded" with the 4 most commonly needed container types to speed customer service.

               Yet we pull up summary reports that summarize data over periods of time as long as 5 years. This requires summarizing data from literally millions of line items records. We do that by running a script once each night that creates one record in a summary table for each type of material purchased on that day with totals for dollars spent and pounds acquired computed and stored in simple number fields. This condenses the data from up to 4000+ line items records down to about 10 records for that day. We then can produce a naumber of different summary reports from this summarized data and get the results we need in just a few seconds even when we do our 5 year comparison report that computes and compares monthly totals and averages for a given matererial over a 5 year time span.

          • 2. Re: Creating a relationship for date range find among multiple tables
            FileMakerNovice

                 Apologies, but the table information above is a massive over simplification.  I thought it would be better to simplify.  Let me go into better detail.

            CustomerSurveys-
            Contains EmployeeID, Date, TotalSurveyReceived, PositiveSurveys, NegativeSurveys
            Basically, throughout the week, the employee will get surveys.  The total amount that day is "TotalSurveysReceived".  Positive and NegativeSurveys are a count of if the surveys were negatively or positively graded.  IE Employee A - 1/2/2013 - 3 - 2- 1.

            Calls-
            Contains EmployeeID, Date, TotalCalls (taken).
            This provides a total amount of calls taken that day.  IE Employee A - 1/3/2013 - 5

            EmployeeTime-
            Contains EmployeeID, Date, TotalStaffedHours, TimeOnCalls, TimeOnBreak, TimeInMeeting
            This provides a line for each employee per day that they are staffed and how they allocated their time throughout the day.  IE Employee A - 1/3/2013 - 8 - 6.5 - .5 - 1

            CallQuality-
            Contains EmployeeID, Date, CallGrade
            This is a quality control report that provides a 1-10 grade for each call listened to.  There could only be one to two grades per week.  Conversely, they could recieve 2 grades in one day.  The dates for the grade may be on days that the employee was not at work.  IE Employee A - 1/1/2013 - 10

            Employee- 
            Contains EmployeeID, FirstName, LastName, Email, etc.

            What I'm trying to do:
                 I would like to calculate metrics and give a point value that will then be added together.  CustomerSurveyGrade would be positiveSurveys/TotalSurveys.  ProductivityGrade would be EmployeeTime::TimeOnCalls/calls::TotalCalls.  CaseQualityGrade would be SumofGrades/CountofGrades.  I would then create a point value for each of these three metrics.  Let's say the survey grade gets 0-10 points.  It's more complex but for simplicity, make this 10*CustomerSurveyGrade.  Each metric gets this type of point value depending on the performance.  These values are then added to give a final grade.  I would want to be able to search this grade by date range.

            Note: ProductivityGrade would be calculated dependent on average of group.  Above 10% of average would get so many points, 5% of average would get a certain amount, etc.

                 Now, due to the necessity of adding from multiple tables and calculating these grades, I do not see a possibility of using the Summary fields.  Hope this explanation is better.

            • 3. Re: Creating a relationship for date range find among multiple tables
              philmodjunk

                   Any time you generalize or simplify, you run the risk of getting a solution that fits the generalized/simplified description of the problem, but not the original.

                   This still leave two options from my previous post:

                   1) If there are significant differences in the fields used in each table, you still may want to use a unified table with related tables for managing the specific differences.

                   It would seem that the data you need to show in your report might be from a set of fields common to all your tables and thus putting them in a unified table that links to these other tables may be distinct possibility.

                   2) This sometimes makes settting up a summary table of denormailzed data worthwhile. In your case, you may need several such tables to summarize the needed data.

                    

              • 4. Re: Creating a relationship for date range find among multiple tables
                FileMakerNovice

                     I've never thought about combining all metric/stats tables into one.  They are imported from an excel sheet but I guess they could be combined without any major drawbacks.  Do you mind elaborating on option two?

                • 5. Re: Creating a relationship for date range find among multiple tables
                  philmodjunk

                       I went into that method in quite a bit of detail two posts back.

                       The basic method is to use a summary field and a looping script to compute the needed values for each new record added to the summary table. You also add a field to the original table that you can set to a value to "mark" it as being used in the summarizing process so that you do not use it more than once. Here's a broad outline of the process.

                       Loop
                          Perform a find for all records not Marked as "summarized"
                          If the resulting found set is empty, exit the loop
                          Constrain your found set to get a group of records for which you want to create a single summary table record with a sub total
                          The summary field or fields now give you the needed subtotal(s). Use set variable and set field to create a new record with this data in the summary
                                  table. if you are creative, you may be able to define a relationship with "allow creation..." enabled that allows you to use set field to both create
                                  the new summary record and populate the needed fields with data or you can put data in variables, change layouts to one for the summary table
                                  create the record and transfer data to it from the variables and then return to your original layout.
                          Use Replace Field Contents to mark this group of records as "summarized".
                       End Loop

                       This script is best run by scheduling it to run after business hours when no one is entering data into the table(s) being summarized. This can be done from a server schedule or from a robot file opened for this purpose by an OS based scheduling utility such as Windows Task Manager.

                  • 6. Re: Creating a relationship for date range find among multiple tables
                    FileMakerNovice

                         Thanks again for all the help.  So I have discovered that I can combine all but two tables and the parent table.  I now have:
                         Employees- Employee ID, names, etc
                         Metrics- Previously: Employee Calls, Time, CustomerSurveys
                         CallQuality- Call Quality is an external data source that I only have limited access to.  Therefore, I cannot combine it's information into my Metrics table.  These call evaluations can be done on days that the employee is not there.  

                         Right now I have a start and end date (global values) and appropriate one to many and cartesian ("x") relationships.  I have a grade for each of the stats in the Metrics table.  I also have the appropriate score for the CallQuality grade in the CallQuality table.  These two tables are not currently related to each other.  They are both related to Employees:
                         Employee          Matrics/Callquality
                         employeeID =     employeeID
                         startdate       X     date
                         enddate        X     date

                         So... Let's say, due to this employee's metrics, they get 80 points and due to the callquality, they get 10.  I would like to have a grade of 90.  How and where can I get these two grades added together?  Do I need to change the relationships?  Is the field needing to be in the Employees table or somewhere else?  

                    • 7. Re: Creating a relationship for date range find among multiple tables
                      philmodjunk

                           Yes, you'll need to change the relationships. The filtered portal method can display a sub total from a set of related records, but you can refer to the resulting value in a calculation to combine your two scores.

                           You'll need a relationship that matches to the same records without using a portal filter.

                           A date range can be implemented in a relaitonship for your Employee to Metrics relationship like this:

                           Employee::EmployeeID = Metrics::EmployeeID AND
                           Employee::StartDate < Metrics::Date AND
                           Employee::EndDate > Metrics::Date

                           But inequalities also have a performance penalty. If that relationshp turns out to be too slow, you might try using a recursive custom function (or a script) that takes Startdate and Enddate and produces a return separated list of all the dates from startDate to EndDate. Then your relationship looks like this:

                           Employee::EmployeeID = Metrics::EmployeeID AND
                           Employee::DateList = Metrics::Date

                      • 8. Re: Creating a relationship for date range find among multiple tables
                        FileMakerNovice

                             Awesome again.  I will try this.  One question though.  You mention the Employee to Metrics relationship.  Would the Employee to CallQuality be the same?

                        • 10. Re: Creating a relationship for date range find among multiple tables
                          FileMakerNovice

                               AWESOME!  My prelim tests work great.  I have another question.  I should probably create a new forum post but I'll ask it here since you've been great.  I would like to have a table for exceptions.  Lets say, we don't want to calculate John's metrics (or call quality) for 2 weeks because he's new.  He then works great for 6 months and then he goes to another training and we want to omit 3 more days.  

                               I'm thinking of a table that is related to Employees.  It would have start and end dates.  It could have several of these exceptions.

                               If you think I should create a new forum post, feel free to let me know.

                          • 11. Re: Creating a relationship for date range find among multiple tables
                            philmodjunk

                                 I think this would make a great new post. That way we invite other folks to contribute that will be put off by the length of this posting. But right this minute, I'm leaning toward using that list of dates field that I mentioned for the date range. If that list of dates omits the "exception" dates, they will be omitted from the calculations.

                            • 12. Re: Creating a relationship for date range find among multiple tables
                              FileMakerNovice

                                   Man... Spoke too soon.  My "Total Grade" doesn't calculate properly.

                                   So, I have a field in Employees called "TotalGrade" and it is giving a grade regardless of date range.  Any clue as to what I am doing wrong.  The layout uses the Employee table.  When I enter the global values for date, it changes all stats besides the total.  Total is a calculation field (unstored) of the other grade values.

                              • 13. Re: Creating a relationship for date range find among multiple tables
                                FileMakerNovice

                                     Cancel that.  I found out my relationship on the external server wasn't set up right.