5 Replies Latest reply on Jan 8, 2014 7:05 AM by philmodjunk

    Task ID Formula Query



      Task ID Formula Query



           I need to generate production Task Numbers which will combine a user Selected Department ID from a drop down list with an Auto Generated Serial Number. e.g the Solution would be "ENG-0001"  meaning Engineering DEPT; Job 0001.  

           The Solution allows us to separate and Analyse Department Jobs.  

           Any Ideas on the formula that allows me to do this and share across other Table Occurrences.

           It is not an accounting code but I need to do that also for a separate form, ideas welcome.




        • 1. Re: Task ID Formula Query

               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.

          • 2. Re: Task ID Formula Query


                 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.






            • 3. Re: Task ID Formula Query

                   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.

              • 4. Re: Task ID Formula Query

                     Hi Phil

                     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.




                • 5. Re: Task ID Formula Query

                       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.