3 Replies Latest reply on Mar 14, 2013 1:54 PM by philmodjunk

    ID field from data in 2 other fields

    ScottRoe

      Title

      ID field from data in 2 other fields

      Post


           I have a Vendors table which has contact info for my vendors.  It includes company, address and phone information.  I am trying to create a "Vendor ID" field which will assign the ID from the first letter of the company name, followed by the last 4 digits of the primary phone number.

           Can anyone help?

            

           Thanks

        • 1. Re: ID field from data in 2 other fields
          philmodjunk

               I don't recommend that you do that. What you describe is not guaranteed to be unique.

               Example

               You might have two companies with the following names and phone numbers:

               Pinnacle Consulting, 209-546-5555
               Pyramid Games, 405-334-5555

               they'd both get vendor ID's of P55555.

               It may be unlikely that you'd get such a combination, but you only need to have it happen once to cause major problems and this is easily avoided if you use a different way to get your Vendor ID values. An auto-entered serial number in the Vendors table is a much better way to go to get Vendor ID's that will always be unique.

          • 2. Re: ID field from data in 2 other fields
            ScottRoe

                 Good Point.  I was thinking the likelihood of that happening would be small enough that it wouldn't be a worry.  But, why buy trouble you don't already own. 

                 I may use the other pattern for customers, but entered manually.  That way, I can add a character if I need to.

                  

                 Thanks again.

            • 3. Re: ID field from data in 2 other fields
              philmodjunk
                   

                        I may use the other pattern for customers, but entered manually.  That way, I can add a character if I need to.

                   The same arguments apply to customers and if you have several users adding new customer records at the same time, you may still get duplicate entries.