# Discussions

View Only

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

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

Posted 03-09-2017 13:08
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?

• #### 2.  RE: Find a field in last record of a table and show it on any record.

Top
Contributor
Posted 03-09-2017 13:20
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.

• #### 3.  RE: Find a field in last record of a table and show it on any record.

Silver
Contributor
Posted 03-09-2017 14:37
Yeah, I remember that Mark suggested me the same thing and it worked for me.

Thanks, Mark!.

• #### 4.  RE: Find a field in last record of a table and show it on any record.

Posted 03-09-2017 16:47
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 :(

• #### 5.  RE: Find a field in last record of a table and show it on any record.

Top
Contributor
Posted 03-09-2017 17:57
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.

• #### 6.  RE: Find a field in last record of a table and show it on any record.

Posted 03-09-2017 18:04
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?

• #### 7.  RE: Find a field in last record of a table and show it on any record.

Top
Contributor
Posted 03-09-2017 18:34
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.

• #### 8.  RE: Find a field in last record of a table and show it on any record.

Posted 03-10-2017 09:42
I ended up starting again and finally got it!
Thanks Mark

• #### 9.  RE: Find a field in last record of a table and show it on any record.

Top
Contributor
Posted 03-10-2017 13:16
Great, thx for letting me know.