In this article, we are publishing a Quickbase solution we developed while creating a custom app for a client. Our client is Guard Services USA, which provides security guard services to nationwide restaurant and retail chains such as Sears, J. Jill, Zales, and Chuck E. Cheese’s.
We integrated the client’s Quickbase app with Twilio’s voice API so that guards beginning or ending a shift call a toll-free number and use a unique PIN to clock in or clock out. This updates records in the app so that Guard Services knows in real time whether whether guards have arrived on site, on time, late, or even failed to show up at all. It also gives them accurate time records.
In what follows, we outline the steps we took in Quickbase to design one component of that process: generating, maintaining, and assigning unique PINs to assign to guards working a specific shift. This solution can be abstracted into a design pattern for maintaining and assigning/re-assigning other items from a fixed inventory of long-lived or intangible assets such as vehicles, musical instruments, phone numbers, berths, or lockers.
A system to maintain and assign an inventory of PINs to new shifts automatically.
- The PINs should be unique insofar as each PIN is associated with a unique guard shift at any one time – but PINs may be reused once the shift is complete.
- Because the system is based on a finite inventory of four-digit PINs, there should be a mechanism for recycling used PINs back into the inventory of PINs available for use in future shifts.
- A PIN should be assigned automatically by Quickbase whenever a new shift is created, but there should be a provision for users to assign a PIN to a shift manually from the available inventory.
- The PINs should be random, or give the appearance of randomness.
- There can be multiple PINs for each shift to accommodate multiple guards needing to clock in and clock out.
The solution involves creating three new tables in addition to the Shifts table, which was already in existence:
- The PINs table holds a complete pre-generated inventory of PINs
- The PIN-Shift Assignments table is a standard many-to-many linking table
- The Next Available PIN table is the linchpin of the solution as a pointer to the next PIN to be assigned
Step 1: Generate PIN Inventory and Create PINs Table
Since the Shifts table was already created, the first step was to generate a list of four-digit PINs. That’s easy enough in Excel: just create a sequential list of numbers 1–9999 and then put them in random order.
When Quickbase assigns a new PIN, it will always be assigning the next available PIN in Record ID# sequence. But, because we have randomized the number sequence in advance, it will have the appearance of a randomly generated number every time.
Now we just create a new table by importing only the PIN column of our spreadsheet (column A in the video). At this point, our PINs table consists only of the default fields and a PIN field, but we’ll be back to work more with the PINs table later.
Step 2: Create Shift-PIN Assignments Table
The Shift-PIN Assignments table links the Shifts and the PINs tables in a N:M (many-to-many) relationship. In many similar cases, a simple 1:N relationship between PINs and Shifts would work, but because our client needed to accommodate multiple guards per shift, each with his or her own PIN, as well as the possibility of spoiled PINs that needed a new one assigned, we created a linking table.
As with any N:M linking table, the two essential fields are the two columns that hold foreign keys to the parent tables—in this case, Shifts and PINs. Accordingly, we created a 1:N relationship between PINs and Shift-PIN Assignments and a corresponding 1:N relationship between Shifts and Shift-PIN Assignments.
In setting up these relationships, we looked up the PIN field from the PINs table, and a Status Formula field from the Shifts table. All Shifts have a status (pending, cancelled, in progress, etc.) that is determined automatically in almost all cases. There are times, however, when a scheduler or dispatcher at our client needs to change a status manually. To accommodate that fact, the Status Formula resolves differences between the computed status and the status set manually by a user. We need to look up this Status Formula field to use in the recycling function of this solution.
Next, we created a formula checkbox field we called PIN Available to indicate whether this record related to a completed shift, after which the PIN could be recycled into the available inventory, along these lines:
// Created 7/3/2017 by Phillip Dennis, Watkyn LLC // To indicate if a shift is complete and the PIN can be recycled If ([Shift - Status Formula] = "Complete", true, false)
Finally, we created a new summary field in the PINs table: # of Shift-PIN Assignments PIN Unavailable. This counts the number of Shift-PIN Assignments related to a given PIN record where PIN Available is false. Since each PIN can be assigned to many successive shifts—but only a single shift at any point in time—if there is any Shift-PIN Assignment associated with an incomplete shift, this summary field indicates that this PIN is unavailable to be assigned.
Step 3: Create the Next Available PIN Table
To give credit where credit is justly due, this part of the solution is an application of Craig Shnier’s “Focus” technique, which is powerful and yet deceptively simple.
We created a table called Next Available PIN, which has the sole purpose of pointing to the next record in the PINs table that is not assigned to any incomplete shifts. If a PIN has never been assigned to a shift, it is available. If a PIN has been assigned to one or many shifts that are now complete, it is available. If a PIN has been assigned to a shift that is still pending (i.e., Shift – Status Formula ≠ Complete) it is unavailable.
Then we created a new formula numeric field in the PINs table called Related Next Available PIN:
// Created 7/3/2017 by Phillip Dennis, Watkyn LLC // To serve as a static reference to the Next Available PIN table 1
Yes, the formula is simply the number 1: every record in the PINs table is a child of Record ID# 1 in the Next Available PIN table.
Next, we created a 1:N relationship between Next Available PIN and PINs using the Related Next Available PIN formula field as the reference field. We then created a summary minimum Record ID# of PINs where # of Shift-PIN Assignments PIN Unavailable = 0 in the Next Available PIN table. In other words, for any given Next Available PIN record, this field points to the first PIN record that is available to be assigned to a new shift. The simplicity and the power of Craig Shnier’s technique is that there is only one Next Available PIN record, and every single PIN record is related to it. This means that this solitary Next Available PIN record can serve as a pointer to the very next PIN that can be assigned out of the entire inventory.
After creating the relationship and this summary field, we added one Next Available PIN record, the only one that there will ever be. There are no fields for us to enter data into. Click add a new record and then save.
Step 4: Create a User Picker Report
The last step for purposes of this article was to create a table report in the PINs table that would be the basis of a PIN record picker. The report just lists PINs but filtered to display only records where # of Shift-PIN Assignments Unavailable = 0. This will allow users to assign PINs to new shifts manually and know they are choosing available PINs.
In this article, we have not covered how we implemented this solution so that new shifts are assigned a unique PIN automatically. Because of the scale of the client’s business, that couldn’t be handled natively in Quickbase since both webhooks and Quickbase actions have a built-in rate limit that is too restrictive. Instead, we chose to inject a script that would assign the PINs using the Quickbase API. We will publish details of that aspect of our solution in a future article.