Forum Discussion

AswinBabu's avatar
AswinBabu
Qrew Member
4 years ago

Creating formula field based on another text field and a sequential number

In my table i would like to have field say designno. that takes value from another text field and append it together with an autogenrated  sequential number. How can i do this ? I tried it using text field and record id, [text field1]&[RecordId], but problem is that record id is not getting updated unless you edit and save without making any change. what's the best way to overcome it or please suggest me another solution
eg:
--------------------------
text-field1  | designno. 
--------------------------
abc             | abc0001
--------------------------
bcd             | bcd0002

------------------------------
Aswin Babu
------------------------------
Formulas and functions
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    4 years ago
    You explain the reason why it is not acceptable for the user to save the record and then get their next sequential number?  As you can imagine it is going to be pretty impossible to generate the next sequential number before the user has saved the record because there could be two users simultaneously entering data and it's hard to know who will save first to get the next number.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------
    • AswinBabu's avatar
      AswinBabu
      Qrew Member
      4 years ago
      By the current method that I use, i will input the textfield1 and and append it with the record id. But after i save and go to table view the design no. field will be abc0000. its same for every other records for eg: bcd000, cde000 etc. I have to edit the record again and in edit I'm able to see the designno. as abc001, bcd002 etc. Then after clicking edit when i save it once again without making any changes designno.'s are properly updated. 
      This method will be a 2 step process & This is not user friendly. 
      Please suggest another or changes to that I can do to this method.

      ------------------------------
      Aswin Babu
      ------------------------------
      • KirkTrachy1's avatar
        KirkTrachy1
        Quickbase Staff
        4 years ago
        Hi Aswin:

        You have come across something which is a little involved but can be done.

        Mark was right about the Record ID# not having been created yet so what to do?

        Well we can create a formula that concatenates the pieces we wish to put together and then a form rule to fire when the record is created that puts that formula into a local scala field.  Temporarily we are using 000000 as the Record ID#.  Next a Pipeline wakes up, recognizes that the key field has 000000 contained and it fires to go back and update the key field to its new value.

        We need a Pipeline that recognizes the record's creation and fires to write the new formula value into the key field.

        It is a lot but the following video goes through the process of setting it up.


        We cover topics like this during out daily M-F "Office Hours" webinars held at 1pm Eastern. These are open Q&A sessions where we model possible solutions to business challenges. https://quickbase.com/webinars/.

        ------------------------------
        Kirk Trachy , Senior Solutions Consultant
        QuickBase | 603-674-5454 M | ktrachy@quickbase.com
        ------------------------------