Discussions

Expand all | Collapse all

Add sequence number in a related table which starts at 1 for each group of relationship

  • 1.  Add sequence number in a related table which starts at 1 for each group of relationship

    Posted 05-23-2018 15:05
    I have a state table- AL, AK, CA, MI, FL and so on. Each state has some products being offered. For instance:

    State-Product
    AL - P1
    AL - P2
    AK - P1
    AK - P2
    AK - P3
    CA - P4
    CA - P5

    I need to add a sequence field something like below:

    State-Product-Sequence
    AL - P1-1
    AL - P2-2
    AK - P1-1
    AK - P2-2
    AK - P3-3
    CA - P4-1
    CA - P5-2

    Please advise.


  • 2.  RE: Add sequence number in a related table which starts at 1 for each group of relationship

    Top
    Contributor
    Posted 05-23-2018 15:36
    Hi Manish,

    Here is a Knowledge Base article that goes over the steps for creating a sequentially increasing number based upon a relationship:

    https://community.quickbase.com/quickbase/topics/how-can-i-set-up-a-numbering-system-to-track-quotes...

    It goes over the steps by step. One caveat is that it makes use of the # of related records to track the sequence so if records are deleted it can throw the numbering off. We usually recommend disabling deletion of records on that table to avoid this happening and instead use a status of inactive to filter out records that are no longer valid on your reporting. This is also a method that works going forward but doesn't automatically work for pre existing records if you have a large volume of existing records. I hope this article is helpful Manish. 


  • 3.  RE: Add sequence number in a related table which starts at 1 for each group of relationship

    Posted 05-25-2018 18:32
    Thanks Evan, this was very helpful, and I was able to implement my requirement. However I've another follow up question-

    If I have to delete a record from within a group, is there a way the other sequence numbers can be re-arranged? For instance if I have following data in the table:

    AL - P1-1
    AL - P2-2
    AK - P1-1
    AK - P2-2
    AK - P3-3
    CA - P4-1
    CA - P5-2

    And say, "AK-P2-2" is deleted, I want to be able to implement the following:

    AL - P1-1
    AL - P2-2
    AK - P1-1
    AK - P2-2 --> DELETED
    AK - P3-2 --> The sequence number is changed from 3 to 2
    CA - P4-1
    CA - P5-2

    Thanks.