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?
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.
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.
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.
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”.
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
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
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