Is there a way to automatically generate my own account numbers or customer IDs when new records are created?

Version 1

    Details

    In the daily operation of businesses, the ability to  efficiently track your contacts in a systematic manner is often  crucial. Keeping up-to-date on the status of your business interactions  with Customers, and Vendors is often key to administrating the  accounting system.

    Is there a way to automatically generate my own account numbers or customer IDs when I create new records?


    Resolution

    Although GoldMine automatically assigns each new record a unique “Accountno” number, an own unique, custom numbering and user faced schema can be created by using the LOOKUP.INI feature.

    GoldMine can keep track of each new record added to the database (be it a customer or prospect) using GoldMine’s “Counter” function. With this function, GoldMine will automatically generate unique, sequentially ordered account numbers.

    - We highly recommend that any manipulation of the LOOKUP.INI file is only done by and in agreement of the GoldMine Administrator.
    - We assume that prior performing / applying any of the below steps the functionality and usage of the LOOKUP.INI is well known, For Example, by experience or working through the Online help chapter and sub chapters of the GoldMine Administrator Guide > Data Maintenance and Management > About the Lookup.ini

    A counter has the following expression: counter("counter name",increase by number,start value, action) where
    counter = command/trigger in the LOOKUP.INI
    counter name = any user defined/desired string for the counter name e.g. myCounter, the counter name can have a maximum of 10 characters
    increase by = number by which the actual counter value is increased  e.g. by each evaluation by 5
    start value = any user defined/desired number which will be the first counter value e.g. 10000
    action = only used in special occasions, when action value is 1 then the actual counter value is reset, when action value is 2 the counter is deleted  

    The counter expression for this example is then &counter("myCounter",5,10000) and should be applied to the LOOKUP.INI
    file located in your GoldMine shared directory.

    AutoUpdate]
    NewRecord=Key2

    Key2]
    Lookup1=Contact
    Otherwise=&counter("myCounter",5,10000)

    For every new record the counter is evaluated and will set the KEY2 value for the first newly created record as 10000, for the second newly created record 10005, for the third newly created record 10010 and so on.

    The counter expression can be combined with most other valid dBase expressions for the LOOKUP.INI for example with additional prefix strings like "01-" or other string expressions.

    In the following example, we are using the KEY5 field to store the counter-generated account numbers and we will use a variation of the original counter expression with a prefix string in the LOOKUP.INI file located in your GoldMine shared directory.

    AutoUpdate]
    NewRecord=Key5

    Key5]
    Lookup1=Contact
    Otherwise=&"01-"+ltrim(str(counter("Cot",1,81260)))

    GoldMine must be restarted in order to begin the sequential numbering, and then the first new record which will be created will contain the account number 01-81260 in the KEY5 field. The next new record will contain the account number 01-81261, and so on.

    We can refine the counter function to add account numbers only to those records that are new customers. The following example, you will use KEY1 containing the Contact Type field and the counter value again applied in the KEY5 field. Add the following variation of the example to the lookup.ini file located in your GoldMine shared directory.

    [AutoUpdate]
    Key1=Key5

    [Key5]
    Lookup1=Key1
    Customer=&"01-"+ltrim(str(counter("CustID",1,81260)))

    Similarly, you can add account numbers based on a different numbering system, only to those records that are new prospects. An account number in this sequence will be created without a prefix but instead an appendix only when the KEY1 field contains the value “Prospect”.

    [AutoUpdate]
    NewRecord=Key5

    [Key5]
    Lookup1=Key1
    Prospect=&ltrim(str(counter("ProspCtr",1,45260)))+"P"

    After a restart of GoldMine, the first new prospect record to be created will contain the account number 45260P in KEY5, The next new record will contain the account number 45261P, and so on.

    Also both possibilities can be combined so that different counters for Customers and Prospects are generated

    [AutoUpdate]
    NewRecord=Key5

    [Key5]
    Lookup1=Key1
    Prospect=&ltrim(str(counter("PCounter",1,45260)))+" P"
    Customer=&"01-"+ltrim(str(counter("CCounter",1,81260)))
    Otherwise=TBD

    Further examples may contain also values from other fields e.g. the counter value and the first 3 characters of the contact name or the counter can also be used to update already existing records in this case the trigger field = update field needs to be added to the [AutoUpdate] section

    [AutoUpdate]
    NewRecord=Key5
    Userdef01=Userdef02

    [Key5]
    Lookup1=Key1
    Otherwise=&ltrim(str(counter("Counter2",13,500)))+left(contact1->contact,3)

    [Userdef02]
    Lookup1=Userdef01
    X=ltrim(str(counter("RC2",1,123)))+"_"+DTOS(date())


    The initial setup of a counter is done in the LOOKUP.INI which initializes the counter, the counter itself and especially the actual value is stored in the LOOKUP table within the Microsoft SQL Server, whereby the FIELDNAME contains the setup counter name as in the above scenario.

    For Example. Cot, CustID, ProspCtr, Counter2 with an indicator that this is a C(ounter) >> the 'C' is added after 2 blanks at the counter name, For Example 'Cot  C'

    A useful SQL query would be SELECT * FROM  LOOKUP WHERE FIELDNAME = 'ProspCtr  C'

    The LOOKUPSUPP field contains the actual last used counter value