Skip to main content
Question

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

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? 
      This topic has been closed for replies.

      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.
      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.
      Terms & ConditionsAccessibility statement