Forum Discussion

MichaelGraham2's avatar
MichaelGraham2
Qrew Assistant Captain
9 years ago

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? 
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    9 years ago
    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.
    • GauravSharma3's avatar
      GauravSharma3
      Qrew Commander
      9 years ago
      Yeah, I remember that Mark suggested me the same thing and it worked for me.

      Thanks, Mark!.
  • MichaelGraham2's avatar
    MichaelGraham2
    Qrew Assistant Captain
    9 years ago
    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 :(
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    9 years ago
    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.
  • MichaelGraham2's avatar
    MichaelGraham2
    Qrew Assistant Captain
    9 years ago
    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?
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    9 years ago
    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.