You appear to have a table of Tasks where this ID needs to be generated. Do you also have a table of Departments?
The Solution allows us to separate and Analyze Department Jobs.
There are a number of reasons why people choose to generate "value encoded ID's" of this sort. Some are good reasons. Some not. But you can separate and analyze by department without using such an ID. Please do not use this ID for the Primary Key to link this table to other tables in relationships.
At this time I am using a Value List for Departments and a Task "Table" with all the fields etc. I use a master "Serial Number" to link Tables.
You may find a table of departments useful, but going with what you have at this point:
Let's say that you have two fields: Department and Sequence. Department is your text field with a value list. Sequence is a number field that will store the number part of your ID. We'll add a third field defined as a text field with this auto-enter calculation:
Left(Department ; 3 ) & Right ( "000" & Sequence ; 4 )
to generate the actual ID's. A unique values validation rule should be specified for it to ensure that two users creating new records at the same time in this table don't accidentally get two records with the same value in this field.
Define a self join relationship that matches records by Department:
Tasks::Department = Tasks|SameDepartment::Department
Set up Sequence to auto-enter this calculation: or use Set Field in a "new record" script to assign to Sequence, the results of:
Max ( Tasks|SameDepartment::Sequence ) + 1
If using an auto-enter calculation, test it carefully and make sure to clear the "do not replace existing values" check box.
OK I had a long day of learning yesterday and reading this today got confused so my mind is probably trying to overcomplicate.
Anyway I have the Three Fields in Task list Table
TaskID (Text with Auto enter Calc) , Department (Uses Value list) and Sequence (to Generates #'s automatically)
however Not sure how and in which field to go about the Define a Self Join.... and Set up sequence.... or use Set Field parts.
I am clearly way back down the learning curve for your skill level so no worries if you pass on this.
Helping out the "newbies" is a main purpose to this forum.
Not sure how and in which field to go about the Define a Self Join.
Department, as I also specified earlier is the field to use in the self join. Go to Manage | Database | Fields. Select Tasks and use the duplicate button (Two green plus signs) to make a copy of this Tutorial: What are Table Occurrences?. You can double click the new occurrence to get a dialog where you can rename the occurrence. In my example, "Tasks|SameDepartment" was the name I gave to the new occurrence.
Then drag from department in one table occurrence to department in the other to link them in a relationship.
From there, the rest of the steps depends on whether you want to set it up as an auto-enter calculation or if you want to put a "new task" button on your layout to click in order to create the new task by performing a script.