How can I make a formula field that will automatically produce running number starting from 30000 and so on as I add new data?

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

I'm creating a table for Purchase Order and I start creating formula-numeric field for (PO running number) and to automatically generate starting at 30000 and so on once I add new data. Kindly assist me on this because I don't know where to start in this formula.

Photo of Bernard

Bernard

  • 116 Points 100 badge 2x thumb

Posted 3 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,760 Points 50k badge 2x thumb
There are two choices.

Low tech is to simply make a formula-numeric field called [Purchase Order #] which is the Record ID# plus about 30,000.



e.g.

[Record ID#] + 29990

No doubt you have been testing and have already used up some numbers.


But the way I actually recommend is to take the extra few minutes to determine how many numbers to add, and import say 30000 dummy rows of data using excel,. and then do a list all and the more button to delete them.

That way the [Record ID#] is the PO #.
Photo of Bernard

Bernard

  • 116 Points 100 badge 2x thumb
Hi Mark,

Thanks for that.
Actually I need to use formula-numeric because I have to use IF function. I have 2 different companies that will use different sets of PO# number.
If [Company]=A, Running Number starts with 30000 and so on, Else [Company]=B, Running number starts with 1000 and so on

Do you think it's possible Mark?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,760 Points 50k badge 2x thumb
There are ways to do this, but it's a bit complicated  to set up - more complicated that I can fully explain here.  But I have set up a similar counting based on Purchase Orders entered by year where the client wanted a sequence like 2016-0001, 2016-0002.  So that is very similar.

In general, the technique involved using Summary Maximums and snapshot fields.  Contact me off line via the information in my profile if would like some consulting assistance to put that in place.

Or rethink how badly you really need to have 2 sequences running.  Maybe, in the spirit of KISS you can live with a PO sequence like

A30000, B30001, B30002, A30003.