14 Replies Latest reply on Apr 28, 2014 2:42 PM by philmodjunk

    Double booking alert

    EdwardAlvarez

      Title

      Double booking alert

      Post

           Hi,

           I am trying to figure out a way to have an alert pop up if an employee gets book twice in a day.

           On the confirmed events table I pick an employee from a pop-up list (being pull from employee records), each confirm event is one unique record. i am trying to figure out a way to let me know if the employee gets double book on the same date (it can be in a different record).

           Attached is the relation ship map.

      Screen_Shot_2014-04-28_at_11.03.30_AM.png

        • 1. Re: Double booking alert
          EdwardAlvarez
          /files/ad20affcd0/Screen_Shot_2014-04-28_at_11.03.03_AM.png 757x627
          • 2. Re: Double booking alert
            philmodjunk

                 Is the employee ID, the only thing that needs to be unique or do you also have an event ID and what you need are unique combinations of Event ID and Employee ID's?

                 If so, there are several different approaches you can take--from a field validation that shows an error if the same employee is selected a second time for the same event to interface designs that simply make it impossible to select the same employee twice.

                 But before I can discuss such options, I need to know the tables and relationships involved as well as the answer to the question that I asked at the start of this post.

            • 3. Re: Double booking alert
              EdwardAlvarez

                   Hi Phil,

                   Event ID and Employee ID are unique and i need them that way as the event ID is the same as the contract ID and that is how i for sure know which event is which (We usually work multiple times at the same address). The employee is unique per employee for Payroll purposes.

                   I would need a validation field, as there are times where we allow them to work two different events the same days, so i would an alert letting me he has been double book, i will then write notes on the event that will let me know why he is there twice on the same date.

                   Once a contract gets approve on the contracts table it will be then get confirmed and it will create a new record on the Confirm Events table. While on the CONFIRMED EVENTS table I select the employees that request each event (there are many events each day). Once an employee name is selected for an event the event's information (Date, address, start/end time) will appear on the employee payroll information. so that the path would be CONTRACTS - CONFIRMED EVENTS - EMPLOYEE PAYROLL.

                   Thank you

                   Ed Alvarez

                    

              • 4. Re: Double booking alert
                philmodjunk

                     Employee and event id must be unique, but only in the tables where these ID's are generated--employees and confirmed events. (And your screen shot had not yet appeared in the forum when I posted my first response.)

                     What is unique is the combination of event ID and employee ID in each record of your join table.

                     You can define a text field in the join table with this auto-entered calculation:

                     _kf_confirmed_event_id & " " & _kf_employee_id

                     Then you can select a "unique values" validation rule. This is a good "insurance policy", but it allows users to make the mistake of adding the same employee twice for the same event before displaying an error message--not the most user friendly of ways of handling this.

                     But a dwindling value list of employees can update to not list an employee in your value list once you have selected that employee for the current event. And other options also work to keep the use from making such a duplicate selection in the first place.

                     See this demo file for an example of this validation field option, a diminishing value list and a "check boxes" selection format as three different ways to prevent this creation of duplicate join table records:

                https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7

                     Just use Open from FileMaker's file menu to open and convert this older format file to the newer file format if you are using FileMaker 12 or newer. When you examine this file, just think of "contacts" as your table of employees.

                • 5. Re: Double booking alert
                  EdwardAlvarez

                       so if i want to do it with the date, do i replace the eventid with event date?

                        

                       EA

                  • 6. Re: Double booking alert
                    philmodjunk

                         Why would you do that when you already have and event id shown in your screen shot? What problem would that solve for you?

                    • 7. Re: Double booking alert
                      EdwardAlvarez

                           The formula you gave works great when i enter an employe twice on an EVENT. BUT if i enter an employee TWICE on a DIFFERENT EVENT on the SAME DATE it will not alert me. I need to know if enter an employee to work two DIFFERENT events the SAME DAY

                      • 8. Re: Double booking alert
                        philmodjunk

                             Then yes, you could use the date in place of the event ID--but be careful, this sounds more like a case where you want a warning--not an actual prohibition preventing the entry of the same employee for two events on the same day. (You might have a few exceptions where the same employee for two events of the same day is actually the correct input.) If that's the case, you'll want to be sure to provide uses with the option to override the warning when it appears.

                        • 9. Re: Double booking alert
                          EdwardAlvarez

                               yes that is exactly the case! some times we allow the same employee to work two events in one day.

                               Do i need to add a date field to the join table?

                          • 10. Re: Double booking alert
                            philmodjunk

                                 The auto-enter calculation that I described could auto-enter the date from confirmed events, but if you do, you'll need to be careful if you ever need to update a confirmed event record by changing the date of that event. If you do, the auto-enter calculation will not automatically update to use the new date--you'll need to add a script or manually do the following after such a date change:

                                   
                            1.           Find all join table records for that event.
                            2.      
                            3.           use Replace Field Contents with the calculation option to re-enter the EmployeeID and date.
                            • 11. Re: Double booking alert
                              EdwardAlvarez

                                   for some reason if i check "ALLOW USERS TO OVERWRITE DURING DATA ENTREE" it will not give me the alert "EMPLOYEE ALREADY WORKING" but instead it give the attached screen shot.

                              • 12. Re: Double booking alert
                                philmodjunk

                                     What happens if you add employeeAlert to the layout or portal?

                                     If that changes the validation error message shown, there are ways to hide this field while leaving it present on the layout.

                                • 13. Re: Double booking alert
                                  EdwardAlvarez

                                       it worked! thank you!

                                  • 14. Re: Double booking alert
                                    philmodjunk

                                         If this is FileMaker 13, you can select this field while in layout mode and enter the word: True into the Hide Object When box in the Inspector's data tab.

                                         If this is FileMaker 12, you may be able to drag this field out to the right of the right hand edge of the layout to hide it.

                                         If this is an older version, you can size the field very small and see if everything still works if you use the Inspector's behavior settings to deny access to the field while in browse mode. If not, you may want to remove the field from the layout's tab order and hide it behind a tab control or something.