Find a field in last record of a table and show it on any record.

  • 3
  • 2
  • Question
  • Updated 2 years ago
  • Answered
We have a system where the key field is not the record ID and also the data we have inherited is not sequencial. At the moment to create a new record they are guessing a number and if it is not used they can add it.

I want to start some sort of regular sequence from the last key field number that we have.

I know that the latest key field number is 500200 and the latest record id is 145789. I wanted to create a formula field that at least would show what the next number for the key field should be.

e.g Last RecordID + 354111 would give 500201 but I cant seem to get the Last RecordID.

I have tried a reverse relationship on the table and creating a Count of RecordID but I end up with a blank field.

What am I missing? 
Photo of Michael Graham | Insight Global UK

Posted 2 years ago

  • 3
  • 2
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,038 Points 50k badge 2x thumb
If you make a table called Max Record ID number and enter a single record in it, it will be [Record ID#] of 1.

Then on your details table make a formula numeric field which calculates to 1.  The formula will just be 1.

Then make a relationships to that single record table and then make a summary field of the maximum of the [Record ID#]  in your details table.

Look that up back down to the details table

Now the details table always knows the highest record ID# which has been used so far.  or if you prefer, you can get the maximum of that other random number data entry number you have been using.
Photo of Gaurav Sharma

Gaurav Sharma

  • 5,758 Points 5k badge 2x thumb
Yeah, I remember that Mark suggested me the same thing and it worked for me.

Thanks, Mark!.
Im doing something wrong because I get a maximum record ID of blank

My KeyField Table has Many Max Record
Summary in KeyField of Maximum RecordID

It just shows empty unless I relate the one record to record id  1 then I get a 1.

Confused, Sorry :(
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,038 Points 50k badge 2x thumb
Whats is the formula for the field [Max Record ID number]?  Did you make that to be a formula field with a formula of 1.
I created a formula numeric [Max RecordID Number] with the formula 2-1 
Thats in the Components table with the Key Field.

But how do I relate that field?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,038 Points 50k badge 2x thumb
You make a relationship where 1 Max record ID# has many Components.  

But rather than accept QuickBase's suggestion to make a new field for you, when you make that relationships select that field that calculates to 1 as the reference field on the right side of the relationship.
I ended up starting again and finally got it!
Thanks Mark
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

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