7 Replies Latest reply on Nov 3, 2010 3:21 PM by Sunny1

    Help with Formula



      Help with Formula



      I need to have a field for an ID number do the following:

      The ID number needs to consist of the Category (MS, BHS, P, etc), the Grade Level (01, 02, etc) and then a Next Serial Value.The Category and Grade level are fields and are set up with value lists.

      How do I do this?  I tried writing a calculated value in Project ID field but not sure the proper way.

      Thank you in advance

        • 1. Re: Help with Formula

          Define what you mean by next serial value.

          If you created two new records in succession, do you want:




          The first is easy, the second can be challenging

          and neither should be used as the primary key linking your records to records in other tables.

          • 2. Re: Help with Formula

            Interesting observation.  Because if I think about it, the second one could get even more trickier if for example I had BHS02001, BHS02002, BHS04001, BHS05001.  Lots of variables there!

            I think I will go with Number 1's method.  And I will keep an auto generated Serial ID as a separate field in the background.  This will be a separate identifier.

            • 3. Re: Help with Formula

              With Option 1, your auto entered serial number can be part of the solution for this.

              Just define a calculation field that returns text as:

              CategoryField & GradeLevelField & SerialNumberField.

              If you need leading zeroes, you can use this variaton:

              CategoryField & GradeLevelField & Right ( "0000" & SerialNumberField ; 5 )

              • 4. Re: Help with Formula

                That is too easy.  You are fantastic. 

                • 5. Re: Help with Formula

                  I have another question.

                  If I have a table that I am using as a value list for a field in another table, (Schools table, School Name Field and School District Field)

                  I want to enter a school name for a student in School Field on Student Info Layout and I want it to put the school district that the school is in into the field School District on the Student Info Layout without having to enter the School District separately each time I choose a school.

                  So the School District is the Parent record in the Schools Table and the School Name is the child. 

                  How do I do this? 

                  • 6. Re: Help with Formula

                    If you have a relationship linking the two tables by school, simply place the School district Field from the schools table on your students layout.

                    You can also define a looked up value field option on a school district field in your student table to copy the district name from the schools table, but I don't think you'll need that option here.

                    • 7. Re: Help with Formula

                      I had tried that and I just realized I had one thing wrong but with your help, it works.  Looking good.