Need The Maximum [Record ID#] of a Table

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered
  • (Edited)
I have a formula that creates a copy of the existing record that a user is currently working on and it works great. I then refresh the screen to present the screen of the newly created record.

var text RefreshRecord=URLRoot() & "db/" & Dbid() & "?a=dr&rid=" & [Clone Record ID#] 
$RefreshRecord

All is good when the user is "cloning" the latest record in the table because I have a formula field in the table that adds 1 to the current [Record #ID]

Essentially  ... [Clone Record ID#] = [Record ID#] + 1

However, if the users decides to clone an earlier record in the table, it creates the new record just fine, but the [Clone Record ID#] is wrong because it's not referencing the "new" record, but rather the record right "after" the record the user chose to clone.

So I want to change the field formula to something like [Clone Record ID#] = Max([Record ID#]) but I know it doesn't work that way in QuickBase. 

Please advise
Photo of Eric

Eric

  • 160 Points 100 badge 2x thumb

Posted 2 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,546 Points 50k badge 2x thumb
No problem.

Make a new table called Max Record ID of Widgets (or whatever is it you are tracking).  Add 1 Record to that table, even with no fields.  It will be Record ID #1.

On hour widgets table make a formula numeric field called [Link to Max Records]
 with a formula of 1.  It will not surprisingly calculate to be 1.

Make a relationships where 1 Max Record ID has Many Widgets and use that formula field [Link to Max Records] as the reference field on the right hand side of the relationship.

Use that relationship to make a Make a Summary of the Maximum Record ID# and look that up own to the Widgets.  Now every Widget record knows the Max Record ID.  Note that you may need to add 1 to it depending on when the user pushes the button compared to when the record the user was on when the copy was made refreshes. 
Photo of Eric

Eric

  • 160 Points 100 badge 2x thumb
Beautiful ... It works like charm. Thank you!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,546 Points 50k badge 2x thumb
Great, thx for letting me know.