Email Address: redstone@earthlink.net

Web: http://www.redstonesoftbase.com

Message Board: http://www.goldmineforum.com

GoldBox-SQL v6.23 for GoldMine® Sales and Marketing TM (MSSQL-7)

INTRODUCTION *

SUPPORT: *

ORDERS: *

INSTALLATION: *

CONFIGURATION: *

FREE UPDATES *

GETTING STARTED *

TECHNICAL DOCUMENTATION *

GOLDMINE DATA TABLES *

SENDING GOLDMINE DATA OUT *

MAINTAINING EXISTING GOLDMINE RECORDS *

ORGANIZING GOLDMINE DATA *

CREATING LISTS FOR FIELD AND RECORD ANALYSIS *

MANIPULATING GOLDMINE USER INI FILES *

MAINTENANCE OF GOLDMINE DATA TABLES *

PERFORMING BASIC DISK FILE OPERATIONS *

RUNNING NON-GOLDBOX PROGRAMS *

BATCH OPERATION LOG ENTRY *

AUTOMATION *

MISCELLANEOUS OPTIONS *

GOLDBOX FUNCTION LIBRARY *

ACKNOWLEDGMENTS *

First, thank you very much for purchasing the finest data management package ever designed for a CRM system as an add-on! There is nothing else like GoldBox in the CRM world. With this package, your GoldMine system now becomes much bigger and more responsive to your particular needs. You will be able to perform data management operations that either you always wanted to, or that you never imagined were possible.

GoldBox per se is not a programming language, but you may want to think of it as a user-definable language specific only to GoldMine. Its current 15,000 users, although they are utilizing the same product, are all implementing it differently. No two GoldBox systems process data in exactly the same way. This is because the program is flexible enough to conform to your particular GoldMine system and your specific needs. It is, therefore, considered a toolbox. Think of it in these terms: Ten contractors receive an identical set of building tools and materials and are tasked with building a house for a buyer. They will build the houses exactly the way the buyers want them – individual to the buyers’ tastes and needs. Each contractor ends up building a unique house, so that when the houses are later compared, they all have a custom look and function a bit differently. Yet, all ten contractors used the identical set of tools and materials. That is GoldBox – you set it up to do exactly what you want with your GoldMine system, based on its common set of tools and utilities.

If you’ve heard of GoldBox before, you might get the impression that it is an Import utility only. Although the Import is very popular, it is still just one of countless capabilities in the program. An occasional comment heard is: "Now that I’ve done my Import, why do I need GoldBox anymore?" Some users even remove it from their systems! This flies in the face of the original concept envisioned when GoldBox was first released in 1993. The concept was to provide a single entity for solving as many data management issues as possible, to all GoldMine users.

The GoldBox program is an incredible value at a very low price. Comments from its users over the years include: "I would have gladly paid 10 times the price for GoldBox" or "GoldBox has saved us thousands of dollars" or "The amount of time saved using GoldBox is immeasurable". The reason for the low price and tremendous capabilities of GoldBox are part of the original design concept: To have GoldBox available on every GoldMine installation. Therefore, if you are an end user of the product, you must have a licensed copy of GoldBox on your system whenever GoldBox performs any processing on your GoldMine data. This is true even if a GoldBox reseller performs that processing remotely, for example. This covers the situation where you send your data to a reseller company, they manipulate it with GoldBox, and send it back .Any GoldBox processing performed on your data when you do not have a licensed copy is considered piracy of the product and a violation of the licensing agreement.

This program is specifically designed for the ‘GoldMine smart’ person in your organization – whether this is handled in-house or out-sourced, for example, to a Solutions Partner or consultant. The ‘GoldMine smart’ person is the one usually acting as a database administrator (DBA), who performs such tasks as adding users to GoldMine, setting security, scheduling data maintenance, customizing Lookup.INI or other INI files, building reports, importing or exporting data, etc. DBAs are skilled at keeping a database management system running smoothly under the hood. A corollary is the relationship between an average automobile driver and a reliable, skilled automobile mechanic. Although the driver operates the automobile and can perform minor maintenance and upkeep, the mechanic performs those deeper functions normally beyond the capabilities or knowledge of the driver. Both are needed to maintain the automobile’s usage, reliability and life span. This symbiotic relationship, between users and DBAs, also exists in the world of database management systems.

GoldBox operates upon GoldMine in a fashion similar to the way a report writer does it, such as Crystal Reports. It goes against the data directly, completely bypassing GoldMine’s interface. No matter what an interface for a CRM system looks like, eventually the results of a user’s actions are stored on disk in data tables. For GoldMine 4 and GoldMine 5, data tables use dBase4 format. For GoldMine Front Office hosted in SQL, data tables use MSSQL-7 format. GoldBox accesses those tables directly, as if there was no GoldMine interface at all. In fact, you never need to have GoldMine open. GoldBox will still operate if GoldMine is open, of course – it’s just not necessary. In some cases, an operation such as an Import might operate faster if GoldMine is not open. And, for maintenance operations such as Rebuild and Reindex, GoldMine cannot be open. We’ll look at some of GoldMine’s major data tables later.

Free support is provided with GoldBox for brief questions on setup or capabilities, or for bug reports. The best way to do this is via direct email, or by posting a message on the Message Board. Phone support is provided as available, and support calls left as voice messages are generally not returned. If you are an end user and purchased through a GoldBox reseller, you should first contact the reseller for support, since it is likely that GoldBox has already been customized in some areas for your GoldMine system.

One of the best independent implementers of GoldBox operations is Paul Laufer. His services are always fee-based. Paul also has a great deal of knowledge on GoldMine and GoldSync. He can be reached in the USA at (562) 597-7188 or paul@telesagesolutions.com

All orders, except in the UK, go through the Add-on Store. The link to this site is found on the first page of the Redstone SoftBase Company Web site. The site address is:

http://www.addonstore.com/goldmine/pages/goldbox.asp

Resellers: If you have not been informed, please contact Redstone SoftBase Company for details on how to order.

United Kingdom orders: Please order from Canonbury Services Limited.

Phone: 0207 359 8524 Email: sales@canonbury-services.co.uk

Fax: 0207 359 7775 Web: http://www.canonbury-services.co.uk

You will either have downloaded or received on CD the setup program. The GoldBox installation is a rather basic install of the software without too much fanfare. Essentially you are just prompted for the path to store GoldBox. Run the Install program. The default path is always on the C drive but you can change this if desired. Recommend you do not store GoldBox under GoldMine, and not in long path names. Since one half of GoldBox’s strength is its powerful automation logic, the shorter the path, the better.

If your GoldMine system is on a network, you can store GoldBox on your workstation, as long as you have full access to the networked GoldMine system, and as long as the BDE Administrator is stored on the workstation. GoldBox requires the BDE Administrator, but does not ship with it, since GoldMine installs this. What, you ask, is the BDE Administrator? Generally you never need to worry about this – just know it’s there and GoldBox must be able to see it to operate.

When you begin the install, the GoldBox system is stored to your GoldBox path. Everything is contained in one path. One sub-directory is created, called OUTBOUND, under the GoldBox path, with nothing stored to it. We’ll look at its usage later.

After the Install completes, GoldBox loads and you are taken immediately to its Configuration Screen. The first thing you may notice is that some of the items on the screen are in bold red. This is a common sight throughout GoldBox, meaning: Those items are REQUIRED entries. All other items are defaults that can also be modified.

Press F1 at this screen for a full description of its items. When you click {Go} to complete the Configuration, GoldBox checks everything and informs you if changes need to be made. You can re-access this screen anytime from the {File} menu.

After exiting the Configuration Screen, GoldBox builds its indexes – you will see the screen flash as each file is maintained. When this completed, you are taken to the login screen. Key in your initials and press {Enter} or click {Go}.

GoldBox will then display a general information panel, and when you acknowledge it, GoldBox informs you it will exit to complete the process. Click {Ok} to exit. Now you are ready to begin using GoldBox.

Unless otherwise indicated, you get free updates to your release of GoldBox. Periodically check the Web site http://www.redstonesoftbase.com to see what the latest version is. This information is displayed on the first page of the Web site. To download an update, click the Treasure Chest, click {Files} and select the appropriate Zip file to update your release. It is best to save the download right in your GoldBox path. Then, use either WinZip or, in DOS, use the PKUNZIP.EXE program to unzip the file. If prompted to overwrite existing files, answer A-ll.

So, you have installed and reloaded the program, and you’re in! At the Top Level Menu, you’ll see a white form in the center with the famous GoldBox logo and other information. Please do not minimize the white form – it has to stay exactly where it is! You can minimize the entire screen, but not the white form. If you do, some of the menu options will magically gray themselves out, and then you’ll have to exit and reload.

Are you ready to just dive right in? Maybe not. You might be a bit intimidated at first, especially if you go into snoop mode and start pulling down menus to see what’s there.

The following portions of the Quick Start Guide will very briefly discuss some things to get you going, but will not repeat what is already in the F1 panels or User’s Guide.

The real documentation is held in the F1 panels of the GoldBox Help System. When at the Top Level Menu, click the {Help} menu, then press {F1}. Click {Contents} or {Search} to find a particular panel. When you are at any other particular screen, pressing {F1} takes you directly to the appropriate panel. You can view it, print it, or, at some panels, jump to other related panels.

There is also a Word 97 document called GX?GUIDE.DOC in your GoldBox path. For GoldBox-5 and GoldBox-SQL, this is nothing more than a single document containing all the F1 Help panels. For GoldBox-4, it is the entire documentation, as there is no F1 Help in GoldBox-4. GoldBox-4 is still sold, maintained, supported and updated but won’t get F1 Help since most GoldMine users have moved on to GoldMine 5 or Front Office. FrontRange Solutions is no longer enhancing GoldMine 4, so you may want to move on to their later products to keep pace with GoldMine’s evolution.

Please note: The GXW Browser will be mentioned below occasionally. This is in GoldBox-4 and GoldBox-5, but not in GoldBox-SQL. There is, however, a Query Analyzer in GoldBox-SQL to view different GoldMine data tables based on a SQL Statement. It may be easier to use than the standard Query Analyzer, because in this one, you don’t have to do the SELECT statement or any JOIN statements.

Let’s look at the major data tables in GoldMine manipulated by GoldBox.

In GoldMine 4, Details are referred to as Profiles.

In GoldMine Front Office (SQL), data tables do not have a .dbf extension.

This is the nucleus of GoldMine. Everyone starts with a Main Contact record. It is so critical that fully one half of the main screen in GoldMine displays Main Contact data only. The Main Contact File, collectively, is actually made up of three files:

Contact1.dbf contains all of the fields you see in the top half of the main screen.

Contact2.dbf contains summary fields such as Comments and Previous Result and displayed data relating to scheduled and completed activities (the Summary tab). It also contains all user-defined fields. These are the 10 default fields (UserDef##) and any which you add to GoldMine (the Fields tab).

Contact1.dbt contains the freeform Notes for all records in Contact1.dbf. If you look at nearly any file in GoldMine using GXW Browser and click [Fields] to view the field structure, you will see a field most commonly called NOTES, which is type 'M' and has a length of 10. Some users are fooled into thinking that you can only put 10 characters into a Notes field. Actually, the NOTES field is just a pointer into the DBT file that tracks where in the DBT the text for the particular record begins and ends. The DBT file is what contains the actual text. The Main Contact Notes are visible from the Notes tab.

When GoldBox talks about the Main Contact File it really means these 3 files together. They are related in such a way that they appear to be just one file. So, if you are importing NEW records into the Main Contact File, you actually have simultaneous access to Contact1.dbf, Contact2.dbf and Contact1.dbt.

All of the following files relate into the Main Contact File. GoldBox refers to them as the Tab files because of the tabs you see running horizontally through the middle of the GoldMine main screen. They can contain multiple records, which "know" to stay attached to a Main Contact record. For example, a Main Contact might have 3 related Details, 2 Calendar Activities, 4 Automated Processes and 27 History records (also known as Completed Activities). The relationship is known as parent-child, with the Main Contact record as the parent and the Tab records as children. There is no magic involved in a Tab record "knowing" which Main Contact it is attached to. In relational database systems, a common field is used to link files together. In GoldMine, this is the Account Number (Accountno) field. If a Main Contact has 27 History records, each History record contains the same Account Number as that in the Main Contact record.

Technically, the Contact2.dbf is a related file because the Account Number links it to Contact1.dbf. But you can have only one Contact2 record per Contact1 record (known as a 1-to-1 relationship). For the Tab Files, you can have as many as disk space will allow (known as a 1-to-many relationship). For the purposes of GoldBox, Contact2 won't be referred to as a Tab File because in concept it is just an extension of the Contact1 file.

ContGrps.dbf: This is unique to each Contact File. It contains subsets of Main Contact records to be handled for specific purposes, as in scheduling, mailing or exporting. The relationship between the Groups file and the Main Contact File is not parent-child but grandparent-parent. The Main Contact File is still the parent but is now accessed via the Groups file that rides over the top of it. When you have a Group active in GoldMine, you are actually stepping through Group records, which then immediately pull up the related Main Contact records. Per Group, it is a 1-to-1 relationship, but a Main Contact can be a member of any number of Groups. Did you know that GoldBox has the best Group Builder available for GoldMine? More later.

Cal.dbf: This file contains the Scheduled, or Pending Activities for Main Contacts. These include things like callbacks, appointments and mailouts. Anything in this file hasn't occurred yet but will hopefully on some date in the future. The Calendar file is global to all Contact Files and is in GoldMine's GMBASE directory. It is a major reason why it is best to work with a single Contact File, or, if you are using multiple Contact Files, to schedule against only a single Contact File.

ContHist.dbf: This file is unique to the Contact File. It contains all Completed Activities. The History file, in any GoldMine system, will usually contain the most number of records.

ContSupp.dbf: This file is unique to the Contact File. This is a "catch-all" Tab file that contains Additional Contacts, Details, Referrals, Automated Process Tracks, Org Chart Records, Links to Documents and a few more. The records in this file "know" what they are simply by a 1-character value in the RecType field. Additional Contacts contain 'C' in the field, Details contain 'P', Referrals contain 'R', Tracks contain 'E', Org Chart Records contain 'O' and Links contain 'L'. Email Addresses are also held in this file as special Detail records and contain 'P' in the RecType field. Org Charts are contained in the ContSupp.dbf file but, like Groups, relate to the Main Contact File with the grandparent-parent relationship (although not in exactly the same way). When you activate an Org Chart you can access other Main Contacts by clicking on the chart members in the graphical display.

Lookup.dbf: Contains the Field Lookups that appear when you press F2 or Rt-Click in GoldMine. The field name in most cases relates records here. This file also contains the Zip Code entries that automatically populate the City and State fields when a Zip is entered. This file is global to all Contact Files and resides in the GoldMine GMBASE directory.

{Batch} {Import/Update} As mentioned, this is one of the big hammers in GoldBox. It also takes the most analysis and planning. Most often, you are bringing data into GoldMine that was setup to work a certain way in another system. Now you have to make it work within GoldMine, and follow the rules setup by FrontRange Solutions for managing its own data tables. The Import/Update is definitely not something you want to just dive into immediately. You first have to have a basic understanding of how GoldMine stores its data. See the above section on GoldMine Data Tables, and of course ensure you review the GoldMine documentation on its data table usage (which is the best source for this information).

Once you have decided where in GoldMine you will store the incoming data, you then must analyze the incoming data to see if its import into GoldMine is feasible. Some quick notes:

The incoming data must eventually be in dBase format. If it is in ASCII or SDF format, GoldBox has tools to convert to dBase, and so does Excel and Access. See the Import/Update documentation at F1 Help for information on how to do this.

Import into the Main Contact File is rather straightforward, for adding new contacts. If you want to Dupe Check the incoming records before they are added, this must be done as an {Update Main Fields} process in GoldBox. Plug in all the fields you need for a new record, then press the {Shield} button at each field. This stops the GoldMine field from being updated on a match. At the Update Setup screen, select the checkbox to Add Unmatched Records as New.

If you are bringing data into the Tab files, determine how the Tab data is stored in the incoming file(s). If it is contained in the same record as the Main data, you can plug in the Tab records so they are attached in a single pass. If they are in separate related tables, determine which field relates them to the Main data. This field value must be mapped when doing the GoldBox Main Import of New records, to the Assistant/Secretary field, one of the 5 Key fields, or one of the first 10 User-defined fields.

GoldBox allows you to match an incoming record to a GoldMine record using multiple fields. The result of the Record Match expression cannot exceed 100 characters. You could, for example, match on Company + Contact, or Company + Phone1 or Company + Contact + Zip Code.

If you want to update existing Tab records in GoldMine from an outside file, or if you want to stop a dupe Tab record from getting into GoldMine, GoldBox provides a mechanism for this. See {Tab Attach, Update, Dupe Detection) in the F1 Help system.

GoldBox anticipates many of the formatting issues you may have when importing data:

• You can Proper Case or UPPER CASE incoming values with button clicks.

• You can select a checkbox to auto-format incoming phone numbers to USA format.

• You can select checkboxes to auto-populate the Last Name and Dear fields of GoldMine. When auto-populating Last Name, GoldBox has transparent logic to ‘walk back’ a maximum of two short titles that might be at the end of a name to populate Last Name with the actual last name.

• You can import just a certain number of records, and you can start importing from a particular record number of the incoming file. These might be used for testing, or to bypass corrupted records in the incoming file.

• You can select the incoming Name format if it is not in GoldMine’s standard format of First, Middle, Last. If the name is coming in as Last Name, First Name, GoldBox can turn it around on the fly without you having to key in a complex expression.

• If your incoming Name is stored across 2 or 3 fields, you can plug them into separate columns and GoldBox transparently combines them into one field value. This is also useful if importing USA Zip codes and the incoming file stores the Zip Code across 2 fields (first 5, then Zip + 4). Or, if Phone numbers are stored across 2 or 3 fields.

• If your incoming Name starts with a salutation, such as Mr, Ms., Mrs. or Dr., GoldBox allows you to apply the StripMrMs() function to it for removal. Map the field, click {Exprn}, then use StripMrMs() as the expression.

• If mapping an incoming Date field stored in character-type format to a true Date type GoldMine field, GoldBox can transparently convert it to a true Date on the fly. Just select the matching format from the Date Mask list. No expression necessary.

• If you have coded incoming fields and want them translated into different values, GoldBox can handle it. Select the checkbox at the Import Setup screen, then press the {Translt} button at each field you want to translate. Key in as many entries as are required. You must determine all possible values to be translated beforehand. This capability is also useful when importing into Calendar and you want to translate incoming user names into the standard GoldMine 8-character User Names.

• Since Calendar was mentioned, if you are importing Calendar records and want to evenly distribute them amongst a group of GoldMine users, you can do this with the RoundRobin() function.

• If you have already setup GoldMine’s LOOKUP.INI file for field lookup-and-replace and want to use it for the Import, GoldBox provides the LookupINI() function.

• If importing into Main Contacts, you can have a Lookup list built automatically of the incoming values. This works only for Main and user-defined fields – it is not available when importing into Tab files. Press the {Lookup} button at each field on which you want a Lookup list built.

• Instead of mapping to a GoldMine field, you can populate it with the result of an Expression, or, you can both map a field and then follow up with an expression.

• When mapping fields, GoldBox displays the incoming field value after you have mapped it, directly above the mapping area. You can also move around anywhere within your incoming file by going forward or backward 1 record, 25 records at a time, or by directly browsing the Incoming File right from the Import Setup or Field Mapping screens. This is useful as a guide to aid you in correct field mapping.

• On an Update of Main Fields, you can automatically increment a GoldMine numeric field with the incoming numeric value. Instead of replacing the GoldMine numeric field with the incoming value, you can add to it by pressing the {Incrmnt} button.

• Options are available for updating Notes. You can completely replace Main Notes with the incoming Notes, or add to the Main Notes. You can add the incoming Notes to the top or bottom, and choose whether to include the GoldMine Date Banner. Replacing or Adding To the Main Notes is handled at the mapping screen with the {NoteAp} button. The other formatting options are at the Update Setup screen.

• You can update multiple matched Contact records with a single incoming record.

• You can write out the existing field values of the fields being updated to a History record before they are overwritten, to be used, for example, as an audit trail.

• You can replace a field in the incoming file when importing New records, or when updating, on matched records or unmatched records.

• GoldBox marks the CREATEBY field of the Update file (an UPDATER Contact File in GoldBox-4 and GoldBox-5, and the UPD.DBF file for GoldBox-SQL) with a code for matches and no-matches. For matches, the code DIDMATCH is written. For no-matches, the code NOMATCH is written. You can use this information to do further processing or analysis.

• For GoldBox-4 and GoldBox-5: You can automatically generate a GoldMine Group of incoming new records, or on matched records for an update.

A very powerful function called VLink2dBase() is provided in GoldBox to allow a virtual link to any dBase table from GoldMine. Please see the documentation for this function. Using a GoldMine table as the master, you can read from or write to any of the fields of the linked table. This might be used as an alternative to Updating, by doing a Global Replace instead and linking to the outside table. In GoldBox-SQL, you also have a VLink2MSSQL() function to link to another SQL table.

{Batch} {Export} Exporting is taking data from one or more GoldMine tables and creating a single flat file, in dBase or Text format. This can be used as:

• a word processor merge file

• an address mailing list

• a file to be used as input by another system

• a file to be used for creating a complex report

• an incoming file to be used by GoldBox for import into Tab files. For example, if you have data in user-defined fields and want to move that information to Details, you would first generate the incoming file by exporting the necessary information. Note: If you are using this capability, always export the ACCOUNTNO field so it can be used for Record Match when you are attaching as Details.

• You can export from a Tab file as the master. For example, you may wish to export Additional Contacts, or Details, or Calendar or History information. GoldBox also sets a transparent link back to the related Main Contact, so you have all of the Main Contact fields at your disposal for exporting, or using in the filter.

• You can combine one exported file into another. Typically this is used when you want to export from both Main Contacts and Additional Contacts and want all the information in a single, sorted file. GoldBox ships with this example scenario, accessible from {Batch} {Export} {Saved Setup}. Alter it according to your needs, or setup your own. This is covered in the documentation at the Export Setup screen’s F1 panel, but, when using this option, you must ensure the {EXPORT AS} field names are identical, the same length and type, and in the same order for the exported files to be combined properly.

• You can export just a certain number of records, independent of the filter.

• You can replace a GoldMine field for each successfully exported record. This can be used in tandem with the above option. For example, you have 1,000 contacts to be exported over a 1-month period, 50 at a time, and you don’t want any record to be re-exported. You can replace a field with a value, then evaluate that field in the filter. Example: You replace the field User-defined #1 with the code: TRADESHOW. In your filter, you would include the statement: Contact2->UserDef01 <> "TRADESHOW". GoldBox will only export records that do not have TRADESHOW in the field, so only un-exported records are handled. Since you are then putting the code TRADESHOW into the field when the record gets exported, that record won’t be included the next time you run the export. This is ideal for segmented exports.

• You can sort the exported file by any of its fields.

• When exporting to ASCII, you can have descriptive labels as the first record, which are drawn from the description column at the mapping screen. You can also define the character field delimiter and the field separator. The field separator can even be defined as a Tab or a Space.

• You can create a 1-field-per-line text file

• When exporting address information, you can sort the exported file by the USPS 3/5 digit method for bulk mailing

• You can add a History record for each exported record, and define the Record Type Code, Activity Code, the Result Code and the Reference.

• You can export up to 1,000 calculated fields instead of a single GoldMine field. Typically examples are PullEmail() for exporting the email address from either Main or Additional Contacts, a literal text value, a total from Completed or Scheduled Sales, a sum or other mathematical calculation based on user-defined fields, or even a field from a linked table. The final example can use the VLink2dBase() function to link the GoldMine table with an outside dBase table. You can use this link in the filter, or include any of its fields in the exported file, even though those fields do not exist in GoldMine!

{Misc} {GXW Browser} {Any File} {List} button. This is available for GoldBox-4 and GoldBox-5. You can create simple list-type reports from any GoldMine table (or indeed from any dBase table).

{Misc} {GXW Browser} {Any File} {Write} button. Displays the full record as one field per line, and includes listing the entire contents of the Notes field. This can be viewed in the scrollable window, or printed.

{Batch} {Merge-Purge} The GoldMine Merge-Purge concept was originated in GoldBox and is generally faster, more failsafe and contains more options than the one in GoldMine. Merge-Purge allows you to identify dupes based on one or more fields, then Merge the related Tab records of the Deleted dupe into the Saved dupe. In GoldBox, you can choose from Batch, Count, Manual or Delete Modes; Save Deleted dupes off to another Contact File; Create Additional Contacts from Deleted dupes; Create a list of dupes in Count Mode; and, in Batch Mode, backfill Saved fields from Deleted fields. You can also save off the field values in the Deleted record that differ from the Saved record to a History record. Main Notes from the Deleted are not added into the Notes of the Saved record. Instead, a special History record is created, and the Main Notes of the Deleted record become the Notes value of the History record.

{Batch} {Global Replace} You can globally replace Tab records, or a Main or user-defined field from a Tab field. You can also use VLink2dBase() and replace GoldMine fields from a linked dBase table directly. If you are looking to globally replace a Linked Document drive/path with a new one, choose {Supplemental}, and at the Setup screen, select {L} as the {Supplemental Record Type}. You will see the checkbox for this option. When the checkbox is selected, click {Field-1} and you are prompted for the rest of the information.

{Batch} {Global Delete} You can globally delete Tab records, and, in GoldBox-4 and GoldBox-5, records from any GoldMine file. That is available from {Misc} {GXW Browser} {Any File} {X-Del} button.

{Misc} {GXW Browser} {Any File} {U-Del} button. In GoldBox-4 and GoldBox-5, you can undelete records (a.k.a. Recall) from any file, as long as you have not done a file Rebuild in GoldMine since you performed the deletions. GoldBox has special logic when you choose the {Contact1} file. It will undelete the Contact record, and all related Tab records attached to it. This should be done as soon as possible, since GoldMine recycles deleted Tab records!

{Batch} {Notes Archival} Moves dated entries in the Main Notes into History. The procedure is based on the unique GoldMine Date Banner that is added when you insert a Note. 3 asterisks surrounding a User Name identify these. If you have imported Main Notes and they have a general banner for each entry, you may be able to use the GenBanner() function to convert them to GoldMine Date Banners. Then you can run this operation to send them to History. A general banner must begin with a date as the first word of a Notes line in order for GenBanner() to convert it.

{Batch} {User Name Changer} GoldBox has identified all the GoldMine tables that track the GoldMine User Name. Instead of doing Global Replaces on these tables individually, you can run this operation to select any or all of them. This is typically used to change a general GoldMine User Name, such as DAVE, to a more descriptive one, such as DPETONIC. Or, if an employee has left your company, it can also be used to reassign the records to a new user name.

{File} {Area Code Split Table} In this operation, you populate a GoldBox table called AREACODE.DBF with the split information. This consists of the Old Area Code, the New Area Code, and the Prefix or Exchange affected by the split. Optionally you can include a City, since in some rare cases this is used to determine a split. Once the table is populated to your satisfaction, you launch GoldBox with 3 simple parameters to run the operation. The 3 parameters are your GoldBox login initials, the code /A, and the drive/path to the Contact file on which to perform the operation. GoldBox will automatically check Phone1, Phone2, Phone3, Fax, any User-defined Phones, and the Phone and Fax of Additional Contacts and make any changes necessary, based on the AREACODE.DBF table. If you have the split information in a dBase or text file, you can also import into the table, via {Batch} {Import/Update} {Area Code Split Table}.

{Batch} {Group Builder} The finest Group Builder for GoldMine is not in GoldMine – it’s right here! This claim is based on the following:

• You never need to do any messy field replacing or pre-Group creation if you have a complex inclusion scenario. GoldBox does everything directly, and never needs to alter any GoldMine data before building a Group.

• All Group Build scenarios are automatically saved and can be re-executed anytime.

• You have more logic choices available for determining whether to add a Contact to a Group by selecting from any of GoldBox’s 100+ functions for your filter.

• The MultiTab() function allows you to scan the Tab records attached to a Contact and include a Contact in a Group if any number of Tab records meets, or even does not meet, a series of sub-filters. For example, you can easily build a Group for those Contacts that have no Email address. Or, for those Contacts where the total Completed Sales in History during a 3-month period exceeds $10,000. Or, for those Contacts which have 2 specific types of Detail records, but do not have a third type. Essentially, your imagination is the only limit on the usage of MultiTab(), one of the truly powerful functions in GoldBox.

• You can add a Contact to a Group based on that Contact’s membership, or non-membership, in any other GoldMine Group via the InGroup() function.

• This may sound unbelievable, but you can actually build a GoldMine Group based on field values in a related dBase table, even though those fields are not tracked in GoldMine! This is another great usage of the VLink2dBase() function. Example: You have purchased a Postal Code table that contains demographic data. One of the fields in this table tracks average income per resident, by Postal Code. You want to do a mailing to Contacts in a Postal Code whose average income is greater than $50,000. It is not necessary to bring the Income field data into GoldMine first. You can evaluate the linked table directly with VLink2dBase() and add members based on what it returns. In GoldBox-SQL, you can also use the VLink2MSSQL() function.

• You can stack any number of Group Builds into a single process, and have GoldBox automatically refresh them once a night, for example.

{Batch} {Groups-by-Break) You can automatically generate GoldMine Groups by ‘breaking’ on unique values found in a field, or a field expression. For example, you wish to build a separate Group for each State in your Contact File. Instead of setting up an individual Group for each State, this option scans the State field and automatically generates a Group for each unique State.

{Batch} {Org Chart Creator} GoldBox lets you automatically generate single-level Org Charts, using the ‘break’ method described above. Or, you can setup one or more filters and add members to an Org Chart if they meet one or more of the filters. In this way, you can build ‘Visual Groups’. Again, you have the power of GoldBox’s function library to pinpoint Org Chart member inclusion, including the MultiTab() and VLink2dBase() functions.

{Batch} {Dupes Between Files} This is available for GoldBox-4 and GoldBox-5. It allows you to compare one Contact File against another, and create a Group of those records found in the other Contact File, based upon your Record Match expression. You might use this, for example, to delete the dupes directly, or copy them into the other Contact File and perform a Merge-Purge.

{Misc} {GXW Browser} This is available for GoldBox-4 and GoldBox-5. You can choose from any of GoldMine’s fields and generate a listing of all unique values in that field, whether an F2 Lookup entry exists, how many records contain the field value, and what percentage of the file contains that field value. This is ideal for use by DBAs, to standardize a GoldMine file before running reports or building Groups. You can also select this option for any GoldMine file, or any dBase file.

{Batch} {Contact Validator} This is available for GoldBox-5. You can setup an unlimited set of rules for record values, and the process will create a listing of those records that did not meet all of the rules. Again, helpful to DBAs for standardizing record entry and Tab file record existence for Contacts. You can access the listing from {File} {Contact Validator Exceptions}

{Batch} {INI Settings Replicator} This is useful for DBAs who want to standardize one or more User INI files with sections from a User INI File chosen as Master. You can replicate one or more sections to any number of other User INI files. The process is saved and can be re-executed, if, for example, you make a minor change to a section and want to easily propagate the change.

(Command Line or Q-file driven) Available for GoldBox-4 and GoldBox-5. You can Pack, or Rebuild, any GoldMine tables. Some users have found that if GoldMine’s maintenance procedure fails on a particular file, this may process the file correctly. The Pack/Rebuild operation removes deletion-marked records, optimizes the Notes file by reducing its size and rebuilds all related index tags from scratch. The GoldBox file GXMDXDEF.DBF contains all of GoldMine’s index expressions and is updated each time a new GoldMine version is released. You may review this file but do not alter it.

GoldBox provides the additional service of creating a log entry for each file handled. This entry shows when the process began, when it ended, how many records the file started with, how many were removed, how many remain, the size of the Notes DBT file before the procedure and the size after.

You can also pass a 4^th parameter called SYNC. This causes GoldBox to verify the record has a Record ID, and if not, it will create one and add a Tlog entry.

You can also pass a 4^th parameter called ZZNEW for GoldBox-4 and GoldBox-5. This causes GoldBox to generate a New Sync ‘zzNew’ record for each record in the GoldMine table. In GoldBox-SQL, this is launched differently (see F1 help). You should definitely be a sync expert when using this option! It is typically only used when you want to clear out the Tlog file and begin sync transfers from a central system from scratch.

(Command Line or Q-file driven) Available for GoldBox-4 and GoldBox-5. Essentially the same as the above procedure, except that only the index tags are recreated.

In GoldBox-SQL, you can launch the creation or the dropping of any SQL index tag.

(Q-file driven) This is available for GoldBox-4 and GoldBox-5. Any dBase4 table can have as many as 47 index tags. You can add an index tag to any GoldMine table or alter an existing one, and this tag will be automatically maintained by GoldMine during normal interactive usage.

This operation is actually run by the freeware BR7 Utility, provided by Redstone SoftBase Company and existing in your GoldBox path as BR7.EXE. To automate the operation, it is setup in a Q-file.

Example: You wish to create an index tag on the Department field and alter the index tag for the Last Name field. You can either add entries to do this within an existing Q-file, or create a new Q-file specifically for this purpose. We’ll assume creation of a new Q-file. At {Misc} {Q-file Setup} select {Add} and key in a Description such as: Additional Index Tags for GoldMine. Make the Q-file name QADINDEX. Click {Quit} and you are taken to the entry setup for this Q-file.

Select {Add}, and from the list, near the bottom, select the {RUN WINDOWS PROGRAM} item. In the {Launch ID or Path Name} column, key in:

If the entry does not fit within this column, select the next column, {4^th Parameter} and finish the statement.

Select {Add} again, select the {RUN WINDOWS PROGRAM} item, and key in:

Click {Quit} to exit the Q-file setup, and do not choose to run the Q-file. You can now store the call to the Q-file from {Start} {Run} or a shortcut.

When the first item is executed, the index is created by BR7, which then exits because of the /E parameter. Then the process repeats for the second index tag on Last Name. The alteration of the Last Name index tag is included as an example because GoldMine’s default index tag expression does not include the Contact’s First Name as a sub-sort after Last Name. This index will now allow you to view, for example, 50 contacts named Smith, sub-sorted by their First Names.

• The paths for the above examples are arbitrary – substitute your actual path. The index tag name, which is the 2^nd parameter, is also arbitrary for the new index, but when altering an existing GoldMine index tag, you must use the same name! The expressions are also arbitrary and are only suggestions. Remember that the result of the index expression cannot exceed 100 characters.

• If you rebuild or re-index in GoldMine, these index tags will disappear unless you re-execute the Q-file.

• Creating extra index tags on a GoldMine file will affect performance.

• Finally, and most importantly: FrontRange Solutions does not recommend, endorse or support this capability!

In addition to creating index tags on any dBase4 table, you can also launch the Pack/Reindex of any dBase4 table. This is done by passing a special parameter after the file name, such as:

From within a Q-file, you can add commands to perform the disk file operations Copy, Rename and Delete. This precludes you from having to loop out to a DOS batch file for these operations. These capabilities may be useful when setting up complex Q-files, for Import, Update or Export operations. When setting up a Q-file, you will see these options near the bottom of the pick list.

Because of GoldBox’s excellent Automation Logic with Q-files, you can include the running of other applications right from within the Q-file. For example, before an Import or Update, you may need to run an outside application that converts an Oracle table to a dBase table. You would put the call to do this above the Import/Update operation so the file is ready for the Import/Update operation (which, as mentioned, uses only a dBase table as the final format). Please ensure the application is self-opening (no prompted user name or password) and self-closing (closes itself after running). That will cause GoldBox to move down to the next item in the Q-file task list and continue processing.

The call to BR7 to create or alter existing Index Tags, mentioned above, is a perfect example of this capability, since BR7 is not part of GoldBox and is an outside application.

Each batch operation that successfully completes, even the running of a non-GoldBox program, gets a log entry called a Batch Session Summary record. You can access these anytime for analysis, by going to {File} {Batch Session Summaries} and clicking the {View} button. The log entry shows who ran the operation, when it started, when it finished, whether it was called from a Q-file, number of records examined and the number processed (done).

Fully one half of GoldBox’s strength is found in its automation logic. You may thing it’s difficult to implement and mistakenly bypass it. In reality, setting up GoldBox’s automation logic is the easiest procedure – it is the icing on the cake. The harder work in GoldBox is in the planning, before you actually setup an operation, and in the setup itself. Once you have that done and have fully tested it, automation is a breeze!

A Launch Operation can be the running of just a single GoldBox operation, such as an Export, or, multiple operations stored to a single Q-file. Let’s say you have setup an export from History and want to auto-execute it anytime. You can do this from the Windows {Start} {Run} if you’d like and it will be saved. Or, from a Windows shortcut, from within another application, or even as an option on one of your GoldMine menus.

All you are doing with a Launch Operation is calling GoldBox with a few parameters. Parameters are items you ‘pass in’ to a program, which it acts upon. In Microsoft Word, for example, you can pass in a parameter after the program call to load a specific document. In the case of GoldBox, you are essentially passing in an Operation Code and the Launch ID of your Setup. A call to launch the above Export might be:

This is a rather typical Launch Operation command, with 3 parameters. The first is your GoldBox login initials; the second is the Operation Code; and the third is the Launch ID. When you run this, GoldBox loads its processor, performs the operation, then exits. All Batch Operations in GoldBox have examples for launching in the User’s Guide so we won’t go into that here.

Now that you see how easy it is to launch a single operation, let’s take a look at the real power of automation in GoldBox.

GoldBox is so versatile in its data management capabilities that many times, you want to run multiple operations as part of a complete batch process. Q-files makes this possible. Their setups are accessible from {Misc} {Q-file Setup}.

Setup of Q-files is already documented so that won’t be repeated here. Essentially, you can add as many operations as needed into a single Q-file, set the order in which the operations execute, and optionally apply a simple filter to each entry for conditional processing. Remember, you can mix in Disk File operations and the calling of non-GoldBox programs in the Q-file. Q-files can contain as many entries as you require – in one scenario, a GoldBox user is calling a Q-file with 150 operations in it! Typically you won’t use that many, but this illustrates the power and versatility of Q-files.

Calling a Q-file is simple. A typical call might be:

As the final piece, a basic timer is included with GoldBox so you can run Launch Operations or Q-files on a timed basis. You can setup a maximum of 35 timer calls, by day of the week and time of day (hour and minute). The timer is accessible from {Misc} {Timer Setup}.

Please be aware that the GoldBox Launch Timer will not execute if minimized. It must be maximized and must be the focused application on your screen in order for it to execute. You may want to load it before you leave work, to run, for example, the above QNIGHTLY Q-file.

The GoldBox Launch Timer is provided as is. You can use other timer programs if you prefer, such as one provided with Windows or a shareware program.

{Misc} {GXW Browser} Available for GoldBox-4 and GoldBox-5. This is the most powerful sync-aware browser of GoldMine tables, with multiple capabilities. You can open up any GoldMine table in row/column format. The Contact File you are browsing is based on the 3^rd button entry at the {Service} {Config Screen}. In GoldBox-5, you can setup a menu option in GoldMine to launch the Browser directly. Press F1 at the GXW Browser screen in GoldBox-5 to determine how this is done, and to see the advantages of GoldMine data from another angle. A special DDE hot-link between the GXW Browser and GoldMine is auto-created so records you are browsing cause the Main Contact in GoldMine to appear instantly.

{Misc} {SQL Query Analyzer} Available for GoldBox-SQL. This is provided to work the way GoldBox-SQL processes SQL statements. Whenever you key in a SQL statement in GoldBox-SQL, you always leave off the SELECT command and begin the statement after the WHERE clause. In this way, GoldBox-SQL shields you from remembering table names and how they are joined to other tables. GoldBox-SQL handles that transparently.

{Misc} {Parse ASCII to dBase} This turns an ASCII text file into a dBase table, typically used before importing. If the parser is not working properly on your ASCII file, it is likely the file is non-standard and cannot be handled. In this case, you can try alternative methods, such as bringing the file into Excel or Access. When the ASCII file is clean, the GoldBox parser provides options not found in Access or Excel.

If you have a recurring Import/Update and the source file is always provided as ASCII, in the same format each time, you can launch the conversion into dBase from within a Q-file. See the {Text to dBase4 Conversion} F1 panel for more information.

This is another formidable capability in GoldBox, not found elsewhere. You have over 120 functions to choose from when building filters or field expressions. Each has a separate F1 panel with a full description. These are accessible from the {Contents} tab in the Help system, or by pressing F1 at the Filter and Expression Builder. In GoldBox-4, these are listed near the bottom of GX4GUIDE.DOC, alphabetically.

These include AddlConFld(), Deleted(), DidSession(), Empty(), File(), FileWild(), Flagged(), HistoryCode(), IIF(), InGMLookup(), InGroup(), InOrgLevel(), LatestSync(), LookupDesc(), LookupINI(), MultiTab(), MultiTab2(), ReferValue(), RollupAcct(), RoundRobin(), TabEdited(), VLink2dBase(), VLink2MSSQL(), VirtualFld(), xFrag()

These include ActBanner(), AfterFirst(), AllTrim(), ANSI(), At(), Canada(), Chr(), CountWords(), DupedWord(), DurConvert(), EndWords(), FieldContains(), FirstCharacter(), FirstLast(), FirstWord(), GenBanner(), Generate(), GetaWord(), GetCity(), GetCounty(), GetCustom(), GetDST(), GetGMT(), GetSICcat(), GetSlsTerr(), GetState(), GetTable(), GeTimeDiff(), GeTimeZone(), GetZip(), GrabCity(), GrabState(), GrabZip(), GXProper(), InList(), IsAlpha(), IsFoundIn(), LastFirst(), LastWord(), LinkedName(), Lower(), Ltrim(), Mline(), PadL(), PadR(), PhoneStrip(), PrimEmail(), PullEmail(), PullWeb(), Rat(), Replicate(), SmoothComp(), SmoothName(), SnipWord(), SoundEx(), Space(), StripFrom(), StripKeep(), StripMrMs(), StripSuffix(), StrTran(), SubStr(), TextSwap(), Transform(), Trim(), UDTbanner(), Upper(), UpperStrip(), USA(), Usterr(), Val(), WordOccur(), WordSwap()

These include Int(), Len(), Max(), Min(), Recno(), Round(), Str()

These include AgeByMonth(), AgeByYear(), CDoW(), Cmonth(), CtoD(), Date(), Day(), DoW(), DtoC(), DtoS(), EndOfMonth(), FirstOfMon(), GetAcct(), Month(), ProDate(), RealDate(), Year()

These include Time(), CalEndTime(), Make24Hour(), RealTime()

I wanted to mention some companies and people who have all contributed to make the GoldBox product line the powerful entity it is today.

FrontRange Solutions – Much appreciation for providing a first-rate CRM system that is widely accepted as the best in its class! Also, many thanks to Product Development, Tech Support, Sales & Marketing and Customer Service for countless recommendations through the years. Without this, GoldBox could never have gotten launched.

SJR Productions – Thank you for running a top-notch Addon Store and making GoldBox easily available to end users and resellers.

Canonbury Services Limited – Many thanks for your dedication in providing and supporting GoldBox in the United Kingdom.

Blue Star Corporation – Thanks to Ted Blue for providing the best training anywhere on the usage of Visual dBase and SQL. Check out http://www.bluestarcorp.com

dBase, Inc. – Thank you to the creators of Visual dBase, the programming language used by GoldBox. And great appreciation goes to the contributors of all that excellent support provided on your newsgroups.

Many thanks to Paul Laufer of TeleSage Solutions for his dedication in learning, supporting and implementing GoldBox, and for his many suggestions and tips on improving the product. Check out http://www.telesagesolutios.com

Special thanks to Bob Gannett, Bob Taylor, Ed Siegel, Paul Redstone, Richard Young, Paul Nahas and many other thoughtful members on Tony Rodgers’s great GoldMine forum at http://www.goldmineforum.com Most of these folks dispense free advice on all aspects of GoldMine, GoldSync and add-on products and they are a great service to GoldMine users the world over.

Heartfelt thanks to the dedicated GoldMine Solutions Partners who believed early in GoldBox and its potential. They provided much motivation to keep the product going, and have contributed greatly to the current capabilities. Unfortunately, their names are too numerous to mention here, they run into the hundreds (but you know who you are!).

Most of all, THANKS to YOU, the end user! You are the base, the foundation holding up the rest of the GoldMine pyramid. Without your creative usage of GoldMine and GoldBox in your business, the flexibility and power combined in these two products might never have materialized, but instead may have faded into obscurity years ago.

Warm regards,

David E. Petonic (a.k.a. The GoldBox Godfather)