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?
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.
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)
• Category - (i.e. "Employee", or another kind)
• ShiftID - (Auto-entered serial number)
• WorkerID - (Number, foreign key to the Workers table)
• 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.
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.
Sorry about the mess it didn't look like that when I hit 'Post'
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?
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.
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.
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
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.
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 =
Workers::Category = "Employee"
Sum ( Shifts 2::cTotalTime ) > 28800 ;
"WARNING: Limit exceeded"
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.
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" )
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. :)
The field cWarning must be a Calculation field - not a Text field with auto-entered calculation.
BTW, so should be cTotalHours.