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
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.
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 :(
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?
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
Great, thx for letting me know.