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

    Help with Formula

    Sunny1

      Title

      Help with Formula

      Post

      Hello.

      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
          philmodjunk

          Define what you mean by next serial value.

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

          MS010001
          BHS020002

          or

          MS010001
          BHS020001

          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
            Sunny1

            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
              philmodjunk

              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
                Sunny1

                That is too easy.  You are fantastic. 

                • 5. Re: Help with Formula
                  Sunny1

                  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
                    philmodjunk

                    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
                      Sunny1

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