11 Replies Latest reply on Jun 30, 2014 3:05 PM by philmodjunk

    DATE RANGE AND 7 DAYS ALERT

    EdwardAlvarez

      Title

      DATE RANGE AND 7 DAYS ALERT

      Post

           Hi,

           i am trying to figure out how to display the date range I look for. for example when I work on payroll we do it Bi-Weekly so for this coming pay period i work with the weeks of 6/9-6/15 and 6/16-6/22 is there a way to  to show the search performed somewhere on the date base. i would like ensure i am being shown records on those dates only.

           DAYS ALERT:

           i would also like an alert if an employee works 7days in a row MONDAY - SUNDAY only. for example if a person works 7 days sunday to saturday that is okay i do not need the alert. but if someone works MONDAY to SUNDAY i would like an alert letting me that person worked 7 days in a row that way i'll know he needs to be pay over time for the hours worked on that 7th day.

           Thank you

           Edward Alvarez

        • 1. Re: DATE RANGE AND 7 DAYS ALERT
          philmodjunk
               

                    is there a way to  to show the search performed somewhere on the date base.

               If you use a scripted find where you enter your find criteria for the find into global fields while still in browse mode and then use the script to perform the find, you have also captured your criteria--such as two dates in a pair of global fields in global fields that can then be displayed on any layout where you need to show this information.

               

                    i would also like an alert...

               Assuming one record per day worked per employee, the following calculation:

               Let ( D = DateWorked - 1 ;
                       D - DayOfWeek (D ) + 2 )

               Will compute the date for Monday of the Monday to Sunday "week" specified. If you define a calculation field with this expression and a Date result type, you can then define a self join relationship with two pairs of match fields that match by employeeID and this calculated date. You can then use the Count function to count the number of related records. If the count is 7 then your employee has worked all 7 days in this Monday to Sunday time period.

          • 2. Re: DATE RANGE AND 7 DAYS ALERT
            EdwardAlvarez

                 Hi Phil,

                 I have questions on for both questions /:

                 Let's focus on 7 day alert first as i feel this is more important in order for me to start using the new data base. I have an Alert set when an employee works two events the same day, to avoid double booking. is there something similar to this that will tell me I have an employee working 7 days in row. i tried the formula you gave me, but I guess i did not enter it correctly. for "DateWorked" i used the date of the event, DayofWeek i used Monday's date for the pay period 6/9-6/22 (6/9 was a monday) but the result is a date 3 days before the date of the event (see screen shot). The formula I am using for the same day employee alert you helped me with this formula  Event_date &  " " & _kf_Employee_ID is there a way to do something similar for the 7 days? 

                 Thank you once again


                 Ed Alvarez

            • 3. Re: DATE RANGE AND 7 DAYS ALERT
              EdwardAlvarez

                   Also in case you'd like to see the relation ship i have set up.

              • 4. Re: DATE RANGE AND 7 DAYS ALERT
                philmodjunk

                     DayOfWeek is not a field, it's a built in FileMaker function.

                     But your data model does not match my assumptions as there was no mention made of a Join table in your original post. That complicates this issue as the date worked (event date) and the record for that employee working for that event are not in the same table.

                     It would appear that all events are single day events in your table. Either events never last for more than a day or a three day event is logged as three records in confirmed events. Is that correct?

                     To employ the method that I described requires either copying the eventdate into a field in the join table, Using a global field in the event table to "tunnel through" an occurrence of events to match to all records in the same seven day time period for the same employee, or an SQL query using ExecuteSQL (Requires FilMaker 12 or newer).

                • 5. Re: DATE RANGE AND 7 DAYS ALERT
                  EdwardAlvarez

                       Hi Phil,

                       yes, each event is one record, but every time i pick an employee to work an event it creates a new record on the Join table for that employee. each of the created record are immediately transfer to the payroll layout, taking the address, start/end time and it also calculates the total hours I also have an Event_Date field on the Join table which gets auto fill with the Event_Date field from the contracts table (each one of those field are also auto enter fields on the Join table and are all copy the same way as the Event_Date). I have attached a few screen shots so that you can get a better understanding.

                       Regards,

                       Ed Alvarez

                  • 6. Re: DATE RANGE AND 7 DAYS ALERT
                    EdwardAlvarez
                    /files/c253b7a8be/Screen_Shot_2014-06-30_at_12.17.50_PM.png 1374x330
                    • 7. Re: DATE RANGE AND 7 DAYS ALERT
                      EdwardAlvarez
                      /files/32fe79c017/Screen_Shot_2014-06-30_at_12.19.27_PM.png 872x592
                      • 8. Re: DATE RANGE AND 7 DAYS ALERT
                        EdwardAlvarez
                        /files/6536e002d6/Screen_Shot_2014-06-30_at_12.20.14_PM.png 1388x344
                        • 9. Re: DATE RANGE AND 7 DAYS ALERT
                          philmodjunk

                               This doesn't alter anything that I posted in my last post. You have data in two different tables that I originally assumed would be in different fields of the same record. You'll need to pick one of the methods I outlined and then I can describe that option in more detail.

                          • 10. Re: DATE RANGE AND 7 DAYS ALERT
                            EdwardAlvarez

                                 Hi,

                                 I believe ""Using a global field in the event table to "tunnel through" an occurrence of events to match to all records in the same seven day time period for the same employee""  would be easier option, unless you think other wise, i am using File Maker 12.

                                 Regards,

                                 Ed Alvarez

                                  

                                  

                            • 11. Re: DATE RANGE AND 7 DAYS ALERT
                              philmodjunk

                                   FileMaker 12 also gives you the option of Using ExecuteSQL. If you are familiar with SQL queries and how to use them with that function, that option can be the simpler choice.

                                   The "tunneling" method involved adding at least two more table occurrences to manage database and one of the relationships will require a global field that a script will need to update with the correct value before this will match to the correct set of join table records in order to produce a match to the correct set of records in order to count them and determine how many exist in the same 7 day time period.

                                   So if you are new to SQL queries, this "tunneling" method may be easier than SQL, but on the other hand, the first option was simply to copy the event date into the join table each time you link it to an event record. That's much simpler, but if you have to modify the date in the events table, you then have to also update the matching date field in the join table records linked to it.