Skip to main content
Question

Formula for unique 6 digit number without referencing Record ID #?

  • May 24, 2018
  • 0 replies
  • 86 views
I have a table i want to use to create quotes with. One of the fields is quote number. I would like to create a formula that creates unique 6 digit number with the last 2 digits of the year as the first 2 digits, followed by 4 digits. For example the first entry of 2018 would be 18000, followed by 180001 and so on. I can not use reference # as the identifier because i would like the last for digits to rollover at the start of the new year. For example the 140th entry of 2018 would be 180140 and the 140th entry of 2019 would be 190140. Any thoughts?
This topic has been closed for replies.

First of all you can use this new field as reference # (key) field , the only requirement is that is should be unique, an I understand it is.
We are going to build a Field call [Quote Number]

You have a table called "QUOTES" 

1) You need to have a parent table I suggest to call it s call it "YEAR" probably you already have it , otherwise you need to create it to get the last number and follow up correlative in the next record.

2) you need to set up in Parent Table of this relationship a Summary Field that calls to the max value of the field we are creating now. [Max Quote Number]

This Summery field is in the parent table in this example table called YEAR 


in this case this field is called [Quote Number], and the new field will call [Max Quote Number]

Then you will create another field in the Table QUOTES it is a lookup field and you need to make it fix so we will make another field text snapshot of this one because the [Max Quote Number]  will be changing  and we want a static number ( this is the key of the successful of this sett up).

https://help.quickbase.com/user-assistance/setting_up_snapshot_fields.html

Now we have a [Max Quote Number Snapshot] in our Table QUOTE, that gives us the previous number.

This is the Field we are building now, and this will be the formula you need to use now in [Quote Number] ; 



List("",
Right(ToText(Year(ToDate( [Date Created]))),2),
Right(List("",(("0000")),(ToText(( ToNumber ([Max Quote Number Snapshot])+1))))
,4))


It works perfectly , I tried it 
.
Ones you have this working properly you can change the key field 

https://help.quickbase.com/api-guide/setkeyfield.html

If you already have relationships working in your app , probably to change now the Key field is not a good idea. In this case make a copy first and see how it works.
Terms & ConditionsAccessibility statement