Discussions

Expand all | Collapse all

Sequential numbering in a field using another record as part of the formula

  • 1.  Sequential numbering in a field using another record as part of the formula

    Posted 05-07-2016 03:17
    Newbie -  so forgive me if this is an easy question to answer. I have a table that has a Project number field for identifying each of our projects (Example 1601, 1602 etc) that get assigned as we acquire a project. I would like to create another field that uses the project number as part of its value and builds sequentially each time I enter a record for that project. For example Contract# 1601-001, 1601-0002.  The next time I go to enter a new contract I select the project and then the contract # field knows that 1601-003 should be the next number.  How do you write that formula?


  • 2.  RE: Sequential numbering in a field using another record as part of the formula

    Posted 05-07-2016 12:48
    I don't have time right now to post a full answer, but here is the answer to a similar question.

    Basically the idea is to create a [Sequence #] field which is based on a formula which will start off the numbering by Department at whatever number you want to start at.

    var number StartNumber =
    Case([Department Number],
    "10",1234,
    "20",2000);
     
    Max($StartNumber,[Sequence # snapshot])

    Then we make a field called [Sequence # plus 1] which is the formula of [Sequence #] +1, and make a summary of the Maximum that field.

     and then do a lookup back down to the Antiques Table and then take a snapshot of that field called [Sequence # snapshot]

    By doing a snapshot we ensure that the numbers don't all get jiggled down when a record is deleted.

    The actual Serial # we display is this formula.

    [Department Number] & "-" & Right("00000" & ToText([Sequence #]),6)




    The concept is to have the contracts be children of the projects.  I'm sure you already have that setup.  Then you do a summary maximum and look that up down to the child ggain but the magic trick is to snapshot the field.  Look up the help on snapshot fields.

    Post back if you get stuck and need more help.  

    Btw, that is not a newbie question.  It's a question with a tricky answer.


  • 3.  RE: Sequential numbering in a field using another record as part of the formula

    Posted 04-02-2018 14:15
    Good morning,

    I had this happen to me " the numbers don't all get jiggled down when a record is deleted".  Now I need to fix it.  But I don't understand the method explained here.  (I am fairly new.)

    My RFI # went from 1003 which is [Record ID#] +1000

    In the course of testing things out, my records jumped now to #1038  

    Please help.


  • 4.  RE: Sequential numbering in a field using another record as part of the formula

    Posted 04-02-2018 14:39
    Blanca
    Can you post a new question explaining what you are trying to do and what you have set up so far?  You can reference this post here in your own post, but it is best to explain your question from the start.


  • 5.  RE: Sequential numbering in a field using another record as part of the formula

    Posted 03-05-2019 18:26
    I would like some help with this mister Quickbase Coach. How do I personally contact you.


  • 6.  RE: Sequential numbering in a field using another record as part of the formula

    Posted 03-05-2019 18:37
    Feel free to contact me at QuickBaseCoach.com for one on one assistance.


  • 7.  RE: Sequential numbering in a field using another record as part of the formula

    Posted 04-16-2018 21:55
    Hi, similar question here - I'm trying to create a field which will tell me how many records exist at the time that a new record is created (and then not change - so, a snapshot?). The purpose of this will be to help me automatically number new records. I'm just not sure how to ask the system to summarize that for me.