Discussions

Expand all | Collapse all

Next Available Record based on an Empty Field

  • 1.  Next Available Record based on an Empty Field

    Posted 05-18-2018 17:51
    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?


  • 2.  RE: Next Available Record based on an Empty Field

    Posted 05-18-2018 17:56
    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.


  • 3.  RE: Next Available Record based on an Empty Field

    Posted 05-18-2018 18:01
    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 !


  • 4.  RE: Next Available Record based on an Empty Field

    Posted 05-18-2018 19:57
    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)


  • 5.  RE: Next Available Record based on an Empty Field

    Posted 05-23-2018 12:14
    Can i code a button to save the record, wait for the db to update and then display Min ungraded Record for grading?