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

  • 0
  • 1
  • Question
  • Updated 6 months ago
  • Answered
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.
Photo of Manish Tewari

Manish Tewari

  • 162 Points 100 badge 2x thumb

Posted 6 months ago

  • 0
  • 1
Photo of Evan Martinez

Evan Martinez, Community Manager

  • 8,774 Points 5k badge 2x thumb
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. 
Photo of Manish Tewari

Manish Tewari

  • 162 Points 100 badge 2x thumb
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.
(Edited)