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

• 3
• Question
• Updated 2 years ago
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?
• 1,252 Points

Posted 2 years ago

• 3
• 72,448 Points
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.
• 6,070 Points
Yeah, I remember that Mark suggested me the same thing and it worked for me.

Thanks, Mark!.
• 1,252 Points
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 :(
• 72,448 Points
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.
• 1,252 Points
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?
• 72,448 Points
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.
• 1,252 Points
I ended up starting again and finally got it!
Thanks Mark
• 72,448 Points
Great, thx for letting me know.