Previous month:
November 2011
Next month:
January 2012

December 2011

wMobile 2.1 for GoldMine - Update 35 Available! New Email features! #goldmine #crm

wMobile, the #1 mobile add on for GoldMine CRM, has a new update available.

With both a mobile and desktop web user interface we are a full web based client for GoldMine customers.

Highlights include:

  • New email center and email client for wMobile Desktop Edition.
  • Updated graphical user interface for wMobile Phone Edition
  • Updated graphical user interface for wMobile Desktop Edition
  • High performance server based email archiving - no more plug-ins and full freedom of choice for email clients.
  • Email folder support in both Phone and Desktop editions

In addition we have 100's of minor refinements, bug fixes and customer requests fulfilled. We are sure you will all find some wish granted!

The changes delivered in update 35 will be rolled into wMobile version 3.0 due in January which will add support for service cases in both Phone and Desktop editions.

 


Get reports out quicker and easier with MasterMine Reporting for GoldMine! #goldmine #crm

Efficient and Effective GoldMine Reporting...

 

- The most efficient and intuitive GoldMine CRM reporting interface available
- Drag-and-Drop fields to position in tables
- Familiar Excel environment
- Quickly drill-down through any summary for full detail
- Make tables, charts, graphs and lists in seconds
- Mailing lists and list views are a snap
- Clone and branch off to make new reports in seconds
- Reports maintain links to GoldMine for click-through and GoldMine Group building

Most reporting options require days of training and only give you flat, single function reports when you're finished. MasterMine's dynamic and interactive reports can be set up in seconds and 1-button-launched from a taskbar when you need them! Start making the useful reports that your organization needs today!

Is your GoldMine turning into a junk drawer? Users not entering data properly? Imported data didn't turn out right? Is it hard to find and fix bad data? Not anymore!  With MasterMine, users quickly find bad data, click-through to GoldMine records AND FIX IT! Or use GoldMine CRM Groups to mass-change large amounts of bad data in seconds. All changes are sync-aware and intuitively correct because you see GoldMine from the top-down, so you know you're hitting the right targets in your data

Contact Wizard Systems for a demo or trial copy.

 


#GoldMine Tip: How to view pending activities of 'curtained' records

'Record Curtaining' in GoldMine is a way of hiding contact records from certain Users, so User A can't see User B's records.

If you are using Record Curtaining in your GoldMine system, you may want to still share activities between users like Calls and Appointments.

Add the following line of code in the GM.INI (found in your network goldmine folder) under the [GoldMine] Section to show the pending activities of curtained records.

[GoldMine]
ShowActvForCurtained=1

This will show the pending activities for all curtained records.

 


GoldMine Tip: A guide to using SQL Queries in GoldMine

This post is provided for informational purposes only and the Wizard Systems GoldMine Technical Support team cannot provide or offer any further information or assistance on the topics in this document. All recent versions of GoldMine, allows the user the capability to use SQL Queries to assist with evaluating the data contained in the GoldMine databases. 

This capability is available through the SQL Query tab of the Filters and Groups dialog box.

Goldmine_sql_query_window


SQL statements consist of building blocks that construct a sentence with which the SQL Query tab communicates with your databases. These statements consist of keywords that indicate an action (such as SELECT, INSERT, UPDATE), operators indicating relations (such as =, +, LIKE, OR, AND) and pointers to tables and columns within your databases (such as CONTACT1, CONTACT1.COMPANY, CONTSUPP.CONTSUPREF).

Groups Based on Queries
Groups can be built based upon the results of SQL queries only if the Accountno field is one of the fields in the SELECT statement. Accountno is a unique field that identifies every contact record in GoldMine individually. It links each contact record to the associated records in other databases, such as Conthist, Contsupp, Cal, etc.

Shadow Fields in GoldMine (U_Fields)
When GoldMine data is re-hosted to an SQL server, U_Fields are created. These fields include U_KEY1 through U_KEY5, U_CONTACT, U_COMPANY, U_LASTNAME and U_CITY. These upper shadow fields contain an exact mirror of the contents of the regular fields that they shadow with one major difference -- the contents of the U_Fields are in UPPER CASE.

It is these fields, not the regular fields that comprise the main indexes on the primary GoldMine contact table. For this reason, it is suggested that queries containing any of the fields that have corresponding U_Fields utilize the U_Fields instead of the regular fields to take advantage of the indexes.

The SELECT Statement
Consider the following example where all data is requested from the Contact1 table.

SELECT * FROM Contact1

Here the wildcard (*) character is used to request ALL of the fields in the Contact1 table. This statement would return the entire Contact1 table displaying all of the fields in a columnar view.

The WHERE Clause
To begin to see the real power of SQL queries, specify that only some fields should be returned and add a WHERE clause to limit the results.

SELECT contact, company, phone1 FROM Contact1 WHERE state = 'Somerset'

This statement will display the contact names, company names, and phone numbers of all contacts that live in Somerset.

This idea can be expanded upon by using comparison operators other than ‘=’ (equal to). These include ‘LIKE [NOT LIKE]’, ‘IN [NOT IN]’, ‘EXISTS’, ‘BETWEEN’, ‘NULL’, ‘ALL’, ‘SOME’ and ‘ANY’. They can be used to refine SQL queries greatly. In addition to these operators, the following table lists common mathematical operators that may be used.

Symbol

Meaning

=

Is equal to

<>

Does not equal

<

Is less then

>

Is greater than

<=

Is less than or equal to

>=

Is greater then or equal to

!<

Is not less than

!>

Is not greater than

 


The LIKE and NOT LIKE Operator
Consider the following example of the LIKE operator:

SELECT company, contact, phone1 FROM Contact1 WHERE company LIKE '%mine%'

This statement will return all companies containing the text ‘mine’ no matter where it appears in the company name. Companies with names like ‘Goldmine’, ‘Carmine Engineering’, etc. will be returned. The comparison IS case sensitive, however, so a company named ‘Minerals Inc.’ would NOT be returned. To insure that all applicable records are returned, the UPPER function can be used to modify the comparison criteria:

SELECT company, contact, phone1 FROM Contact1 WHERE UPPER(company) LIKE 'MINE%'

With this modification, the all-uppercase version of the company field will be compared to the uppercase ‘MINE’ text using the LIKE operator. Notice the omission of the first % (percent) symbol as well. This change dictates that the statement will only return records whose company field BEGINS with ‘MINE’ as opposed to ‘%MINE%’ which will return all companies that CONTAIN ‘MINE’. Likewise, using ‘%MINE’ would return all companies that END with ‘MINE’.

Similarly, a NOT modifier can be added to the LIKE operator to negate the query and return all companies that do not match the search criteria.

SELECT company, contact, phone1 FROM Contact1 WHERE U_company NOT LIKE 'MINE%'

This query will return all companies that do not begin with ‘MINE’ regardless of the case of the letters involved. Notice that with this query, the U_company upper shadow field is used. This addition will, in all probability, speed up the query because the U_company field is an indexed field.

Since the GoldMine 4.00.9303 release, GoldMine adds information to the Conthist reference field (conthist.ref) to denote linkage of the activities to other contacts. This can become problematic when comparing text literally to the contents of the conthist.ref field as the following query does.

SELECT ref from Conthist WHERE trim(ref) = 'Blank Letter'

This query might not return all activities because the actual contents of the conthist.ref field might be ‘Blank Letter (Tim Wilmot) (oc:Maxine Drew)’ instead of the expected ‘Blank Letter’ value apparent from within the program. Using the LIKE operator instead of ‘=’ (equal to) can insure that the correct records are returned.

SELECT ref from Conthist WHERE trim(ref) LIKE 'Blank Letter%' The IN and NOT IN Operator SELECT company, contact, phone1 FROM Contact1 WHERE TRIM(U_company) IN('GOLDMINE','MICROSOFT','ORACLE,)

The above statement will return all contacts whose uppercase company name is exactly like one of the three listed above. Notice the addition of the TRIM function which will TRIM all spaces off of the company field to facilitate direct comparison of the company field to the values listed in our IN clause.

IN can be negated using NOT in the same manner that LIKE can be.

JOINS
Creating SELECT statements requires a familiarity with the database structures of GoldMine and how the different tables relate to one another.  This becomes apparent when attempting to return data from multiple tables.

Consider this statement:

SELECT company, contact, userdef01, userdef02 FROM Contact1, contact2 WHERE contact1.accountno=contact2.accountno

Notice that fields from multiple databases are being selected. Company and Contact are in Contact1 while the Userdef fields are in Contact2. Knowing that Contact1 and Contact2 are tied together via the Accountno field is key to constructing this statement successfully. Having the WHERE function limit the returned results to the records in Contact1 and Contact2 sharing the same Accountno’s guarantees that each row of data returned will be associated with one particular contact and that all four fields displayed will be the data associated with that particular contact. This method is known as a JOIN. Also notice that the WHERE clause specifies which table the fields are in. This is important to note because without using the table.field specification, the query engine cannot determine which Accountno the query is referring to because the FROM clause specifies two tables, both of which contain an Accountno field.

EXPRESSIONS
The results of expressions can be displayed along with static data. Try this:

SELECT company, unumeric1-unumeric2 FROM Contact1, contact2 WHERE contact1.accountno=contact2.accountno

Observe that the Company name and the difference between two numeric user defined field values is displayed for all contacts. The subtraction operation performed in the SELECT statement was able to do this as the data was returned and before it was displayed.

The AND and OR Clause Operators
The addition of an AND or an OR clause can limit the results of queries by using more than just one simple criteria. Consider this statement that lists all activities (contact, phone, reference, and date) completed in the space of one year.

SELECT contact, phone1, ondate, ref FROM Contact1, Conthist WHERE contact1.accountno=conthist.accountno AND conthist.ondate > '01/01/10' AND conthist.ondate < '01/01/11'

Now consider this query that lists all activities completed in January OR March.

SELECT contact, phone1, ondate, ref FROM Contact1, Conthist WHERE contact1.accountno=conthist.accountno AND ((conthist.ondate > '01/01/10' AND conthist.ondate < '31/01/10') OR (conthist.ondate > '01/03/10' AND conthist.ondate < '31/03/10'))

Notice the usage of parenthesis to group operations and the use of the OR to specify that either of the date ranges is acceptable.

ORDER BY
An ORDER BY clause can be added to sort the query results by completion date as well:

SELECT contact, phone1, ondate, ref FROM Contact1, Conthist WHERE contact1.accountno=conthist.accountno AND conthist.ondate > '21/03/98' AND conthist.ondate < '01/04/02' ORDER BY conthist.ondate

More than one field can be specified in the ORDER BY clause to provide multiple sort levels such as:

SELECT contact, phone1, ondate, ref FROM Contact1, Conthist WHERE contact1.accountno=conthist.accountno AND conthist.ondate > '01/01/10' AND conthist.ondate < '01/01/11' ORDER BY conthist.ondate DESC, contact1.contact

. . . which will sort the data in the same manner as the previous query adding the contact field as a secondary sort after the primary sort on the completion date. Also notice the DESC modifier in the ORDER BY clause. This will specify that the primary sort, conthist.ondate, will sort in DESCending order. ASC is used in the same way to denote an ASCending sort, although this is the default.

 


GoldMine Tip: Populate sales value from product when sales forecasting #goldmine

When scheduling a forecasted sale, the price field can be automatically updated for a selected product.

1. Prepare your products list:

- Log into GoldMine as Master
- Select... Schedule | Sale
- Click in the Products field and right click
- You will now see your list of products
- Highlight a Product and select Edit, The F2 Entry dialog box appears displaying the Product name
- Enter the price in the following format e.g. 'your product name'//350.00 (or whatever the price is)
- Repeat for each product

2. You are now ready to test.

Created a new forecasted sales and pick one of your products....

 


GoldMine Tip: How to integrate Gmail with GoldMine #gmail #goldmine #crm

Gmail is an increasingly popular email platform and it's easy to setup your Gmail account in GoldMine using the POP or IMAP protocols, so all of your Gmail is tracked and stored against your customer records.

First you have a choice of using POP or IMAP email 'protocols' with Gmail and GoldMine...


What is POP and IMAP?
POP and IMAP is what allows you to download messages from Gmail's servers onto your computer so you can access your downloaded mail with a programs like GoldMine, even when you aren't connected to the Internet. If you’re trying to decide between using POP and IMAP, we encourage you to use IMAP.

Unlike POP, IMAP offers two-way communication between your web Gmail and your email client. This means when you log in to Gmail using a web browser, actions you perform on email clients and mobile devices (ex: putting mail in a 'work' folder) will instantly and automatically appear in Gmail (ex: it will already have a 'work' label on that email the next time you sign in).

IMAP also provides a better method to access your mail from multiple devices. If you check your email at work, on your mobile phone, and again at home, IMAP ensures that new mail is accessible from any device at any given time.

Finally, IMAP offers a more stable experience overall. Whereas POP is prone to losing messages or downloading the same messages multiple times, IMAP avoids this through two-way syncing capabilities between your mail clients and your web Gmail.

1. First you will need to enable your Gmail accounts for either pop or imap email collection

Steps on enabling Pop Collection can be found at https://mail.google.com/support/bin/answer.py?answer=13273

Examples of the default pop Settings can be found at https://mail.google.com/support/bin/answer.py?answer=13287

Steps on enabling IMAP collection can be found at https://mail.google.com/support/bin/answer.py?answer=77695

Examples of the default Imap Settings
https://mail.google.com/support/bin/answer.py?answer=78799


1. Log into GoldMine as the user and setup the email account. I have used the Imap settings in my example below.

i. Select... Tools | Options | Email
ii. Fill in your email settings..

Goldmine_integration_gmail_1

Getting Mail Server: imap.gmail.com
Username: youremailaddress@gmail.com
password:  xxxxx
Tick Use Encrypted Connection (SSL)
Smtp Server: smtp.gmail.com
Your Return address :  youremailaddress@gmail.com

iii. Press the 'Accounts' Button

Goldmine_integration_gmail_2
 

On the left ‘Getting Mail’ side...

Tick ‘Auto Retrieve’
Do not Tick ‘Auto Delete’ (This option would be ticked for Pop collection)

-Tick Use SSL Encryption
-Port: 993
On the right ‘Sending Mail’ side...

Smtp Server: smtp.gmail.com
Username: youremailaddress@gmail.com
password:
Authentication: Auto
Tick Use start TLS
Port: 587

iv. Select OK, and you should now be ready to use Gmail in GoldMine!