[libreoffice-users] Base: Need help with interface to complex staff rota.

Hi all,

Really hope you can help me with this.

I currently use spreadsheets for this, but as I'm sure you hear a lot:
spreadsheets are getting out of hand and I need a better way to handle this.

First, I already know the underlying table structure for the planned staff
rota. Also, how to add and update staff. It's the user interface to creating
the rota input that I need help with. I'll try to describe it as best as I
can.

I'd like to have headings for the days of the week at the top that stay
visible if I need to scroll down with a list of staff on the right side,
that stay put if I need to scroll left or right (if the window is resized
kind of thing).

The tricky part comes because instead of the usual start, finish, and total
unpaid hours for the day (mainly lunch break, the following situations can
occur:
a) The staff member's hours can be split into two shifts for the day. For
example, morning 9-11, then 16-18. If unpaid hours is used to and start = 9
and finish = 18, then that really wouldn't show the true work day.
b) The staff member could be called away to another location. Their time and
place need to be listed in our store, but not count towards the budget of
the location I am in charge of. I have two kinds of rotas to deal with this:
The one for my location and one that has an added cell for location. The
main rota doesn't have that added location so there are more rows available
to use.
c) The staff member could be called away in an emergency to another location
in the middle of their planned shift, so morning one location, and afternoon
a different location.
d) A staff member from another location may need to be added to the rota for
display and budget calculation.
e) I need to keep track if my location is the last location worked by an
employee, because of overtime calculation. It's easy to keep track for my
employees: just look for the last shift worked if it was at my location or
elsewhere, but the trouble comes if I have an employee from another
location, so I need to track that.

I have planned to have a separate table for daily shifts for each employee,
so an employee can have 0 to many shifts each day. It's a simple table of
ID, location, start, finish, unpaid hours. 

Currently in the spreadsheet, I have three rows: Start, Finish, unpaid
hours. Then those three rows merged into one for the name column. I have the
employee hourly pay on the same row, along with other needed info that
affects pay. At the top of each day column is a true/false variable for if
that day is a double pay day. The unpaid hours row and the true/false row
are hidden when printed, so there are gaps between row edges. I figured in a
database, all this info is hidden behind the scenes, so a better formatted
rota can be printed for display.

How I'd like to enter date:
I'd like to be able to click a day for an employee and be able to add all
the shifts that he/she is working that day: location, start, finish, unpaid.
I also need to input if the day is a Holiday and the hours for that or a
regular. If no data, then employee is off.
Also, having the budget displayed on the overall form with all the employees
and a running total would be helpful so I know if I can add more shifts.
Don't know if this is possible. I know it's possible on the report of the
table, but the form to input data?

The final layout of the printed rota:
Since currently I have gaps due to hidden rows, I'd like a cell for the
shifts to have adjustable size text. I need those three rows for Sheets to
do the calculations, but since in a database it's all behind the scenes, the
cells for the shifts can contain plain text. If there is no shift that day,
then the text "OFF" to be displayed, if holiday, then "HOLS" (I only have a
small square to work with), if single shift, the Start time, then a -, and
then the finish time on a new line take up the whole box. An employee will
never have more than two shifts in a day. If two shifts, then the start -
finish of fist shift on top (separated by the -), and the next shift
underneath. The text would need to be smaller to fit that.
Then the total hours of employees working that day at the bottom of each day
column. Also, the budget, the total pay, and difference at the very bottom.

Like I said, this is complex. Any help to get me going on this would be
greatly appreciated.

Thanks,
Jon



--
View this message in context: 
http://nabble.documentfoundation.org/Base-Need-help-with-interface-to-complex-staff-rota-tp4148245.html
Sent from the Users mailing list archive at Nabble.com.

-- 
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted