Discussions

Expand all | Collapse all

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.

    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.

    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.

    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.

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