Is this type of report possible?

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

I have a QuickBase where each record is an inspection report about a clinic site's inspection. When a new record is created, the clinic site is chosen as one of the fields. I've been asked to build a report that shows clinic sites that haven't been inspected in the past 30 days. I've been scratching my head about this today and can't think of a way to report on something like that. Anyone have any suggestions or tricks? I was considering some kind of calculated column. Thanks!

Photo of Matthew

Matthew

  • 0 Points

Posted 3 years ago

  • 0
  • 1
Photo of QuickBasePros (Laura Thacker)

QuickBasePros (Laura Thacker), Champion

  • 5,590 Points 5k badge 2x thumb
Are your Clinics a parent-record or are they a multiple choice field?


If your Clinics are a parent-record, then you can summarize the maximum Inspection date to the parent-Clinics table from the associated Inspections and determine which Clinics then have the last inspection earlier than 30 days in the past.

If, however, your Inspections table simply uses a text-multiple choice field to assign an Inspection to a clinic, then you would have a harder time generating such a report; because QuickBase can only tell you what it has, not what it doesn't have (unless you have something to summarize to).

If this is the case, I strongly recommend creating a table of "Clinics" against which inspections can be associated and you then have the ability to summarize data about the inspections up to the clinics (such as how many, how many in a given time period etc.).  You would need to build this table, and connect all the existing Inspections to it; replacing the old multiple choice field with a new relationship field (either Related Reference field if you make the Clinics field value the Key Field; or a Proxy field).  Then make sure users cannot edit the old multiple choice field.  You would also have to update any formulas, reports, lookup fields etc. which were pointing to the old multiple choice field and update them to look at the new Clinic value from the relationship.
Photo of Matthew

Matthew

  • 0 Points
Thank you, Laura. It is a multiple choice field that I am using rather than a parent table. I'm not sure if I can change it up at this point to use a parent table. Regardless, I'm trying to test something and can't seem to be able to select a Date type field to summarize the maximum value. It seems like it only allows choices of Numeric type fields. I tried the calculated column approach with a Date type and a formula of ToDate([Date Created]), but it errored out. Appreciate the advice!
Photo of QuickBasePros (Laura Thacker)

QuickBasePros (Laura Thacker), Champion

  • 5,590 Points 5k badge 2x thumb
Try a summary report where your Row Grouping is your Clinic Name and use a Cross Tab Column of Date of Inspection combined by Month.

Then use a filter where [Date of Inspection] [is on or before] [30] [day(s) in the past].  You would then need to manipulate this in Excel to get a more concise list of which month the last inspection was for each Clinic.

Alternatively; you could still build a parent table for your use without affecting other users.

1.  Build your new Clinics table

2.  Make the [Clinic] field the Key field of the table

3.  Build your relationship between Clinics < Inspections

4.  Build your Count of Records field

5.  Build your Summarize Last Inspection Date field

6.  Change the Type on your [Related Clinic] reference field from Text to Formula-Text.  Use the [Clinic Name] field value as the formula.

7.  Run a report in your new Clinics table that shows only Clinics where the [Last Inspection Date] is on or before 30 days in the past


Voila.