1 2 Previous Next 17 Replies Latest reply on Feb 6, 2013 4:53 PM by FileMakerNovice

    Omitting dates

    FileMakerNovice

      Title

      Omitting dates

      Post

           Hello,

           I am interested in omitting date ranges while an employee is in training, sick away, etc.  Here is the

      Relationship View:

           Employee--Stats
                         \
                         Calls--CallQuality

            

      Tables:

           Employee
           Consists of Employee Name, Manager, Employee ID, StartDate (global), EndDate(global) and Calculation fields that provide a range of "Metrics" from Stats and Call Quality.

           Stats
           Consists of employee ID, dates that the employee worked, how many customer surveys they received, the grades of these surveys, how long the employee was on calls, how long they were on break, etc.

           Calls
           Consists of employee ID, dates a call was analyzed, and a call ID.  The call ID is a unique number given to a call.  Sort of like a case id.

           Call quality
           This holds values (1-10) for certain skills.(CustomerSkills, TechSkills, ListeningSkills, etc)

      Relationships

           Employee to Stats
           EmployeeID=EmployeeID
           StartDate<=Date
           EndDate>=Date

           Employee to Calls
           EmployeeID=EmployeeID
           StartDate<=Date
           EndDate>=Date

           Calls to Call Quality
           CallID = CallID

            

           I currently have a layout in form view that I enter the start and end dates and it gives me the metrics for an employees that I perform a find on.  I also have a layout in list view that gives me a list of all employees.

           I am looking to have a table of start and end dates for an employee of dates that I'd wish to exclude.  This would have employeeID, start, end, note.  For instance, John Doe was training 1/1/13 to 1/2/13 and was sick from 1/13/13 to 1/15/13.  I do not want these dates to be included in the two layouts mentioned.

           I am having difficulty determining how to incorporate these into the layouts.  Please note, I cannot combine States or Calls.

        • 1. Re: Omitting dates
          philmodjunk

               I believe that we discussed this in a previous thread.

               If we modify your relationship:

               EmployeeID=EmployeeID
               StartDate<=Date
               EndDate>=Date

               to be:

               EmployeeID=EmployeeID
               cDateList=Date

               Where cDateList is a field with a return separated list of dates, we can omit all such dates by omitting them from this list of dates in the cDateList field.

               There are several ways to produce that list. One way is to have this relationship:

               Employee----<DatesGraded

               Employee::__pkEmployeeID = DatesGraded::_fkEmployeeID

               with a field, DateGraded in DatesGraded to record the date and with one such record for every date from DateStart to DateEnd--something that can be generated from a script, but with the dates an employee is absent deleted from the table or excluded from the relationship.

               Then cDateList can be a calculation field set to return text defined as:

               List ( DatesGraded::DateGraded )

          • 2. Re: Omitting dates
            FileMakerNovice

                 You are correct, sir!  We have spoken about this database before but not this specific functionality.

                 Once again, my novice knowledge is going to show...

                 I have two layouts mentioned above: "Rank" and "Scorecard".  Currently, in these layouts, if I enter the global start and end dates, it returns the Metrics within this range.  If I wanted to see John Doe's Metrics for the month of January, I'd enter the start and end date and it would provide me with only the Metrics for that time period.  I believe the method you mentioned would remove this functionality.  Am I mistaken on this?  Whould I need to change the layouts to make this functional again?

                 The second part of my confusion is the need to confirm that I understand correctly with the addition of a few questions.  The steps to implement what you suggest would be:
                 1.  Create a new table "DatesGraded"
                 2.  In DatesGraded, create a field for employee ID and DateGraded
                 3.  Relate it to Employees by Employees::EmployeeID=DatesGraded::EmployeeID
                 4.  Create a field in Employees as cDateList defined as a calculation =List(DatesGraded::DateGraded)
                 5.  Create a script upon action, it deletes dates from Start date to End date in "DatesGraded".

                 My first question on this is how do all dates get populated in the table "DatesGraded"?  My next question would be how do I make my layouts work with this?  As mentioned above, I still would need to be able to enter a start and end date and only see that period of Metrics.  

            • 3. Re: Omitting dates
              philmodjunk

                   Starting from step 5, you would want a script that CREATES records in DatesGraded from date start to date end.

                   But then to modify the date range to omit specific dates, you would delete those specific records for the given employee.

                   I had envisioned a fixed interval for date start and date end. We'll need to modify the relationships, not the layouts, to adjust for flexible date ranges:

                   Employee::__pkEmployeeID = DatesGraded::_fkEmployeeID AND
                   Employee::StartDate<=DatesGraded::Date
                   Employee::EndDate>=DatesGraded::Date

                   A portal to DatesGraded could then include a delete portal row button to delete dates you want to omit from the date range.

                   A looping script can use the start and end dates to create those needed for a given employee.

                   Hmm, but hang on, the more I think about this one, the more i want to turn this "inside out" to make the data entry for specifying dates to omit easier. We can adjust this approach, I think, so that records present in the related table will specify dates to be omitted rather than dates to be included as I have set up now. Stay tuned for a follow on post...

              • 4. Re: Omitting dates
                philmodjunk

                     Let's try this relationship:

                     EmployeeID=EmployeeID AND
                     StartDate<=Date AND
                     EndDate>=Date AND
                     cOmitDates ≠ Date

                     And the relationship to the related table (with a name change to reflect the reversed function of this table):

                     Employee::__pkEmployeeID = DatesOmitted::_fkEmployeeID

                     and now cOmitDates should be defined as:

                     List ( DatesOmitted::Date ) & ¶ & 1

                     Make sure to clear the "do not evaluate if all referenced fields are empty" check box.

                     You need to clear this checkbox and include the & ¶ & 1 in this calculation to make sure that cOmitDates is never empty.

                     Now you can set up a portal to datesOmitted on your Employees table where you can add dates for any days that you want to exclude from the grading calculations. You can even add a field to this table and place it in the portal to record the reason this date is to be omitted.

                • 5. Re: Omitting dates
                  FileMakerNovice

                       Okay... So this:
                  "EmployeeID=EmployeeID AND
                  StartDate<=Date AND
                  EndDate>=Date AND
                  cOmitDates ≠ Date"

                  Is referring to the two tables, Stats and Calls, correct?

                  I think I've got it.  Just a bit of testing to see if it is working.

                  • 6. Re: Omitting dates
                    FileMakerNovice

                         So I created the table "DatesOmitted" and related it with an EmployeeID=EmployeeID.  I then created cOmitDates with the above formula making sure that I unchecked the box for do not evaluate if empty.  I then related above to my other tables.  Now my data does not pull up properly.  When pulling up the Employees table and displaying cOmitDates, It has a "?" and no values.  Did I do something wrong?

                    • 7. Re: Omitting dates
                      FileMakerNovice

                           My issue still persists.  My previously mentioned "Rank" and "Scorecard" no longer produce any data.  Here is:

                            

                      Relationship View:

                                        DatesOmitted 
                                        /
                           Employee--Stats

                                         \
                                         Calls--CallQuality

                            

                      Relationships

                           Employee to Stats
                           EmployeeID=EmployeeID
                           StartDate<=Date
                           EndDate>=Date
                           cDateList≠Date

                           Employee to Calls
                           EmployeeID=EmployeeID
                           StartDate<=Date
                           EndDate>=Date
                           cDateList≠Date

                           Calls to Call Quality
                           CallID = CallID

                           cDateList is devined as a calculation:  List(Ranking » DatesOmitted::dateToOmit) & ¶ & 1 with "Do not evaluate if all referenced fields are blank."  Unchecked.  Calculation result is "Number" with number of repititions at 1.

                           What am I missing?

                      • 8. Re: Omitting dates
                        FileMakerNovice

                             Okay... So I changed the field type to "Text" instead of "Number that it was before and cOmitDates now displays the expected return+1.  The layouts are still not working though.  sad

                        • 9. Re: Omitting dates
                          philmodjunk

                               did you call it "cOmitDates" or "cDateList"? you should have one or the other, but not both.

                               What return type did you specify for this field? I'd guess from the question mark that you specified "date" when you should have specified "text".

                               It may look wrong to match a calculation field that returns text to a field of type date in your relationship, but it does work.

                          • 10. Re: Omitting dates
                            FileMakerNovice

                                 Apologies.  I copied from the previous comment.  I do not have "cDateList" only "cOmitDates".  I did have the field as "number" but corrected that to "text" now.  The field cOmitDates appears to be correct now with ¶ 1.  But my layouts do not function.  

                            • 11. Re: Omitting dates
                              FileMakerNovice

                                   Odd.  It started working when I put one date in one employee's "DatesOmitted" table.  All other employees it does not function with.

                              • 12. Re: Omitting dates
                                FileMakerNovice

                                      

                                     So.  All my relationships were correct and the fomula was as you mentioned.  I attempted several iterations of "List(OmittedDates::date) & ¶ & 1".  I tried the following:
                                     List(OmittedDates::date) & ¶ & 1
                                     List(OmittedDates::date) & ¶ & 1/1/2010
                                     List(OmittedDates::date) & ¶ & "1"

                                     None worked until I finally tried List(OmittedDates::date) & ¶ & "1/1/2010" and it began to work.  As long as I put "1/1/2012" in quotes, it doesn't matter what date I put in there.  Any reason why this would happen?  I see no problem with putting in a old date and omit it through portal filtering later but was just curious if you had any clue why this happened and why it worked?

                                • 13. Re: Omitting dates
                                  philmodjunk

                                       Apologies back. I ran a few tests and found that every items in cOmitDates must be a valid date. Change your calculation to:

                                       List(DatesOmitted::dateToOmit) & ¶ & "1/1/0001"

                                       I'm also finding that a portal to my employee data table (the one that has to filter by date range and employee ID) isn't updating automatically when I add/remove/change an omitted date record. I've had to use Refresh Window [Flush Cached Join Results} to get it to update so you may need to use that script step to force an update after changing data in the portal to the employee's omitted dates.

                                  • 14. Re: Omitting dates
                                    FileMakerNovice

                                         Oh.  I didn't even think about checking that.  My layout that gives the user an option to omit dates is not on the same layout that the data is given.  Would I need to use the refresh window as you mention?

                                    1 2 Previous Next