1 2 3 Previous Next 35 Replies Latest reply on Jun 23, 2009 6:31 PM by comment_1






      Hi I am desperate for help with a function.  I have FMP V8.5 and I've tried reading heaps of solutions but I just get more confused as there seems to be a dozen options for any one thing.  Add to that the fact that I really don't understand all this language and you have one crazy person who is going around continually muttering to herself.


      OK the facts .......  1  I am trying to create a database which will incorporate a fortnightly roster for people who work shifts. These shifts are not necessarily regular from day to day or week to week and there are often several short shifts filled by an individual in any one day).


      2 One group of these people are not able to work longer than 8 hours in any one day or 40 hours in any week. 


      3 I have  'Date' ; 'Worker' ; 'Start Time' ; 'Finish Time' ; 'Total Hours' fields along with other stuff.  


      4 I would like something that would calculate (on entry) :    If 'worker' field = "employee" and 'total hours' fields for any 'Date' add up to  > 8 please return an error message which allows user to edit or ignore and continue. 


       5 As mentioned the roster is fortnightly with a 'Fortnight Begins' date and each fortnight runs from Mon - +14 (Sun ).  So I would also like to notify user if any Mon-Sun week the hours for 'Worker' are > 40.


      Does this make any sense and can someone please, please help me !


      Kind regards,


        • 1. Re: Functions

          tricias wrote:
          I have  'Date' ; 'Worker' ; 'Start Time' ; 'Finish Time' ; 'Total Hours' fields along with other stuff.  

          1. Where is the field that identifies the person working the shift? Do you have a parent table of people, to which the current table (Shifts?) is related?


          2. What does the "Worker" field represent? Is it a category to which the person belongs? If so, why isn't it in the parent table?

          • 2. Re: Functions

            Hi comment,


            Thank you for responding.  Yes I do have a table of support workers which also includes the other field.  Can I please send you a small untidy sample file which might explain better what I wish to do.





            • 3. Re: Functions

              tricias wrote:
              Yes I do have a table of support workers which also includes the other field.

              Well, then you don't need to duplicate it in the Shifts table. So, assuming a structure of:

              • WorkerID - (Auto-entered serial number)
              • FirstName
              • LastName

              • Category - (i.e. "Employee", or another kind)

              • ShiftID - (Auto-entered serial number)
              • WorkerID - (Number, foreign key to the Workers table)

              • Date
              • StartTime
              • EndTime
              • cTotalTime - (Calculation, = EndTime - StartTime)

              and related as:
              Workers::WorkerID = Shifts::WorkerID

              you can enforce a limit of 8 hours per shift for certain workers by validating the EndTime field with a calculation =
              not ( Workers::Category = "employee" and EndTime - StartTime > 28800 )


              Or, if you don't like validation, define a calculation field (result is Text) =

              Case ( Workers::Category = "Employee" and EndTime - StartTime > 28800 ; "WARNING: Limit exceeded" )



              In order to impose a limit of 40 hours per week, you will need to define another calculation field and a self-join of the Shifts table - but I will pause here to let you catch up.

              • 4. Re: Functions

                I'm sorry I haven't explained myself very well!  In my shift form I have:-

                                                                                                               Shift 1        Shift 2         Shift 3         Hrs per day           Shift 4
                Support Workers::SW ID (populated from Support Workers table)       JJ100          JJ100          JJ100                                      TS101

                Support Workers::SW Type  (populated from Support Workers table)   Employee    Employee     Employee                                Contractor

                Fortnight Begins  (Drop down calendar)                                           1/6/09        1/6/09         1/6/09                                    1/6/09

                Shift Date (drop down calendar)                                                    4/6/09         4/6/09         4/6/09                                    4/6/09

                SU Given Name (drop down list)                                                   Bob              Tim            Sue                                        Honey

                SU Last Name (drop down list)                                                     Brown           Noke          Jones                                     Simpson

                Service Users::Service User ID (populated from Service Users table) BB5000         TN5001       SJ5002                                   HS5003

                Start Time (24 hr time)                                                              06:00            10:00          15:00                                     08:00

                Finish Time (24 hr time)                                                             09:00            14:00          18:00                                     19:00

                Total Hours (calculated 'finish time - start time')                             03.00            04.00          03.00            10.00                  11.00


                SW Total Hrs (this is where I need it to show error if >40 hrs in week 1 OR week 2 

                Various other fields relevant to each shift. 


                You will note that the first three shifts are all done in the same day by the same worker with the same worker type who, in this case, is working >8 hours  (Shift 4 is being done by different criteria so does not need to be monitored.  It is included just to show the difference).  The functions I want can be run in the background without a field and just show up as an error message with 'Change" or 'Ignore' on entry of the shift that takes the hours over total 8.  There are times when an employee does work more than 8 hrs in one day so I want to be able to accept the greater hours if necessary.  


                These same 'support worker type' shifts need to be brought to attention if they exceed 40 hrs. in any one week of any roster (Monday to Sunday). 


                Unfortunately there is a great deal of inconsistency with hours and shifts due to the nature of our work (we are a not-for-profit organisation who works with people with disabilities).


                I do hope I have managed to make it all much clearer for you and thank you for your patience.






                • 5. Re: Functions

                  Sorry about the mess it didn't look like that when I hit 'Post'



                  • 6. Re: Functions

                    tricias wrote:

                    The functions I want can be run in the background without a field and just show up as an error message with 'Change" or 'Ignore' on entry of the shift that takes the hours over total 8.  There are times when an employee does work more than 8 hrs in one day so I want to be able to accept the greater hours if necessary.

                    Instead of validation, you can use a calculation field to return a warning in case of exceeding the given limits. Unlike validation, it wil not meet your original request to "return an error message which allows user to edit or ignore and continue" - it will just place a message on the layout. You cannot do this without a field - at least not in version 8.5.

                    However, I am greatly confused by the structure reflected in your last message. If I am reading this correctly (which I may not, because it's VERY difficult to read), this one record shows that on day 4/6/09 worker # JJ100 worked three shifts, for three different service users, and that ANOTHER worker, # TS101, worked a fourth shift for a fourth user.

                    So my question is: what is this record a record of? What does it represent in real life? Are these 4 shifts ALL the shifts that occured on the given day? I suspect not. So what do these 4 shift have in common that puts them in the same record?

                    tricias wrote:

                     You will note that the first three shifts are all done in the same day by the same worker

                    I note that there are three separate fields to indicate the worker, so there COULD be a different worker for each shift (and there IS ANOTHER worker working the 4th shift).



                    I believe you should have an individual record for each shift worked. This way there would be ONE calculation for the total hours, ONE relationship to the workers table, and so on. It will be also easy to calculate the weekly hours of a worker - which given your current structure (again, if I understand it correctly) is nearly impossible.

                    • 7. Re: Functions



                      I am really struggling to explain what it is I'm trying to do.  PLEASE understand that I have NOT done this sort of thing before so have mercy on me:)   It's like a foreign language.   I did apologise for the messy email, it didn't look messy when I posted it.


                      Each shift is entered in a SEPARATE RECORD because they COULD be done by different workers.  


                      Of course there are MANY shifts on any given day with well over 400 service users but I didn't think you would want to see all that.  


                      Each support worker has an individual record for each shift they work.

                      My first criteria is the SW Type so shift 4 was ONLY to show you the difference in TYPE.


                      I have tried to copy and paste my individual record layout into one of these messages but all I get is a small box with a question mark.  I am MORE THAN HAPPY to send you a copy of my file with dummy data if you can tell me how to do it as I can't find a way to attach anything to these messages either.


                      Please continue to be patient with me, this is a very steep learning curve.


                      Kind regards,




                      • 8. Re: Functions
                           You cannot attach files on this forum - but you can upload them to a server (e.g. www.mediafire.com), and post a link to them here.

                        However, keep in mind that what you have (and doesn't work) is of limited use - what's more important is what you SHOULD have. I suggest you review my second post in this thread (which I have edited for more detail) and see if it matches your structure
                        • 9. Re: Functions



                          Thank you for hanging in there with me.  I have this structure, including the relationships.  It's NOT limit of 8 hrs per shift ............  I need limit of 8 hours per DAY per WORKER.  I had thought of using a 'case' function but will be happy to have whatever it takes to get it to work.


                          Kind regards,

                          Tricias  :) 

                          • 10. Re: Functions
                               OK, then we need to sum the hours of several records - which means we need a relationship to identify those records.

                            The required relationship is a self-join of the Shifts table. This means you need to place a new occurrence of the Shifts table on the relationship graph (click the Add button and select the Shifts table - it will be initially named 'Shifts 2').

                            Connect Shifts and Shifts 2 in the following way:

                            Shifts:: WorkerID = Shifts 2:: WorkerID
                            Shifts:: Date = Shifts 2:: Date

                            Now go to the 'Fields' tab and make that calculation field in Shifts =

                            Case (
                            Workers::Category = "Employee"
                            Sum ( Shifts 2::cTotalTime ) > 28800 ;
                            "WARNING: Limit exceeded"

                            • 11. Re: Functions

                              Well I have created the new occurrence of the table and made the connections.  I also now know what a self-join relationship is   :) 


                              Shifts::Employee Type = Shifts 2::Employee Type


                              Shifts::Date = Shifts 2::Date


                              Then I made the Calculation for cTotal Hours =


                              Case(Support Workers::Employee Type = "Employee" and Sum(Shifts 2 ::cTotal Hours) >28800 ; "WARNING: Limit exceeded" )


                              The field type is calculation and now I am just getting a blank field.  Did I need to leave in "Finish Time - Start Time" ?  The field is also unmodifiable so I am unable to test it to see if the warning message comes up.


                              Kind regards,







                              • 12. Re: Functions

                                I suspect two mistakes in your implementation:



                                The relationship needs to match the individual worker - NOT the worker type (otherwise you'll be summing the shifts of ALL workers of type "Employee").  So the first matchfield needs to be WorkerID, not  Employee Type.


                                The field Employee Type shouldn't even be in the Shifts table, because it does not describe the shift - it describes the worker. A shift "knows" who the worker is, and it can get any information it needs about them directly from the related record in the Workers table, with no need to duplicate the data.*



                                (*) This assumes the worker's type is constant. It would be different if the same worker can work in several different capacities.





                                You need TWO calculation fields in the Shifts table:


                                cTotalHours (result is Time) =

                                EndTime - StartTime



                                cWarning (result is Text) =

                                Case (Support Workers::Employee Type = "Employee" and Sum (Shifts 2 ::cTotalHours) >28800 ; "WARNING: Limit exceeded" )

                                • 13. Re: Functions



                                  Oh wow  ::) I'm so excited, it works :)  Now I just need to refine it a little.  I must have something set incorrectly as the warning stays even if I change the hours to <8.  The settings are "Auto enter, Calculation replaces existing value, Evaluate Always, Always Validate"  


                                  Also the warning only shows on the last shift entry the same as a total would show at the bottom of your sums.    Can the warning show on all shifts that have gone to make up the >8. And .... I just tried it with another support worker and it didn't work and I can't find anything different.   Thanks for helping me. :)

                                  • 14. Re: Functions

                                    The field cWarning must be a Calculation field - not a Text field with auto-entered calculation.



                                    BTW, so should be cTotalHours.

                                    1 2 3 Previous Next