8 Replies Latest reply on Sep 19, 2012 6:26 AM by comment

    Auto Entry into Fields based on other fields

    zubir007

      Hi,

       

       

      I have a databse which has a field called site and I made the site field into a pop up menu with a custom value list. THe value list is as follows:

       

       

      -

      Baltimore

      Ohio

      New York

      Washington

       

       

      Underneath I have a field called ID. What I want to do is automatically fill in the ID field when I choose a specific site. For instance if it is my first record and I choose "Baltimore" I automatically want my ID field to be filled out with "001" and then the next time I do another record from "Baltimore" I want it to fill the ID as 002 and so forth (Note: I want the format to be 00x going from 001 to 999). To make things even more complicated, I want to do the same for the other sites, so the ID for Ohio, New York, and Washington all start with 001 and move on. So at the end of the day i might have a record list of these ID's

       

      New York 001

      Washington 002

      New York 003

       

      Washington 001

      Washington 002

      Washington 003

       

      .....and so forth........Also I need to make sure that the ID's don't repeat within a site. Is this all possible? This is very complicated and wasn't sure if it is possible. Can someone tell me how this can be done. Thank you.

        • 1. Re: Auto Entry into Fields based on other fields
          comment

          zubir007 wrote:

           

          Is this all possible?  This is very complicated and wasn't sure if it is possible. 

           

          Yes, it is complicated. What's worse, it is very difficult to implement reliably, chiefly because two users may be creating a new record at the same time and end up with a duplicate. What's even worse, if you ever delete a record, or modify a record by changing the site, the neat arrangement you have described won't be so neat anymore.

           

          The good news is that it's not necessary. Simply assign each new record a meaningless ID - an auto-entered serial number is best for this purpose. If you like, you can calculate the ordinal number of each record within its own site on the fly - however, this number is not permanent and shouldn't be used for identifying the record.

          • 2. Re: Auto Entry into Fields based on other fields
            zubir007

            Hi

             

            In terms of error only one user from each site would be able to acvess the database so error would be minimal. Keeping that in mind can u tell me how to do it the way i want it please. I would much appreciate it.

            • 3. Re: Auto Entry into Fields based on other fields
              LyndsayHowarth

              I think you can auto-enter a code such as you describe but I don't think you should confuse it with  the need for a record ID... which I believe (as I think does Michael) should be unique.

               

              Another field... let's call it 'code' can calculate the next code based on a relationship with the text values. To do that you might have max(relationship::code)+1 and it would calculate once the city is validated. There are tricky bits... as 001 is not a number it is text so you would have to restore the leading 00's and I'm sure you will find a custom function on Brian Dunning's web site for that.

               

              - Lyndsay

               

              Sent from my iPad

              Lyndsay Howarth

              11th Hour Group Pty Ltd

              • 4. Re: Auto Entry into Fields based on other fields
                comment

                Lyndsay Howarth wrote:

                 

                There are tricky bits... as 001 is not a number it is text so you would have to restore the leading 00's and I'm sure you will find a custom function on Brian Dunning's web site for that.

                 

                Actually, that's the easy part - and there is a native function you can use:

                 

                SerialIncrement ( "New York 000" ; 5 )

                 

                will return "New York 005".

                • 5. Re: Auto Entry into Fields based on other fields
                  zubir007

                  Hi,

                   

                   

                  Thanks for the input guys but this doesn't help me because this is going over my head.  Can you please give me specific "baby step" directions on how to do this.  Thank you. 

                   

                  -Zubir007

                  • 6. Re: Auto Entry into Fields based on other fields
                    comment

                    zubir007 wrote:

                     

                    Can you please give me specific "baby step" directions on how to do this.  Thank you. 

                     

                    Do you mean "can you spend more time explaining how to do this, after you have explained why it's not worth doing"? I am afraid my answer is 'sorry, but no'.

                    • 7. Re: Auto Entry into Fields based on other fields
                      zubir007

                      Hi,

                       

                       

                      Ok 1. You don't have to be rude about it 2. Its a requirement for a project that is why I am asking you for your help.  If you don't want to help then just say u don't want to help.  Don't have to be rude about it

                      • 8. Re: Auto Entry into Fields based on other fields
                        comment

                        I don't think my answer was rude. Would you prefer a "no" without any explanation? Or would it be more polite not to answer at all?