We use Coda for scheduling. Each day has two 12 hour shifts. About 25 people can sign up for shifts.
Before a shift starts anybody can sign up
During an active shift, only managers can add people
Once a shift is complete it can’t be changed
We want everybody to be able to view past, present and future shifts.
I currently have a filter to show only future shifts and the present shift is shown as a formula, the page is locked. Past shifts get copied as text to another table and deleted from the schedule table.
We have had problems with people adding themselves mid-shift. Hidden pages and page locking hasn’t stopped this from occurring. I don’t think the people doing this are even trying to cheat, they are the least tech-savvy people we have.
As far as I understand, Hidden Pages and Locking are not for security. This certainly plays out true in our situation. The only option I can think of is to break our schedule out into 3 different Docs and try to manage cross-doc syncing.
Modify Existing Rows. Every week an automation creates the 2 shifts for the next 7 days, so 14 rows.
The page is a formula of the current shift, followed by a table of upcoming shifts. Past shifts are hidden via filter until the nightly automation moves them to an archive table and deletes it from the schedule table. This is the current setup, no need to keep it that way since it isn’t working so well.
Our shifts start at 8. In version history I have seen changes to a shift well after it has started. Once it is started only a manager (either via rights or a column in our people table) should be able to change it. Once a shift is complete nobody should be able to change it.
One other note, not everybody has smartphones, etc. We have a shared kiosk type setup that is always logged under a “dummy” account. This is why I use multi-select lists instead of a button tied to user().
Here is a screenshot, names blurred. The top is the formula, the page is locked.
They can click on a future shift day and add themselves as Officer, EMT or Firefighter for that shift. Wednesday Day shift is already hidden, and the page is locked. Version history doesn’t tell me if they are unlocking, removing filters, adding themselves and then resetting or how it is happening. I just need to make sure it stops.
Am I missing something simple?
No, you are not missing something simple.
What you want to accomplish is just not all that simple - if I understand your posting correctly you are trying to build a scheduling app, with some specific rules for your organisation, like: timely enlisting, no doubles, no alterations…and probably a few more. If you are not a programmer by trade, Coda could be the tool of choice to accomplish this, but it is just not all that easy. You have to make choices as to how people enroll (external form, embedded form, table) and how you want your people to interact with your app.
In this community, a lot of people are eager to help you solve specific problems, but you are not posting about a specific problem or formula you can’t get to work, but describing an app.
If you need help with building an app (or building the app froms scratch) you have come to the right place, but you are not likely to find someone that is going to do this work for free.
I think it would take about a week to get a good foundation for this project build - if you have a budget to get this started, please let the community know.
If you only want some help with specific functionality, you will often find some (free) help here too, but you have to be specific about the help you need.
I am an “old” programmer (late 90s to 2013) by trade, not anymore. This is an attempt to make something “permanent”, former programmer laughs at that. But fully custom means only 1 person understands it. If I leave or die, can it live on? This is a rural volunteer fire department, budgets aren’t there for hiring teams of people.
The main issue- once a thisrow.(date/time) has passed, how can I lock it? Not just hide it or “page lock it”, but make it read only (they should still be able to see the rows). Is it possible in a single Doc? Or do I have to make 3 schedules (past, present, and future) in different Docs to lock expired schedules?
@Web_Master Cross-Doc is actually the way how I would do it as well. Since you are using page locking, I’m assuming you are in the Team or Enterprise plan, which allows you to perform cross-doc actions and have private folders. Unfortunately I am not, so I couldn’t test all parts, but this is what I would recommend:
Put your source doc that contains the tables Shifts, People and Roles in a private folder. Create one relation Control for people and shifts, as well as one button for each shift modification option (add person to shift, remove person from shift…). The editing restrictions can be implemented as disabeling conditions for these buttons, i.e. disable if the selected person is a worker and the shift start is in the past or if the person is a manager and the shift end is in the past.
The display doc contains one way sync cross-doc tables from Shifts and People as well as similar control values and buttons. When pressing a button, a cross-doc action sets the relation controls in the source doc to the same value as previously set in the display doc and pushes the equivalent button afterwards (if enabled).
User handling
If you want to make sure that people can only edit their own shifts, but from the same dummy account, you could create a password protection (sort of) via the cryptography pack. Let everyone pick a password once, that gets hashed and stored in a column in People. When editing a shift, people would have to enter their password into a text control, the cross-doc buttons would then hash it and send it to the source doc. Only when the hash values match, the change gets perfomed. Refreshing the page would be necessary after each edit to prevent people from restoring entered passwords by undoing actions.
I hope this provides a little orientation how you could improve the security.
If this was my project, I would stay away from crossdoc, because there is a delay for the sync - and it becomes complicated in a hurry.
I made a small sample with a possible way how you could collect data and display the schedule. Obviously, there are many ways to make it look a lot better.
