Group and Color-Code Quickbase Records That Share the Same ID

A question in the Quickbase community asked how to color jobs that share the same ID (original thread here). Say "Job 1234" and "Job 2345" both have 523 in the ID field. The asker wanted those rows to stand out, and ideally to sit together so the same crew could be assigned to every job with ID 523.

One reply suggested a formula query to find the duplicates. That works, but I'd start somewhere simpler. A report grouping gets you most of the way with no new fields. If you also want only the repeated IDs to light up, there's a native way to do that with a small parent table, and as a bonus it gives you one place to assign the crew.

I built all of this in a sandbox app to check it. The formulas below are copied from that build, and the example data is made up.

What we're working with

A Jobs table with fields like these:

Your ID field might be called something else. All that matters is that jobs belonging together carry the same value in it.

Option 1: Group the report by the ID (no new fields)

This is the quick win, and for a lot of teams it's all they need.

1. Open the Jobs table and create a new Table report (or customize an existing one).
2. In Columns to display, include Job, your ID field, Status, and anything else the person assigning crews needs to see.
3. Scroll to Sorting & Grouping. Switch from the default to sorting/grouping records your own way.
4. Pick your ID field as the first entry and set it to group (not just sort). For a number field, choose to group by equal values, so every 523 lands under one header instead of being bucketed into ranges.
5. Optionally add a second sort level under it, like scheduled date or job name, so the jobs inside each group are in a useful order.
6. Save the report with a clear name, for example "Jobs by Crew ID".

Now every job with ID 523 sits together under a single header, and so does 611 and the rest. Nobody has to hunt for matching numbers.

Want a count per group? The simplest trick is a numeric formula field called "Job Count" with the formula 1. Add it to the report and turn on totals for that column in the report options. Each group's subtotal is then the number of jobs with that ID. (Option 2 below gives you a real count field instead, which is more useful if you want to filter or color on it.)

Add row colors on the report

Grouping keeps matches side by side. Row colors make the report easier to scan. Row colors are set per report, so they don't add a field to your table.

1. In the same report's settings, find the Color-code rows section.
2. Choose to color rows using a formula.
3. Enter a formula that returns a color. A hex code in quotes works, and an empty string means "no color".

Coloring by status looks like this:

Case([Status],
  "Scheduled", "#FFF4CC",
  "In Progress", "#D6EAF8",
  "Done", "#D5F5E3",
  "")

Case checks the field against each value in turn and returns the color paired with the first match. The last "" is the fallback for anything else, so those rows stay white. You can color by any field: a crew field, a priority, or a checkbox like "Needs crew assigned".

If you'd rather test the logic before putting it on a report, create a temporary formula text field with the same formula and look at the values it returns. That's how I checked the one above.

Option 2: Highlight only the IDs that appear more than once (natively)

Grouping shows everything. If you specifically want to flag the IDs that show up on two or more jobs, you need a count of how many jobs share each ID. You can get that without a formula query by giving the ID its own small table.

The idea: a Crew Groups table with one record per ID. Jobs become children of that table, connected by the ID they already have. A summary field on Crew Groups counts the jobs, and a lookup brings that count back to each job.

Step 1: Create the parent table

1. Add a new table called Crew Groups (call it whatever matches your process).
2. Add a field for the ID. Make it the same field type as the ID field in Jobs. If the Jobs field is numeric, this one must be numeric too.
3. Add any fields that belong to the group as a whole rather than to one job. A Crew field is the obvious one.

Step 2: Make the ID the key field

1. Go to the Crew Groups table's settings and open its fields list.
2. Use the option to set the key field and choose your new ID field.

Quickbase now uses that ID, not Record ID#, as the value child records point to. Key values have to be unique, which is exactly what we want: one Crew Group record for 523, one for 611, and so on.

Step 3: Relate Jobs to Crew Groups using the existing ID

1. In the Jobs table's settings, open Table-to-table relationships and create a new relationship.
2. Choose Crew Groups as the parent (one) and Jobs as the child (many).
3. When asked for the reference field in Jobs, pick your existing ID field instead of letting Quickbase create a new one.

Every job is now linked to its Crew Group automatically, because it already holds the matching ID. You don't have to re-enter anything on the job records.

Step 4: Count the jobs and bring the count back

1. In the relationship settings, add a summary field on Crew Groups that counts Jobs. Call it "# of Jobs".
2. Still in the relationship, add a lookup field on Jobs that pulls in "# of Jobs" from Crew Groups. While you're there, look up Crew too.

In my build the lookup came in labeled "Crew Group - # of Jobs", and I kept that name in the formulas below. Adjust it to match yours.

Step 5: Flag and color the repeats

Add a formula checkbox field on Jobs called "Shares ID?":

[Crew Group - # of Jobs] > 1

Then, on your grouped report, use this as the row color formula:

If([Crew Group - # of Jobs] > 1, "#FCE4D6", "")

In the sandbox, the results looked like this:

You can also filter a report to "Shares ID? is checked" to get a to-do list of only the grouped jobs.

The bonus: assign the crew once

This is the part that answers the original goal. Because the crew lives on the Crew Group record, you set it once for ID 523 and every job with 523 shows the same crew through the lookup. Change it on the group and all of those jobs update together. No copying a crew name across three job records and hoping nobody misses one.

The catch: the group record has to exist

Look at Job 2701 in the table above. There's no Crew Group record for 808, so its count and crew are blank. The job still saves fine, it just has nothing to look up yet. A few ways to handle that:

• Create the Crew Group record when a new ID comes up. Adding a Crew Groups report or an "Add Crew Group" link near the Jobs form makes this quick.
• If IDs come from a spreadsheet, import the list of IDs into Crew Groups first, then import the jobs.
• If jobs are created from another system, a Pipeline can check for the Crew Group and create it when it's missing.

Blank counts aren't greater than 1, so jobs without a group simply aren't highlighted. They won't throw errors.

About the formula query approach

For completeness, here's the formula query version as a numeric formula field on Jobs. Swap 9 for the field ID of your ID field:

Size(GetRecords("{9.EX.'" & ToText([Crew ID]) & "'}"))

It returned the same counts as the summary field in my test (3, 3, 3, 2, 2, 1). It also counts orphan IDs like 808 as 1, since it doesn't need a parent record.

The trade-off is speed. A formula query runs a search of the table for every row it's displayed on. On a few hundred jobs you won't notice. On tens of thousands, reports and forms that show the field can slow down noticeably, and that's felt by everyone using the app. Summary fields and report grouping are handled much more efficiently, which is why I'd reach for those first and keep the formula query for small tables or one-off checks.

Which one should you use?

• Just need matching jobs side by side? Group the report by the ID. Done in two minutes, no new fields.
• Want quick visual cues? Add a row color formula to that report based on status or crew.
• Need to flag only the repeated IDs, or assign one crew to the whole group? Add the Crew Groups parent table keyed on the ID, with a summary count and lookups.
• Small table and you want a count without a new table? The formula query works, just keep an eye on performance as the table grows.