Carry Open Deficiencies Into Every New Inspection in Quickbase

A question in the Quickbase community (original thread here) came from someone running crane inspections. Each inspection form has a crane picked from a multiple choice field, 23 checklist questions, and up to four deficiency fields at the bottom. New deficiencies go to an "Open Crane Deficiencies" table through a pipeline, and when maintenance fixes one, the record gets deleted. What they wanted: when a new inspection starts, the crane's existing open deficiencies should already be on it, so the inspector can confirm what's still there and add anything new.

Trying to fill four fixed fields automatically gets messy fast. What if a crane has five open items? Which slot does each one go in? A small change to the table structure makes the whole thing simpler and drops the limit of four. That's what this guide walks through.

I built the tables below in a sandbox app and ran the pipeline's search and create steps against sample data to make sure the relationships and filters behave the way I describe. The cranes, deficiencies and inspectors are made up.

The idea in one paragraph

Give cranes their own table. Make Inspections and Deficiencies both children of Crane. Stop deleting deficiencies and give them a Status of Open or Closed. Then add a small Inspection Findings table that sits between Inspections and Deficiencies: one finding per open deficiency per inspection, with a Still Present checkbox. A pipeline creates those findings when an inspection is created, and the inspector checks them off in a grid right on the inspection form.

Step 1: Create a Cranes table

Right now the crane is a multiple choice field on the inspection. That works for picking one, but nothing else in the app can relate to it. A Cranes table gives every crane a record that inspections and deficiencies can both point at.

1. Create a table called Cranes with fields like Crane Name and Location. Add whatever else you track (serial number, capacity, last load test).
2. Add one record per crane. If you have a lot, copy the choices out of the existing multiple choice field and import them.

Step 2: Make Inspections a child of Cranes

1. Open the Crane Inspections table settings and go to Table-to-table relationships.
2. Create a new relationship with Cranes as the parent. Quickbase adds a reference field. I named mine Related Crane.
3. When it asks about lookup fields, bring in Crane Name so the name shows on the inspection.
4. On the inspection form, replace the old multiple choice field with Related Crane. Inspectors pick the crane from a drop-down just like before.

Once your existing inspections have Related Crane filled in (you can do this with a quick import that matches crane names to Record ID#s), you can retire the old multiple choice field.

Step 3: Set up Deficiencies with a Status

Your existing "Open Crane Deficiencies" table can become this table. Two changes:

Relate it to Cranes, not the other way around. Today, Inspections are a child of Open Crane Deficiencies, which is backwards for what you want. An inspection can have many deficiencies, and a deficiency can show up on many inspections, so neither should be the parent of the other. Instead:

1. In the deficiencies table, create a relationship with Cranes as the parent. Reference field: Related Crane. Look up Crane Name.
2. Create a second relationship with Crane Inspections as the parent. I called this reference field Found On Inspection. It records which inspection first spotted the problem. It's optional, but it makes the history much easier to read.

If you're reusing the old table, remove the old relationship (where Inspections were the child) after you've moved the data over. Don't remove it first.

Close deficiencies, don't delete them. Add these fields:

• Status: a multiple choice text field with the choices Open and Closed. Set the default to Open.
• Date Closed: a date field.

Change the maintenance close-out from "delete the record" to "set Status to Closed and fill in Date Closed." If maintenance has a button or pipeline that deletes the record now, point it at an update instead. You keep the full history of every deficiency on every crane, and the pipeline in Step 6 simply ignores anything that's Closed.

Step 4: Create the Inspection Findings table

This is the piece that replaces the four fixed fields. A finding means "on this inspection, here's what we saw about this deficiency."

1. Create a table called Inspection Findings.
2. Add a relationship with Crane Inspections as the parent. Reference field: Related Inspection. Look up Inspection Date if you like.
3. Add a second relationship with Crane Deficiencies as the parent. Reference field: Related Deficiency. Look up Description and Status.
4. Add a checkbox field called Still Present.
5. Add a multi-line text field called Inspector Notes. This one's optional, but inspectors tend to want somewhere to write "worse than last month."

Because a finding is a child of both tables, each inspection can have as many findings as the crane has open deficiencies. Two, four, or nine all work the same way.

One thing I noticed when testing: the Status lookup on a finding always shows the deficiency's current status. On an old inspection, a finding for something maintenance has since fixed will show Closed. That's usually what you want. If you'd rather keep what the status was on the day of the inspection, add a plain text field to Findings and have the pipeline copy the status into it.

Step 5: Build the Findings report and put it on the inspection form

1. On the Inspection Findings table, create a table report called something like "Findings for inspection." Include Still Present, Deficiency - Description, Deficiency - Status and Inspector Notes.
2. Turn on grid edit for the report. In the report settings, look for the option that lets people edit records right in the report (the wording varies a little by Quickbase version).
3. Open the inspection form in the form builder. Add a report link for the Crane Inspections to Inspection Findings relationship (in the newer form builder it's an embedded report element) and pick the report you just made.
4. Put it near the bottom of the form, under the checklist questions, where the deficiency fields used to be.

While you're in the form builder, also embed a report of Crane Deficiencies through the Found On Inspection relationship, labeled something like "New deficiencies found on this inspection." That's where new problems get added.

Step 6: Build the pipeline

This is what fills in the findings. It runs once, when an inspection is created. Build it in Pipelines using the Quickbase channel.

Trigger: Record Created

• Table: Crane Inspections.
• Under the fields for use in later steps, include Related Crane.
• Add a condition so the trigger only runs when Related Crane has a value. If someone saves an inspection without a crane, the pipeline won't go looking with a blank crane.

If your Pipelines account shows On New Event instead, use that and select only the event for a new record.

Step B: Search Records

• Table: Crane Deficiencies.
• Conditions (all must match):
  • Related Crane equals {{a.related_crane}} (drag in Related Crane from the trigger)
  • Status equals Open
• In the fields for later steps, include Record ID#. Add Status too if you're saving a snapshot of it.

In the sandbox, this search for the first crane returned its two open deficiencies and skipped the one that maintenance had already closed, and it also skipped an open deficiency on a different crane. If you'd rather write it as a query string, it's {9.EX.'1'}AND{7.EX.'Open'}, where 9 is the Related Crane field ID and 7 is the Status field ID in my sandbox. Use your own field IDs, and put the crane value from the trigger where the 1 is.

Step C: For each record found, Create Record

Inside the Search Records step's For each loop, add Create Record:

• Table: Inspection Findings.
• Related Inspection = {{a.id}} (the Record ID# of the inspection that started the pipeline)
• Related Deficiency = {{b.id}} (the Record ID# of the deficiency for this loop)
• Leave Still Present blank.

If the crane has no open deficiencies, the search comes back empty, the loop doesn't run, and nothing is created. That's the right result.

Save the pipeline and turn it on.

How the inspector uses it

1. The inspector creates a new inspection, picks the crane and saves. Open the record right away after saving, or use Save & keep working.
2. The pipeline runs a few seconds later and creates one finding for each open deficiency on that crane. If the findings grid is still empty, refresh the page.
3. In the grid, the inspector checks Still Present on anything that's still a problem and adds notes. Grid edit saves without opening each finding.
4. Anything new goes in the "New deficiencies" section on the form.

I left Still Present unchecked by default on purpose. The inspector has to look and actively check each item, which is a better record than a box that was already checked.

Step 7: Make adding new deficiencies one click

The Add button on an embedded Deficiencies report fills in Found On Inspection, but it doesn't know the crane. So I added a Formula - URL field on Crane Inspections called Add New Deficiency that fills in both. Put it on the form as a button:

URLRoot() & "db/" & [_DBID_CRANE_DEFICIENCIES]
  & "?a=API_GenAddRecordForm"
  & "&_fid_9=" & [Related Crane]
  & "&_fid_11=" & [Record ID#]
  & "&z=" & Rurl()

• _fid_9 and _fid_11 are the field IDs of Related Crane and Found On Inspection on the deficiencies table in my sandbox. Look yours up in the deficiencies table's field list and swap them in.
• [_DBID_CRANE_DEFICIENCIES] is the table alias. Find the exact alias under the table's Advanced settings. In my sandbox the table was named "Blog Demo - Crane Deficiencies", so its alias came out as _DBID_BLOG_DEMO___CRANE_DEFICIENCIES.
• &z=" & Rurl() sends the inspector back to the inspection after they save the deficiency.

For the second sandbox inspection, the field produced this link, with crane 1 and inspection 2 filled in:

https://<your realm>.quickbase.com/db/<deficiencies table>?a=API_GenAddRecordForm&_fid_9=1&_fid_11=2&z=...

Since Status defaults to Open, a new deficiency is open from the start. That means it shows up on the next inspection of that crane automatically. It won't be added to the current inspection's findings, because the pipeline already ran when the inspection was created. It's already listed on this inspection under "found on" anyway.

What a few inspections look like

Here's how the sample data played out in the sandbox, for one crane:

Nobody picked anything from a list after the first inspection. The open items just followed the crane.

Useful extras

• Open deficiency count on the crane. On the Cranes to Crane Deficiencies relationship, add a summary field that counts deficiencies where Status is Open. Put it on crane reports so maintenance can see which cranes need work.
• A check that the pipeline ran. On Crane Inspections, add a summary field that counts Findings. If a crane has open deficiencies and an inspection shows zero findings, look at the pipeline's activity log.
• Flag items the inspector didn't check. A finding with Still Present unchecked means the inspector didn't see the problem anymore. You could make a report of those for maintenance to verify and close, or add a second pipeline that runs when a finding is updated. I'd keep closing a deficiency as a maintenance decision rather than closing it automatically.
• Old inspections stay accurate. Findings belong to a specific inspection, so changes on a later inspection don't touch earlier ones.

Summary

• Make Cranes a table, with Crane Inspections and Crane Deficiencies as its children.
• Give deficiencies a Status of Open or Closed and stop deleting them.
• Add Inspection Findings as a child of both Inspections and Deficiencies, with a Still Present checkbox.
• A pipeline (Record Created on Inspections, then Search Records for that crane's Open deficiencies, then Create Record in a For each loop) creates one finding per open deficiency.
• Embed the Findings report with grid edit on the inspection form, and add new deficiencies through the form. They'll show up on the next inspection, with no limit of four.