Employee Database - Shift Openings Workflow - Help with Design / Workflow

[Joseph_Workman]

Greetings Everyone,

I need some help with an employee staffing database. We use it to track employees, shifts, open shift trade request, pay, vacation etc

I need make a way that the people in the database can check a box to indicate they want to post a shift as open to trade. The following tables are setup: staff, shift, shift posting, shift posting response.

This was my idea for a workflow.

1. Staff member uses a form to select the shift they need to trade. They indicate this via a checkbox called “Trade Needed”
2. Staff member uses a second form to post this to our available shifts.
3. Other users use a third form to indicate their interest in the shift.

I can’t figure out a way to allow the staff members to select that checkbox.

The restriction is that we don’t have airtable user account for each member of the staff. There are currently 40 contractor we have and the price doesn’t scale.

Can anyone point me in the right direction?

[Justin_Barrett]

I’m guessing the checkbox you’re talking about is on the shift record itself, in the[Shift] table, where all shifts are listed. The problem is that Airtable won’t let you edit an existing record via a form. There’s a workaround for this that works in certain situations, but might not be ideal for yours.

I suggest merging steps 1 and 2 from your list into a single step. Make a form on the [Shift Posting] table, with a field that points to records in [Shifts]. The person with a shift to trade will fill out the form, choosing the record for the shift they want to trade. This will add a record to [Shift Posting], where others can see what’s available.

To claim a shift, staff would use a similar form on [Shift Posting Response], which would let them pick an available shift from [Shift Posting]. The default view in [Shift Posting] could be set with a filter to only show shifts that haven’t been claimed (i.e. no link from [Shift Posting Response]). Once a claim is added in [Shift Posting Response], it becomes unavailable for someone else to claim.

[Joseph_Workman]

If I merge Steps 1 and 2 how will I limit the view to only show the shifts that the user currently has?

The only solution I can think of is custom views and then custom forms for each and every user. Which is a lot of duplicated effort.

This is my biggest obstacle.

[Justin_Barrett]

Joseph_Workman:

f I merge Steps 1 and 2 how will I limit the view to only show the shifts that the user currently has?

That’s certainly a problem I didn’t consider. I got the impression that you were okay giving staff members access to the full, un-filtered shift list, trusting that they will pick a shift to trade that is their own. If you don’t feel that’s a viable option, and need to isolate the list of available shifts on a form to only show those for a single staff member, you’re correct. That can’t be done without custom views. At least not with Airtable’s own tools.

There are some more robust form tools out there that can tie into Airtable, and which may be able to do what you need. I don’t have experience with them myself, but the two that come to mind are On2Air: Forms (by @openside) and JotForm

UPDATE: It has since been brought to my attention that On2Air: Forms uses JotForm, so the two suggestions are actually part of a single solution.