13 Replies Latest reply on Sep 5, 2014 2:13 PM by RelativeNewbie

    Auto name tasks/jobs AA - ZZ per project

    RelativeNewbie

      Title

      Auto name tasks/jobs AA - ZZ per project

      Post

           Overview:

           An existing customer initiates a new PROJECT with us for which there is a new project number (numeric is fine and this is being done with the serial number option).  However, every PROJECT has several JOBS which need their own reference and in the past we have named them AA - AZ, BA - BZ ...  This makes it easier for the guys in the shop doing the work to fill out their timesheets.   They know they are working on (customer, project number, job name) they have to charge out materials and time against each individual job as they are billed separately.

           Does anyone have any solutions? PLEASE.

           Thanks

           Karen

        • 1. Re: Auto name tasks/jobs AA - ZZ per project
          philmodjunk

               Do you have a table of Jobs linked to your table of projects by that project serial number?

          • 2. Re: Auto name tasks/jobs AA - ZZ per project
            RelativeNewbie

                 Hi PhilModJunk,

                 I do, as a 1 Project to many Jobs as follows:

                 Projects                      Jobs
                 __ProjectIDpk           __JobIDpk
                                                     _ProjectIDfk

                 I've linked the ProjectIDpk to the ProjectIDfk in the jobs table.

                 Thanks
                 Karen

            • 3. Re: Auto name tasks/jobs AA - ZZ per project
              philmcgeehan

                   If you have a serial number that starts from 1 for each project then you can use a calculated field like this:

                   Char ( ( PROJECTID - 1 ) / 26 + 65 ) & Char ( Mod ( PROJECTID - 1 ; 26 ) + 65 )

              • 4. Re: Auto name tasks/jobs AA - ZZ per project
                philmcgeehan

                     Starts getting weird if you go over ZZ or project 676, so you might need to look at modifying the calculation a little.

                • 5. Re: Auto name tasks/jobs AA - ZZ per project
                  RelativeNewbie

                       Hi Phil,

                       Probably not doing this right.  I've created a field in the Jobs Table called Job Name where I've been manually filling in the AA, AB etc Job name.  I've tried to put this calculation in that field, but it comes up with 0 every time I make a new entry.  Which field were you suggesting I make the calculated field?  I thought if the ProjectID and JobID were serial numbers they couldn't be recurring i.e. Project 1 has Jobs called AA, AB, AC and Project 2 also has Jobs called AA and AB.  Thanks for the heads up on the max number of entries, we seldom ever go over CZ.

                       Thanks
                       Karen

                  • 6. Re: Auto name tasks/jobs AA - ZZ per project
                    philmcgeehan

                         OK, so you'll have projects that have multiple jobs, and it's the jobs that you want the double alpha incrementing characters for, right?

                         You'll need to have a calculated field in the Job table that will count the number of jobs for a given project. So the first job for each project will be 1 and the next job will look at the max of this field and add 1.

                         Then you'll have an additional calculated field, with the calculation that I suggested which needs to refer to the incrementing number.

                    • 7. Re: Auto name tasks/jobs AA - ZZ per project
                      philmcgeehan

                           That sounded quite confusing when I read it back.

                           The calculation that I supplied earlier only converts a number to two text characters:

                           1 → AA,
                           5→AE,
                           27→BA,
                           and so on...

                           So you'll need an existing number field to reference in my calculation.

                      • 8. Re: Auto name tasks/jobs AA - ZZ per project
                        RelativeNewbie

                             Hi Phil,

                             Thank you so much for all you help.  It's taken me some time being an absolute newbie and all but I seem to have it working.  I had so much trouble with the calculation field to count the existing number of related records.  All the guidelines point to the calculation pointing to the Projects FK in the Jobs table but the only way I could make it work was to point to the Projects PK in the Projects table (The Projects table is definitely the Parent table).  I've attached a screen shot of the two tables and would greatly appreciate it if you could advise it this would be a problem down the line if I had other related items to the Projects table via other relationships.

                             Thanks
                             Karen

                        • 9. Re: Auto name tasks/jobs AA - ZZ per project
                          RelativeNewbie

                               It would only let me upload one file, I should have zipped them.

                          • 10. Re: Auto name tasks/jobs AA - ZZ per project
                            RelativeNewbie

                                 Hey Phil,

                                 I just hit a hiccup.  This is working fine in the Job Layout and the Job Table but when I create a new job in the Job Portal on the Project Layout, it doesn't work, it creates the next number but then continues to repeat the same number.

                                 I'm going nuts here, do you have any advise on where I'm going wrong?

                                 Thanks
                                 Karen

                            • 11. Re: Auto name tasks/jobs AA - ZZ per project
                              RelativeNewbie

                                   I've just noticed that it works for one record only if I change between layout mode and browse mode it somehow registers the next number and gives the correct JobName for the following row, and that row only.

                                   I thought it might also work if I changed between tabs on the same layout view but that doesn't apply, only if I change leave that particular layout and come back.  Why is it only registering the record count on layout change?

                                   Aargh

                              • 12. Re: Auto name tasks/jobs AA - ZZ per project
                                RelativeNewbie

                                Does anyone have any suggestions for me?

                                Phil's calculation to turn numbers to letters worked perfectly.  I added a calculation to get the last related record +1 in order to get the numbering right and that also works fine in the Job layout.  However when I go to the Job portal in the Projects layout it only works for one new record and then just repeats the same letter.  If I change to another layout and come back it works again for one new record.

                                Does anyone have a script or something that will instruct the portal to update the calculations after every new entry so that it comes up with the correct number of records?

                                Thanks
                                Karen

                                • 13. Re: Auto name tasks/jobs AA - ZZ per project
                                  RelativeNewbie

                                  I resolved the issue by creating a button at the end of the portal row which initiates the following script:

                                  Freeze Window
                                  Commit Records/Requests [No dialog]
                                  Go to Field [Select/perform; Jobs::Job Description]
                                  Go to Portal Row [Last]

                                  Thanks for all your help Phil