Forum Discussion

EricEric3's avatar
EricEric3
Qrew Trainee
8 years ago

Need The Maximum [Record ID#] of a Table

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
  • 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. 
  • I know this post was made a long time ago and maybe there is an easier way now, but this post really helped me.  The only thing I'll add is remember to create a lookup field after you create the summary field in order to see the max rec id in the records table itself.  Again, this was really helpful and thanks!