Automatic Sequence Number Field

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
------------------------------