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!
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.
Then use a filter where [Date of Inspection] [is on or before]  [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