GoldBox-5 / Data Tools for GoldMine® is a product of:

Redstone SoftBase Co. 10488 Eastborne Ave. #202 Los Angeles CA 90024

Web .......... www.redstonesoftbase.com Phone.............(310)441-0782

Email .......... redstone@earthlink.net Fax................(310)441-0782

A special EXE called GX4TOGX5.EXE is included in your GoldBox-5 package if you are upgrading from a GoldBox-4 system. This copies and converts system files from GoldBox-4 to GoldBox-5. This program should be run successfully only ONCE! Also, if you are pointing to different GoldMine® Contact Files, be sure to access the Saved Setups and change the Contact File paths! If you had Saved Setups for Importing Linked Documents, these must be recreated in GoldBox-5 since a new option was added.

To run the utility: Load it from {Start} {Run} like so: c:\goldbox5\gx4togx5

Select your GoldBox-4 Path.

Click {Go} to run the utility.

>> placing their copy on a client's machine or workstation.

>> connecting their laptop to a client's network and using their NFR copy to run GoldBox operations against a client's system.

>> having multiple GoldMine® systems on one machine and running a single copy of GoldBox against all of them.

Any GoldBox operation which is run against your GoldMine® data requires that you have a licensed copy of GoldBox for that system. If you detect that a reseller is running a GoldBox operation against your data (for example, by connecting their laptop to your network) and you do not have a licensed copy of GoldBox, please inform Redstone SoftBase Company immediately at:

310-441-0782.............(Voice)

redstone@earthlink.net...(Email)

You cannot place your copy on a client's machine, and you also cannot connect your laptop to a client's network and run GoldBox operations against their GoldMine® data. Finally, you cannot use a single GoldBox system against multiple GoldMine® systems on your machine or network. If you are going to use GoldBox for any reason upon a client's GoldMine® data, the client must have a licensed copy of GoldBox.

Thank you for purchasing the finest set of database tools for your GoldMine® system! GoldBox-5 is a Visual dBase7 32-bit Windows application which manipulates the fields, records and files that comprise the backbone of your GoldMine® system: the data itself. It performs specific tasks very efficiently and allows you to "gang" several utilities to produce the desired results. Your increasing familiarity with GoldMine® and GoldBox-5 will make it easier to determine which tools to use for a particular scenario.

For brevity, please note that the word GX5 may be used in the following document to indicate GoldBox-5.

Since one could conceivably use GoldBox-5 for many money-saving purposes within a regular 30-day evaluation period, there is a No-Return policy on the software!!! Please evaluate your needs, view the demo and ask specific questions before buying GoldBox-5. Redstone SoftBase Company will not accept any returns of the product, for any reason. No Exceptions.

Although GoldBox-5 is time-tested and has proven successful in thousands of implementations, there is no implied guarantee from Redstone SoftBase Company concerning its usage. As with any software package, use at your own risk! The author makes no claims and assumes no responsibility for the integrity of your data files. If you are in doubt about any feature, please backup your system.

GoldBox-5 should be installed in a directory called GOLDBOX5 but must NOT be placed as a subdirectory under GoldMine. Also, do not load GoldBox-5 in a directory with long path names. There is no security on GoldBox-5 so you may want to load it on the local drive of the database administrator's workstation. Before loading the program, please see the GETTING STARTED section below.

2. Change to this directory. Example: CD\GOLDBOX5

Pkunzip the file. Example: (Assumes GOLDBOX5.ZIP in Root Drive)

PKUNZIP C:\GOLDBOX5

PLEASE BACKUP CONTACT FILES BEFORE PERFORMING A FILE-ALTERING GOLDBOX-5 UTILITY! It is the only way to insure a reversal of a GoldBox-5 procedure that did not perform according to your plan. Redstone SoftBase Company is not responsible for the integrity of your files. GoldBox-5 works deep within the file structure of GoldMine. Its usage requires some basic database knowledge. GoldBox-5 was designed for those who can get underneath the excellent interface GoldMine® provides and attain an understanding of where, how and why GoldMine® data is stored. If this is not within your immediate realm of knowledge, you might educate yourself with the GoldBox-5 and GoldMine® documentation, or have a GoldMine® Solutions Partner consult with you about implementing this powerful data management tool. FREE support must be limited to very brief inquiries or bug notices. Support answers are not guaranteed to solve your particular issue, although 100% correct answers are the goal.

Voice: This is strictly an as-available service, Mon-Fri, 9-5 Pacific. Support is provided by the author, who may or may not be present during regular business hours. Calls left on the machine are generally not answered since there is no feasible way to absorb the expense of long distance return support calls.

Solutions Partner: If you purchased GoldBox-5 from a GoldMine® Solutions Partner who also implemented it on your system, you should first contact their company for inquiries specific to your setup.

An optional 43,000 record table of USA Zips, States, Counties, Cities, Time Zones and a blank Territory field. After unzipping in the GoldBox-5 directory, run {Service} {Pack GX5 Files} to reset indexes. This is a 1-time download only! This file is called the Virtual Lookup Table and is used by the 10 Get() functions in GoldBox-5, if you choose to use them. They are documented below in the Function Definitions section.

Please note: Any work you do on client's data which uses GoldBox-5 requires that the client purchases a licensed copy. This is true for any situation: whether you are performing GoldBox-5 processing onsite, at your site for testing, at your site for delivery of a final product, or remotely to their site. GoldBox-5 is priced and promoted to exist on every single GoldMine® machine.

Please order from Canonbury Services Limited.

Phone: 0207 359 8524

Fax: 0207 359 7775

Email: sales@canonbury-services.co.uk

Web: http://www.canonbury-services.co.uk

If you will be Importing into Tab Files or Updating: Please open your regular Contact File in GoldMine® if it is not already selected. Go to F-ile, O-pen Database, N-ew. At the Description prompt, key in: Updater File for GoldBox-5. Click on N-ext. At the Create GoldMine® Database prompt (see Hint below), key in a path to the new Updater Contact File. It is critical that the final portion of the subdirectory is called UPDATER since GoldBox-5 looks for it exclusively. Click N-ext. Click N-ext. Click F-inish.

Hint: When adding the Updater Contact file, at the prompt asking for the path, click the ellipsis and get the COMMON file. Then overwrite the word COMMON with UPDATER. That saves keystrokes and makes the path consistent with the path-naming scheme of the Common Contact File.

You must create a special User in GoldMine. Click on F-ile, C-onfigure GoldMine, U-sers File, N-ew. At Username prompt, key GOLDBOX5. At Full Name prompt, key GoldBox-5 User Name. This is all that is required - no password is necessary, and you can assign whatever rights you wish. You may want to assign appropriate rights and password to prevent users from accessing this way.

• Configuration tab
• System Folder
• INIT Folder
• Change the SHAREDMEMLOCATION to 7000 or higher. Chances are it is currently blank.

After you have created your Updater Contact File and added the GOLDBOX5 user to GoldMine, it's time to load GoldBox-5. If you haven't done these things, see above.

A bit more on installing GoldBox-5. Although you can install it on the network, only 1 user can access at a time. GoldBox-5 will address a multi-user version of GoldMine® of any size (remember, the dBase IV version only, not the SQL version!). GX5 itself, though, is a single-user program! Typically, in a network environment, and since GX5 is normally used by the system or database administrator, and, since there is no security on GoldBox-5, you may want to install it on a workstation. Just as long as that workstation can access the network version of GoldMine. You cannot install GoldBox-5 on more than 1 workstation unless you have a separate GoldBox-5 serial number for each install.

The easiest way to load GX5 is to go to {Start} {Run} in Windows and put the load command in. Remember to include the drive letter and full path. Example: C:\GOLDBOX5\GX5

Your command is saved in the {Start} {Run} box so you can easily reload anytime.

You can also setup a shortcut icon, using the above command as the shortcut.

After you have loaded GoldBox-5 the first time and logged in, you can save a few keystrokes by adding your login initials to the above load command. For example: C:\GOLDBOX5\GX5 DEP

That will take you right past the login screen and directly to the Top Level Menu.

You can also load GoldBox-5 with one of these special parameters:

Same as selecting the option at the {Service} {Config Screen}, to NOT USE GoldMine's FIELDS5.DBF database to display Field labels. Sometimes this file gets corrupted.

Finally, there are only 27 (yes, twenty-seven!) other ways to load GoldBox-5, depending on the operation you wish to launch. For more information, see the section LAUNCH CAPABILITY.

This is simplicity itself. Just key in the initials you would like to use for access and press Enter or click [GO]. There are no "wrong" initials and there is no limit on the number of sets of initials which can access GoldBox-5 (but remember, only 1 at a time). If you click [Cancel] you are exited from GX5.

If no GoldMine® User Name is assigned to your initials, you are taken immediately to a list box showing all GoldMine® User Names in your GoldMine® system. Select one that will be linked to your GX5 login initials. By default, this GoldMine® User Name is used for stamping records in the LASTUSER field of GoldMine® records you add or modify, and is also the default User Name on Calendar and History imports.

Remember you can add your login initials to the load statement to bypass the Login Screen.

This has been a much-requested feature by GoldMine Solutions Partners. It is essentially used to shield clients from accidentally altering certain Setups and System Files when the Solutions Partner has complete responsibility for maintenance and implementation of the GoldBox-5 system. Launched batch operations are not affected by Lockout, only certain Interface options.

The feature appears under the {Services} tab.

At the screen, de-select any option that you do not wish to be accessible by the client. When you click {Go}, the information is written to GX5SYS1.DBF - but not the file in your GoldBox-5 root path. When you selected the option, the file was copied to the OUTBOUND path under your GoldBox-5 path.

After making the changes, you can either send the GX5SYS1.DBF file from the OUTBOUND path to your client, or include it in the GX5TRANS.ZIP file if you are also transferring other Saved Setups or Q-files. In either case, the file you send should be placed in the GoldBox-5 ROOT path of the client's GoldBox-5 system - NOT in their OUTBOUND path.

Menu options you have de-selected will not appear at all on the menus. Browser options you de-selected will be grayed out at the GXW Browser.

If you later need to give full access to your client, simply have them erase the GX5SYS1.DBF in the GoldBox-5 Root path. Or, you can send them another one, with your desired options selected or de-selected.

This stores basic setup information about GoldBox-5. The first 2 prompts are critical and must be accurate. Always click [GO] to exit the Config Screen because GX5 has accuracy logic built in to ensure that your paths are correct.

[GoldMine® Root Path] Click to select. This is the path where the GMW5.EXE, DATADICT.DBF, USERS.DBF and most importantly the GM5S32.DLL reside. On some GoldMine® systems, GM5S32.DLL may not be in this path. If not, you will have to copy it there, since GoldBox-5 only looks for GM5S32.DLL in the GoldMine® Root path! The GM5S32.DLL is used by GoldBox-5 for sync stamping. You can (Alt-Tab) out to Windows if you have to locate and copy this DLL into the GoldMine® root path, then (Alt-Tab) back to GX5. GX5 won't let you exit until it sees the file in this path.

(GoldMine® Root Path) This is an editable prompt because you might want to just change a drive letter from the default of C to D or F or whatever. Also, although UNC paths starting with the double-backslash \\ should be recognizable by GoldBox-5, you may have to substitute a standard Drive Colon Backslash instead, as in changing \\ to H:\ .

[GMBASE Files Path] Click to select the subdirectory under the GoldMine® Root Path called GMBASE. This is the path where SPFILES.DBF, CAL.DBF, LOOKUP.DBF, GMTLOG.DBF and other general GoldMine® files are stored. On older GoldMine® systems, these files may actually reside in the GoldMine® Root Path so you may have to do a little checking in Explorer or whatever if you suspect this.

(GMBASE Files Path) Editable if you just want to edit the path directly, as above for the Root path.

[GXW Browser Cont. Path] Always press this button to select the Contact Path, as it is a solid test of whether the GMBASE Files Path is correct. This displays the same Contact Files list you see in GoldMine® if you click {File} {Open Database}. Select the Contact Path you want the GXW Browser to use. Remember, you can always come back to this selection and choose a new one if you want the GXW Browser to open a different Contact File.

(GXW Browser Cont. Path) Edit this prompt only if you have to change a UNC code to a standard Drive-Colon-Backslash (although a UNC Contact Path should work fine).

{GM User} This is a required entry and must match your GoldMine User Name login. GoldMine Software requires that the GoldBox-5 user have Master Rights before running any operations. This value, and the following password (if used) are evaluated by GoldBox-5 to determine if you have Master Rights. The name you key in here is also the one that is stamped into the LASTUSER field on those GoldMine files you modify with GoldBox-5.

(GM Password) This is your GoldMine password that is keyed into GoldMine's login screen, if you use a password. If not, please leave this blank. This entry will display as you type it, but will be hidden when you exit the Config Screen. The password is encrypted by GoldBox-5 and will not appear anywhere in the software. The password will display in UPPER case here, which is fine. This prompt, together with the above GM User prompt, are used by GoldBox-5 to determine if you have Master Rights.

| Displayed Date Format | Highlight the format you wish dates to be displayed in. If you choose 2-year display, the next prompt is critical.

(2-Yr Date Display: Start of 100-year span) Selecting 2-year date display is a bit limiting because you can only see a maximum 100-year span. Mostly this is Ok but what if you are tracking birthdays? Anyway the default of 1910 means that all your dates in GoldMine® must fall within the range of 1910-2009. If you key in 1950, the date span is 1950-2049, etc. This is strictly up to you - if you choose a 4-year date display this prompt is ignored and you can key in dates from 0100 through the next century.

(USA Time Zone) This is only necessary if you are using the GX5 function GeTimeDiff(). That function uses this entry to calculate the number of hours you differ from another Contact's time zone. There are USA time zones which display in the Speed Tip for the prompt.

(Default Zip Length for Get() Functions) Default of 5 is normally Ok. Used only by the GX5 Get() functions for returning values from the Virtual Lookup Table.

[GO] Always click here to exit - don't press Alt-F4 or click the X-cancel. GX5 checks the accuracy of your path entries and informs you if they are in error. Also, your entries are then saved into the GX5 CONFIG.DBF file.

This option packs and rebuilds the index tags of GoldBox-5 system files (NOT GoldMine®). It is automatically invoked upon first time loading or loading after applying a GX5 update. You'll also be reminded to do this if you have not selected the option within the last 45 days.

If you have a Paradox file and would like to use it in GoldBox-5 for Browsing or Importing, this option provides an easy solution. You simply select the Paradox file, which always ends in the 2-letter extension of DB. To make this procedure very simple, GoldBox-5 will always copy the Paradox file to its dBase4 counterpart in the same path, but with the standard extension of DBF. If there was a Paradox memo field (the extension is MB) that becomes the dBase memo extension of DBT. After the copy procedure finishes, you can browse the file via {File} {Browse ANY dBase4 File} or with the BR7 Utility.

This is the screen you will normally be returned to after completing an operation. You'll see the 6 tabs along the top: {Batch} {Misc} {Service} {File} {Help} {Exit}. I realize you might be used to seeing {File} as the first tab in Windows programs but in GX5, the{Batch} tab is where most of the action takes place and I feel it should be the most prominent. To me, {File} means just that, and in GX5 the {File} tab displays various GX5 system files you can browse and setup.

{BATCH} This menu lists all the operations that can be launched (executed from a Command Line, Q-file or Timer). Those options preceded by a checkmark indicate that they can be launched in Interactive Mode. See the section 'Interactive Mode' for more information. Underneath the first 12 options is the 1..2..3..4 section. These options will be populated with your latest Saved Setup accesses, so you can click on one and instantly open the related Setup screen. The {Direct Launch Ops} are those operations which can be launched but which have no Saved Setup.

{MISC} Contains operations that are not launchable. {GXW Browser} applies to GoldMine® file operations, {Q-file Setup} and {Timer Setup} to GX5 procedures. {Interactive Mode} provides an information panel on this powerful capability, documented later in the manual.

{SERVICE} Documented above.

{FILE} Allows access to various GX5 system files for viewing and setup.

{HELP} A single panel is displayed showing general information about GoldBox-5.

{EXIT} Exit out of GoldBox-5. Using this Exit or the one under the {File} tab remembers your latest Saved Setup accesses. These will be displayed in the 1..2..3..4 section under {Batch} the next time you load GoldBox-5. It also releases various DLLs. Always exit this way – please do not click the 'X' at the top right of the screen to exit!

Under {Batch} {Contact Validator} is the 1-2-3-4 section. GX5 remembers the last 4 Saved Setups you viewed and displays the Launch ID here, the most recent appearing first. This lets you simply click on one and advance directly to the Saved Setup Screen. Please note that your most recent settings are preserved when exiting GoldBox-5, but only if you exit via the [Exit] option at the {Top Level} or {File} menus.

Under {Batch} {1-2-3-4} is the option {Direct Launch Ops}. These are 11 other operations you can run in Batch Mode in GoldBox-5, but which have no Saved Setups.

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, CONTACT2.DBF and CONTACT1.DBT.

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 Prev 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-5 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-5 refers to them as the Tab Files. 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 the files together. In GoldMine, this is the Account Number, or Accountno field. If a Main Contact has 27 History records, each of those History records 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-5, Contact2 won't be referred to as a Tab File because in concept it is just an extension of the Contact1 file.

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 Cal.dbf 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.

The file is ContHist.dbf and 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.

The file is ContSupp.dbf and 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 and Links to Documents. 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. 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.

ContGrps.dbf is the Groups file and 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, 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.

Although now a rare situation, you may have duplicate Account numbers in the CONTACT1 file of your GoldMine® system! This situation must be rectified immediately since GoldMine® will not operate correctly with duplicate Account numbers in the Main Contact File. The GoldBox-5 Merge-Purge may "hang" if it encounters duplicate Account numbers.

To determine if you have dupe Account numbers, go to {Misc} {GXW Browser} {CONTACT1 File}. Click the {Break} button, then double-click the Contact1->Accountno field. Run the operation. When you View the list, at the 3rd record the Unique Values number and the Examined number should be the SAME. The HOWMANY column should contain only the number 1. If you have entries with number 2, these are duplicate account numbers! Write down the record number which appears at the far left of the screen for each. Or, [Copy] these records to an SDF file which you can then view in Notepad.

Use BR7 to manually edit the Account numbers of the dupes. Browse CONTACT1.DBF and choose the CONTACC index tag with the [View] button. Click [Goto] to access the record number of each instance of a duped Account number. The record below should have the exact same Account number. Recommend you change the last 3 characters of the Account number to {xxx}. When finished, repeat the above procedure to recheck.

File formats determine how data is stored. For example, GoldMine® uses the dBase IV file format to store its contact and system information, and GoldBox-5 uses the same. For the Import and Export utilities in GoldBox-5 you will need to know the file format of an incoming file (for Import) or how you wish the flat file to be formatted (for Export).

Clipper/dBase3: Index files have the extension of NTX (if Clipper) or NDX (if dBase3), Memo file has the extension DBT. The memo file is where Notes are stored.

dBase IV: Same as above with different memo format. Index file is MDX - it's just one file, which contains "Tags", or sub-indexes. You can have up to 47 sub-indexes inside each MDX file.

FoxPro: Same as above but with different memo format, whose extension is FPT. Index file extension is CDX or IDX. GoldBox-5 can't currently read FoxPro files so you'll have to convert them to dBase before attempting an import.

Paradox: This file format is similar to dBase but is just different enough to not be considered part of the xBase family (Clipper / dBase / FoxPro). As a result, it is now generally considered to be a defunct file format. If you have a Paradox file you wish to browse or import into GoldMine® with GoldBox-5, go to {Service} {Copy Paradox to dBase4 File}.

ASCII Delimited: ASCII means American Standard Code for Information Interchange, and is a common format many different applications use to make data formats consistent between them. You may hear this type of file referred to as "comma delimited". Some call it ASCII 2 but that is incorrect (an assumption that the 2 final characters of the acronym are the Roman numeral 2). In reality, a true ASCII Delimited file is quote delimited, comma separated. It must be in the following form or an error will result:

"S. Holmes","221B Baker St.","London","UK","Detective",38,18420122

"J. H. Watson, M.D.","221B Baker St.","London","UK","Doctor",47,18330425

Fields are numbered from left to right; commas separate fields. The double-quote character surrounds character fields so that commas can appear in a character field without the program thinking that a new field is starting. The last 2 fields in the example are the age and birth date. The age is a numeric field (no quotes) and the birth date is a standard ASCII date field (YYYYMMDD as a numeric field). You will need to use the Date Mask so GoldBox-5 can auto-format it. At the Import Field Template, press [DtMask] at the GoldMine® field and select {yyyymmdd} as the format.

SDF (System Data Format): This is also an ASCII text file, but there are no field delimiters. Each record line is the same length, and you identify fields by number ranges. Typically this might be a file created by a report, written to disk. The above ASCII example looks like this in SDF:

S. Holmes 221B Baker St. London UKDetective 3818420122

J. H. Watson, M.D. 221B Baker St. London UKDoctor 4718330425

The dBase4 DBF file name has a restriction: It cannot begin with a non-alpha character! Always make sure the file name begins with an A-Z character. You can have numbers or underscore in the next 7 characters, but no embedded spaces or special characters. Also, keep the file name 8 characters or less.

A dBase file is made up of fields and they also follow a strict naming convention:

• Each field must be uniquely named.
• Each field name can only be 10 characters in length or less.
• Each field name must begin with an A-Z character. The remaining 9 characters can only be A-Z, 0-9 or the underscore. No embedded spaces or special characters.

Especially with a program like Excel, where column headings become field names, you must ensure the column headings conform to this rigid naming convention!

This is the best way to convert an ASCII file into a dBase file – it is superior to all other known methods.

This option works only for ASCII Delimited files, also known as CSV files or Comma Delimited. For an SDF Fixed Length Text file, which is basically a report format type of text file, use the procedure listed in the "Import/Update" section, or another software package such as Microsoft Access or Excel.

The GX5 Import requires the final Incoming File in dBase format, even though ASCII and SDF files can be auto-converted by GoldBox-5 prior to an Import. But first you need a DBF file with the correct field layout. Previously, you had to create one manually in the BR7 Utility, or in another package such as Microsoft Access or Excel. Now you have a quick and superior method to do this with little or no manual intervention. This procedure even gives you the option of turning long character fields of more than 250 characters into Memo fields!

To properly save an Excel spreadsheet into a 'true' ASCII CSV file, you MUST choose the one further down in the list, the one labeled: Text (OS/2 or MS-DOS) (*.txt) Please DO NOT choose the one labeled Text (Tab Delimited) or the one labeled CSV (Comma Delimited). Remember: always choose the option further down in the list entitled: Text (OS/2 or MS-DOS) (*.txt) That option will use delimiters on character fields where necessary, which is what you want.

[ASCII File] Press this button to select the ASCII file to be parsed. It will populate the entry to the right, into which you can also manually key in the path and file name.

(Field Separator) This defaults to a comma and in almost all cases should be retained. Please try to avoid getting a file or saving a file with anything other than a comma as field separator. However you can key in a different character here if you are sure that the ASCII fields in your file are separated by something other than a comma. The only other occasionally used character for this purpose is the Tab, and you can indicate that by keying in a capital T. If you do use a separator character other than a comma, GoldBox-5 will do an EXACT parse of the file, which will take longer. The fast parse method, which uses Visual dBase's APPEND FROM command, won't recognize anything other than a comma as a field separator. That is important to note if you plan on doing automated conversion with the Text-to-dBase4 utility.

(Field Delimiter) This defaults to a double-quote, which is the generally recognized standard. You can key in another character such as a pipe if that is how character fields are delimited. The word 'delimited' in this context means how character fields in the ASCII file are surrounded. The sole purpose of the delimiter in character fields is so that you can include the normal separator character, a comma, within the field so that the parser does not interpret the comma as a new field. If a file has its character fields delimited by a Tab you can key in a capital letter T to denote. GoldBox-5 will attempt to ignore the delimiter in a text entry if it is not immediately followed by a separator value (comma). In that case, if you had double-quotes within a text entry that you wanted as part of the text, they should be properly interpreted.

(View ASCII Rec) You're going to get to review the field structure before the actual DBF file is created, and to assist, GoldBox-5 parses out an ASCII record. The values from the parsed record display alongside the field layout as a guide. This allows you to go to a particular ASCII record. The default is 2 because the first record may contain field label descriptions, not actual data, and it may be incomplete. But you can view record 1 if you want. Suggest not going too deeply into the ASCII file for a record, as it takes more time to locate the line, the higher it is.

(DBF File Name to Create) Self-explanatory. Note that this file is always placed in the GoldBox-5 root path, and it always starts with the characters DD so you can easily identify it (for later erasure, for example). Key in up to 6 more descriptive characters using only letters, numbers or underscore. No special characters or embedded spaces! Please note that if you are auto-creating Memo fields and you wish to move the DD*.DBF file to another location on your disk, you must also move the DBT file!

• If the DBF file name already exists, you are prompted to overwrite it.
• Once the Optimized Parsing begins it may take awhile, depending on the speed of your machine, how many fields are in each ASCII record and how many total records. The blue bar of the form displays the progress of the parsing. You cannot stop the process once it begins, other than pressing Ctrl-Alt-Del, going to the Task Manager, and ending the task. The process is called 'optimized' because it does not use a predetermined length for each field, but dynamically calculates how long each field will be. If a particular field has no value in any of the records, it is assigned the default length of 5.
• You are put into the Parse Review Screen when the Optimized Parsing completes. You don't have to do anything here, but you can rename fields if you want, to make the mapping easier. Suggest using the actual GoldMine® field name to make it even easier. If you are going to rename fields, make sure you stick to the dBase Naming Convention (see "Import/Update" section), because no checking is done on your field names. Click [Quit] to exit.
• If GoldBox-5 detects that any field length is greater than 250 characters, a message displays with this information. You have a choice: you can have GoldBox-5 chop the field length down to 250, or you can have it create a Memo field in its place to bring in the full value.
• The DBF file with the field layout you just reviewed is now created.
• Optionally, you can append the ASCII records into the DBF file. Most times you want to do this. If there are no Memo fields, the Append operation runs rather quickly. If you do have Memo fields, the Append will happen more slowly, since each ASCII record has to be individually converted into a dBase record. As in the Optimized Parsing, this process cannot be stopped from within GoldBox-5 once it begins. After the Append completes you are taken into a BR7 session of the DBF file. Here, you can review the data and even Modify the Field Layout if desired. Now you are ready to import the DBF file into GoldMine, or use it for whatever purpose you desire.

Please note that if you are doing a recurring import and are always given an ASCII file with no changes to the field layout, you only have to do the above parsing procedure one time. Once the DBF file is created, you can use GoldBox-5's automated Text-To-dBase4 Conversion operation from within a Q-file. See the next page for details on this operation.

Generally there are 2 main parts to GoldBox-5: The User Interface and the GX5 Batch Processor. Each has its own EXE file. The User Interface is GX5.EXE. The GX5 Batch Processor is GX5BATCH.EXE. You will only load GX5.EXE, ever. The GX5 Batch Processor is called by the User Interface, even if you are running a GoldBox-5 operation directly from the Command Line, a Q-file or the GoldBox Launch Timer. If you do happen to load the GX5BATCH.EXE directly, it will do nothing and exit immediately.

The User Interface is where you setup the operations in GoldBox-5. It also contains the procedures that run independently of the GX5 Batch Processor, such as the GXW Browser. Finally, it contains access and maintenance procedures for GoldBox-5.

The GX5 Batch Processor has no interface ability except for the F10 key, which on most operations you can press to Halt the running of an operation. But that's it. Information about the currently running operation is displayed as it progresses. At the end of the operation, an acknowledgement prompt is displayed if you ran the operation from the User Interface. If you launched the operation, it won't display, and the GX5 Batch Processor will either exit or continue with the next operation in a Q-file.

Since the creation of a New Setup is consistent for the 11 operations that require them, it will be generally discussed once instead of for each operation.

There are 26 separate operations you can launch (I.e. automatically execute) in GX5, and 11 of them have Saved Setups. These are records in a GX5 system file called GXSETUP.DBF+DBT. Operations having Saved Setups are the Import/Update, Export, Merge-Purge, Dupes Between Files, Group Builder, Groups-by-Break, Org Chart Creator, Global Delete, Global Replace, Notes Archival and Tab File Log Creation.

When you scroll down the {Batch} menu you will see sub-menus pop up to the right for the above named operations. The first option is always {Saved Setup} and that will be discussed later, so please ignore it for now.

Underneath the {Saved Setup} option will be either a single option called {New Setup} or a GoldMine® File Type. Merge-Purge, Dupes Between Files, Org Chart Creator, Notes Archival and Tab File Log Creation only have the {New Setup} option, because these operations always use the Main Contact File as master. The other operations contain different GoldMine® File Types, and you select one of them depending on the task you are going to accomplish. The File Type you select becomes the master file that the GX5 operation evaluates when processing. These are discussed separately in the documentation section for each.

When you select either {New Setup} or one of the GoldMine® File Types, the New Setup form is displayed.

In the blue title bar, the operation is briefly described.

Following are the items in the form:

(Operations's Launch ID: XX +) The GX5 Batch Processor requires an ID so it knows which Saved Setup to open. All start with a special 2-letter code assigned by GoldBox-5, like so: IG = Import/Update; XP = Export; MP = Merge-Purge; DU = Dupes Between Files; UG = Group Builder; BY = Groups-by-Break; OC = Org Chart Creator; GD = Global Delete; RE = Global Replace; NA = Notes Archival; HL = Tab File Log Creation. The rest of the ID you key in. For Imports, Exports and Groups-by-Break, you can use up to 6 characters. For all the other operations, you can use up to 10. GX5 contains logic that won't allow you to create a Launch ID more than once. After keying the Launch ID, you can either press Enter, Tab or click into the next prompt.

(Setup Description) You have up to 32 characters to describe the purpose of the operation. For the Group Builder, you only have 24, because the Setup Description also doubles as the Group Name. More on that in the Group Builder section. Please note that an entry is required in this and the previous prompt. Also note that you can alter the Setup Description at the Setup Screen, but not the Launch ID. The Launch ID is the DNA of the Saved Setup - you can't change it, and it is absolutely unique.

[GO] Checks the validity of the Launch ID and if everything is Ok, opens the Setup Screen.

[Cancel] Returns to the Top Level Menu.

Following are the items on the Saved Setup form:

| Saved Setups | A list box containing all the Saved Setups for an operation. Highlight the one you want and press one of the 5 buttons below. If you double-click an entry that Setup is opened. If you highlight an entry and press Enter it is also opened.

There are 4 pieces of information displayed for each Saved Setup:

The following describes the 5 multi-colored buttons at the bottom of the form:

[Open] Opens the Saved Setup. Same as pressing Enter or double-clicking.

[Launch] Prompts you to immediately launch the operation. If you answer Yes to the (Confirm) prompt, the GX5 Batch Processor is opened and the operation begins.

[Transfer] A GoldMine® Solutions Partner (GSP) most often uses this option. Typically scenario: A separate copy of GoldBox-5 is purchased for the client (as previously stated, that is required if GX5 operations are to be run against a client's GoldMine® data). The GSP sets up and tests various operations on his or her computer, then wants to transfer them to the client's GoldBox-5 system. Here's how to do this: You, the GSP, set up and tested the operations on your GoldMine® system on your C drive. Your client's GoldMine® system is on their network G drive.

[Delete] The highlighted Saved Setup is deleted. If you are deleting an Import or Export Setup, the related Field Mapping DBF and MDX are also deleted.

[Cancel] Return to the Top Level Menu.

The 12 GX5 operations requiring Saved Setups have a consistent Setup Screen look, and some of the options are identical across all Setup Screens. Therefore those common options are discussed here.

When the Setup Screen form is displayed, the blue bar at the top of the form notes the operation and if appropriate, the GoldMine® Main or Tab master file which is to be evaluated during the operation.

Some of the Setup Screens are rather simple, others are more complex or "busy". For example, the Export Setup Screen is the busiest because there are so many options available (you wanted 'em, you got 'em!). But a busy screen shouldn't intimidate you because there are only a few items which require action. Appearing in Bold designates these. The above mentioned Export Setup Screen contains only 3 items requiring your action - you can ignore the others if you wish and stick with their default entries.

There are 10 items on each Setup Screen which are consistent, and they are discussed here. Any deviation from the generic use of an item is discussed in the section of that operation. There are also Record Scoping items which only apply to Export, Group Builder and Global Replace, and these items appear at the bottom of the Setup Screen.

The 10 common items are at the top of the form and terminate with the [Contact File] button.

(Setup Description) This is the 32-character description of the Setup previously discussed in the "New Setup" section. You can change it here if desired. For the Group Builder, this is the actual Group Name so please exercise caution before changing it.

[Copy] You can copy any Saved Setup to a new one. This conserves keystrokes, especially if you only need to alter a few of the items of a Saved Setup. Pressing this button takes you back to the New Setup form where you will key in a new Launch ID and Description. If the Launch ID is unique, the current Setup is copied to a new one. GX5 won't display this new Setup, though. You are returned to the current Setup Screen and must access the copied Saved Setup from the Top Level Menu. Please note that for Imports and Exports, the Field Mapping Templates are also copied, which is where real keystroke conservation occurs.

[Supt] This button produces an ASCII text file of your entire setup, which is loaded into the viewer. Its major purpose is to produce a file which can be attached to an email message when you need support on a GX5 operation. Send this to either the GoldMine® Solutions Partner or the software publisher when requested. But it's also a nice way to see everything on one screen, especially for Imports and Exports. GoldBox-5 tells you the name of the file it produced, which is always the first 8 characters of the Launch ID as the file name, with an extension of TXT.

[Note] You can track a narrative for each Saved Setup if desired. This doesn't affect the operation at all, it is just provided for your convenience. If you are a GSP it's also a good way to communicate the Setup's purpose to the client, since this entry will be included in the Transfer of Saved Setups.

[GO] Clicking here automatically saves your Setup Screen entries. If something is missing or incorrect, GX5 will inform you of it and you are returned to the Setup Screen. If everything is Ok, the Setup Screen Action Box displays. Select one of the 4 radio buttons:

[Primary Filter] The Speed Tip for this button displays the purpose of the Filter. On some operations, the Filter is optional, on others it is required. Please refer to the specific operation section for more info. You are always taken to the general Filter and Expression Builder form whenever this button is clicked. The Filter and Expression Builder is described in the last section of the manual.

<Primary Filter> Displays as much of the Filter as will fit on the screen. Click the [Primary Filter] button to see the whole thing if the display is truncated.

[Contact File] All Saved Setups require you to select the Contact File upon which you intend to run the operation. A list box is displayed, showing all available Contact Files. Select one, and the result is written into the next prompt.

(Contact File) This is an editable prompt only because GoldBox-5 may not recognize a UNC path (although normally it should). In that case you'd have to change the double-backslash \\ to a standard Drive-Colon-Backslash.

The operations under the {Batch} menu can all be launched via {Start} {Run}, a shortcut icon, within a Q-file or with the GoldBox Launch Timer. For specific launch examples, see the related section for that operation in this document. On many, LAUNCH EXAMPLES are given.

The Launch option lets you use GoldBox-5 as an unattended batch processor. It's easy to do since the main work has already been done in the Saved Setups. In addition, with the GoldBox Launch Timer you can cause any GoldBox-5 batch operation to occur for any day of the week, any time (by minute).

Launching multiple operations requires a Q-file. Please see Q-files

All successful runs of any operation under the {Batch} menu are noted as entries in {File} {Batch Session Summaries}. You can click [View] at any entry for a more readable summary panel.

A launch statement requires at least 3 parameters. Separate each with at least 1 space. CASE is not essential. From {Start} {Run} or a shortcut, include the full path to GoldBox-5.

C:\GOLDBOX5\GX5 DEP /I IGMAIN

{START} {1} {2} {3}

2. The function call letter, preceded by a forward slash. Valid letters:

/A Area Code Split Replacement

/C Copy a Disk File

/D Globally Delete Tab Records

/E Export

/F Text File Conversion to dBase4 DBF file (valid only in a Q-file)

/G GoldMine® Group Builder

/I Import into Main, Tab Files, Main Lookups or Update existing Main records

/J Run a DOS program (EXE) or batch file (BAT) (valid only in a Q-file)

/K Notes Archival

/L Append a Lookup.Ini-type of file into LOOKINI.DBF

/M Merge-Purge

/N Rename a Disk File

/O Organization Chart Creation (do NOT use zero - use the letter 'o')

/P Pack any GoldMine® file

/Q Q-file launch, for Multi-Operation Launch in GX5 Batch Processor.

/R Globally Replace a Field

/S Turn Sync Stamping ON / OFF (valid only in a Q-file)

/T Tag Rebuild for any GoldMine® file

/U Dupes Between Files (Compare 1 Contact File to another w/ Record Match)

/W Wipe (Erase) a Disk File

/X Access GoldBox-5 for file maintenance, etc. (not Q-file launchable)

/Y Groups by Break

/Z Run a Windows Program (valid only in a Q-file)

/X Access GoldBox-5 for file maintenance, etc.

/0 Alter a field in a GoldBox-5 system file

/1 Contact Validator

/2 Write Batch Session Summaries to Text file

3. The Saved Setup name or label. Example: IGFULL

View the LAUNCH ID column at the Saved Setup pick list. For Imports, Updates and Exports, the 3rd parameter is always a DBF file name of the Field Template. Imports and Updates begin with IG. Exports begin with XP. You do not need to include {.DBF), GoldBox-5 adds it for you.

For Merge-Purge Label begins with MP

For Dupes Between Files Label begins with DU

For Group Builder Label begins with UG

For Groups by Break Label begins with BY

For Organization Chart Creation Label begins with OC

For Global Replace of a Field Label begins with RE

For Area Code Split Replacement Full Contact Path

For Notes Archival Label begins with NA

For Contact Validator Label begins with CV

For Text-to-dB4 File Conversion Full Path to Text File

For GoldMine® File Pack GM5 Root, GMBASE or Contact Path + File

For GoldMine® Tag Rebuild GM5 Root, GMBASE or Contact Path + File

For File Command COPY File being Copied (Drive, Path, File Name)

For File Command RENAME File being Renamed (Drive, Path, File Name)

For File Command WIPE File being Wiped (Drive, Path, File Name)

For Running Windows EXE Drive, Path, EXE file (+ optional parameters)

For Running DOS EXE or BAT Drive, Path. EXE file (+ optional parameters)

For Appending Lookup.Ini Drive, Path, Text file (+ optional parameter)

The launch capability allows no changes to the Saved Setup or Field Mapping Templates and runs the complete utility unattended. Please ensure all information in the Saved Setup is correct before proceeding.

Q-files are one of the hidden gems of GoldBox-5, an item which exponentially increases the value of the product. Also known as Program Controllers, you can use a Q-file to perform any number of GoldBox-5 operations, plus run any other self-running, self-closing Windows or DOS program. One user recently reported that they are running 139 separate GX5 operations sequentially from a single Q-file! The more you understand the power of Q-files, the greater control you will have over automated data management.

You must create a "queue" file for Multi-Operation Launching. This might be the case, for example, if you wanted to sequentially launch the following 8 operations: A Text-to-dB4 conversion for the Main Import; a Lookup.Ini append; another append of a Lookup.Ini-type of file; a Main Import; a Text-to-dB4 conversion for the History Import; a History import; an Area Code Split and finally a Groups-by-Break by Key1 Territory. See below for the example setup in the Q-file called QMAIN.DBF.

Selecting this option takes you to a browse of the Q-file Master Setup Table, which is the GX4WQ.DBF system file in the GX5 path. This is used simply to organize your Q-files. Click [Add] for a new entry, or highlight a desired Q-file definition record. If adding, ignore the first field called Copyout, as that is only used for transferring Q-files. Key in a Description and the Q-file name. The Q-file is a DBF file and must begin with the letter Q. This is forced by GoldBox-5. Key in the next 7 characters using only letters, numbers or underscore. Click [Quit] and the Q-file is loaded.

Click [Add] for a new launch record. From the list, select either the Saved Setup of your choice, or, at the top of the list, select the type of launch operation you want which doesn't have a Saved Setup. When you choose an operation without a Saved Setup, GoldBox-5 provides an entry screen for the appropriate defaults and options.

The indexed {Sort} column lets you set the execution order of the operations. Key in any number from 0 to 9999. Please double-check to ensure that the operations are occurring in the correct order.

The {B-cd} column, short for Batch Code, is automatically filled in and should always be left alone.

The next column is {Launch ID or Path Name} and is automatically filled in when you select an operation with a Saved Setup. It should never be altered for those operations. For operations which do not have a Saved Setup, please see the appropriate section above in the Launch area for that operation to determine what goes in here. This column equates to the 3rd parameter of the launch statement examples.

To add a 4th, 5th, 6th or 7th parameter, scroll to the appropriate column and key your entry. Most of the Saved Setups don't require other parameters but some of the Direct Launch Operations do, such as the Text-to-dB4 operation. Using the above example as a guide, entries in a Q-file called QMAIN might be:

0 F c:\GOLDBOX5\infile.txt c:\goldbox5\infile.dbf ASC

10 B c:\\gm5\lookup.ini

20 B c:\\gm5\adlook1.txt APPEND

30 I IGMAIN.DBF

40 F c:\GOLDBOX5\inhist.txt c:\goldbox5\inhist.dbf ASC

50 I IGHIST.DBF

60 A c:\gm5\common

70 Y BYTERRTY

Launch your Q-file just as if it was a single operation. The launch for the above example would be:

GoldBox-5 will load briefly, then the GX5 Batch Processor will execute the Q-file operations. Each operation, when completed, will get its own separate entry in the Batch Session Summary file.

Alternate Launch Option: When you exit from the Q-file by clicking [Quit] at the Browse screen, GoldBox-5 gives you the option of running the Q-file directly, instead of from a launch command.

Please note that some operations, such as the Text-to-dB4 converter, require a Q-file for launch.

By default, each item in a Q-file is processed. However, you can attach a condition to one or more items in a Q-file. For example, you run a Q-file on Monday, Wednesday, Friday, but you want a certain operation to occur only on Friday, such as an update of Accounting data into GoldMine. In that case, you would highlight the appropriate item in the Q-file and press the [Exprn] button. The expression for the example is: Upper(CDoW(Date())) = "FRIDAY"

Please keep in mind when keying an expression that you can only make use of the generic functions in GoldBox-5, such as CDoW(), Cmonth(), Date(), CtoD(), File(), Upper(), SubStr(), Trim(), etc. In other words, use any function which does not rely on a particular data file being open for accurate evaluation. The Q-file evaluation occurs before any GoldMine® or GoldBox-5 data files are opened. If you use a function which relies on an open data file, you will get an error and the process will halt!

If GX5 encounters a condition on a Q-file item, it is evaluated. If the result is TRUE, the item is processed normally. If not, the item is skipped and the next Q-file item is evaluated.

For most operations, you have a maximum of 150 characters for the expression. For Text-to-dB4 you have only 50.

With the GoldBox Launch Timer you can run up to 35 separate timed executables. Although designed specifically for GoldBox-5, you can use it to execute any Windows EXE in any path. The timer is based on days of the week and time by hour & minute.

Prior to the time you wish the operation to launch, load GBTIMER.EXE from Windows. From {Start} {Run} the call would be: C:\GOLDBOX5\GBTIMER.EXE This assumes GoldBox-5 is on the C drive in a directory called GOLDBOX5. Make the changes necessary to map into your GoldBox-5 location.

A small box appears in the middle of your screen to show that the timer is active. When the timer hits the desired day and time, the event is launched. After the event completes the timer is again redisplayed and is still active. NOTE: The timer will only function MAXIMIZED. It must be the focused application.

If you are testing this, please be aware that the GoldBox Launch Timer may not immediately execute after the clock turns over to the target hour and minute. It should execute any time within that minute, and experience has shown that mostly it happens about 30 to 40 seconds into the target minute. If the clock turns over and your program didn't execute, please re-access GoldBox-5 and verify the correct setup of the GoldBox Launch Timer.

Click on the [Close] button to deactivate.

The actual timer itself is a Visual Basic program written by George Hayes of BCI Solutions.

GoldMine® is a tremendous piece of software. It is well designed, feature-rich and allows easy manipulation of its data from an outside program, such as GoldBox-5. This document has discussed GX5 as a batch type of program, where an entire file is evaluated and actions are performed globally. However, GoldMine® allows manipulation via an outside program of just the current record you are viewing, and GoldBox-5 takes advantage of this capability, via GoldMine's LOOKUP.INI and [FileMenu] options.

The LOOKUP.INI logic in GoldMine® allows additional record processing that is automatically invoked in various ways. Two features are the [OnNewRun] and [OnEditRun] sections in the LOOKUP.INI file. These both allow an outside application to be called when a new record is added or when the record is edited. For further information on OnNewRun and OnEditRun, see the GoldMine® Help panel LOOKUP.INI and view Launching Applications when Records are Created and Edited.

The surprise for using Interactive Mode is that you create the Setups or Q-files just as if you were going to run them as standard batch operations! Even the call to run the Setups from the LOOKUP.INI file is identical! So, there is nothing new to learn. You just need to be aware that the operation will work only against the current record you are viewing in GoldMine, if you call it from GoldMine's LOOKUP.INI. If you call it directly, it will run against the entire file, such as in normal batch processing.

You will probably use OnNewRun in the vast majority of cases, so that will be our example. The setup of the call to GoldBox-5 is identical for both OnNewRun and OnEditRun. Here is a typical usage as it appears in LOOKUP.INI:

If you are calling a single GX5 operation at OnNewRun you don't need to set it up in a Q-file first in GoldBox-5. Just call it directly. Use a Q-file only when you want more than 1 GX5 operation to occur upon adding a new Contact record. Let's assume the Q-file you are calling, QNEWREC, looks like this:

1 E XPNEWREC.DBF

2 I IGNEWCAL.DBF

3 I IGNEWPRO.DBF

4 R RENEWREC1

5 R RENEWREC2

6 G UGNEWGRP1

7 G UGNEWGRP2

For field replacement, you'll probably use GX5-specific functions such as GetCounty(), GeTimeZone(), GetSlsTerr(), Generate(), and LookupINI() because these are either not available in GoldMine® or have expanded capability. You can even use GetState() and GetCity() if you don't have GoldMine's Zip lookups. But…there is a potential Big Dilemma: When you add a New Contact in GoldMine, the initial record entry screen does not allow you to key in the Zip Code. You just have the Company, Contact, Phone, Extension and Email Address. It's a problem because the Get() functions in GX5 need the Zip Code to find the values in the Virtual Lookup Table (ZIPTABLE.DBF). And the OnNewRun application executes before you can edit fields in the new Contact record. How can GX5 use a valid Zip Code to return entries from the Virtual Lookup Table when you don't yet have a Zip Code entered?

Solution: Key the Zip Code into the 6-character Phone Extension field. In your first Global Replace Setup, choose Contact1->Zip as [Field 1]. Replace it with the expression: Contact1->Ext1. At the [Field-2] button, select Contact1->Ext1and replace it with: " ". See what you've just done? You have replaced the Zip Code with what was entered in the Phone Extension field in GoldMine, then you've cleared out the Phone Extension field. Now you can use any of the Get() functions, so your [Field 3] value would be Contact1->State and the expression is: GetState(). And that is how your Group Builder setup in item #7 above is able to evaluate whether the Contact is in the state of California.

For field replacement, remember the LookupINI() function if you want to use a LOOKUP.INI file which exceeds 64K. Please see the information on LookupINI() in the Function Definitions section for full information. Normally you would populate the LOOKINI.DBF file first before users start invoking Interactive Mode as a Command Line call. Example from {Start} {Run}:

Note that you can create any number of GX5 interactive operations when adding other record types, such as Details, Addl Contacts, Calendar and History items. Please follow the examples listed in the GoldMine® Help panel. The call to GX5 is the same, it is only the file name which changes. The Contact1 file is shown in the example because that is assumed to be the most likely usage of Interactive Mode.

Fortunately GoldMine® recognizes 'local' Lookup.ini files, that is, a LOOKUP.INI file in the Contact Path on which you are working. If it sees one there, it will read that. Otherwise, it reads the global one in the GoldMine® root path. This is most fortunate because GX5 Setups have the Contact File on which to perform the operation stored in the Setup record. You can see that problems will occur if the operation you are calling has the COMMON Contact File stored in the GX5 Setup, and you are adding a record to the DEMO Contact File. GoldMine® only passes the record #, not the Contact File, to the outside application. Let's say you added a record to the DEMO Contact File and it is record # 22. If there is only a global LOOKUP.INI file, then GoldBox-5 is going to pull up record #22 in the COMMON Contact File and do its operations there. This of course is not what you intended and could cause havoc. To avoid this, you must copy your LOOKUP.INI to all the Contact paths the user will be accessing. In each Contact Path's LOOKUP.INI, alter the OnNewRun or OnEditRun statements so that you get the results you want. If no GX5 operations are to occur for the DEMO Contact File, for example, simply delete the OnNewRun or OnEditRun calls to GoldBox-5. Or, if you want the same operations to occur, you must create separate Setups in GoldBox-5 with the proper Contact File selected! GoldBox-5 makes this easy because you can quickly copy a Setup to a new one, then just alter the Contact File entry. Remember: Separate Setups, separate Q-files and separate LOOKUP.INI files if you want the same GX5 operations to occur on separate Contact Files! It takes a bit of management but there simply is no way around it. Ensure this works properly BEFORE you make it available to your users – test, Test, TEST!

Although invoking GoldBox-5 on creation of a New Contact is very useful, you might also want to select an interactive operation just as if it was part of GoldMine's menu system. The powerful, yet simple to implement [FileMenu] option is how this is accomplished. For more information, go to Help Topics in GoldMine® and do a Find on the word FileMenu. GoldMine® allows up to 9 executable items on their menu system. Below is a typical example of usage:

You wish to pull up a log of History activity to review or print before calling a client. Set up a Tab File Log Creation in GoldBox-5 to include those records you want – the default is all History. Note: The Tab File Log Creation is the easiest one to setup – all that is required is the Contact Path! For details on the options at the Tab File Log Creation Setup screen, see the appropriate section in this manual.

Go to the user's INI file and edit it (in Notepad, for example). If you log into GoldMine® as Dave, you would edit DAVE.INI in the GoldMine® Root path. Look for the label [FileMenu]. If it is not there, add it. Underneath, add your Command Line call as per the example in GoldMine's Help panel. Example:

If you wish to repeat the same operation against Contacts as you access them, you can keep the Interactive DDE Link open by following the instructions on the Message Box. This allows for much faster operation and does not require the constant initialization and release of the DDE conversation.

Each GoldBox-5 system is single user only, but you can have separate licenses, each on a workstation, if you need the Interactive Mode for multiple users on a network. These can all access the same GoldMine® system on the server. If you need multiple licenses at the same site, please contact Redstone SoftBase Company directly – it is not handled by the distributor.

Tracks a Date and Time session record for all batch operations which complete successfully. Records are sorted so the most recent operations appear first. Ideal as an audit trail of successful unattended GoldBox-5 operations. A warning message will appear when this file reaches 10000 records for you to delete some or even zap (clear out) the file.

Since the fields as they display in the Browser are a bit cryptic, you can click the [View] button to see a more comprehensive summary of any record.

This file ships with the most common short titles that may appear for incoming names. It is used exclusively by the Import/Update to do the 'walk-back' procedure on putting the correct Last Name into GoldMine's LASTNAME field. You can alter this file as necessary. Example:

Little Bill Daggett, Jr.

William Munny II

English Bob IV, Atty

Strawberry Alice, CPA

As long as JR., II, IV, ATTY and CPA are in this table, GX5 will 'walk-back' up to 2 short titles to pluck the correct last name in each of the above examples and deposit into the LASTNAME field.

This file displays the results of your most recent running of the Contact Validator. The Company, Contact and Accountno fields are tracked for each contact. The first column is Notes and holds the exceptions for that particular Contact record. Click [Memo] to view. Fields that did not pass the Rules you setup will display the Field Name and Description, the Current Value, and the Expression which was run against the field. Following that are the optional Tab file expressions, showing the Description and Expression.

This is called a "virtual" lookup table because you can use a value returned from it, rather than a GoldMine® field, in your filter expressions. This opens up a whole new set of possibilities in GoldMine® data manipulation. Examples:

• You want to send a mailing to all vendors in Ventura County in California. Problem: All contacts you mail to are tracked as Additional Contacts, where you are not tracking counties. The export filter expression: Contsupp->State = 'CA' .AND. Upper(GetCounty('Contsupp->Zip')) = 'VENTURA'
• You want to use the Group Builder in GoldBox-5 to create a standard GoldMine® Group of all contacts in the West-Central sales territory. In order to centralize the territories, you don't track the territory in a GoldMine® field but instead have altered the Virtual Lookup Table and assigned the territory based on zip code. The Group Builder [Primary Filter]: Upper(GetSlsTerr()) = 'WEST-CENTRAL'
• You are importing records and want to put the USA Time Zone in a User-defined field. Your Field Replace Expression at the Field Mapping Template for Userdef01: GeTimeZone() This is also a great way to get the County into a field, with: GetCounty()

All GoldBox-5 expressions are aware of the Virtual Lookup Table. Remember, you can use a field or value other than the Main Zip Code for the lookup.

Using this file and its associated functions, you can also:

• Increase the width of all 5 fields in the table.
• Besides the 5 default fields, you can use the BR7 Utility to add up to 1000 fields to the table, including a Notes field. Click the [Modify] button in BR7 to add your fields. These can be used with the GetTable() function to be returned as values for a filter or field replace expression.

In addition to the above information, please see the Get() Functions in the "Function Definitions" portion at the end of this document.

If you track SIC codes in a GoldMine® field this file lets you replace another GoldMine® field with its Category description. The associated GoldBox-5 function is GetSICcat(). You must pass in the GoldMine® field name which holds the SIC code. Example: GetSICcat('Contact1->Key3').

This is the same file used when you press [Translt] at the Import Field Mapping Template and key in Field Translations, and also when you use the GetCustom() function for global replacing. You can also use GetCustom() on import as a Field Replace Expression.

An example for using GetCustom() in a Global Replace:

You already replaced Key 1 with a county using GetCounty(). Now you want to lookup the associated Salesperson and replace Key 2. Modify this file like so:

COUNTY SALESPN VENTURA William Munny

COUNTY SALESPN SAN BERNARDINO Ned Logan

COUNTY SALESPN LOS ANGELES Sally Two-Trees

Notice the Label and Field columns DO NOT correspond to the field you are replacing. You could have used anything else descriptive. This gives you an unlimited number of unique custom replace setups.

The Field Replace Expression would be:

GetCustom('COUNTY->SALESPN', 'CONTACT1->KEY2')

The important point in the function call is to separate the field from the file with the <dash/greater-than> characters which you are probably already familiar with.

This file is used by the WordSwap() function, which swaps a word or phrase found in a field with a replacement value from the table. You are responsible for maintaining this file.

WordSwap() is typically used to re-capitalize words (such as Ibm to IBM) or de-capitalize (And to and) after you have used a function to force a field to Proper Case. In GoldBox-5 the [Proper] button at the Import Field Mapping Template and the Proper() function are used for Proper Casing a field.

WordSwap() is auto-invoked when you use [Proper] button at the Import Field Mapping Template; you do not need to call separately. See the Function Definition of WordSwap() for more information on usage.

Similar to the above table and is used by TextSwap(). Whereas WordSwap() looks for text with a space on both sides (word or phrase), TextSwap() looks for literal text no matter where it occurs in the field.

Typically used for Company, Contact and Address translation if you used the [Proper] button at the Import or the Proper() function. Ideal to reformat names like MacDonald, McVee, O'Leary, O'Donnell, etc. See the Function Definition of TextSwap() for further information on usage. Typical entries in this table might appear as:

Maca MacA

Mcb McB

O'l O'L

O'd O'D

Rr# RR#

This is the table which drives the Area Code Split Replacement capability in GoldBox-5. Please see this section for information on how to setup the table.

This option allows you to view the totals that were created on the field for the most recent running of {Misc} {GXW Browser} {Break button}.

This holds sub-filters, expression fragments and sequence number definitions, which are used by MultiTab(), xFrag() and Generate(). For information on each of these functions, please review the "Function Definitions" section at the end of this document. Following are descriptions of the 6 columns in the file.

Def. Label - Required Entry. It is the unique name for each expression and is always the first parameter of MultiTab(), xFrag() and Generate(). It is 11 characters long and is indexed. For the xFrag() and Generate() functions, you can use all 11 characters because only 1 entry is sought in the table. For MultiTab(), you can only use a maximum of 9 characters to identify the sub-filters. The final 2 characters of the label are reserved for sorting. The character that determines sorting is the forward slash (on the question-mark key). You then follow the slash with one character. If you are using MultiTab() to check whether a client has 3 different types of Insurance Detail records, for example, your entries might look like this:

INSURANCE/1 Fire Insurance Contsupp->Contact="Ins: Fire"

INSURANCE/2 Home Insurance Contsupp->Contact="Ins: Home"

INSURANCE/3 Auto Insurance Contsupp->Contact="Ins: Auto"

Your MultiTab() call would be: MultiTab("INSURANCE", "CONTSUPP", "P")

As you can see, only the portion of the label preceding the forward slash was passed in by MultiTab().

Your Definition Label can be shorter than 9 characters when using MultiTab(). Please remember to use the slash sorting immediately after the final character of the label, as in: COMPUTER/1.

Description - Optional. A short description about the purpose of the expression.

None? - Used only by MultiTab(). If you key the word NONE in here, MultiTab() will return a TRUE if NO records were found, based on the Expression.

Expression - Required by MultiTab() and xFrag(). Click [Exprn] for the Filter and Expression Builder.

Places - Required by Generate(). This field and the following are related, and are used only by the Generate() function for the generation of sequential numbers. It defines how many places the number will contain. The number of places in the sequential number determines how many leading zeros are stuffed into the number. For example, if you key 6 into this field, numbers will be returned as 000001, 000002, 000003, etc. If you key 4 into this field, numbers will be returned as 0001, 0002, 0003, etc. Always make

sure you key a value high enough so that you will never run out of numbers!

Current # - Optional. This is used only by Generate(). The starting value is always 0, and if you leave it at 0, the first number will be returned as 1 (with leading zeros). You can key in any numeric value so that the first returned value is one greater. For example, if you keyed in 100000 here, the first returned number would be 100001. When using the Generate() function, GoldBox-5 auto-increments this field.

Entries in this table are usually entered by clicking the [Write] button at the Filter and Expression Builder. This file is used to store any number of filters or expressions which you may want to retrieve while building an expression. They are retrieved by clicking [Get] at the Filter and Expression Builder.

This file is used by the SmoothName() function.

For information, please see FUNCTION / SmoothName()

This file is used by the SmoothComp() function.

For information, please see FUNCTION / SmoothComp()

The GXW Browser contains 7 features not found in the BR7 Utility, and the BR7 Utility contains 6 features not found in the GXW Browser. Between them, you have quite a formidable file manipulation capability. The 7 extra features you get in the GXW Browser are:

7. List records to View/Print, with Filter, Record Limit, Ordering - the [List] button.

The Filtering and Field Expression capabilities in GoldBox-5 are the best in the GoldMine world. Because of GoldBox-5's superior access and powerful function logic, you should never need to create a GoldMine Group to perform a GoldBox-5 batch operation.

A GoldBox-5 filter evaluates to ONLY True or False. What happens after evaluation depends upon the GoldBox-5 operation. In filters, you'll ALWAYS use at least one sign such as EQUALS, NOT EQUALS, CONTAINED WITHIN, etc. Connectors are often used, such as AND, OR, NOT.

Field Expressions differ from Filters in that they usually do not evaluate to True or False, but rather to a literal value. They are generally much shorter than filters and typically don't need mathematical signs or connectors (unless you are using the IIF() function). Functions are almost always used for expressions. The result of the expression is, for example, replaced into a field on an import, or exported out as a value.

In GoldBox-5, filters and expression are stored with each individual setup, but you can also save filters and expressions globally to GBFILTER.DBF in the GX5 directory. This is done with the [Write to File] button. The [Get from File] button retrieves saved expressions.

The length limit on filters or expressions is 254 characters. If you need to shoehorn a long expression into this limit, you can remove ALL spaces. Some functions can be shortened to 4 characters - for example, SUBS can be used in place of the SUBSTR function (substring). UPPER can be shortened to UPPE. You can also use the xFrag() function to use separate 254-character expression fragments. Please see the explanation for the very powerful xFrag() function below in the function listing.

One of the best known tricks to shorten an expression is to use the dollar sign Operator whenever you want to know if a particular value is one of several (or many). Typical example: You want a filter to return TRUE if the State field is California, Washington, Oregon, Arizona, Nevada, New Mexico or Utah. If you build this with an expression assembler, you'll probably get something like: Contact1->State = "CA" .OR. Contact1->State = "WA" .OR. Contact1->State = "OR".....etc. With only 254 characters available, you may run out of space quickly. By using the versatile dollar sign Operator, which means Previous String is contained within the Next String, your expression can now look like this:

Left(Contact1->State, 2) $ "CA.WA.OR.AZ.NV.NM.UT"

Which literally says: Are the first 2 characters of the State field contained in the following string? Notice that there is a period separating the state abbreviations. You must always use a separator symbol in the string list, otherwise the letters would all run together and you might get inconsistent results. Suggestions are the period, comma, slash, hyphen or asterisk.

An expression can be in UPPER, lower or a ComBination of the 2 cases, except when comparing a literal string value, as in the following example: Upper(Left(Contact1->Key1, 8)) = "CUSTOMER"

If you had keyed in "Customer" or "customer", the expression would never find a hit because you converted the Key1 field to UPPER CASE with the Upper() function.

Used between expression fragments - there is always something on either side of a Mathematical Sign.

+ Add Or, add strings ("John " + Contact1->Lastname)

- Subtract Contact2->Utotal - Contact2->Utax

* Multiply Contact2->Uitem * Contact2->Uqty

/ Divide Contact2->Utotsalary / Contact2->Utotemploy

** Exponentiation 25 ** 2 (Answer is 625)

= Equal to Contact1->Key1 = "PROSPECT"

GoldBox-5 assumes that if you are using a single equal sign, then it will only look at the first 8 characters of the Key1 field. So if your Key1 field said "PROSPECTOR", you would get a "hit" on this record anyway. If you need Exact finds, use the double equal.

== Exactly equal Contact2->Comments == "Hold Account" + Space(50)

In this case you want to know if the Comments field ONLY contains the words "HOLD ON ACCOUNT". Since the Comments field is 65 characters, you had to hook 50 spaces onto the end of your string value.

<> Not equal to SubStr(Contact1->Phone1,2,3) <> "310"

< Less than Contact2->Udate < CtoD("01/01/94")

<= or equal to Contact2->Udate <= CtoD("12/31/94")

> Greater than Contact2->Utotsales > 4000000

>= or equal to Contact2->Udate >= CtoD("04/01/94")

$ Previous in Next Left(Contact1->Zip, 5) $ "90210.90069.90405.90034"

This is a very useful operator for shortening long expressions. Please see the above section on LENGTH for more information.

( ) Evaluate First (Contact1->Key1-> = "CUSTOMER" .AND. Contact1->State = "CA") .OR. .NOT. Empty(Contact1->Key2)

Parentheses by themselves are usually not necessary in expressions but they sometimes have a place in math operations and when using the {Or} Connector, as in the above example. In math expressions, the result of whatever is between the parentheses is calculated first. In {Or} expressions, parentheses are good for grouping expression fragments properly.

There are only 3 - And, Or, Not. Mostly you will be using the {And} Connector. The {And} and {Or} must always have something on the left and right. You can start an expression with the {Not} Connector, as in: .NOT. Empty(Contact1->Key3). Make sure a period or a space is on either side of the Connector, otherwise the Expression will be seen as invalid.

.AND.

Previous AND Next statements are True

Trim(Contact1->State) $ "OH.MI.IN" .AND. Contact1->Key2 = "NEW LEAD"

Previous OR Next statement is True

Experience has shown that the {OR} Connector, if not used properly, can cause your filter or expression to not evaluate properly and give you false hits or not enough hits. Mostly you will be using the {And} Connector so first determine if that is all you need. Normally you will not use parentheses in expressions but they are very handy when using {Or}. (SubStr(Contact1->Phone1,2,3) = "310") .OR. (Upper(Contact1->City) = "LOS ANGELES")

Next statement is NOT True

.NOT. Left(Contact1->State, 2) $ "CA.UT" (State isn't California or Utah)

The top 2 text lines, appearing in navy blue, provide custom information about the expression you are creating. They will always change depending upon where in the GX5 program the Filter and Expression Builder was invoked.

The white entry prompt underneath is where you assemble the xBase expression. You can either key it in directly, or assemble it by clicking items from the list boxes below.

The titles above each list box serve a dual purpose: They label the list boxes, and, by clicking them, allow you to insert the currently highlighted item in the list box at the cursor position in the entry prompt. Rest the mouse over any of the titles, and the Speed Tip explains how to do this. You first highlight the item in the list box; make sure the cursor is resting exactly in the entry prompt where you want the item inserted; then you click the title button.

The 3 list boxes categorize the fragments that make up an xBase expression. To insert an item onto the end of the entry prompt, highlight the item and double-click it. If you scroll down the list boxes you'll see more items for all of them. In effect, all keys on the keyboard are listed as items, allowing you, in effect, to build the expression entirely with the mouse!

In the Functions box, the functions are initially sorted by type. If you want to sort them alphabetically, Double-Right-Click in the box. This is a toggle between Category and Alphabetical sorting.

The 7 buttons that appear along the bottom are described as follows:

[GO / SAVE] Exits the Filter and Expression Builder and writes the current expression into a field or a Setup Screen item.

[Show Result] Displays the result of the expression. This works in most cases, and is generally used to verify that you have no syntax errors in the expression. It is a rough guide that your expression is Ok or not Ok. When it works, it will return a TRUE or FALSE for a filter and a hard result for Field Expressions. If the button returns a FALSE, it does not mean the expression is in error - it means the record you are on (normally record 1 of a GoldMine file) doesn't happen to meet the filter condition. An error box doesn't necessarily mean the expression is syntactically incorrect, but usually it does, so please recheck the expression. Normally the only times this will return an error box is when you have a pipe expression for Updating, or when keying expressions for one of the GoldBox-5 system files. To repeat: View this button as a guide and not as a definitive indicator that your expression is valid or invalid.

[GM Filter] Displays a description list box of your GoldMine filters. You can select one to be appended onto the end of the current expression. Please be aware that most GoldMine filters will work in GoldBox-5, but most GoldBox-5 filters won't work in GoldMine (because of the extra functions provided in GX5).

[Write to File] Writes the current expression to the global GBFILTER.DBF file, allowing you to retrieve it at a later time (see following button). You are prompted for a Description, and then both the Description and the Expression are written into the GBFILTER file.

[Get from File] Allows you to get, or retrieve, a previously written expression from the GBFILTER file. GX5 will always append the expression onto the end of whatever you currently have in the entry prompt. If you want a complete overwrite, make sure you blank out the entry prompt first.

[Cancel / Abort] Exits the Filter and Expression Builder but does not return the expression to the calling screen.

Think of functions as the most reliable friends you ever had. When you call them, they always give you something back – ALWAYS. You call the function, it does some work or calculations, and gives you back the result. Sometimes you can call the function by itself, such as Date(). This function knows to give you back the current system date. Other functions need something to work with so they can do their jobs and return the correct result. Those little items you pass to the function are called parameters. These are the values found between the parentheses of the function, each separated by a comma. They are "passed in" to the function for processing. For example the SubStr() function requires 2 and sometimes 3, as in: SubStr(Contact1->Company, 1, 1), which returns the first character of the Company Name.

All functions conform rigidly to this rule: They evaluate whatever is between a set of parentheses - they MUST begin with a right (open) parenthesis, and end with a left (closed) parenthesis. Make sure you do not use the square or curly brackets. The "legal" parentheses appear on the number row of the keyboard.

The incredible thing about functions is that you can nest them, which means using multiple functions on a single field. For example: Strzero(Val(SubStr(Contact1->Userdef03,1,6))) is a valid expression. If you count the number of right parentheses, they must exactly match the number of left parentheses. Typically, when expressions are invalid, there is a missing or misplaced parenthesis. GoldBox-5 always evaluates the 'deepest nested' function first, and works outward. In the above example, first the result if the SubStr() function is returned, then the Val() function turns that into a numeric value, then the StrZero() function turns it back into a String (a.k.a. character) value and pads it with up to 6 leading zeroes.

In the long description of each function the {Param} columns list the parameters you can pass. The word {reqd} indicates that the parameter is required - you will get an error if it's omitted. The word {opt} indicates that the parameter is optional. In the function examples strings are surrounded, or delimited, by the double-quote character. Remember that you can use single-quotes or the square brackets to surround a string as long as they are consistent. Following are the 3 legal string delimiters:

' ' " " [ ] Examples:

'D. Petonic' "D. Petonic's PC" [Dave says: "Get GoldMine - it's great!"]

This function will only work for {Batch} {Global Replace}. At the [Field 1] button, select Contact1->Notes. For the Field Replace Expression, select this function.

Please see the example at the page bottom of {Batch} {Global Replace}. Act! banners must be in the exact format in the example in order for this function to recognize them.

A GoldMine® User Name. If not passed, the GoldMine® User Name assigned to your GoldBox-5 login initials is used.

The converted Main Notes field.

ActBanner() or ActBanner("DAVE")

Although this is typically used by the Export, you can also use it for any utility which allows you to evaluate a Tab file as the master file. Typically, this would be the Global Replace or Group Builder utilities.

The word "CAL" or "CONTHIST", surrounded by quotes. This should match the Tab file you are evaluating as master (either the Calendar or History file).

The Additional Contact field, surrounded by quotes.

The value of the Additional Contact field you passed in as the 2nd parameter. If no Addl Contact was found, returns a blank space.

Returns the Reference field value of the related Additional Contact.

Returns the Merge Codes field value of the related Additional Contact..

Everything after the first space in a character field.

Result: E Petonic (Assumes the name is David E Petonic)

The smaller date value, as a true DATE. This can be an expression, a date field, or even a date stored in a character field (you would surround it with the CtoD() function).

The larger date value, as a true DATE. If you do not pass this parameter, the current system date is used.

The number of months between the 2 dates as a NUMERIC value.

AgeByMonth(Contact1->LastDate) > 6

Returns TRUE for Main records which have not been modified in at least 6 months

Returns TRUE if no Sale was closed within the last 9 months

The smaller date value, as a true DATE. This can be an expression, a date field, or even a date stored in a character field (you would surround it with the CtoD() function).

The larger date value, as a true DATE. If you do not pass this parameter, the current system date is used.

The number of years between the 2 dates as a NUMERIC value.

Assuming that the person's birth date is stored in User-defined #1 in character format, returns a TRUE if the age of the person is 50 or over.

The STRING (or Character field).

The trimmed STRING. If the string was empty, returns a null.

The field name.

The field value with the special international characters intact.

(Assumes the incoming DBF file is called Incoming – substitute actual file and field.)

A STRING value, such as a space or comma or a particular word.

The STRING or field you are searching.

NUMERIC position within the string.

Pulls out the first word of the Contact Name, typically the First Name, which might be replaced into the GoldMine® DEAR field.

The time in the form hh:mm as a STRING.

The number of minutes the event lasted as a NUMERIC.

An event ending time as a STRING in the form hh:mm.

CalEndTime() Result: 13:45

Assumes Calendar Activity Time is 12:00 and the Duration is 105.

A field or STRING. If not passed, Contact1->State is assumed. You must surround the field with quotes.

LOGICAL True or False.

Export Canadian prospects

The day of the week as a STRING.

CDoW(CtoD("02/01/97")) Result: Saturday

The ASCII number of a character (or byte).

STRING - The ASCII character assigned to the ASCII number.

Character Return plus Line Feed

The month as a STRING.

Result: February

The STRING, CHARACTER field or MEMO field. If passing in a Character or Memo field, do not surround the field with quotes.

The number of words in the string, as a NUMERIC.

Result: 6

Result: 3 (Assumes David E Petonic)

The DATE value as a STRING.

The passed STRING value as a DATE.

Evaluates whether the Calendar Activity Date is in the month of February, 1997.

Returns TRUE if the Calendar Activity Date falls within the previous week.

The day of the month as a NUMERIC.

Result: 1

TRUE if the record is *deleted* (deletion marked). Useful only for the GXW Browser. You will notice that *deleted* records cause the Status Bar to turn blue when you are resting upon them, making it easy to identify them.

When doing a [Srch] in the GXW Browser. This will allow you to access each *deleted* record sequentially, if you continue to press [Srch].

On a GoldBox-5 Export, where many DBF tables are opened at the same time, this ensures you want to check the CONTACT1 file.

The Batch Utility Code as a STRING.

The Batch Utility Description as a STRING.

The Date the Batch Utility began. If not passed, the current date is assumed. Normally you would not pass this parameter, as you usually want to evaluate current operations.

TRUE if the Batch Operation is found in the Batch Session Summaries file, FALSE if not.

Returns TRUE if the Pack of CONTSUPP was successful today, FALSE if not.

The day of the week as a NUMERIC. Sunday is the 1st day of the week.

Result: 7

The DATE value.

The passed DATE as a STRING.

Returns who modified Main Contact record as: DAVE at 02/01/97.

The passed date as a STRING in the form yyyymmdd.

Result: 19970201

If you are doing a Global Replace on the Contact1->Contact field, typically you would put the following as the Primary Filter:

DupedWord('Contact1->Contact') (One parameter is passed)

The Field 1 Replace expression would be:

DupedWord('Contact1->Contact', 1, 1) (Three parameters are passed)

The character field, surrounded by quotes.

Any value, such as the number 1.

Any value, such as the number 1.

If you pass 1 parameter, returns a TRUE if a duped word is found, FALSE if not.

If you pass 2 parameters, returns the duped word, capitalized. If you pass 3 parameters, returns value of character field with the duped word removed.

The following examples assume the Contact1->Contact field, and the record currently being evaluated contains: Dave Petonic Petonic

Returns: True

Returns: PETONIC

Returns: Dave Petonic

The number of minutes, as a NUMERIC value. See the Example below for converting when the Incoming Duration value is a number of hours. If the Incoming Duration value is stored in a Character-type field, you must surround it with the Val() function to turn it into a NUMERIC value.

The Duration in the format hh:mm:ss. Please note that the final portion, the seconds, will always be returned as 00.

Result: 04:42:00 (which says: 4 hours, 42 minutes)

Result: 01:30:00 (This assumes the incoming field Infile->ActyLength is a Character-type field, and is stored as hours. In the above example, assumes the Duration was 1.5, meaning we had to multiply this value by 60 to get the number of minutes.)

Any valid field type.

A LOGICAL True if the value is empty, a FALSE if not.

Empty CHARACTER field: Blank

Empty NUMERIC field: 0

Empty DATE field: / /

Empty MEMO field: Not Recognized! See IsBlank()

Empty LOGICAL field: .F.

.NOT. Empty(Contact2->Prevresult)

Assumes you are importing into Addl Contacts. This would be the filter to key in when importing into the Updater file.

Assumes replacing Key1 with "PROSPECT" if it is empty.

The last day of the month as a DATE.

Result: 02/28/97

The STRING, CHARACTER field or MEMO field. If passing in a Character or Memo field, do not surround the field with quotes.

The First and Last words of the string, separated by a single space.

Result: ONE SIX

Result: David Petonic (Assumes David E Petonic)

Unlike with the dollar sign operator, this function is not case sensitive. All values are automatically UPPER-CASED by GoldBox-5 during the field checking. See the example below.

The field name. Do NOT surround by quotes.

The value or values to be checked in the field. If passing in more than one value, you MUST separate each by the pipe character (normally this is the shift-value of the backslash key).

TRUE if the field contains one of the passed in values, FALSE if not.

Returns TRUE if the Merge Codes field contains ABC, DEF, GHI or JKL.

If you did not use this function, the dollar sign operator equivalent would be:

"ABC" $ Upper(Contact1->Mergecodes) .OR. "DEF" $ Upper(Contact1->Mergecodes) .OR. "GHI" $ Upper(Contact1->Mergecodes) .OR. "JKL" $ Upper(Contact1->Mergecodes)

The file name, with full path.

TRUE if the file is on disk, FALSE if not.

This assumes you are doing a global delete on Linked Document records where the document itself does not exist on disk. The file name is stored in the Address1 and Address2 fields of the Supplemental record.

The file name, with full path.

TRUE if at least 1 file matching the mask is on disk, FALSE if not.

This should return TRUE, since MAILBOX.DBF is in GoldMine's GMBASE path.

A space is interpreted as a non-keyboard character, as is a Carriage Return/Line Feed.

The field name. Do NOT surround by quotes.

A string of keyboard characters, surround by quotes. Please note that if you do pass in your own string of characters, the function will interpret this string literally – it is NOT case sensitive!

Strips any non-keyboard first character from the Comments field.

Keeps stripping the first character of Key1 until a number is encountered.

The GoldMine® field on which to perform the conversion. If you do not pass this parameter, Contact1->Contact is assumed (Main Contact name).

Any value, such as the number 1. If the name is LastName FirstName (there is no comma separating the names, just a space), pass this parameter. Please note that if you need this capability, you must pass in the field name as the 1st parameter regardless.

The STRING in FirstName LastName format.

Result: David E Petonic (assumes name as Petonic, David E in GoldMine).

For an Additional Contact name.

Result: David E Petonic (Assumes name was Petonic David E in GoldMine).

Although you will usually be evaluating past months, you can just as easily evaluate months in the future, such as those found in the GoldMine® Calendar. You could, for example, build a group of contacts who are scheduled for an appointment sometime in the next 3 months. Once the Group Setup is built, it doesn't need to be altered as you normally would if hardcoding the date range.

The date to be evaluated, as a true DATE value. If you do not pass this, the current system date is used.

If you want to address any month other than the current month, you must pass the 1st parameter and a number here. A negative number will walk backward from the Date you passed as the first parameter. A positive number will walk forward. When passing a negative number, ensure there is no space between the minus sign and the number.

The first day of the month, as a DATE value.

Below examples assume a pass-in date, or current date, of July 5th, 1998:

(Returns 07/01/98)

(Returns 04/01/98)

If building a Group, for example, this returns a TRUE if the Completed Sale Date is greater than or equal to the first day of the previous month and is less than or equal to the last day of the previous month.

If building a Group of all contacts who have a scheduled Appointment during the next 3 to 4 months, this filter will return TRUE if the Appointment is scheduled for July, August, September or October 1998.

The first word from the passed in field – everything up to the first space.

Result: David (Assumes the name is David E Petonic)

TRUE if the GoldMine® record has been Flagged.

This function will only work for {Batch} {Global Replace}. At the [Field 1] button, select Contact1->Notes. For the Field Replace Expression, select this function.

Any Notes line that begins with a date is converted to a standard banner, and everything past the Date is bumped down to the next line. The function will ignore any user name or initials, or any time in the generic Date banner, because these are too inconsistent to accurately parse. The generic time 12:01am is used for all converted banners.

If the date is followed immediately by a colon, the function will transparently handle it.

A GoldMine User Name. If not passed, the GoldMine User Name assigned to your GoldBox-4 login initials is used.

The converted Main Notes field.

GenBanner() or GenBanner("DAVE")

Assumes a Notes entry of:

Will convert to:

Most of the time, you will just use the standard capability of this function, by passing in a single parameter. However, flexibility has been added to this function to allow more control. Please see the paramater choices below.

The Definition Label of the expression in the Auto-Replace table.

The numeric position in the sequential number, after which you want to insert a character value.

The character value you wish to insert somewhere within the sequential number. This will be inserted AFTER the above position.

The number to use as an Increment value. Default is 1, which increments the sequential number by 1. If you pass the number 5 as an example, the numbers will be incremented by 5.

An indicator to use Time logic if you want to increment time by minutes.

A sequential number in the form of a STRING.

Returns ID#000023. For the next record, returns ID#000024, etc. The {ID#} is the prefix and is optional.

Scenario: You are importing 100 Scheduled Calls into the Calendar with GoldBox-5. You wish to schedule the time of the calls at 2-minute intervals. You want to start the first call at 1:00 pm.

Go into {File} {Sub-Filters} and add a new entry. Make the Definition Label MINUTES2. Make the PLACES value as 4. Make the CURRENT # as 1258. This means you want a 4 digit number generated. The Generate() function first adds the Increment value to the Current # before doing anything else, so your first Time would actually be 13:00 (1:00 pm).

At the Import Field Mapping Template, plug in your Calendar fields as desired. Make sure you plug in an Activity Date, since that is a required entry. At the Activity Time field, press {Exprn} and key in the above entry in bold as the expression. What happens is the following:

GoldBox-5 looks for the MINUTES2 entry in the Sub-Filters file. It adds 2 to the current value (based on the number 2 as the 4th parameter above). Because you passed a 5th parameter of 0, that tells GoldBox-5 to properly increment time, so that when the value is 1358, the next will be 1400 instead of 1360. You also want to insert a colon in the middle of the generated number so the time format conforms to GoldMine's standard time format of hh:mm. The 2nd parameter above (2) means to insert the character value after the first 2 characters of the generated number. The 3rd parameter is the character value you wish to insert.

If you are appending 100 Scheduled Calls into Calendar, the result of the above is that the calls will be scheduled 2 minutes apart, starting with 1:00 pm and ending with 4:18 pm.

NOTE: If you are doing this on a regular basis and want to reset the time in the Sub-Filters file back to 1258 for the next time, you can pass in a Q-file entry to accomplish this. In your Q-file, select {Add} and choose the entry near the bottom of the list called {0 REPLACE FLD IN GX5 FILE}. Edit as follows:

LAUNCH ID = SEQNBRS (The name of the GX5 file you wish to modify)

4TH PARAMETER = MINUTES2 (The Definition Label of the entry you wish to modify)

5TH PARAMETER = SQ_NUMBER (The Name of the Field you wish to modify)

6TH PARAMETER = 1258 (The value you wish to place into the SQ_NUMBER field)

This is the same as the first example above, except you wish to skip ID numbers by 3 instead of the default of 1. The 2nd parameter is the same as the PLACES value, and the 3rd parameter is simply 2 double-quotes with nothing in between them. You are passing dummy values for the 2nd and 3rd parameters because the Skip value is the 4th parameter.

The ACCOUNTNO field. If omitted, Contact1->Accountno is assumed.

The Creation Date as a true Date.

Assumes Contact1->Accountno starts with A00229. Returns 02/29/2000.

Returns same as above. Please note that even though you are passing in the Accountno field from History, it is still the Creation Date of the Main Contact (CONTACT1).

The STRING, CHARACTER field or MEMO field. If passing in a Character or Memo field, do not surround the field with quotes.

The number of the Word you wish to get.

The numbered Word as a string.

Result: FOUR

Result: E (Assumes David E Petonic)

There are 2 indexes on the VLT: The main one on the PostalCode and the 2nd on City plus the first 3 characters of the State field. By default, GoldBox-5 assumes you want to use the PostalCode index when using the Get() functions.

Most of your work will be in the setup of the VLT's Territory field or other fields you have added. You'll probably do global deletes to start with if you want to remove records from the table that don't affect your business. The rest will be global replaces. You can do the setup within GoldBox-5 via the {File} {Virtual Lookup Table} option. This will put you into the GXW Browser.

The interaction of the Get() functions with the VLT is fairly easy to understand. They all work the same: You pass in a value (normally a GoldMine® field, by default the Contact1->Zip field). GoldBox-5 opens the VLT and attempts to find the value based on one of the 2 indexes (the default is the PostalCode index). It then returns the found value, based upon which Get() function you used. This value can be replaced into a field, exported, imported or even used in a filter. That's why it's called the Virtual Lookup Table. You can use a VLT value in a filter even though that value may not exist in GoldMine!

The 10 Get() VLT functions are:

Returns what is in the TERRITORY field.

Returns what is in the POSTALCODE field. For this, of course the Zip code is not used for Lookup because that is what you wish to know. You would therefore pass in the following:

You are passing in 35 characters of the City field and 3 of the State, plus the number 2 as the 2nd parameter, indicating you wish to use the 2nd Ziptable index.

Returns what is in the ST field.

Returns what is in the COUNTY field.

Returns what is in the CITY field.

Returns what is in the field name you passed in. See below, {Param 3 (opt)}

Returns TRUE if the Contact complies with Daylight Savings Time plan.

Returns Contact's difference in hours from Greenwich Mean Time (GMT). For example, Eastern time is 5 hours behind, Pacific 8 hours behind, etc.

Returns difference between YOUR time and Contact's time as # of hours. The {Service} {Config Screen} now has 2 prompts to track your Time Zone and whether you IGNORE the Daylight Savings Time plan. These are used to accurately calculate the time difference whether you both use DST, you do but the Contact doesn't, or the Contact does but you don't. For example, if you are in New York and your Contact is in Los Angeles, this function would return -3, indicating 3 hours behind. If you are in Denver and your Contact is in Chicago, the function returns 1, indicating 1 hour ahead. If you are in Phoenix and the Contact is in San Francisco and the date is July 31st, this function would return 0, since Daylight Savings advanced the clock in San Francisco but is not observed in Arizona. This function returns 99 if the passed-in Zip is invalid, not found in ZIPTABLE or is a non-USA postal code.

Returns 1 of 7 USA time zones for the Contact as Atlantic, Eastern, Central, Mountain, Pacific, Alaska or Hawaii.

You'll probably use the GetSlsTerr() function the most, so that is the one that will be fully described.

If no parameter is passed, GoldBox-5 assumes you want to lookup the value of the Contact1->Zip field in the VLT and return the whole value of the Territory field. There is a prompt in the {Service} {Config Screen} called {Sub Zip}. This defaults to 5, which is the standard USA Zip Code length. Change if necessary.

STRING: You can pass a field other than ZIP, or even a literal expression. It must be surrounded by quotes, as in: GetSlsTerr("SubStr(Contact2->UserDef01, 1, 5)")

NUMBER: Must be 1 or 2. This designates which index you want to activate in the VLT for the lookup. The default is 1, the PostalCode index. However, you might wish to return the Territory based on City+State, which is the 2nd index. Your expression would be: GetSlsTerr("Contact1->City + SubStr(Contact1->State,1,3)", 2)

STRING: This parameter only relates to the GetTable() function. It is the name of the field you added into the VLT with the BR7 Utility. It must be surrounded by quotes. The following example assumes you used [Modify] in the BR7 Utility to add a field called METROCODE: GetTable("SubStr(Contact1->Zip, 1, 5)", 1, "METROCODE")

STRING: The name of the label in the Field Translator Table, surrounded by quotes.

STRING: The name of the field holding the value to be checked in the Translator Table.

The STRING replacement value from the Field Translator Table.

STRING: The GoldMine® field containing the SIC code, surrounded by quotes.

The SIC Category as a STRING.

Click [Exprn] at City, State, & Zip fields. In the Expression column, put the following: (Assumes an Incoming File called INDATA.DBF; substitute your own file name)

The Grab() functions only work if the City-State-Zip field contains all 3 values. If the incoming value only contains City and State, you'll have to add the Zip Code to the expression, as in: GrabCity("Trim(InData->Address) + Trim(InData->Zip)").

The Field or Expression containing the City-State-Zip value, surrounded by quotes.

The stripped City, State or Zip, depending on which Grab() function you used.

A field or STRING.

The STRING in Proper Case format.

GxProper("DAVE PETONIC") (Result is Dave Petonic)

Typically, you would use this function to export a calculated field. For example, when exporting from History as master, you would press {Nu-Field} at the mapping screen. Key in HistoryType as the name of the exported field. Select this function call as the expression.

The Record Type field of History or Calendar. If you select this function call out of the functions box at the Expression Builder, the default is Conthist->Rectype. You can also pass in Cal->Rectype if you are evaluating Calendar as the master file.

Any value, such as the number 1. This is only used for History records. It will return either the word Successful or Unsuccessful for completed Appointments, Calls, Events, Other Actions, Forecast Sales or Next Actions.

Returns, for example, Forecast Sale if the current History Record Type is 'S'.

Returns, for example, Appointment if the current Calendar History Record Type is 'A'.

Returns Successful or Unsuccessful based on the Param 2 description above.

The condition to be evaluated. Any legal expression can be entered.

What should be returned if the condition is True.

What should be returned if the condition is False.

The result of the 2nd parameter if the condition is True. Otherwise, the result of the 3rd parameter is returned.

Assumes you are exporting in GoldBox-5. You want to export the word BLANK if the Key1 field is empty, otherwise export the field value.

The first 2 examples are the simplest use of the function, for Main Contact and user-defined fields. The 3rd example is examining the Result Code in History for Calls only. In this case you must pass in the Field Code, which is MCALLRSTCD. Many of the Calendar and History fields use a Field Code in the GoldMine® Lookup table because of the different activity types. To see a particular field code, choose option {Batch} {Verify Lists}. In the Fields Pick list, the last column displays the field codes for Calendar and History.

Example for usage: You want to Globally replace the Reference field with a beginning asterisk for all Completed Call History records where the Result Code is NOT in the Lookup list. Your global filter expression would be:

.NOT. InGMlookup("Conthist->Resultcode", "MCALLRSTCD") .AND.

Conthist->Rectype = "C"

Your Field Replace expression would be:

"*" + Conthist->Ref

The GoldMine® Field Name, surrounded by quotes.

The Field Code for Calendar and History fields.

TRUE if the Field value has an F2 Lookup entry.

The GoldMine® User Name (the owner of the group).

The Name of the Group.

Any value, such as the number 1. Used only if you want to use the InGroup() function while Updating.

TRUE if the contact is found in the group, FALSE if not.

An ARRAY which contains the list values. Don't be intimidated by the word "array" - it's very easy to create. Within curly brackets, you place the string values, each surrounded by quotes and separated by commas. That's all there is to it.

The GoldMine® character field name, surrounded by quotes.

LOGICAL True if any of the passed list elements is found within the GoldMine® field.

Returns True if Crosby or Stills or Nash or Young is found somewhere within the CONTACT field; otherwise it will return False.

Example: In GoldBox-5, you created Org Charts with {Batch} {Org Chart Creator} where the Break expression is the Key1 field and the Org Title expression is "TERRITORY - " + Contact1->Key1. In GoldMine, the Org Charts appear as TERRITORY - EAST, TERRITORY - CENTRAL, TERRITORY - WEST, etc., with the Members of each territory displaying underneath. You wish to roll up Completed Forecasted Sale amounts from the Members of each Territory and write the total amount into a user-defined field called Contact2->uSalesTot for each unique Territory.

Once the Org Charts are created, use the 2-step Data Shuffle to perform the rollup.

1. Export from History using the following global filter:

ContHist->RecType = "S" .and. InOrgLevel("TERRITORY", 1, "MEMBER")

Which says: Export the record if it is a Forecasted Sale and the Main Contact is a Member of the TERRITORY Org Chart, Level 1. You'll note that you can pass in a fragment of the Org Chart Title - this way, all Territories are picked up.

At the Field Template, select the ContHist->Accountno field and click [Exprn]. Key in: RollupAcct("TERRITORY") Also, select the Forecast Sale field ContHist->Duration.

2. Select {Batch} {Import/Update} {Update Main Fields}. Plug the ACCOUNTNO into the {Rec Match > ACCOUNTNO field for Record Match. Plug DURATION into the Contact2->uSalesTot field, which should be numeric. Click [Incrmnt] to turn *INCREMENT* on.

3. At the Import Saved Setup screen, you may wish to create a Group of matched records so in GoldMine® you can scroll through Top Level records that were incremented.

A fragment of the Top Level Title surrounded by quotes, or, a space if you want to address a Section Level regardless of the Top Level Title. Typically, this might be the case where the Company Name is the Top Level Title, but there is a Section called SALES under it.

The Level as a NUMBER. Pass 1 for the Top Level, 2,3,4,5 or 6 for the Section Levels.

The word "MEMBER" or "LEVEL".

A fragment of the Section Level Title surrounded by quotes. This is not required if you passed in 1 as the 2nd parameter.

TRUE if the Org Chart record was found, FALSE if not.

Returns TRUE if the record is a Member of the Sales Section under IBM as Top Level.

Returns TRUE if record is a Member of any 2nd Level Sales Section.

Returns TRUE if record IS NOT a member of a Territory Top Level.

The NUMERIC value.

The NUMBER (whole number - no decimals).

Result: 224

LOGICAL True if the first character is A-Z, a-z.

The STRING or Field name.

LOGICAL True if the field is blank.

Exports record if the Notes field has something in it.

The list is created by going to {File} {Field Translator Table} and keying in the entries. The first 2 columns provide the identifier for the list. For example, you might enter USERGRP at the LABEL column and CALENDAR in the FIELD column. Then key the GoldMine User Names into the EXISTING VALUE column. You would not key anything into the REPLACE WITH column.

This function operates on the same principle as the GetCustom() function, except a replacement value is not returned. Instead, a TRUE is returned for a found value.

The name of the label in the Field Translator Table, surrounded by quotes.

The name of the field holding the value to be checked in the Translator Table, surrounded by quotes.

TRUE if the field value is found in the list, FALSE if not.

The STRING or GoldMine® Field name. If you don't pass a parameter, GoldBox-5 assumes Contact1->Contact, the Main Contact name.

The STRING Name in Lastname, First Name format.

Result: Petonic, David E

The word after the last space in a character field.

Result: Petonic (Assumes the name is David E Petonic)

This function is record-based, not field-based. The return value means the record was added, or one of its fields was modified. GoldMine does not track individual field edits on a newly-added record which has not synced. Therefore, this function will only tell you the most recent date the record was added or modified.

The GoldMine table name, surrounded by quotes. When you pass in "CONTACT1" as the table name, LatestSync() automatically checks the Contact2 file as if Contact1 and Contact2 were just one record (which, in effect, they are).

A true Date value indicating the latest (most recent) activity on the record. If no Tlog for the record is found, LatestSync() returns 01/01/1990 as a true Date.

Result: 02/03/2001

Result: 02/05/2001

Result: Returns a True if the latest sync entry is equal to today's date.

The STRING or field or memo.

Number of characters as a NUMERIC.

Result: 40

Result: 15 (assumes David E Petonic)

Result: 15,560 You may want to use the above example to determine whether to archive the Main Notes to a Linked Document if they are larger than a designated number.

The display file fields are Address1 and Address2 in the Contsupp Linked Document record. GoldMine does not actually use the values in these fields to initiate the link. They are currently used just to display as much of the file name as possible. Therefore, replacement of these fields is strictly for cosmetic purposes. The link is initiated based on the file name in the LINKEDDOC memo field.

Typically in a Global Replace of the display file fields, select Contsupp->Address1 as the Field-1 value and Contsupp->Address2 as the Field-2 value. See Example below.

The number 1, for example. This optional parameter is only used when replacing the Address2 field with the remainder of the file name, if it exceeds 40 characters.

The full file name, if you pass in no parameters. If you pass 1 parameter, GoldBox-5 checks whether the file name exceeds 40 characters. If so, characters 41 through to the end of the file are returned. If the file name is 40 characters or less, a blank value is returned, which blanks out any extraneous characters in Address2.

Result: the entire file name. This would typically be replaced into Contsupp->Address1.

Result: characters 41 through the end of the file name, if the file name exceeds 40 characters. Otherwise, a blank value is returned. This would typically be replaced into Contsupp->Address2.

The GoldMine Field Name, surrounded by quotes.

The Field Code for special Calendar and History fields, as outlined above.

The Description if found, otherwise a blank value.

Returns the Description for the Result Code of a Completed Sale.

An unusual and powerful feature of this function allows you to define which LOOKUP.INI file to evaluate, and lets you append 2 or more LOOKUP.INI-type files together into a super-Ini for evaluation. This, in effect, breaks the 64K limit on the size of a LOOKUP.INI file imposed by GoldMine.

As of version 5.37, this function was changed in 2 ways:

GoldBox-5 puts the LOOKUP.INI file into its own system file called LOOKINI.DBF, then manipulates that file to return a value. So, before using the function, you must populate GoldBox-5's LOOKINI.DBF file by running a command line call (or a Q-file entry). This is very easy, and is accomplished like so:

Where:

C:\goldbox5\gx5 = The drive, path and executable file call to GoldBox-5

Dep = sample login initials to GoldBox-5. Please substitute your own.

/L = the Operation Code

c:\progra~1\gm5\lookup.ini = File location. Please substitute your own.

By default, LOOKINI.DBF is cleared out (zapped) prior to appending LOOKUP.INI. However, you have another option available. You can combine one or more Lookup.Ini-type files together, and GX5, in effect, sees this as one big LOOKUP.INI. That is typically the case, for example, if you are using Zip Codes to define Sales Territories, and the standard LOOKUP.INI is not big enough to hold all the values. To do this, you simply pass in the parameter APPEND, like so:

The first call clears out LOOKINI.DBF and appends the standard LOOKUP.INI. The second call adds a file called ADLOOK1.TXT into LOOKINI.DBF, preserving the existing entries. This assumes you have setup ADLOOK1.TXT in the exact same way as LOOKUP.INI. If you are continuing entries of a particular field label from LOOKUP.INI into ADLOOK1.TXT, just continue the entries – DO NOT put in the same field label! ADLOOK1.TXT file can be greater than 64K, since it is not evaluated by GoldMine. Make it as big as necessary. The file name ADLOOK1.TXT is only used as a suggestion – you can call it anything, as long as it conforms to the short-name convention of 8 characters for the file name and 3 for the extension (no embedded spaces).

You can also swap in different LOOKUP.INI files for your operation. For example, you may wish to do a Global Replace on some fields using a local LOOKUP.INI in the Contact directory, then follow with a Global Replace on other fields using the default LOOKUP.INI in the GoldMine root directory. A Q-file might look like so:

L c:\progra~1\goldmine\common\lookup.ini

R REKEYFLDS

L c:\progra~1\goldmine\lookup.ini

R REUDEFFLDS

Typically, you'll probably replace the same GoldMine field as the label name. For example, if you are replacing Key5, you would call the Key5 label. However, this is not required. You can use any field label's lookup-and-replace list in Lookup.INI. For example: LookupINI("UserDef10", "Contact1->Key5") can be the field replace expression for Contact1->Key5. You are telling LookupINI() to use the UserDef10 label's lookup-and-replace list, and if it does not find anything, to return the value of Key5. If it does find something, that is replaced into Key5. In short: The field label does not have to match the GoldMine field you are replacing.

GoldBox-5 recognizes the {Otherwise} entry. It also recognizes the &Username and &Userfullname macro expressions. It does not evaluate the {Overwrite} entry or the lack of one, since you can easily control that in GoldBox-5. For example, if you only want to replace fields that are empty, include that in the Primary Filter.

The Main Contact field name, surrounded by quotes. Do not pass in the file name of Contact 1 or 2; only the 10-character field name is needed. Do not pass in the square brackets, as GoldBox-5 automatically attaches those.

The GoldMine field value you want returned if the function does not find a replace value in Lookup.INI. This ensures that you do not blank out the field you are replacing if the function does not return anything. Please make sure you surround the entry in quotes.

(Assumes an Import and you pressed the [Exprn] button at the Import Field Mapping Template for the Key1 field. This is the Field Expression for Key1.)

(Assumes a Global Replace of Key3. Even though you are replacing Key3, you can use the lookup-and-replace list for [UserDef02] in Lookup.INI. If LookupINI() does not return anything, the value of Key3 is returned, in effect replacing Key3 with itself.

The STRING in lower case.

Result: david e petonic

The STRING or Character field.

The trimmed STRING. If the string was empty, returns a null.

The field name containing the am/pm time as a STRING.

Any value, such as the NUMBER value of 1. If you don't pass this, the time will be returned as a military time without the colon, as in hhmm. Normally you will want to pass the 2nd parameter so it will be returned as hh:mm.

The 24-hour (military) time as a STRING.

Assumes value is 1:30pm. Result: 13:30

The second NUMBER.

The larger of the 2 numbers as a NUMERIC.

Result: 687

The Memo field.

NUMERIC - The line number. Default is Line 1.

NUMERIC - Length of line to be extracted, between 8 - 255 characters. Default is 50.

The line of text as a STRING.

The second NUMBER.

The smaller of the 2 numbers as a NUMERIC.

Result: 244

The month as a NUMERIC.

Result: 2

1. Checks Tab records attached to a Main Contact and returns a TRUE only if a series of sub filter conditions are ALL true. You may be familiar with building a filter that evaluates, for example, Detail records, such as: Return a TRUE if the Reference is Auto. With this capability, you can now say, for example: Check the 15 Details attached to this Contact record. Return a TRUE if there is an Insurance Detail Reference of Auto, another with Home and another with Life. If there are only 2 found for the Contact, let's say one with Auto and one with Life, but no Home, then the function returns FALSE. You can use this capability for building Groups, Exporting or Global Replacing a Main Contact field. Another typical example might be to export addresses for a mail merge if the contact has purchased 3 specific items. Tab file types that can be searched include Calendar, History, Additional Contacts, Details, Linked Documents, Referrals, Mailbox and the Opportunity Manager.

2. You can reverse the logic of the search by including the word NONE in the setup of the sub filter, making this function exceedingly powerful and flexible. In this way the sub filter returns TRUE if NO records were found based on the condition.

3. You can have MultiTab() return a TRUE if a certain NUMBER of Tab records meeting the sub filter condition is found.

4. You can have MultiTab() return a TOTAL of any numeric field (or numeric expression on a field, as with the Val() function). Or you can pass in the number 1 as the numeric expression to return the number of Tab records meeting the filter. This can be exported out or used as a value to be evaluated by a filter expression. For example, you may want to export the contact's total sales figure during the first quarter of 1998.

You can string MultiTab() calls into different Tab files within the same master filter, such as checking History and Detail records, making this even more powerful.

The master filter is the one you are presented with in the GoldBox-5 utility, such as the Group Builder. This is where the calls to MultiTab() are made, along with any other global conditions. The sub filters which are referenced by the MultiTab() function are entered at the {File} {Sub Filters / Seq # Generation} option. The only columns which must be filled in are DEF LABEL and EXPRESSION. At the DEF LABEL column, key in a code followed by a slash, then by a number or letter. You MUST use the forward slash character. At the EXPRESSION column key the filter directly. If you want the sub filter to return a TRUE if NO records were found meeting the condition, key the word NONE in the {NONE?} column. Examples of sub filter expressions:

DEF LABEL NONE? EXPRESSION

---------------- -------- ---------------------------------------------------

PRODUCT/1 Upper(Conthist->Ref) = "GOLDMINE"

.AND. Conthist->Rectype = "S"

PRODUCT/2 Upper(Conthist->Ref) = "GOLDSYNC"

AND. Conthist->Rectype = "S"

PRODUCT/3 NONE Upper(Conthist->Ref) = "GOLDBOX-5"

.AND. Conthist->Ref = "S"

SALES1Q98 ContHist->Rectype = "S" .AND. ContHist- >Ondate >= CtoD("01/01/98") .AND. ContHist->Ondate <= CtoD("03/31/98")

You are checking Completed Forecast Sales in the History file and want to place into a Group those contacts who have purchased GoldMine® & GoldSync but not GoldBox-5.

The Definition Label as a STRING, surrounded by quotes. Please note that you do not pass in the slash character or what follows it - that portion of the Definition Label is used only for sorting the definitions in a logical order.

The Tab file to be evaluated as a STRING, surrounded by quotes. There are only 5 valid entries and they are: CAL, CONTHIST, CONTSUPP, MAILBOX and OPMGR.

The Supplemental Record Type (required if Param 2 is CONTSUPP). Valid types are C for Additional Contacts, P for Details, R for Referrals, L for Linked Documents. NOTE: If you are going to use the following COUNT parameter, Calendar or History, you must pass something as the 3rd parameter so the 4th can be evaluated.

The NUMBER (Count) of Tab records which needs to meet the sub filter condition, or, the numeric expression on which to return a TOTAL (see next parameter).

Any parameter, such as the number 0, to indicate that you want to have MultiTab() return a TOTAL figure from the Tab records.

Logical TRUE if a Tab record is found for each defined sub filter, or, if you are using the NONE indicator, will return a TRUE if that sub filter DOES NOT find a Tab record based on the condition. In other words, all sub filters have to return TRUE for the entire MultiTab() function to return a TRUE..

If you pass in a 5th parameter, MultiTab() will return a TOTAL as a Numeric value.

Example:

per the above example setup

Returns TRUE if the contact has Auto, Home and Life policies, but no Boat.

Returns a TRUE if at least 4 records were found in History as Completed Sales for 1997. Note the 3rd parameter is empty, which is fine.

Returns the total of Completed Sales per contact for the 1st Quarter, 1998 to be exported out. In this case you would use MultiTab() function in 2 locations (see the above sub-filter example). The first is in the filter expression like so: MultiTab("SALES1Q98", "CONTHIST") This will only export records that have at least one sale in the first quarter 1998. At the Export Field Template, you would use a Numeric Calculated Field and use the above function call at the expression column to return the TOTAL of the History's Duration field (where the Sale Amount is stored).

The STRING, NUMERIC or DATE value.

Length of the padded STRING to be returned.

The character with which to pad. Default is space.

The padded value in the form of a STRING.

Result: ****GOLDBOX-5

Result: GOLDBOX-5****

The GoldMine® field for the phone number, surrounded by quotes. If you don't pass this optional parameter, Contact1->Phone1 is used as the default.

The stripped phone number as a STRING.

If the Phone1 field was (310)441-0782, the result is: 3108396530.

For Merge-Purge, uses Company plus Contact plus the stripped Phone1 number as record-matching scheme.

Strips the Addl Contact Fax Number.

Any value, such as a NUMBER 1.

A TRUE if the Detail is the Primary Address (when passing NO parameter).

Returns TRUE if you passed a parameter and the Detail is an E-mail Address.

Assumes you are exporting. This is entered in the Filter prompt.

The Detail date as a DATE.

Returns TRUE if the Detail Date equals Today's date.

The word MAIN for the Main Contact's Primary Address, or the word ADDL for the Additional Contact's E-mail Address. It must be surrounded by quotes.

The E-mail Address if found; otherwise, a blank string of 75 in length.

NOTE: When you use PullEmail(), one of two variables is populated with the Merge Codes value for that Email Address. You can then use either of these in a Filter, for example, to export only those email addresses that contain a specific value in the Merge Codes field.

For PullEmail('MAIN'), the variable is EmailMergeMAIN. For PullEmail('ADDL'), it's EmailMergeADDL. GoldBox-5 UPPER CASES and trims the variable.

An example of the variable's usage in a filter might be:

The Web Site Address if found; otherwise, a blank string of 75 in length.

Result: www.goldboxonline.com

A STRING value, such as a space or comma or a particular word.

The STRING or field you are searching.

NUMERIC position within the string.

Pulls out the last word of the Contact Name, typically the Last Name, which might be replaced into the GoldMine® LASTNAME field.

The date value as either a STRING or a DATE.

The NUMBER of the format you wish to be returned.

A form of the passed date, based on the value of the 2nd parameter.

The following assumes Saturday, February 1st, 1997 as the example date. The letter following the call to RealDate() shows the data TYPE returned.: {C} is CHARACTER or STRING; {D} is DATE; {N} is NUMERIC.

RealDate(CtoD("02/01/97"), 1) {C} Saturday, February 1, 1997

RealDate(CtoD("02/01/97"), 2) {C} Feb 1, 1997

RealDate(CtoD("02/01/97"), 3) {C} February 1997

RealDate(CtoD("02/01/97"), 4) {N} 32nd Day of Yr (Julian date)

RealDate(CtoD("02/01/97"), 5) {C} February 1, 1997

RealDate(CtoD("02/01/97"), 6) {C} February 1

RealDate(CtoD("02/01/97"), 7) {C} 020197

RealDate("970201", 8) {D} 02/01/97

RealDate("19970201", 9) {D} 02/01/97

RealDate(CtoD("02/01/97"), 10) {C} FEB 1st

RealDate(CtoD("02/01/97"), 11) {C} FEB 1,97

RealDate("01-Feb-97", 12) {D} 02/01/97

RealDate("97-02-01", 13) {D} 02/01/97

RealDate("February 1 1997", 14) {D} 02/01/97

RealDate("1st February '97", 15) {D} 02/01/97

RealDate("01021997", 16) {D} 02/01/97

RealDate("010297", 17) {D} 02/01/97

RealDate(CtoD("02/01/97"), 18) {C} 970201

Choice {8} is a quick way to convert the GoldMine® Creation Date into a True Date, which is stored as the first 6 characters of the Account Number. Your call:

RealDate(Contact1->Accountno, 8).

Choice {18} is the opposite, and can be used to evaluate the Creation Date on Main Contact records for Scoping.

Choice {13} is the way some TeleMagic systems store the Recall Date (field is RCL) and the Last Called Date (field is LCL). On an import, click [DtMask] and choose {yy/mm/dd} as the format from the Date Mask table.

Time to convert, in the form hh:mm as a STRING. If not passed, current time is used.

Any value, such as the NUMBER 1.

The civilian time as a STRING.

Result: 1:30 pm

Result: 1:30pm (no space between am/pm and time)

dBase4 file name of the record number you wish to return, surrounded by quotes.

The physical Record Number as a NUMERIC.

Assumes an Export in GXW Browser, will only export Record 155.

Assumes you are doing a GX5 Export from History File as master and you wish to export the record number of the related Contact1 record.

Typically you would be addressing the 'child' records, which contain {To} in the Referral field. The word {of} is found in the 'parent' record. Let's assume the Reference for the Referral is "Company Member". You would want to use the MultiTab() function in your global filter, as in: MultiTab("REFERTO", "CONTSUPP", "R") Your sub-filter, entered at {File} {Sub-Filters} would be: ContSupp->Contact = "To" .AND. ContSupp->ContSupRef = "Company Member". This will only address records which have the "Company Member" Referral record and are 'children' of the Company record.

The Reference of the Referral, or a fragment of it.

The Contact1 or Contact2 field name to be returned.

By default GoldBox-5 will return a null string (an empty character value) if the 'parent' Referral is not found. If you wish to return a Date field, pass in "D". If you wish to return a Numeric field, pass in "N".

The value of the Referred record's field, which is the 2nd parameter.

Returns the value of Key1 of the 'parent' record.

Returns the value of UDateBegin of the 'parent' record as a true date value.

Returns the value of ULYsales of the 'parent' record as a true numeric value.

The character or characters to repeat, surrounded by quotes.

The NUMBER of times to repeat the character or characters.

A STRING comprised of the replicated character or characters.

Result: Judy Judy Judy

You can also pass in a Field Name as the 4th parameter of the function, and instead of returning the Account # of the Section or Top Level record, it will return the value of the field you passed in. This might be useful for including a field or fields from the Section or Top Level record when you are exporting data from Member records.

A fragment of the Top Level Title surrounded by quotes, or, a space if you want to address a Section Level regardless of the Top Level Title. Typically, this might be the case where the Company Name is the Top Level Title, but there is a Section called SALES under it.

If you want to return the Account # of the Top Level record, do not pass this parameter. Otherwise, this is the NUMBER of the Section Level.

A fragment of the Section Level Title surrounded by quotes.

A CONTACT1 or CONTACT2 field name, surrounded by quotes, if you wish to include a field from the Section or Top Level record in the export.

The GoldMine® Account Number of the found Section or Top Level record, or, if you passed a field name as the 4th parameter, the value of the field.

Assumes you are rolling up totals from Members of the Top Level Title TERRITORY.

Assumes you are rolling up totals from Members of the Section 2 Level SALES for the Top Level Title of IBM.

Assumes you are rolling up totals from Members of the Section 2 Level SALES for ANY Top Level Title

Returns the contents of Contact2->Utotal of the found Section or Top Level record, to be included as an exported value.

The NUMBER of decimal places to round. Passing a zero rounds to the whole number, where if the decimal is 0 - 4, it is rounded down, 5 - 9 is rounded up.

The rounded NUMBER as a NUMERIC.

Result: 100

Result: 122.50

The ideal example for usage is assigning a Calendar Call record to a group of telemarketers when importing New Main Contacts. With RoundRobin(), you can evenly distribute the calls in a random fashion so each telemarketer gets the same number of records on their Calendar. For this example, you would use the 'plug-in' capability when importing New Main Contacts, by selecting the Calendar checkbox at the plug-in box of the Import Setup screen.

For multiple entries, press {Photo} to photocopy the current record, then change the telemarketer's name. Add as many entries as needed. Make sure the Label and Field column values are identical for each entry. Your entries might look something like this:

LEAD TELEMARKET DAVE

LEAD TELEMARKET ALINA

LEAD TELEMARKET GENE

LEAD TELEMARKET MICHAEL

LEAD TELEMARKET AUDREY

Exit the Browser and setup your Import of New Main Contacts. Plug-in Calendar records. At the black Field Mapping screen, press the green {CAL} button to get down to the Calendar fields. Press {Expression} at each field for which you want a hardcoded entry. For the GoldMine User Name, press {Expression} and key in:

Assuming you are importing 1,000 records, the 5 telemarketers above will each get 200 scheduled callback records in their calendars.

For Merge-Purge and Updating from an outside source, this function 'smooths' the Company Name into a value that may find more 'hits'. It is highly recommended that whenever you use the Company Name as all or part of a Record Match, apply this function instead of just including the Company Name.

Below, information for using this function in both the Merge-Purge and the Update/Attach operations is provided. Please pay special attention to the Update/Attach section!

By applying the function, the following Company Names would be virtually seen as the same:

To reiterate: The actual Company Name is not altered, as long as you are not replacing it. Replacing the Company Name with the result of this function call is not recommended and is not supported by Redstone SoftBase Company.

The field to be translated, typically the Main Company Name. If you select the function directly out of the Functions box at the Expression Builder, you will see that the first parameter is already included.

A 'smoothed' version of the Company Name, to possibly find more 'hits' in a Record Match situation. The returned value is always padded to 40 characters, since Record Matches require a consistent length.

This uses the 'smoothed' Company Name and the Main Phone Number as Record Match.

If you are doing an Attach into Tab files, ignore this step. When doing an Update of Main records, press the {Shield} button, found at the upper right of the black Field Mapping Template. This is critical, so that GoldBox-5 will not replace the actual Company Name on a match! An 'X' should appear in the Description column when the field is properly shielded.

Press the {Expression} button and key in: SmoothComp(Contact1->Company), or select it directly out of the Functions box by double-clicking it.

At the white Update Setup Screen, press {Record Match} and include the function call SmoothComp(Contact1->Company).

For Merge-Purge and Updating from an outside source, this function 'smooths' the Contact Name of similar First Names into a value that may find more 'hits'. It is highly recommended that whenever you use the Contact Name as all or part of a Record Match, apply this function instead of just including the Contact Name. What the function effectively does is swap the first word of the Contact Name with a generic name, based on entries in the SmoothNm.DBF table. If the original Contact Name starts with Mr., Mrs., Ms. or Dr., these are stripped off, and the next word is then considered the First Name.

Below, information for using this function in both the Merge-Purge and the Update/Attach operations is provided. Please pay special attention to the Update/Attach section!

By applying the function, the following Contact Names would be virtually seen as the same:

Bill Jones (what is actually returned)

Mr. Billy Jones

Will Jones

Willy Jones

Willie Jones

Dr. William Jones

Sue Johnson (what is actually returned)

Mrs. Susan Johnson

Suzan Johnson

Susanne Johnson

Ms. Susanna Johnson

Suzanne Johnson

Suzanna Johnson

Dr. Suzy Johnson

Suzie Johnson

To reiterate: The actual Contact Name is not altered, as long as you are not replacing it. Replacing the Contact Name with the result of this function call is not recommended and is not supported by Redstone SoftBase Company.

The field to be translated, typically the Main Contact Name. If you select the function directly out of the Functions box at the Expression Builder, you will see that the first parameter is already included.

A 'smoothed' version of the Contact Name, to possibly find more 'hits' in a Record Match situation. The returned value is always padded to 40 characters, since Record Matches require a consistent length.

This uses the 'smoothed' Contact Name and the Main Phone Number as Record Match.

If you are doing an Attach into Tab files, ignore this step. When doing an Update of Main records, press the {Shield} button, found at the upper right of the black Field Mapping Template. This is critical, so that GoldBox-5 will not replace the actual Contact Name on a match! An 'X' should appear in the Description column when the field is properly shielded.

Press the {Expression} button and key in: SmoothName(Contact1->Contact), or select it directly out of the Functions box by double-clicking it.

At the white Update Setup Screen, press {Record Match} and include the function call SmoothName(Contact1->Contact).

The STRING, CHARACTER field or MEMO field. If passing in a Character or Memo field, do not surround the field with quotes.

The number of the Word you wish to delete.

The passed-in string with the numbered Word deleted.

SnipWord("ONE TWO THREE FOUR FIVE SIX", 4)

Result: ONE TWO THREE FIVE SIX

Result: David Petonic (Assumes David E Petonic)

The STRING or field name to be converted into a Soundex value.

A 4-byte STRING in the form of letter-number-number-number. You will normally never use the return value directly.

An example of a record-matching expression for Org Chart Creation.

The NUMBER of spaces you passed in.

The NUMERIC value you wish to convert.

NUMBER: The total length of the returned STRING.

NUMBER of decimal places.

A value to pad leading spaces, as a single CHARACTER

A STRING of the length and decimals specified by the parameters.

If the UTOTAL field is 5000, the result will be: 5000.00, with one leading space.

Result: 000022

The field to examine.

The string of characters you wish to remove from the field.

The STRING stripped of the passed in characters.

Result: abcghijk

This assumes that a numeric amount is included in Key1 along with alpha characters. The above example strips the letters from the field and evaluates the remaining numbers as a true numeric, by virtue of the Val() function. This expression, used in a filter, would return a TRUE if the numeric portion of the field was greater than 1000.

The field to examine. Do not surround by quotes.

The field value stripped of all except the default keyboard or passed-in characters.

In this example, we're assuming some extraneous characters are in the Contact Name field of Additional Contacts. GoldBox-5 will remove everything except keyboard-entered characters.

The field to examine. If no parameter is passed, Contact1->Contact is assumed.

The STRING stripped of the Mr/Ms/Mrs word if it exists.

If the Addl Contact name is: Mr. David E Petonic, result is: David E Petonic.

This function is unique in that it actually returns 2 items: The stripped name, and the stripped Short Title. The stripped name is returned directly by the function, as expected. The stripped Short Title is written into a public variable called ShortTitle. This allows you to replace the Contact Name with the stripped name and another field with the stripped Short Title in the same operation. For example: In a Global Replace, you select Contact1->Contact for Field-1. The replace expression is: StripSuffix(). You select Contact2->Userdef01 for Field-2. The replace expression, assuming you wish to replace User-Defined #2 with the stripped Short Title, is: IIF(Empty(ShortTitle), Contact2->Userdef02, ShortTitle)

GoldBox-5 automatically ignores Short Titles which are a part of a person's name. These include:

Jr, Sr, I, II, III, IV, V, 2nd, 3rd, 4th, 5th.

The field to examine. If no parameter is passed, Contact1->Contact is assumed. The field name must be surrounded by quotes.

The Name stripped of the last word if it is determined to be a Short Title. The Short Title itself is stored to a public variable called ShortTitle. If no Short Title is found, the Name is returned as is.

Returns: David E Petonic Writes CEO to the public variable ShortTitle.

Returns: Harry S. Callahan. Writes ESQ. to the public variable ShortTitle.

The STRING or MEMO to be searched.

The STRING to locate.

The STRING value to replace the found one with. If you don't pass this parameter, the found value is removed completely.

The translated STRING or MEMO.

Result: Give him a sedative

In Company field, replaces the word {Co.} with {Company}

The STRING or field to be evaluated.

NUMBER: The starting position within the string to begin the evaluation or extraction.

NUMBER: How many characters to evaluate, starting with the position you passed in the 2nd parameter. If not passed, the rest of the characters are evaluated or extracted.

The sub portion of the original STRING.

Evaluates the Zip+4 value

Assume phone (310)441-0782, Result: 310

You can also use this function to put the record pointer at the highest or lowest Tab record using date or numeric criteria instead of the default LastDate and LastTime. For example, you may want to put the pointer at the highest Completed Sale amount.

The word FIRST or LAST, surrounded by quotes.

The name of the GoldMine® Tab file, surrounded by quotes.

The Supplemental Record Type, surrounded by quotes, if you are evaluating Tab records in CONTSUPP. If evaluating Calendar (CAL) or History (CONTHIST), you can optionally pass in a record type so that only that record type is evaluated.

The date or numeric expression to be evaluated for Highest or Lowest.

You can pass in a filter, surrounded by quotes, to evaluate targeted Tab records.

Nothing. Puts the record pointer at the First edited or Last edited record, or, if you pass a 4th parameter, the Highest or Lowest record.

Puts record pointer at most recently edited Detail record.

Assumes you have a subfilter for HISTSALE where ContHist->Rectype = "S" so the MultiTab() will return TRUE only on those records having at least 1 Completed Fcst Sale. TabEdited() then points to the First Edited (oldest) Fcst Sale.

Puts record pointer at Highest Sale Amount record in History. The word "FIRST" is associated with Lowest, and the word "LAST" is associated with Highest.

1. Map the incoming name to the Contact1->Contact field.

2. Press the [Proper] button (assumes incoming data is all in UPPER case)

3. Press [Exprn] and key in: TextSwap(Contact1->Contact)

Since this function is based on the values in the TEXTSWAP.DBF table, ensure that file is properly maintained. At the {File} {Text Swap Table}, key in whatever entries are needed. When this function is called, the entire list is compared against the character field, and any matches found (from the Find This column) are swapped with the Repl With column value.

The STRING field or memo to be searched. Must NOT be surrounded by quotes.

The swapped version of the passed STRING.

The STRING, NUMBER, DATE or LOGICAL value.

The formatting mask, also known as a picture clause, which MUST be surrounded by quotes. There are 2 types of designations you can use to format the value:

The first one deals with the entire value, so it can be considered global. You designate this by passing in the {@} sign followed by a single character. The table is below:

@B - Displays numbers left-justified.

@C - Displays letters CR after positive numbers (for Credit).

@D - Displays a Date in the SET DATE format, normally USA.

@E - Displays a Date in the British format, which is dd/mm/yy.

@R - Inserts Nontemplate characters. Example: "@R # # # # # # # #". The @R forces a blank between each character, and the {#} sign forces only numeric values.

@X - Displays letters DB after negative numbers (for Debit)

@Z - Displays zeros as blanks.

@( - Encloses negative numbers in parentheses.

@! - Displays characters A-Z in UPPER CASE.

The 2nd designation deals just with individual characters and is not global. These are:

9 - Displays a digit for any data type. You'll use this one a lot for numeric values.

L - Displays LOGICALS as T or F.

Y - Displays LOGICALS as Y or N.

! - Converts an alpha character to UPPER CASE.

$ - Displays a dollar sign in place of a leading space in a numeric.

* - Displays an asterisk in place of a leading space in a numeric.

. - Specifies a period.

, - Specifies a comma.

The passed in value in the form of a formatted STRING.

Result is: $ 12,345.60

The STRING or Character field.

The trimmed STRING. If the string was empty, returns a null.

A GoldMine® name other than the one assigned to you by GoldBox-5.

A true date value. If passing this, you MUST pass the above parameter.

A time value in the form hh:mm. If passing this, you MUST pass the first 2 parameters.

A banner in the form: *** USERNAME *** Month Day, Year hh:mmam/pm A character return / line feed is included.

Result: *** DAVE *** February 2, 1997 1:30pm

The STRING in UPPER CASE.

Result: DAVID E PETONIC

The field name or string, surrounded by quotes. If you don't pass this required parameter, you are prompted to exit GoldBox-4.

The stripped STRING.

UpperStrip("Contact1->Address1") (123 Main St. #202 returns 123MAINST202)

UpperStrip("Contact1->Address1", 1) (123 Main St. #202 returns 123MainSt202)

A field or STRING. If not passed, CONTACT1->STATE is assumed. You must surround the field with quotes.

LOGICAL True or False.

A field or STRING. If not passed, CONTACT1->STATE is assumed. You must surround the field with quotes.

LOGICAL True or False.

The STRING value to be converted.

The NUMERIC value of the characters in the STRING. If an alpha character is found in the STRING or it is blank, the function returns a zero.

Result: 86,400 (number of seconds in a day)

Expression for putting 3 lines of incoming text into Notes on an import.

VLink2dBase(), which is short for 'Virtual Link to a dBase Table', allows you to link a GoldMine table to any other dBase table, or to a source ASCII file that has first been auto-converted by GoldBox-5 to dBase. The term 'virtual' means you can use any of the records and fields of the linked table in your GoldBox-5 operations, without those records or fields being stored in GoldMine itself. This scenario is typical for Exporting and Group Building. However, you do have the option to Import, Update or Replace GoldMine fields with values from the linked dBase table. In many cases, this is what you will use the function for, especially for a faster and more direct way of Updating GoldMine from an outside source.

Not only can you READ any of the fields of the linked dBase table, you can also WRITE to any of them, by using a literal value or a GoldMine field or field(s) expression.

The function works when there is a 1-to-1 or a many-to-1 relationship between the GoldMine table and the linked table. For example, if using an Account ID in Key5 to link to an Accounting System table for a Global Replace operation, GoldBox-5 will find only 1 occurrence in the linked table (if it exists). However, if you have multiple Contact records with the same Account ID, each will find the same record in the linked table, allowing you to update multiple matches from 1 source record. In review: In the linked dBase table, only one match (if it exists) is ever found. In the GoldMine table, you can have multiple matches, but each of those matches will find only the 1 linked table record. The lookup into the linked table is based on an index, therefore multiple occurrences of the same key value will never be seen. Only the first one that is found by the index.

VLink2dBase() also allows you to link a Contact File back into itself, or into another Contact File anywhere on your system. The fields of the linked Contact are now available to you (typically for READ only). This is ideal if you keep 'child' records in one Contact file and 'parent' records in another (or even in the same Contact file). When you call the function correctly (see the information for Param 7 below), VLink2dBase() automatically establishes a relation to the Contact2 fields (containing your user-defined fields) so that you have access to them as well.

A typical example for using VLink2dBase() is if you purchase an updated Postal Code table from a vendor (there are many offering such services on the Web). In GoldMine, you can only track City and State (or Province) based on Postal Code. In your purchased table, you have Postal Code, City, State, County, Time Zone, Country, Region, Phone Area Code, Longitude and Latitude. You now have access to all of these extra fields for Importing, Updating or Replacing GoldMine fields, or, for Exporting and Group Building. You do not necessarily need to bring the extra demographic fields into GoldMine – they are available to you on a virtual basis. And, of course you can write to this Postal Code table from GoldMine, if desired, although in this situation that would probably be rare.

Another typical example for usage is linking to dBase tables in an Accounting System. You may wish to link with a table to populate user-defined fields with sales data, or build Groups based on sales data contained in the linked records. Again, you can also write to the Accounting System table from GoldMine if desired.

Following are the preparatory steps before using this powerful function:

NOTE: You will be using the BR7 Utility (BR7.EXE) to view and setup the linked dBase table. If this is not in your GoldBox-5 path, or if you are not on BR7 version 2.35 or later, please download BR7UP.ZIP from the Web site and unzip it in your GoldBox-5 path – overwrite all files if prompted.

If it does not exist, you must create an Index Tag for the linked dBase table so a match from the GoldMine record can be found. First, click {View} to see the current Index Tags and expressions. If you want to refresh the Index Tag, click {Reset} to rebuild all the Index Tags from scratch. If the Index Tag does not exist, click {AddTag} to begin the process of creating it. Before continuing, please note the following:

• The result of the Index Tag expression cannot exceed 100 characters. Normally this is not a problem.

• The Index Tag expression must always evaluate to UPPER CASE. VLink2dBase() always converts the GoldMine Match value to UPPER CASE transparently, so your Index Tag expression must be built on UPPER CASED field value(s).

• You must determine the GoldMine field, or fields, you are going to pass in as the Match value, and the field or fields in the dBase table which correspond to that Match value. In most cases, you will probably have an ID type of field that is common to both the GoldMine table and the dBase table. Examples of these are a Postal Code, Account ID, Social Security Number, Order ID, Customer Number, etc. Therefore you would be building an Index Tag on just a single field of the dBase table. If you are matching on more than one field, for example GoldMine's Company plus Contact fields, you must ensure the lengths of the combined fields in the Index Tag expression match up to what you are passing in from GoldMine as a Match value. Example: You are using Company + Contact as the GoldMine Match value. In GoldMine, Company is 40 characters in length and Contact is 40 also. In the dBase table, the company information is held in a field called FIRM with a length of 35 and the contact name is in a field called CLIENTNAME with a length of 30. Your Index Tag expression would be: Upper(PadR(Firm,40)+ClientName). Your GoldMine Match value to be passed in is: Contact1->Company+Left(Contact1->Contact,30).

To create the Index Tag, click {AddTag}. At the next prompt, key in the Index Tag Name, which must be 10 characters or less and must start with A-Z. Click {Ok} and you are taken to the Expression Builder for creating the Index Tag expression. Start the expression with Upper(, then double-click a Field from the Fields Box. If just a single field, close the Upper() function off with the right parenthesis. The following are points to remember when creating the expression.

• Do not use the AllTrim(), Trim(), Ltrim() or Rtrim() functions in your expression!

• Do not use spaces in the expression. This lets you launch the rebuild of the Index Tag from a command line call or a Q-file without problems. Correct example: Upper(PadR(Firm,40)+ClientName).

• Click {Go} after keying the expression. The Index Tag is created, and the table displays with it active.

• You can auto-launch the creation of the Index Tag from a command line call (in {Start} {Run} or from an icon, for example), or from a Q-File entry as a Windows EXE. This can also be used to refresh the existing Index Tag before using the VLink2dBase() function. In the following example, please assume the dBase table name is ACCTFILE, the Index Tag you wish to create is named ACCTID, and the dBase field containing the Account ID is called ACCOUNT_ID. Your call into the BR7 Utility is: BR7 d:\data\acctfile acctid INDEXON Upper(Account_ID) /E This will open BR7, load the ACCTFILE found in the D:\DATA path, create an Index Tag called ACCTID on the UPPER-CASED value of the ACCOUNT_ID field, then exit.

This concludes the discussion on how to setup the linked dBase table with its Index Tag. You are now ready to use VLink2dBase() in your GoldBox-5 operations.

If you intend to WRITE to the linked dBase table, you must always pass the word "WRITE" for this parameter.

This parameter is optional, and is the Alias of the linked dBase table. An 'Alias' is just another name for the linked dBase table. You never need to pass this parameter if your linked dBase table is a non-GoldMine file. If you are linking, for example, to another Contact File, or the same Contact File you are working on, then you MUST pass in an Alias so that GoldBox-5 can open it under a different name. If you want to link to another Contact File, or the same one you are working on, pass in the word "C1GMFILE" as the Alias. When VLink2dBase() sees this, it transparently opens up the Contact2 file and sets a relation into it, so you can use any of the User-defined fields. The Alias for Contact2 then becomes C2GMFILE. Please note that if you are linking to a GoldMine table with VLink2dBase(), you would only want to use READ capability. If you use WRITE, the change won't sync.

For WRITE: Returns TRUE for a match found in the linked dBase table, FALSE if not.

VLink2dBase("c:\mydata\postcode", "zipcode", "CountyName", "Contact1->Zip", "Contact1->Key5") In this example, you are linking into a POSTCODE table you purchased, and you want to populate the Key5 field with the County from the table.

.NOT. Empty(Contact2->UserDef01) .AND. .NOT. Empty(VLink2dBase("d:\progra~1\goldmine\common\contact1", "contkey1", "C1GMFILE->Company", "Contact2->Userdef01", "Space(1)", "", "C1GMFILE") In this Export example, you are using all 7 parameters because you are linking your current GoldMine Contact File back into itself. The call to VLink2dBase() is actually done in the Primary Filter, not as an Export field expression. In this case, you are evaluating Client records and want to link to the related Lawyer record, and return the lawyer's Company Name to Export it out. In the Lawyer records, the Lawyer ID is stored in Key1. In the Client records, the Lawyer ID is stored in UserDef01 and NOT in Key1. You are passing in the UserDef01 value for lookup by the Key1 index to find the Lawyer record. If no match is found, VLink2dBase() returns FALSE. Your Primary Filter will then ignore those Contacts that do not have a match because you passed in a Space(1) to be returned on No Matches, and you have a .NOT. Empty() around the VLink2dBase() function call. In your Field Mapping Template, to export out the lawyer's Company Name, create a calculated field by pressing the {Field} button. The field name might be: LAWYERCOMP. The Description can be: Lawyer's Company Name. The Expression would be: C1GMFILE->Company.

VLink2dBase("f:\acctdata\acctsale", "acctid", "acctsale->salestotal", "Contact1->Key2", "Contact2->UsalesTot") In this example, you are Globally Replacing a User-defined field called Contact2->UsalesTot with the Sales Total value found in your Accounting System table. The call to VLink2dBase() is the field expression. You would press {Field-1} and select the Contact2->UsalesTot field. At the Expression screen, you would key in the above example. You are using the Key2 field to find a match in your linked dBase table called ACCTSALE. You are passing in Key2, and GoldBox-5 uses that value to find a match in ACCTSALE, based on the ACCTID Index Tag that you created. You want to return the value of the SALESTOTAL field to populate Contact2->UsalesTot. If no match is found, you want to replace Contact2->UsalesTot with itself.

VLink2dBase("f:\acctdata\acctsale", "acctid", "GMacct", "Contact1->Key2", "Contact1->Accountno", "WRITE") In this example, you wish to find a match in your ACCTSALE linked dBase table and write the GoldMine Account Number into a field of that table called Gmacct.

• Like most of GoldBox-5's functions, VLink2dBase() is generic – it simply returns a value. This makes it very powerful because you can use it anywhere. You can use the returned value to be evaluated in a Primary Filter for operations such as Export, Group Builder, Global Replace. Or you can use the returned value as a field replace expression, in Global Replace or Import/Update. If you are WRITING to the linked dBase table, VLink2dBase() returns a True if the Write succeeded, False if not.

• Please remember that the key word for this function is 'Virtual'. You only need a Match value in GoldMine to link into the dBase table. You don't necessarily need to dump field values from the table into GoldMine to perform an operation because you can evaluate those field values in the linked dBase table directly.

• Although the usage of this function may seem complex, it really is not, once you get accustomed to it. There are essentially only 2 parts: You setup the linked dBase table with an Index Tag so a Match can be found. Then you call VLink2dBase() with the necessary parameters, which are: The name of the linked dBase table; the Index Tag to find a particular record; the field in the linked dBase table you want returned as a value, or you want to write a GoldMine value into; the Match value from GoldMine to lookup the matching record in the linked dBase table; and the value you want returned if No Match is found in the linked dBase table. Those are the only 5 required parameters to the function call. You can also pass in 2 optional parameters of: whether you want to READ from or WRITE into the linked dBase table; and an 'Alias' name to open the linked dBase table under if it is a GoldMine table or is named the same as a GoldMine table.

• You can link to more than 1 dBase table in the same operation. Probably a rare situation, but it can be done.

• To follow the above hint, if your linked data is provided as an ASCII file, you can still perform the operation automatically. You would first create the initial dBase table from the ASCII file, via GoldBox-5's {Misc} {Parse ASCII to dBase}, Microsoft Excel, Microsoft Access or another program. You can then launch the auto-conversion of the ASCII file to dBase with a Q-file entry. See the section {Text to dBase4 Conversion} for more information.

• Replacement for Update Main Fields: If you are doing direct replacements of Main or User-defined fields, use the Global Replace and this function. Previously, you always had to use Import/Update for this purpose. You can still use that option if your scenario is more complex, but this is an alternative which can operate faster and more efficiently.

Scenario: You have one user-defined field in GoldMine, called UsaleGoal, which tracks the current month sales goal figure per client. So, if you go to ABC Company, their sales goal figure might be $10,000 for the month of November. Next month, it will change to a new value perhaps. The sales goal figure comes in from your AS-400 accounting system, which is an automated weekly update.

You would NOT map an incoming field to GoldMine's Contact2->UsaleGoal field. Instead, you would press the [Exprn] button, and key in the following expression:

The assembled field name. Field Name must result in a STRING value.

The field value, which can be any TYPE.

See above.

The STRING or Character field.

The character, word or phrase you are looking for, surrounded by quotes.

The number of occurrences, as a NUMERIC.

WordOccur(Contact1->Contact, ".") Assumes name is: David E. Petonic, ESQ.

Result: 2 (found 2 periods)

When searching the STRING field or Memo, this function will only look at values that have a space on either side of them (standalone words). The function will "stuff" a space in front of the first word and after the last word so that they get evaluated.

This function is automatically invoked if you used the [Proper] button at the Import Field Mapping Template for a field.

The STRING field or memo to be searched. Must NOT be surrounded by quotes.

The swapped version of the passed STRING.

The function returns the result of the Expression Fragment you stored in the Sub-Filters file. And it's easy to setup. All you need to enter at the Sub-Filters file is a unique Definition Label (the Def Label column) and the expression. You can string as many xFrag() functions together in you Primary Filter or Field Expression with the AND, OR, NOT, or with mathematical operators such as Plus, Minus, Equals or Not Equals.

A unique Definition Label, surrounded by quotes. Case is not critical, as GoldBox-5 automatically converts your passed-in Definition Label to Upper Case.

The result of the expression you stored in the Sub-Filters file. The result can be any Type you require: Character, Numeric, Date or Logical (True / False).

Assumes you have an entry in the Sub-Filters file with a Definition Label of COMPANY and an expression of: Contact1->Company = "Redstone SoftBase Company" .OR. Contact1->Company = "GoldMine® Software Corp." .OR. Contact1->Company = "Corporate Computer Center". xFrag() returns True or False, depending on whether the Contact record being evaluated meets or does not meet the expression.

The year as a 4 digit NUMBER

Result: 1997

The Import/Update tandem in GoldBox-5 is one of its showcase capabilities. Barring the writing of your own custom application, or the use of a custom utility provided by GoldMine® Software, this is the best method for getting data into GoldMine. You may want to contact GoldMine® Software to see if they can provide you with a custom utility for transferring data from Act!, TeleMagic, Maximizer, Ecco, etc.

Importing is the most complex operation in GoldBox-5 but you should not be discouraged - that's just the nature of it. All other GoldBox-5 operations start from within your GoldMine® data, with which you are already familiar. Importing is the assimilation of non-GoldMine® data into your system, so naturally there are going to be variables involved. Non-GoldMine® data can be anything, can be formatted different ways, and is usually set up to run a certain way in another system. So it's a bit of a leap to transform this data into meaningful records in your GoldMine® system. But not to worry - you can do this! There is no magic involved in any computer program - they all follow a logical path to accomplish a task. And besides, GoldBox-5 anticipates many of the Import issues you will encounter. That said, let's go forward.

Implementing a successful import strategy, whether a one-time conversion from another contact manager or a recurring update on a weekly basis from a mainframe accounting system, requires careful planning. It's like painting a room - most of the work is done in the preparation. Therefore, please read the entire section on Import/Update before even selecting an option from the menu. Even if you are just importing NEW Main Contacts into GoldMine, read the following first, because how you set up that import can adversely affect subsequent imports if not carefully planned and done properly.

The import always requires an Incoming file, or perhaps files, especially if you are importing into the Tab Files in GoldMine. The Tab Files are those tabs along the bottom half of the GoldMine® main screen, specifically the Contacts, Details, Pending, History, Links etc.

GoldBox-5 will only import from 1 Incoming File at a time. GoldBox-5 does not deal with specific contact managers or applications when importing - it only deals with standard file formats. So, the first issue to be resolved: Get the incoming data you wish to import into one of these standard formats: dBase3, dBase4, ASCII Delimited or SDF Fixed Length Text. But read on, because GoldBox-5 requires that the final Incoming File is only a dBase3 or dBase4 file!

Please check out dBase4 File & Field Naming Conventions

This is critical information if you are, for example, converting an Excel spreadsheet to dBase for importing.

If you already have your incoming data in a standard ASCII file, see Parse ASCII to dBase

GoldBox-5 can create a dBase table directly from an ASCII file.

If your incoming data is in SDF Format, as fixed length text, see dBase4 File Creation in BR7

This might be the case if your incoming data was created by a report.

You can use the BR7 Utility to view incoming dBase data and maybe do some preliminary cleanup before bringing it in. Within GoldBox-5, you also can use the {File} {Browse ANY dBase4 File} option. This might be useful to eliminate dupes, for example, from your Incoming File before importing.

Before the import, you have to determine exactly where in GoldMine® you wish the incoming data to reside. For clues, please review:

GoldMine TAB Files

GoldMine CALENDAR File

GoldMine HISTORY File

GoldMine SUPPLEMENTAL File

Although the following information is certainly a bit geeky it is provided because you may want to understand the basic concept of how GX5 imports data. GX5 actually shields you from how things are really done as much as possible, and you are prompted for what is needed to run an operation. But this will help you understand a bit of the logic on this most complex of operations, the Import/Update.

The import of NEW Main Contacts is the most straightforward. Although much more powerful than GoldMine's standard import, the basic concept is the same. You select your Contact File and Incoming File, choose basic options, map incoming fields to GoldMine® fields, and run it. Plug-in of Tab records is a twist, and will be fully discussed in Plug-In Tab Records. When this operation runs in the GX5 Batch Processor, it's a single process. Please Note: There is no import into the Updater Contact File when you want to import NEW Main Contacts! In fact, the [Contact File] button at the Import Setup Screen is specially programmed to exclude the Updater Contact File from the list box. When importing NEW Main Contacts, always choose your real Contact File, the one you wish to find the new records lovingly nestled in after running the procedure - Do Not import into Updater!

Having said that, it's just the opposite for all other Import/Update scenarios. The data always initially comes into the Updater Contact File, and only into the Updater Contact File! Actually you don't have to sweat this because GoldBox-5 won't allow you to bring the Incoming data into any other Contact File.

To setup an Updater Contact File, see UPDATER Contact File

The flow of the Update/Attach process looks something like this:

Please do not be intimidated by the above information! GX5 knows what it needs and will prompt you for missing entries while you are setting up the operation. Still, we're going to get a bit geeky here. To explain the above figure:

Phase 1: Incoming data is first 'parked' in a special Contact File called Updater. This is why you were directed at the beginning of this manual to create a new Updater Contact File in GoldMine. The Updater Contact File (from here on called Updater for brevity) is a 'work area' or 'temporary holding area' for Incoming data. You're never going to open this file with GoldMine® (unless you are curious), the data won't sync, even many of the indexes of this Contact File are not activated during GoldBox-5 operations (for speed). This file's sole purpose is to transform 'wild' Incoming data into 'tame', or consistent data, so that GoldBox-5 can then go ahead and do further processing with it.

Let's say you are attaching Detail records. You might assume that GX5 takes Incoming records and creates Details in Updater, then re-attaches them to the Target Contact File. Nope! Incoming data, no matter where it will eventually end up in GoldMine, is always first placed into Contact1 and Contact2 fields. Always! If you do your Attach of Details successfully and then get curious and say: "I want go see what the Updater file looks like after this", you can open the Updater file in GoldMine, choose [Maintain Databases] to reset the Contact1 File indexes on it, then scroll through the records in Updater and have a good look. What you'll see is data all over the place. That's because GX5 parks the Incoming data in whatever convenient fields exist in Contact1 and Contact2. It's during Phase 3 that GoldBox-5 transforms the Updater data into Tab records and attaches them.

Enough about Phase 1 for now. Put simply: Phase 1 parks Incoming data in Updater, and only in Contact1 and Contact2 fields.

Phase 2: Record Match. This is the most critical issue to resolve when updating or attaching. Without a reliable record match, the 'parked' records in Updater won't find their mates in the Target Contact File, and therefore nothing gets updated or attached.

For Updating Main Fields, you can use any of the fields in the Target Contact File, including all user-defined fields. When you are attaching to Tabs, you can choose from 21 fields for Record Match. These are Company, Contact, Phone1, Secr (a.k.a. Assistant), Country, the 5 Key fields and user-defined fields 1 through 10. Typically you'll use no more than 3 fields to match, and in most cases, just 1. The key point is that the result of your Record Match cannot exceed 100 characters (that's a Visual dBase limitation). For example, the following is a Record Match of exactly 100 characters: Contact1->Company + Contact1->Contact + Left(Contact1->Phone1, 20). The Company and Contact fields are both 40 in length, and that plus the 20 from Phone1 equals 100.

This is most critical: Before you even begin the import into Updater, you have to determine the record match in the Incoming File. You have to know it beforehand so you'll know where to map the field(s) when at the Field Mapping Template.

Phase 3: The actual update or attachment, from Updater to the Target Contact File. This piece roughly corresponds to the 3rd main screen of the Import/Update and will be discussed later.

We'll stop here on pure theory and dive right into the actual setup of the Import/Update operation.

In GoldBox-5, the Import/ Update is comprised of 3 major screens, each a different color for clarity: Import Setup Screen (gray), Field Mapping Template (black) and Update Setup Screen (white). The Update Setup Screen is not presented when you are importing NEW Main Contacts.

Further information on Import/Update is found in the below links, which can also be accessed directly from the appropriate screens:

This operation takes incoming data and attempts to find a match in your Target Contact File. If a match is found, the fields you mapped update the Contact fields. By default, GoldBox-5 updates all the fields you mapped. If the incoming value is exactly the same as the value already in the GoldMine field, GoldBox-5 transparently sees this as a non-update of that field and will not add a Tlog entry (for sync).

For more information on Record Match, please see Update/Attach Record Match

You can use the Update Main Fields operation to stop duplicates from getting into GoldMine, and you can also update just a certain number of fields on a match, but add an entire new record if no match is found. For more information, please see the {Shield} button at Import Field Mapping Template. Also see the {Records finding NO MATCH: Add as NEW?} checkbox at the Update Setup Screen

The above 2 links also provide information on the many options available to you when updating, such as conditionally updating individual fields, Notes updating and writing back to the Incoming File for Matches or No Matches.

GoldBox-5 provides an additional service during updating. In the Updater Contact File, the CREATEBY field is used to note whether the record found a match (the word DIDMATCH appears in the field) or whether the record did not find a match (the word NOMATCH appears in the field). You could then go into GoldMine, select the Updater Contact File, reset its Contact1 indexes, then build a filter or Group based on this field value to determine the success of the operation.

This is the most critical issue to resolve when updating or attaching. Without a reliable record match, the 'parked' records in Updater won't find their mates in the Target Contact File, and therefore nothing gets updated or attached.

For Updating Main Fields, you can use any of the fields in the Target Contact File, including all user-defined fields.

When you are attaching to Tabs, you can choose from 21 fields for Record Match. These are:

Typically you'll use no more than 3 fields to match, and in most cases, just 1. The key point is that the result of your Record Match cannot exceed 100 characters (that's a Visual dBase limitation). For example, the following is a Record Match of exactly 100 characters:

The Company and Contact fields are both 40 in length, and that plus the 20 from Phone1 equals 100.

This is most critical: Before you even begin the import into Updater, you have to determine the record match field or fields from the Incoming File. You have to know this beforehand so you'll know where to map the field(s) when at the Field Mapping Template.

[Primary Filter] This prompt should be selected after you have mapped your fields! In most cases you probably won't be using a filter - you'll want to import all of the Incoming records, which is the default. However, you can limit the import by evaluating any of the Incoming fields. For example, if you have a huge mailing list Incoming file but only want to bring in those contacts located in California, your filter might be: Upper(Trim(InFile->TheState)) == "CA" Another time-saving capability of the filter is when you are Attaching Tab records. Each Tab Import requires that at least 1 or 2 fields contain a value, otherwise the Tab record is not attached. They are called "Tab critical" fields and are discussed a bit later on. The non-attachment of Updater records which have an empty "Tab critical" field is handled transparently in GoldBox-5 during Phase 3, the Attach phase. But you can prevent the record from coming into Updater in the first place, thereby saving time. For example, if you are importing into Additional Contacts, the Contact Name is the "Tab critical" field. To prevent blank incoming records from coming into Updater, your filter might be: .NOT. Empty(InFile->Name) .

[Contact File] If importing NEW Main Contacts, this is Contact File receiving the NEW records. Example: The COMMON Contact File. If updating or attaching Tab records, this is the Target file. It is the Contact File to be updated or against which Tab records will be attached or updated. Example: The COMMON Contact File.

(Contact File) Displayed as a prompt in case GoldBox-5 cannot open a path formatted with UNC, with the double-backslash as the first 2 characters. Replace with standard Drive-Colon-Backslash if necessary.

[Incoming File] This is the dBase file you are importing. You'll notice that only files with the extension of DBF are listed. If your incoming dBase file does not have an extension of DBF, please rename it!

(Incoming File) Displayed as a prompt in case GoldBox-5 cannot open a path formatted with UNC, with the double-backslash as the first 2 characters. Replace with standard Drive-Colon-Backslash if necessary.

[Browse] Allows you to open the Incoming File in the GXW Browser.

[Updater File] NOTE: This button and its related prompt will not appear if you are importing NEW Main Contacts. Normally the Updater file is pre-selected by GoldBox-5 and you can skip past it. If you don't have an Updater file, GX5 reminds you to create it in GoldMine® and provides the steps.

(Updater File) Displayed as a prompt in case GoldBox-5 cannot open a path formatted with UNC, with the double-backslash as the first 2 characters. Replace with standard Drive-Colon-Backslash if necessary.

[Write Back] NOTE: This button and its 2 related text display entries will only appear if you are importing NEW Main Contacts. They will be hidden for Updating and Tab Record Attachment. This option allows you to write back a value into a field of the Incoming File if the incoming record was imported into GoldMine. For example, you may wish to write the GoldMine® Account Number into an Incoming File field. Or, maybe just replace a logical field with the value TRUE. You are first prompted to select the Incoming field, then the expression with which to replace it. For example, the Incoming field might be InFile->Gmacct and the expression would be Contact1->Accountno.

(Qty to Import) How many records you wish to import. The number 0 means import ALL, the default. Typically you might change this to 10, for example, if you are testing. Make sure you put it back to zero when you do the actual import!

(Start w/Rec #) Which record in the Incoming File that you wish to start importing. The default is Record # 1, the default, which translates to the top of the Incoming File. Typically you would change this if an import errored out for some reason and you need to restart it.

(Name Format) Incoming Names typically are tracked 1 of 3 ways: 1 = First-Name Middle Last-Name as in David E Petonic. 2 = Last-Name, First-Name Middle as in Petonic, David E. 3 = Last-Name First-Name Middle as in Petonic David E. The default is 1. This precludes you from having to write a complex expression to turn a name around, since GoldMine® tracks contacts as First-Name Middle Last-Name.

(Default Area Cd) For USA / Canada phone numbers: If incoming Phone numbers are missing the area code and you know the common area code for them, you can key it in here. GoldMine® requires the area code for each phone number.

(Translation Label) This prompt relates to the Field Translation check box discussed below. It defaults to the current Launch ID. The Launch ID is used as the first part of the search value when using the Field Translation feature. The 2nd part is the GoldMine® field name. For doing similar imports and using field translation entries you have already keyed in for another Saved Setup, change the name to that saved IG file name (leave off the .DBF). This precludes you from having to rekey the entries (or duplicate them). NOTE: Be extremely careful about which Field Template name you key in! The Launch ID can be seen in the list box when you select {Saved Setup}. Please DO NOT add the DBF extension to this prompt.

GoldMine® stores phone numbers in (###)###-#### format unless International. Regardless of the incoming format (I.e. ###-###-####) GoldBox-5 puts the number into GoldMine® format if you select this option.

Incoming numbers are determined to be USA format only if, after stripping out non-number characters, the length of the remaining value is either 7, 10 or 11. If it's 7, the default area code is inserted. If it's 11 and the first character is a (1), the (1) gets stripped off and number is assumed to be USA. If the length is anything other than 7, 10 or 11, the number is replaced into the field with no formatting and the phone status is flagged International.

Phone numbers auto-formatted by GoldBox-5 include PHONE1, PHONE2, PHONE3, FAX and user-defined fields added which begin with UPHONE as the first 6 characters of the field name.

The 1st character of the STATUS field in GoldMine's Contact1 file determines whether all phone numbers are displayed as is (International) or USA format.

Recommendation: You may want to make 2 passes over the incoming data. The first pass would bring in all USA/Canada numbers, and you would check this option. You can set a filter to identify USA records Example: USA() .OR. CANADA() .OR. USTERR(). The 2nd pass, for non-USA format, would have this as the filter: .NOT. USA() .AND. .NOT. CANADA() .AND. .NOT. USTERR(). These 2 expressions are shipped with GoldBox-5 and are accessible by pressing [Get] at the Filter and Expression Builder.

Bonus: GoldBox-5 transparently handles the situation where a title appears as the last word of the Contact name. Examples:

In each of the above cases, the last name will be correctly placed into the LASTNAME field. In the 2nd example, GoldBox-5 will walk back 2 fields to put the word "Mason" into the LASTNAME field.

See the option {File} {Last Name Title Exceptions} for more information.

NOTE: If some of your incoming contact names begin with Mr., Ms. or Mrs. you may want to press [Exprn] at the Field Mapping Template for the CONTACT field and select StripMrMs() from the Functions list box. This will strip the salutation off, so that the actual first name of the Contact is placed in the DEAR field.

This option provides you with a powerful shortcut to doing global replaces as each record is imported. Essentially you are translating a field from one value to another. GX5 takes the field value, searches the Translator table for that value, and if it finds it, returns the value to replace it with. Otherwise, the original value is retained. It is exceedingly powerful if your import happens to require this capability and will save you a ton of work.

The Translation values are setup at the Field Mapping Template. See [Translt] button at the Field Mapping Template section later for more information.

[Grp-Ref] This button allows you to overwrite the default Group Name with an expression of your own. Mostly you should not do this, except if you wish to add to an existing Group. The default expression which appears to the right of this button is nice because it marks the date and time of the Import. If you do choose your own Group Name, you would key it in literally, surrounded by quotes, at the Filter and Expression Builder.

<Grp-Ref> Displays the result of the Group Reference expression. As mentioned, stick with the default unless you are adding to an existing Group.

Please see Plug-In Tab Records

[Map Fields] The final item at the Import Setup Screen, this takes you to the Field Mapping Template. Underneath this button is displayed the name of the Template.

After exiting the Field Mapping Template, remember you can click the [Primary Filter] button now to limit the Incoming records. As previously stated, most of the time you won't do this - you'll be bringing in all the Incoming records.

If you are happy with the Import Setup so far, click [GO}. If you are importing NEW Main Contacts, GX5 presents the Setup Action Box and you can choose the next action.

When you click [GO] on an Update or Attach to Tabs procedure the first time, you are taken to the final major screen of the Import, the Update Setup Screen, the dreaded white screen. Just kidding - nothing to dread here. If you re-access the Import Setup Screen later, you can access the Update Setup Screen again, anytime, by clicking [GO] and selecting * Go to Update Setup * from the Setup Action Box.

Import Setup Screen

Update Setup Screen

When you press the [Map Fields] button, a lot is happening behind the scenes before the screen is displayed. We'll only mention 4 things though, because you may receive an acknowledgement prompt before you can proceed.

1. GX5 checks that the Contact File, Incoming File and Updater File are valid. If not, you are informed and are placed at the offending button to reselect. Also, GX5 checks that the file extension is DBF.

3. If you are doing a {NEW Main Contacts} import, GX5 helps you out by attempting to auto-map as many fields as it can. This only happens the first time you press the [Field Mapping] button. You are informed to verify that the auto-mapping went according to your intentions. If not, you can blank out the field(s) or substitute another field when you get to the Field Mapping Template.

4. If you are re-accessing the Field Mapping Template, GX5 verifies that the fields you have mapped actually exist in the Incoming File. If one does not, GX5 keeps the mapped field, but offsets it by a space so that the GX5 Batch Processor will ignore it and not error out. GX5 could have been programmed to just blank it out, but it is offset instead so you can see what was originally mapped. Normally this won't be a problem, but sometimes, an Incoming File is altered after the Import Setup. Maybe you add, modify or delete a field name in the Incoming File. This feature is a graceful way to inform you that your original field mapping no longer conforms to the current state of the Incoming File. And of course this will occur if you select a different Incoming File from the one you originally chose and mapped!

A Field Mapping Template stores the mapping of incoming fields, or dBase expressions, or both, to your GoldMine® fields. It is a standard dBase4 file, stored in the GoldBox-5 directory. The file name starts with IG and ends in DBF as the extension. This is also the Launch ID for the Import Saved Setup. So, when you access the Field Mapping Template, you are actually browsing a DBF file.

A lot of information is displayed on this screen so let's take a look and find out what it all means:

At the very top, the blue title bar displays the Template name, which is the Launch ID. Also in the blue title bar, one of 3 things will display: VALUE >> followed by the value of the Incoming field you have mapped; VALUE >> followed by the result of an Expression; or, nothing will display, meaning nothing is going to happen on that GoldMine® field.

Directly underneath are the 22 buttons for various actions on the Template and we will skip past these for now. Each will be documented a bit later.

Under the 22 buttons is a Status Bar. This displays which Incoming record is currently being viewed, the Incoming File name, how many Fields it contains and how many Records. To assist you in Field Mapping, you can move around in the Incoming File to view certain records. Those buttons will be discussed shortly.

Just to the right of the Status Bar are the 2 buttons which allow you to instantly access the Top of the Template [7 T] or the Bottom [8 B].

The black screen area is the Template itself. The first 4 columns are self-explanatory, showing the GoldMine® Field Description, Field Name, Type, Length and Decimals. The "L" column is normally blank but will contain the small letter "x" if you want to populate a GoldMine® Lookup list for that field. Next we see the 3 columns with the headings of Field-1, Field-2, Field-3.

Field-1 is where most action occurs, and it displays in light-blue to stand out. Only 1 of 3 things will be in this column: It can be blank, meaning nothing will happen for that GoldMine® field. It can contain an Incoming field name. Or, it can contain the small letter "e", which means only the result of an Expression will populate the GoldMine® field.

The Field-2 and Field-3 column usage is related for character-type fields. The sole reason for their existence is to make it easy for you to import multiple incoming fields into one GoldMine® field without having to key in an expression. Example:

In the Incoming File, the Contact is stored in 3 separate fields:

FIRSTNAME, MIDNAME and LASTNAME. Plug FIRSTNAME into Field-1, MIDNAME into Field-2 and LASTNAME into Field-3. GoldBox-5 prompts you for a custom separator between fields. Usually you'll take the default of a space, but in the case where a zip code is stored in two separate fields, you may want to choose a hyphen. You can key in X for no separator (if for example, a zip code is stored in two fields and the second field contains the plus 4 already with a leading hyphen). When importing 3 lines of text into a Notes field you can choose a character return/line feed as the separator to make the lines of text appear on separate lines in the Notes entry.

Please note that if, for example, you only had FIRSTNAME and LASTNAME fields you would plug them into Field-1 and Field-2. You cannot skip Field-2 and then plug something into Field-3; GoldBox-5 won't see it and your data won't be properly imported.

On GoldMine® fields which are Date or Numeric type, the Field-2 and Field-3 columns are used only to display helpful information and are ignored by the Import process.

If you click on the scroll bar at the bottom, several other columns reveal themselves.

The DBF-File column is the actual name of the GoldMine® file being imported into.

The Ord column shows the order in which Expressions are implemented.

The Field Replace Expression column is self-explanatory.

The Sep column shows separator values between Field-1 and Field-2, and between Field-2 and Field-3, if you are mapping into them, as discussed above.

None of the columns are directly editable - you access them via the buttons at the top.

When GoldBox-5 imports, each record is taken care of entirely before moving on to the next one. The following is the sequential action taken on each imported record:

1. GoldMine® fields are first replaced with the Incoming File's field. If you have a small "e" in Field-1, it is not evaluated here, but in step 2. If you pressed [UPPER] at some fields, their values are converted to UPPER CASE. If you pressed [Proper] at some fields, their values are converted to Proper Case and the WordSwap() function is automatically invoked. If you have ending titles in the Incoming contact name, GoldBox-5 'walks back' to get the actual Last Name and puts it into the GoldMine® LASTNAME field. NOTE: When GoldBox-5 imports into GoldMine® character fields (most are character), the values are always flushed left automatically, regardless of whether the incoming value had leading spaces. This is OK most of the time. A possible problem would be if you import a numeric value into an indexed GoldMine® field, like a KEY field, which you want to be properly indexed. You can get around this in GoldBox-5 by pressing [Exprn] to reinsert leading spaces, as in: Example: PadR(Trim(Contact1->Key2), 8) This forces all numbers to have up to 8 leading spaces so that they line up properly for the index. If you wanted to pad the number with leading zeros the expression is: PadR(Trim(Contact1->Key2), 8, '0').

2. The expression column is then examined, by default, from top to bottom. If you use the [Order] button, they are evaluated according to the numbers assigned. Expressions found are evaluated and fields are replaced with the results. This allows you to use an imported field value in an expression to replace another field.

4. Finally, if you marked fields by pressing [Lookup], GoldMine® lookup lists are built from the imported values.

In the black screen area, there is a large black arrow on the extreme left which indicates the current record. You can use the mouse to rest on a different record, or use the Up and Down arrow keys. You can also press PgUp or PgDn to advance 1 screen up or down. If you press Home, you are taken to the 1st column, the one labeled "GoldMine® Field Description". Press End, and you are taken to the last column, the one labeled "Sep".

And now to those 22 color-coded buttons up top - in just a moment.

One of the buttons, [Map] is defaulted, which means that if you press Enter, it is selected. This is certainly the most common button you'll be pressing which is the reason for the default. A note about pressing Enter to select [Map]. Although you can't see it (because Edit mode is turned off in this Browse window), every time you press Enter, the next column in the black screen is accessed. Eventually you'll see the display shift one column to the left. Simply press Home to re-establish the "GoldMine® Field Description" as the 1st column. Sorry - nothing can be done about this - it's the way Visual dBase7 operates. Another way to select [Map] is to double-click with the left mouse button at the record of your choice. That does not shift the display. So, to review, there are 4 ways to select [Map]: Click the [Map] button once; Double-click in the black screen area at the desired record; Press Alt-M; press Enter.

The buttons up top are color-coded for specific purposes:

Blue: General-purpose buttons, most having to do with setting up a GoldMine® field.

Black: Allows movement within the Incoming File, strictly to assist you in mapping fields.

Green: Visible and active only for the Update of Main fields.

Red: Exit from the Field Mapping Template.

We will start with the [Map] button, move left, down and then right to cover all 12 blue buttons.

[Map] Pops up a white list box in the center of the screen of all Incoming fields, in the order in which they appear in the file. There's more to this list box than is immediately apparent.

The first item in the list is <Blank Out>. Select this to blank out, or space out, a field you had previously mapped. There is also one of these at the bottom of the list. The description is <* = Mapped> but the effect is the same.

Speaking of which, you will notice that an asterisk appears at the far right of some fields. This indicates Incoming fields which you have already mapped, just as a bit of helpful feedback.

You can select a field by highlighting and double-clicking, highlighting and pressing Enter, or highlighting and clicking the [Go] button at the bottom.

If you selected [Map] on a GoldMine® field which has an Incoming field in the Field-1 column, that field is the one initially highlighted. Otherwise, GoldBox-5 tries to anticipate your mapping by moving 1 down in the list.

If you click [Find], GoldBox-5 will take you to the GoldMine® field where the highlighted field has been mapped. Use the asterisk marking as a guide to determine whether the field is currently mapped.

Finally, you can click [Cancel] or press Esc to abort mapping a field.

[Map-2] This is only active for character-type fields and will allow you to map a field to the Field-2 column, as discussed previously. You must have a field mapped to Field-1 before invoking.

[Map-3] Allows you to map a field to Field-3. You must have a field mapped to Field-2 before invoking.

[DtMask] Short for Date Mask, this is only active if you are mapping to a date-type field and the Incoming date value is character-type. It is automatically invoked when you first map the field.

A list box appears with a selection of date format types. To help you select the correct one, it is best to look directly above the box to the VALUE >> entry which is now grayed out but still visible. It will show you the Incoming date as it appears in the Incoming File. If it's blank, you might want to use the black buttons to find an Incoming record which has a value in that field.

When you select a value from the | Date Mask | list box, the format code you chose appears in the Field-2 column as a guide.

[UPPER] This is a toggle. Forces the mapped value to be UPPER-cased in the GoldMine® field. The capital letter "U" appears at the end of the "GoldMine® Field Description" as an indicator.

[Proper] This is a toggle. Forces the mapped value to be Proper-cased in the GoldMine® field. The small letter "p" appears at the end of the "GoldMine® Field Description" as an indicator. When this is active the WordSwap() function is automatically invoked by GoldBox-5 to replace words that may have been inadvertently decapitalized (such as IBM or AT&T). These replacements are based on the SWAPWORD.DBF file, which you can access and modify at {File} {Word Swap File}.

[SubFilt] Provides instant access to the Sub-Filters file, if, for example, you wanted to use the Generate() function to put a sequential number in a field. You can also access this file from the {File} tab at the Top Level Menu.

[Translt] This lets you build a list of Translation entries for the field. You are taken to the Field Translator Table in the GXW Browser. If there are no current entries for the field, a blank one is created. You'll see that the Label column is populated with the Launch ID and the Field column is populated with the GoldMine® Field you wish to translate. You should not edit these entries. The only columns which should be modified are the Existing Value column and the Replace With column.

Here are the 2 translation options available:

Incoming Value matches the value in the Existing Value column: This is the most common usage of the table. You key a single value into the Existing Value column, corresponding to the Incoming value. Example: Press the [Translt] button at the Contact TITLE field. Key in VP at the Existing Value column. Key in Vice President at the Replace With column. As each record is imported, if the incoming TITLE is VP, it is replaced with Vice President. Example: Key in SLM at the Existing Value column. Key in Account Executive at the Replace With column. If the incoming TITLE is SLM, it is replaced with Account Executive. Example:

IGMAIN TITLE VP Vice President

IGMAIN TITLE SLM Account Executive

IGMAIN TITLE FINO Financial Officer

IGMAIN TITLE PRES President

IGMAIN TITLE MKTE Marketing Executive

Another example is when importing into Calendar (or History), and the incoming Sales Person name is a full name, but you want to translate it to a standard GoldMine® User Name of 8 Upper-case characters. Example for Calendar:

IGCAL DEPARTMENT WILLIAM KIRK WKIRK

IGCAL DEPARTMENT LEONARD SPOCK LSPOCK

IGCAL DEPARTMENT DEFOREST MCCOY DMCCOY

IGCAL DEPARTMENT NICHELLE UHURA NUHURA

Incoming Value is contained within a range: Assume you want to replace the KEY1 field with a sales territory based on zip code ranges. Import the first 5 characters of the zip code into KEY1 (you can use the [Exprn] button to remove Zip+4 characters as in: SubStr(Contact1->Key1,1,5). Then set up the table like so:

44000|44060 WEST

44200|44220 CENTRAL

44303 SOUTH

44400|44500 NORTH

When GoldBox-5 finds a pipe character in the Existing Value column, it converts the value to the left of the dash as the low end and the value to the right into the high end. The incoming value is then evaluated to see if it falls within the range provided by the low and high ends. If so, the Replace With value is written into Contact1->Key1. Zip-codes-into-territories is just a single example of this powerful capability. Another example might be to rate your customers based on their total sales, or number of employees, etc.

[Lookup] This is a toggle. Note: This button is disabled when Attaching to or Updating Tab files.

You can add the final field value to GoldMine's Lookup file as the last step before GoldBox-5 advances to the next record. This saves you from rekeying data into F2 Lookup entries in GoldMine.

This feature exists only for Importing directly into the Target file or Importing to later Update the Target file. It is not available for Attaching to Tab Files. It is active only for character and numeric type GoldMine® fields, including any user-defined fields. It is inactive for the ZIP/POSTAL CODE and NOTES fields.

The Main Lookup file in the GMBASE path of GoldMine® is the only one addressed by this procedure.

The small letter "x" appears in the L column when active.

[Order] This is very rarely used but is handy in the following example: Let's say you are using an expression to populate the UserDef10 field. You then want to take the result of what is in UserDef10 and use it in an expression at the Key2 field. Since GX5 by default evaluates the Expressions from top to bottom, this won't work, because the result won't have been put into UserDef10 before the Key2 expression can use it. In this case, click [Order] and change the number to anything higher than zero. That will cause all the other Expression results to be done first, then the Key2 expression.

[Exprn] Allows you to apply the result of an expression to the GoldMine® field. The Field Replace Expression is classified as either exclusive or secondary. Exclusive simply means that the expression is the only thing happening to the GoldMine® field. For these, the small letter "e" is written into the Field-1 column. Secondary means that you are first mapping an Incoming value to the GoldMine® field, then applying an expression result to it.

Exclusive example: GoldMine® field: UserDef01. You wish to put a USA time zone into User-defined field #1. Click [Exprn] and select the GeTimeZone() function from the Functions list at the Filter and Expression Builder. Make sure you close the function off with a right-parenthesis. When you [GO] back to the Template, you will see a blue small "e" in the Field-1 column, and the GeTimeZone() function in the Field Replace Expression column.

Secondary example: GoldMine® field: Contact. Incoming names are coming in with the Mr. or Mrs. or Ms. in front of them and you wish to strip it off. You would first map the Incoming field to the GoldMine® field. Then click [Exprn] and select the StripMrMs() function. Make sure you close the function off with a right-parenthesis. The Field-1 column won't show the small "e" because you have a field mapped to it. But you will see the StripMrMs() function call in the Field Replace Expression column.

Please note that all fields of the GoldMine® record which are receiving Incoming values can be used in your Field Replace Expression. You can also use any of the Incoming fields. For example, the following Total Sale Amount could be placed into the UserDef01 field:

Ltrim(Str(InFile->Quantity * InFile->UnitPrice, 12, 2)) This multiplies the Quantity times the Unit Price, uses the Str() function to turn that result from a numeric to a character value (because UserDef01 is a character-type field), and then trims off leading spaces with the Ltrim() function.

Previously we discussed how GoldBox-5 only imports into Contact1 or Contact2 fields, even if you are Attaching to Tab files. This is important to remember when keying in a Field Replace Expression. Always write the expression using the actual Contact1 or Contact2 fields! As an example, when importing into Details, the Detail value is temporarily stored in the Updater File's Contact2->PrevResult and the Reference in Contact2->Comments. You'll notice that the actual Detail fields, ContSupp->Contact and ContSupp->ContSupRef, don't even appear in the | Fields | list box at the Filter and Expression Builder. Also, when importing NEW Main Contacts and you wish to plug in Tab records, you'll notice that those File and Field names are different as well. The File name begins with GX4_ and the field name is not a standard GoldMine® field name. Example: GX4PROF->PF1_REF (The Reference field of the first Detail record you are plugging in). This point is made as a reminder that when using a field in an expression, always select it from the Fields list box when you are at the Filter and Expression Builder screen.

The pipe character |, which is normally found on the backslash key, is used in the Field Replace Expression for a very specific purpose: When updating the CONTACT1 or CONTACT2 fields, you can replace the Target field with an expression instead of the Updater field value. The pipe is used to split the Expression into 2 separate expressions in case you first want to use one during Phase 1, when importing into Updater, then another during Phase 3, when updating the Target Contact file. Even if you are not using a Phase 1 expression, you must still start the expression with the pipe so GoldBox-5 sees it as an Update expression and only uses it during Phase 3. Example:

str(val(contact2->userdef01),8) | iif(val(c2upd->userdef01)=0, 'X', c2upd->userdef01)

pipe

The statement to the left of the pipe is evaluated during the Phase 1 Import into Updater, which replaces Updater's User-Defined 1 with the incoming number padded by up to 8 leading spaces.

The statement on the right of the pipe is ignored during the Phase 1 Import, but is evaluated at the Phase 3 Update, where Target's fields are replaced by Updater values. The expression says: If the numeric value of the Updater value is zero, EMPTY is written into the Target's User-Defined 1. Otherwise, the Updater's User-Defined 1 value is used. Important points when using this powerful feature:

• ALWAYS use the pipe as the first character of the expression when you are not using one for the Import but are for the Update.
• The Target field values must always be preceded by Contact1-> or Contact2->. The Updater field values are preceded by C1Upd-> or C2Upd->. You can use any fields from Target or Updater, not just the one being replaced, in the expression for evaluation.
• You will always receive a standard Visual dBase7 error box if you click on [Result] for a pipe expression, because naturally dBase7 does not know how to evaluate the pipe. Also, the C1upd and C2upd files are not open at this time and that in itself would cause the error. If you do click [Result] here, just click [Ignore] to get past the error box. You'll simply have to ensure that the expression is correct by your own evaluation before proceeding. If it is truly incorrect, the GX5 Batch Processor will catch it and halt. This is why it is advised to test operations out thoroughly.
• Space and your imagination are the only limits for this feature.

[Lock] Allows you to lock, or freeze, a number of columns starting from the left. They will stay frozen to the left as you scroll right in the Browse window. Key in zero to unfreeze all columns.

[>>] Moves ahead 1 record in the Incoming file. Click the [25] to move 25 records ahead.

[<<] Moves back 1 record in the Incoming file. Click the [25] to move 25 records backward.

[Browse Incoming] Allows you to open the Incoming File in the GXW Browser. Here, you can view the entire record, locate any record, etc.

[Blank] This is a toggle. Inserts a small "b" in the GoldMine® Field Description column, and also inserts a pipe expression at the Field Replace Expression column. We discussed pipe expressions above in the [Exprn] button section, so this is a great way to see what is meant by a pipe expression and how one is properly written. This expression means: If the Updater value is blank, do not blank out the corresponding Target value. When you toggle this off, the "b" is removed and so is the expression.

[TgtVal] This is a toggle. Inserts a small "v" in the GoldMine® Field Description column, and also inserts a pipe expression at the Field Replace Expression column. We discussed pipe expressions above in the [Exprn] button section, so this is a great way to see what is meant by a pipe expression and how one is properly written. This expression means: If the Target value is not blank (has something in it), do not update it with the Updater value. When you toggle this off, the "v" is removed and so is the expression.

[NoteAp] This is a toggle. When you are updating Notes, the default GX5 uses is to completely overwrite the Target Main Note with the Incoming Note value. However, this button allows you to append to an existing Target Note value. When you select it, the GoldMine® Field Description column will read: NOTES {NotAp} APPEND TO. Also, the Len column for this field will read -10. When you toggle it off, the GoldMine® Field Description column will read: NOTES {NotAp} OVERWRITE. The Len column will read 10. Later, at the Update Setup Screen, several check boxes are displayed for Notes updating, and these will be discussed later. Incidentally, the {NotAp} in the GoldMine® Field Description column does not mean Not Append. It is just an indicator to press the [NoteAp] button to change.

[Incrmnt] This is a toggle. It is active for Numeric fields only. Allows you to increment the Target field's numeric value (add to it) instead of completely replacing it. You could do this with a pipe expression too but this is so much easier. The word *INCREMENT* appears in the Field-2 column when this is toggled on.

[Shield] This is a toggle. It always means, when toggled on: Never update the corresponding Target field. The letter 'X' will appear in the GoldMine® Field Description column. This is a very powerful option, used for two particular situations when updating.

Situation #2: You want to 'dump' incoming data into Updater fields so you can use their values in pipe expressions. For example: You are updating GoldMine® from an accounting system. You track both Prospects and Clients in the same Contact File. Prospects and Clients are assigned ID numbers by the accounting system, but when a Prospect becomes a Client, the ID number changes. The record match is in Key1, the ID number. When Prospects become Clients, the accounting system gives you a file with the old ID number and the new one. You want to match to Target using the old ID number found in Key1, then change that ID number to the new one, all in the same operation. Sounds impossible but GoldBox-5 can handle this. Plug the old ID number into Key1. Plug the new ID number into UserDef01, then click the [Shield] button so the 'X' appears in the Description column. At the Key1 field, click [Exprn] and key in a pipe expression like so: | C2upd->UserDef01 The effect of this is: During Phase 3 of the operation, GX5 finds a match in Target using Key1, then replaces the Key1 field with the result of your pipe expression. The Target's UserDef01 field does not get updated, because you shielded it.

[Quit] Always select this button to exit from the Field Mapping Template. You are taken back to the Import Setup Screen.

Remember that you can always press [Map Fields] to go back to the Field Mapping Template, and your previous work is automatically saved.

[Update Filter] For Update Main Fields: This is optional and most of the time you will ignore it. The filter is examined during the Phase 3 Update. There is a twist with this filter, so please be careful. The twist is: If the result of the filter is TRUE, the Target record which Updater is matched to is bypassed and is not updated at all. For example: You use Key Field #2 to track the type of contact - Vendor or Client. You don't want to update Vendor records. Filter is: Upper(Trim(Contact1->Key2)) == "VENDOR"

This button operates in a completely different way for Attaching to Tabs.

Tab Attach, Update, Dupe Detection

[Record Match] Required entry. The most critical item on this form. Click and select a Field expression for matching an Updater record to a Target record. Pick fields only from the Fields list box. You can string fields together, or even pieces of them together. The result of the Record Match cannot exceed 100 characters.

If you are Attaching to Tabs, the Record Match is defaulted to the field(s) you selected for Record Match at the Field Mapping Template. There are 21 fields you can choose from when Attaching to Tabs: Company, Contact, Phone1, Secr, Key1 through Key5, Country, Accountno and UserDef01 through UserDef10.

For Updating Main Fields, you can use any of the Main fields in the match, including user-defined fields.

Naturally the best match is a single field such as an Account number or ID number. But you can go for a multi-field match like so: Contact1->Company + Contact1->Contact + SubStr(Contact1->Phone1, 1, 20)

You can also use the GoldMine® ACCOUNTNO field as Record Match. Sometimes you might generate the Incoming file from your GoldMine® data itself, via a GoldBox-5 Export. For example, maybe you imported (in GoldMine® or GoldBox-5) and the Incoming data was all in UPPER case. You want to make the fields Proper case. Best way to do this is export out the UPPER-cased fields along with the GoldMine® Account #, and use the ACCOUNTNO field as Record Match. Plug the other fields in where they go, and press the [Proper] button for each. Return to the Import Setup Screen, press [GO], and at this button of the white screen, [Record Match], you would choose Contact1->Accountno as the Record Match field.

Scenario #1: You are doing an Update of Main Fields and the Incoming data is spread across 2 or more files. For this example, let's say it is contained in 2 identically formatted files, called InFileA.dbf and InFileB.dbf. You would first create a Setup for bringing in InFileA data. When you get to the Update Setup Screen, do not select this option. DO select the option to STOP after Import of records into Updater. Run the operation. Now create a Setup to bring in the InFileB data. At the Update Setup Screen, select this option. Run the operation. The effect of this is: When bringing in the InFileA data, GX5 exits out of the operation after 'parking' the records in Updater – Phase 2 and Phase 3 never occur. When bringing in the InFileB data, GX5 won't clear out the Updater Contact File, but will add the InFileB data to it. The records already in Updater are from the InFileA operation. For the InFileB operation, the InFileB data is added to Updater and Phase 2 and 3 of the operation proceed normally. This saves some processing time, since the Phase 2 and Phase 3 portions of the operation did not occur for the InFileA data. It happens all at once during the InFileB operation.

Scenario #2: You are importing NEW Main Contacts. Your Incoming File, let's call it InFile.dbf, contains 10 sets of fields to track equipment. You want to put these in as Detail records. GoldBox-5 nicely handles the first 6 pieces of equipment, because you can 'plug in' up to 6 Details. Now you have 4 sets of fields left, and you must make a separate pass over each.

For bringing in Set # 7: Select the option to STOP after Import of records into Updater.

For bringing in Set # 8: Select the Do Not ZAP option and the option to STOP after Import of records into Updater.

For bringing in Set # 9: Select the Do Not ZAP option and the option to STOP after Import of records into Updater.

For bringing in Set # 10: Select the Do Not ZAP option only.

The effect of this is that you do only a 'park' of incoming data for Sets # 7, 8 and 9. Set # 10's operation brings in the Set # 10 fields, then proceeds normally with Record Match (Phase 2) and Attach (Phase 3).

Note: If you import an email address and plug it into its spot (actually the Contact2->NextAction field), it will be attached properly when the New record is added. Normally you would [Shield] this field at the Field Mapping Template. However, this is not how to add email addresses to existing records. You would choose {Batch} {Import/Update} {Email Addresses} and attach them just like any other Tab type.

[Incoming Field] This is the field in the Incoming File you wish to replace. You will be prompted for the Field and the Field Replace Expression.

[Incoming Record Match Field Expression] An index tag in the Incoming File is created based on this expression. That allows GoldBox-5 to take the Record Match value from an Updater record and find it in the Incoming File immediately, so its field can be replaced. Please ensure that the Record Match result for the Updater is the same length as this expression result. Example: Your Record Match expression is: Contact1->Company + Contact1->Contact. Each of these fields is 40 characters. But in the Incoming File, the Company field is only 35 characters and the Contact is 30. To match up the lengths, your expression here would be: PadR(Incoming->CompName, 40) + PadR(Incoming->ClientName, 40) (Again, this assumes the incoming DBF name and the field names - substitute your own).

[GO] Always click this button to exit so that your item entries are saved. You are returned to the Import Setup Screen. You are now ready to Run the Operation, assuming that your Import Setup Screen and Field Mapping Template are complete.

You can do further processing on records, for example, which did not find a match. GoldBox-5 flags unmatched records by putting the word NOMATCH in the CREATEBY field of the Updater records. If desired, you could then run a GX5 automatic export of these records, re-import them into Updater, then use a different matching scheme to try and find a match in the Main Contact File. If you are going to run a GX5 Export on records in Updater, remember to first pass in a call to rebuild the Updater indexes, since GX5 does not maintain Updater's indexes (greatly increases the speed of the operation). Typically you'd have 3 entries in a Q-file for the Index Rebuild, the Export and the Import/Update using a different Record Match. You can also use this method if you want to run an update against more than 1 Target file. You could export NOMATCHes out of Updater and use that as the Incoming File to match against a different Contact File. Typical case: You track Clients in one Contact File and Prospects in another.

This rectangle and its associated 6 check boxes appear at the bottom of the Import Setup Screen only when you are importing NEW Main Contacts. It is not visible for any other scenario.

PLEASE NOTE: If your incoming Tab data is held in related tables, you must use the standard routine of importing into Updater, then Attaching.

The "plug in" of related Tab records at the same time you are importing full Main Contact records puts the GoldBox-5 import in a class by itself. This situation is ideal if you are importing from a flat file, such as a D&B file, where Tab data is held in sets of fields in the same record as the Main data. It precludes you from having to make separate passes over the flat file into Updater, and then Attaching. It is also handy if you just want to "hard code" Tab records as new contacts are imported (for example, an A/P Track or Scheduled Callback). The process allows you to plug in, all at the same time, up to:

Tab Record Tab Critical Field(s)

• 15 Additional Contacts Contact Name
• 15 different types of Details Detail and Reference
• 15 different Scheduled Activities (Calendar) Activity Date
• 15 different Completed Activities (History) Activity Date
• 2 Automated Process Tracks Track # (Title Field)
• 1 Linked Document File Path

The fields for the Tab record plug-ins appear after the Main and User-defined fields at the Field Mapping Template (the "black screen"). Simply go to as many records as you need and plug in their values, just as if they were incoming Main or User-defined values. You can press [Exprn] at any fields in the plug-in area and hardcode an expression. Basically, all the keys and capabilities active for the Main and User-defined fields are valid here, except for [Lookup] (add to Lookup List).

GoldBox-5 has hardcoded logic in it to examine the one critical field for each Tab record type to determine whether the record actually gets added. In other words, if that critical field is blank, the record is not added. Please examine the above table to be aware of the critical field(s) for each Tab record type.

Please note that if your incoming flat file has, for example, 17 sets of fields for Additional Contacts per Main contact, you can still get them all in. You would bring in 15 with this method, then make a separate pass over each of the 2 remaining into Updater, and Attach.

To plug in Tab records, check the appropriate item in the PLUG IN rectangle.

The following is specific information for each Tab file type and is germane when you are at the Field Mapping Template:

You can plug in up to 15 Additional Contacts. The CONTACT field must contain a value in order for GoldBox-5 to attach the record.

The fields for this Record Type are pretty straightforward. Please note that if you plug in a value at the EMAIL field, GoldBox-5 will automatically create the related E-mail Address Detail record.

You can plug in up to 15 Detail records. The DETAIL and REFERENCE fields must both contain values in order for GoldBox-5 to attach the record.

Normally you need to hardcode the DETAIL value, since this is used as the Category, or Type, of Detail record. Press [Exprn] and key in a string surrounded by quotes. Example: "Insurance Policy"

If you want the REFERENCE value to be included in a Lookup table, press [Exprn] at the field directly under the PROF DATE field and key in "Y" at the Expression prompt. You may wish to do this when the Reference value is generic and applies to many Details. You normally don't want this if the Reference is unique to the Contact, such as a policy #, serial #, etc.

The DETL DATE field defaults to the Creation Date. If you want to put a more meaningful date in the Detail, such as an Expiration Date or Birth Date, plug in the incoming date value directly. If the incoming date is contained in a Character field, remember that you can press [DtMask] to have GX5 format it on the fly.

Please note that the DEAR, FAX, ADDRESS3 and MERGECODES fields appear in the Field Mapping Template even though GoldMine® does not yet display them. GoldBox-5 operations will address these fields in Exports, Global Replace, Group Builds, etc.

You can plug in up to 15 Scheduled Calendar Activities. The ACTIVITY DATE field must contain a value in order for GoldBox-5 to attach the record.