Discussions

 View Only
  • 1.  Automatic Sequence Number Field

    Posted 11-21-2019 21:37
    Hi All,

    I have a field called a number with this data: PRF/Finance/11/2019/1

    which is manually type on the field. The idea is created automatic sequence number filed with this format
    PRF/UNIT NAME/MONTH/YEAR/NUMBER

    How it's possible with Quickbase to create this?

    Many thank

    ------------------------------
    Syaeful Bahri
    ------------------------------


  • 2.  RE: Automatic Sequence Number Field

    Posted 11-21-2019 22:33
    Syaeful,

    This would be text field and not a number.

    What is the source of all these components?   What does the ERD look like for these tables and the table where this new field resides?

    How is the final component NUMBER indexing forward?  Is it starting at 1 and proceeding for all records or is it tied to the YEAR component? 

    The formulaic logic of your field has to be explained before you can build the syntax to create it.




    ------------------------------
    Don Larson
    Paasporter
    Westlake OH
    ------------------------------



  • 3.  RE: Automatic Sequence Number Field

    Posted 11-24-2019 21:26
    Hi Don,

    PRF/UNIT NAME/MONTH/YEAR/NUMBER

    PRF is just a text to identify is a Purchase Request Form

    Unit name: I have a unit name table which is multiple-choice contain all unit on my organization. User selects their unit name to make the request.

    Month is just current month

    Year is just current year

    Number will start from 1 for each unit. A sequence number

    The complete table structure such as below

    Requested By: Multiple choose
    Unit Name: Related unit requested by (Example Finance)
    Request Date: Date (today as default) (Example today 25-11-2019)
    Purchase Number: PRF/Finance/11/2019/01 (Proposed automatic numbering)

    Thanks Don, really appreciated

    ------------------------------
    Syaeful Bahri
    ------------------------------



  • 4.  RE: Automatic Sequence Number Field
    Best Answer

    Posted 11-24-2019 22:37
    Syaeful,

    Assuming that [Request Date] is a field in the record you are building [Purchase Number] , the first parts are easy.  You just need a Formula Text Field.

    The field Purchase Number is Formula Text

    "PRF" & "/" & [Unit Name]& "/" & ToText(Month([Request Date])) & "/"& ToText(Year([Request Date])) &"/" & [Auto Number]

    The last part for [Auto Number]  needs more thought.
    Do they have to be sequential?   By that I mean must they go  1,2,3, ...

    You obviously need them to be unique, but can you skip a number if a particular Purchase Request Form is not approved for a Purchase Number?

    If that is acceptable, then you can use the Record ID# from the Purchase Request as the last part of the sequence, [Record ID#].

    If that is not acceptable then you will need to build another method to generate the final sequence number.   The best way would be a script that looks at all of your Purchase Requests, sees what is approved, determines the last number and then indexs that by one and writes it to a field in the Purchase Request Form.

    However if you are going to the effort to write script, You might as well have it do all the work and create the Purchase Number.

    Another option is to create a table just for generating the sequence number.  You can use a Trigger to create a record in that table when the Purchase Request is approved and then relate them.   Use the [Related Sequence Number] as the final part of the Formula Field



    ------------------------------
    Don Larson
    Paasporter
    Westlake OH
    ------------------------------



  • 5.  RE: Automatic Sequence Number Field

    Posted 11-25-2019 02:43
    This is interesting. 

    I think I will make Record ID# as replacement for the sequential for the temporary solution. It's amazing knowing that any possible scenario will occur like Purchase Request Form is not approved, etc.

    Thanks for your solution and explanation Don


    ------------------------------
    Syaeful Bahri
    ------------------------------



  • 6.  RE: Automatic Sequence Number Field

    Posted 11-22-2019 08:52
    Hi Syaeful,

    You can make this field a formula field that is made out of a concatenation of other fields. For example:
    1 - Make the field called number a formula field
    2 - Create a multi select field called UNIT NAME with the values  [Finance, IT, etc..]
    3 - If the date in the field is today use a formula formated date field and convert to text
    so your number formul field will be:
    PRF & [UNIT NAME] & ToText(Today())
    you can get fancy and fromat the today) in many ways


    ------------------------------
    Souheil Karam
    ------------------------------