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
------------------------------
Original Message:
Sent: 11-24-2019 21:25
From: Syaeful Bahri
Subject: Automatic Sequence Number Field
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
Original Message:
Sent: 11-21-2019 22:33
From: Don Larson
Subject: Automatic Sequence Number Field
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
Original Message:
Sent: 11-21-2019 21:36
From: Syaeful Bahri
Subject: Automatic Sequence Number Field
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
------------------------------