2 Replies Latest reply on Dec 7, 2011 9:39 AM by philmodjunk

    Sequentially numbering a field based on two different series

    DianeBrabers8092

      Title

      Sequentially numbering a field based on two different series

      Post

      I think I am making this one too complicated.  We have a contact id field that needs to be automatically assigned based on two sequential numbering systems.  Thus, if the ContactType field is "client", it should be numbered as 1 + the last client value, but if the ContactType field is a "contractor", it must be numbered as 1 + the last contractor value.  Is there a way of handling this inside the field definition?  Other ideas?

       

      Thanks for the help.

        • 1. Re: Sequentially numbering a field based on two different series
          mgores

          The easiest would be if the clients and contractors were in two different tables.  If they are all in one table I would have one contactID number that was auto entered serial number regardless of the contact type.  Then set up a separate typeID field.  Have a script performed a find based on the type in the new record to set field type ID to contacts::type & "-" Get(record count)+1

          you could trigger that script with an On Modify for the contact type field.

          • 2. Re: Sequentially numbering a field based on two different series
            philmodjunk

            I think I am making this one too complicated.

            I agree with that statement. It's not that the database couldn't be designed to do this. It's just that this is a numbering system that will be complicated to implement--especially in multi-user systems where two or more users might be adding new records at the same time--without also having to limit the flexibility of your desgn--such as putting the records in two separate tables as Mark suggests.

            I know of an approach that could be used to assign ID numbers from different number series here (Such ID's could NOT be used as primary keys in a combined contacts table), but need to know some answers to these questions first:

            Why do you need two separate ID number series? (What problem does this solve for you?)

            Can the same person be both a "client" and a "contractor"?