Next Available Record based on an Empty Field

  • 1
  • 1
  • Question
  • Updated 6 months ago
  • Answered
Howdy folks,

So heres my delima, I currently have a very rudimentary button that allows users to edit the next record (based on Record ID+1) and provide a grade.

However, this workflow breaks when a user grades records out of order. Can i make a button which will display the "Next Available" record (i.e. a record with no grade) for my users?

Long story short: Can i make a button which will display another record with a specific field left empty?
Photo of Giuseppe macri

Giuseppe macri

  • 514 Points 500 badge 2x thumb

Posted 6 months ago

  • 1
  • 1
Photo of QuickBasePros_IDS

QuickBasePros_IDS, Champion

  • 2,266 Points 2k badge 2x thumb
You can identify a child-record by first summarizing the "Minimum" [Record ID#] where a criteria is met ([XX] is empty); then use that Minimum Record ID value in your custom-button formula for the user to edit the "next available" record that needs updating.
(Edited)
Photo of Giuseppe macri

Giuseppe macri

  • 514 Points 500 badge 2x thumb
So if its a single table application, would i just create a relationship with the table itself and go from there?

#For yall who came here late, the above solves the problem, you just need to use another table to house the minimum ID #. #Powerof1 !
(Edited)
Photo of QuickBasePros_IDS

QuickBasePros_IDS, Champion

  • 2,266 Points 2k badge 2x thumb
Since you only have a single table; I would advise the following steps:

1.  Build a 2nd table (there will only be 1 record in this table).
2.  Build a relationship between table 2 and table 1
3.  Build a summary field that pulls up the Minimum [Record ID#] from Table 1 where [Field is empty] is Null/is empty.  Call this [Min Empty Record ID#]
4.  Add [Min Empty Record ID#] as a LOOKUP field in the relationship
5. Change the [Related Reference Field] from a numeric-reference field to a formula-numeric field with the formula value of 1

You MUST create your summary field BEFORE you change the related-reference field value to a formula; otherwise Quick Base will not let you build the summary field.

You can now see that the [Min Empty Record ID#] as a Lookup Value can be used in a button.  I would recommend only making your [Update Next Available Record] button visible in VIEW mode; because your records that are being populated must be saved before the Min-Record ID# updates and can be used in a button.

Here's the App I built to test this.  https://laurahillier.quickbase.com/db/bnp4n3qqi (open to everyone)
Photo of Giuseppe macri

Giuseppe macri

  • 514 Points 500 badge 2x thumb
Can i code a button to save the record, wait for the db to update and then display Min ungraded Record for grading?