Friday, May 28, 2010

GoldMine: Two Groups are Better Than One

A client asked: We need a way to combine contacts that are in either of two groups (e.g. the Customer group and the Prospect group).

To do this, you need to create the groups and then use a SQL Query to combine the groups based on their Ref value. An example of the query is below:

select contact, company, key1, accountno from contact1 where accountno in select accountno from contgrps where userid in (select recid from contgrps where accountno like '*M_ %' and (ref='Customer' or ref='Prospect')))

The "Customer' group and the 'Prospect' group are based on filters that check the value of Key1. The query then looks for either of the groups Ref values to add the contact to the query results.

To search for different groups, you modify the ref=' ' contents in the query. You can also adjust the fields returned (contact, company, key1 and accountno are shown in the example above). Use the GoldMine field name (e.g. key2) to insert in the SQL query.

GoldMine: What is a Personal Base

When using the Knowledge Base, is there a way to enter information that is available only to the user that entered it?

Yes, within the Knowledge Base, there is a section called the Personal Base. This shows the entries only to the user that entered it.

To use the Personal Base:
* Select Go To > Knowledge Base > Knowledge Base window appears.
* Under the Knowledge Base, select the Personal Base
* Each Personal Base is only available to the user that created it

To create a Personal Base book:
* Click the New Book icon or right-click
* In in the left pane, type a name.
* In the Personal Base, you may want to enter text in the Book
* You can also add folders and pages to your book
* Once you have entered your text, click the save icon

For additional features of your Personal Base, right click on the Book. There are options to print the topic, email to a contact or attach a file. These features are also available in the Knowledge Base. You can also link to a contact, create a table of contents for the topic or move the topic to the public Knowledge Base.

GoldMine: Meet with the Entire Group

We have the need to schedule multiple contacts for the same meeting. Is there a way do this from the same scheduling window?

You can schedule multiple contacts from the Calendar as follows:
* Open the Calendar > Click Schedule Appointment
* Click the icon next to the Contact shown
* Click New > Add, Select contacts to add
* Click Schedule > multiple contacts appear on the Calendar

To complete the appointment:
* Open the Calendar > right click Complete
* Enter any information > click Complete All Activities
* The activity is completed for all contacts scheduled

This method saves time when scheduling an activity with multiple contacts and when completing the activity.

GoldMine: Delete the Attachment with the Email

Did you know that when you delete an email in GoldMine, the attachments are not deleted by default. Here's how you can delete both emails and attachments:

* Select Tools > Options from the menu

* Select the Email tab

* Click on 'More Options' to open the E-mail Preferences dialog box

* Select the 'Advanced' tab

* In 'Message', check 'Delete attachments when deleting the mail'

Now whenever you delete an email, the email attachments will also be deleted

GoldMine: Multiple Contact Scheduling

Did you know that you can schedule the same meeting for multiple contacts using GoldMine? You can schedule multiple contacts from the calendar in the same scheduling window.

Here's how:

* Open the GoldMine Calendar and click Schedule Appointment
* Click the icon next to the Contact shown
* Click New > Add. Select contacts to add
* Click Schedule and multiple contacts appear on the Calendar

To complete the appointment:

* Open the Calendar, right click and select Complete
* Enter any information and click Complete All Activities

The activity is completed for all contacts scheduled.

GoldMine: The Detail Tab Search

How do you search for information stored in GoldMine’s Details tab?

Unlike many fields in GoldMine, the GoldMine Details tab has a one-to-many relationship. Listed below are all the fields associated with a Detail. An example would be using Details to store and search for Serial Numbers.

GoldMine Detail Information:
* Detail Name: the type of information (e.g. Serial Numbers)
* Details Reference: the primary look-up (e.g. Product Name)
* Note: a memo field to describe the entry
* Audit Information: enters the date the Detail was entered
* Custom Tab: there is the ability to create a custom tab

Custom Fields: GoldMine Premium allocates 12 fields that can be custom labeled with predefined field lengths and formats.

How to Search for a GoldMine Detail:
* GoldMine Search Center: drop down Search By and select Details
* GoldMine Details Tab: right click on category of Details, select Lookup
* GoldMine Lookup Wizard: seach using custom SQL queries
* GoldMine Universal Search: find data stored in any Details field
* GoldMine Premium Dashboards: only in GoldMine 9.x

GoldMine: Two Kinds of Email

Did you know that there are two kinds of email you can send in GoldMine: Internet email and GoldMine user-to-user email. User-to-user email can be used send an email to another GoldMine user who is on GoldMine system.

An advantage of GoldMine user-to-user email is the way it stores your communications. You should be aware, however, that when using GoldMine user-to-user email it handles attachments differently than Internet email.

By default, GoldMine does not send a copy of the attached file when sending email to a GoldMine user, but rather simply creates a link in the email that points to the location of the originally attached file.

This is designed to save space on your server by cutting down on the creation of duplicate copies of the attachment. This is a useful feature, but it can cause problems: 1) If the receiving user does not have access to that file location, the receiving user will not be able to see or open the attachment and; 2) Often a user does not realize when they open the attachment they are editing the sender’s original file, not a copy of the file.

To avoid these problems, change the email preferences as follows:

* Go to Tools > Options
* Select the Email Tab > More Options button
* Select the Composing tab
* Uncheck the box 'Send attachments as links for GM users'.

GoldMine: MyGoldMine Tool

What is the purpose of My GoldMine and how do I configure it?

Use My GoldMine as a personalized resource for current information from selected GoldMine and Internet sources.

* Select Web > My GoldMine. The My GoldMine window appears.

* Depending upon the configuration you selected, My GoldMine displays with 2 or 3 columns, as tabs or boxes, and with default, Windows, newspaper, or XP page style.

To change the setup, style, and content, select Personalize Content. The Configure My GoldMine dialog box opens.

* To add to the content of each column, click Add Item(s) to Columns. The Select Item to Add dialog box appears.

* To view the complete information where it is not displayed, clicking the hyperlink expands the view to include the detailed information.

Note: If you selected the Tab format in the Configure My GoldMine dialog box, you may need to scroll to the bottom of the column and double-click the resource's title bar. Use the arrow to minimize the view on the tab format. The Box format keeps your resources actively displayed at the top of the column.

* To copy information from the My GoldMine display, highlight the section and right-click.

* Select Copy from the local menu.

* Choose Select All to copy the entire My GoldMine display to the clipboard. Then paste the selected section or the entire section in your target application.

* To refresh news feed sites manually rather than wait for the automatic refresh, click Refresh.

ACT: Rolling Over Activities

A client asked: Can the ACT! Calendar roll over activities to the next day?

Yes, but this should be used with care. There are often better ways to track past due items than rolling them over. For example, open your Task List and filter the list to Past. This way you do not automatically roll over less important items and mix them with really important tasks or calls.

If you need to roll over activities, however, do the following:

* Select Tools > Preferences > Calendar & Scheduling tab
* Click Scheduling Preferences > Select the Activity type
* Click 'Roll over to today' > Click OK

The next time log in, if you have activities to roll over, ACT! will list the number and type to confirm. If you do not want to roll over the activities, click Cancel.


Di you know that you can View your live Act! data within other programs. By the way, if the thought of opening Windows Control Panel puts you into a cold sweat, you can skip this one.

However, if you are a little more technically oriented, and like to tinker, you’ll find this Act! feature interesting.

Act! is built using many state-of-the-art Microsoft technologies. This includes things such as the .NET programming framework and SQL Server. Because of this, Act! adheres to various industry standard behaviors and functions.

One such feature is the Act! OLE DB Provider for Reporting. This is installed when Act! is installed on your PC, you never see it unless you go looking. While getting into the details of setup and usage are beyond the scope of this postr suffice it to say that this allows you to directly access most portions of your Act! database in programs like Microsoft Access and Excel.

Before you get nervous about your data, rest assured that the data link is read-only and that is why it is called the Provider for Reporting.

So if you are an Excel guru, being able to create reports and calculations with Act! data in your familiar tool can be a huge time saver. Be sure to check this one out!

ACT: Sharing Notes

A client recently asked: When we have several contacts from the same company, is there a way to share Notes without retyping them?

Yes, the Notes feature in ACT! is very versatile and provides Notes sharing, filtering and formatting. Despite this functionality, however, Notes should not be used as a substitute for History information.

To use the Notes functions:

* Select a Contact > Select Notes > click 'Insert Note' icon
* To link the Note to other contacts, click the 'Contact' button
* Accept the date/time or modify to a scheduled date/time
* Attach any files or shortcuts to the Note
* To allow viewing by a Group or Company, click 'Share With'
* You can add formatting, bullets, change fonts, etc to the text
* At the bottom, you can mark the Note as Private

When you open the linked Contacts, all the text entered, attachments and formatting will be displayed in the Note for each linked contact. If you change a Note, a dialog box will ask if you want the change made to all contacts or just the current contact.

ACT: Schedule Database Sync Silently

Did you know that you can schedule Act! to synchronize remote user data automatically.

Act! supports on-the-go users even when they do not have internet access. The trouble is, road warriors often hate to be bothered with minor details like syncing their database – they have more important things to do.

Starting with Act! Premium 2007 and included in Act! by Sage 2009 also (sorry – if you’re using the standard versions of Act! 2007 or Act! 2008, you miss out on this one), the Act! Scheduler can be configured to run silently in the background and synchronize remote users databases without them having to do a thing.

They do not even have to have Act! running! How’s that for on-the-road style? Also, for admins out there – you can also schedule their system to back up their Act! database nightly

ACT: Connect to a Web Page to a Contact

This feature often goes undiscovered. When Act! is installed on your PC, it adds an item to the tools menu in Internet Explorer: ‘Attach Web Page to Act! Contact'

Clicking this menu item brings up a dialog box like the one used when you are manually selecting what contacts to attach an email to.

You can pick one or many contact records at the same time – Act! saves the entire contents of the current web page to the attachments folder of the database you are using and creates an item on the history tab showing the web address.

Being that the page is actually saved to your local Act! system – in other words it is not just a link – if the web page goes offline, changes or you are not on the internet, you can still open and reference the page from your Act! contacts.

If you sync, it can also add to other users

MSCRM: PickList Values

A client asked: I started to add a new picklist item and it gave it the number 200.000. There are only two items in the list.

This is by design. This is consistent with other Microsoft software and prevents conflicts with new system attributes that are added by Microsoft. To recreate this example, you can do the following:

* Select Settings > Select Customization > Select Customize Entities
* Select Attributes > Select a Picklist > Open the attributes
* Click Add > the dialog box will show 200,000 as the starting value
* Change the 200,000 to 200 and attempt to add the attribute label
* You should receive a error message box stating to enter a number
between 200,000 and 2,147,483,646 > Click OK
* Add the label > Save and Close > Publish

You will now see your new picklist item in whatever field you modified.

MSCRM: Quote Tracking

A client asked: Is there a report that shows how many quotes are turning into sales?

1. Use Advanced Find:
* Click Advanced Find > In Look for, select Quotes.
* Click Select > Select Status.
* Select Equals > Click Enter Value > Click Select button.
* In Select Values choose Active, Draft, and Won.
* Click OK > Click Find > Available quotes appear.

2. Export the list of quotes to a PivotTable:
* Click Export to an Excel worksheet
* Click Dynamic PivotTable > Click Select Columns.
* Select Owner, Potential Customer, Status, and Total Amount.
* Click Export > Click Open.

3. Add fields to the PivotTable
* Drag Status to the Drop Row Fields area.
* It now displays totals by owner and stage: draft, active, or won.
* Drag Owner to the Drop Page Fields area.
* It now displays quote totals by the user assigned to the quote.
* Drag Potential Customer on top and right of the Owner column
* It now displays all draft, active, and won quotes from last month.
* Drag Total Amount to the Drop Data Items area.
* It now displays a total of all draft, active, and won quotes.

Save the Excel worksheet and close it. Make some changes in the quotes in CRM. Open the Quotes worksheet and it will display the changes made because it is dynamically linked to CRM.

MSCRM: Sorting Multiple Columns

A client asked: Is it possible to sort multiple columns in Microsoft CRM?

You can sort a single list of records by clicking on the column heading. The arrow indicates the direction of the sort (ascending or descending). To sort by multiple columns, you would do the following:

* Select the primary column and click the column heading
* To sort the secondary column, press CTRL+SHIFT while selecting the additional column heading.
* The columns are sorted by the order you select them

To try this, sort by Account Name (as the primary sort and then add some additional fields). For example:

* Select Accounts
* Select Account Name > Sort Descending
* Select Main Phone (hold down CTRL+SHIFT)

The view should be rearranged by Account Name and Main Phone

MSCRM: Direct Email Signature

Do you know how to add your signature when using CRM web email?

First, create signature email templates in the Personal Options area of the Web application. This is done as follows:

* Select Settings > Tools > Options
* Select Email Templates > click New > select Global
* Enter your name in the Title and Re: in the Subject line
* Enter your signature in the Description
* Add your web address (e.g.
* Save and Close

After your signature email template is set up, you can use it in a number of Microsoft CRM email activities, including Direct Email. Compose your email and before you send it, select the signature email template to include.

* In the message body, put your cursor where you want the signature.
* On the Formatting toolbar, click Insert Template.
* Select the signature email template you want, and then click OK.

Your signature will now appear in the body of your email message.

MSCRM: Which Price Is Used

A client asked: Are List Price, Standard Cost and Current Cost related to the calculation of quotes, orders and invoices?

To illustrate how the fields are used during the creation of a quote, try the following:

* Select an Account (e.g. Marks Group)
* Double click to open the Account
* Click New to create a Quote > Enter the required information > Save
* Select Existing Products > Click New Quote Product
* Enter required fields > Return to Information > Click Recalculate
* Save and Close > Return to Settings > Product Catalog > Products
* Double click the Product > Review List Price, Standard Cost and Current Cost

The List Price and the Quantity in the Quote make up the selling price on a quote. The Standard Cost and Current Cost are for information purposes only and can be used in reports or views.

GoldMine: Tracking Activity Codes

A user asked: Is it possible to show Activity Codes in the History tab?

By default, GoldMine’s history tab does not show the activity’s activity code, which is a three letter code used to categorize activities.

If you want to show this, edit your username.ini file:
* Find the [ActvObj] section.
* Add this line after the section header:


* Save it, close and reopen GoldMine

You should now see Activity codes in the history tab.

GoldMine: Tracking the Customer Source

Ever wondered where your records are coming from?

You can use SQL query to show the number of records created, by their source. The dates can be changed to show whatever date range you'd like.

select source, count(*) as "#", max(accountno) from contact1 where createon >= '1/1/2010' and createon <= '12/31/2010'
group by source
order by source

If you use dBASE GoldMine, this will display the information (without a summary):

select source, company
from contact1
where createon >= '1/1/2010' and createon <= '12/31/2010'
order by source

QuickBooks: Time Tracking

A client asked: What can I do to enter time data into QuickBooks?

There are several different ways to get time data into QuickBooks:

* Use the Stopwatch to time an activity while performing it.
* Enter the time data manually, either on a timesheet or by activity.
* Import time data tracked by the QuickBooks timer.
* Download online timesheets using QuickBooks Time Tracker

Note: some of the methods require additional software.

QuickBooks: Merging Records

A client asked: Is there an easy and safe way to merge these together into a single vendor?

Yes, you can merge vendors and a number of other record types. All transactions contained in the merged name will be modified to contain the survivors name.

* From your Lists Menu > Click on Vendors
* Determine which vendor name will be the survivor.
* Determine which vendor will be merged off the list
* Edit the vendor that will no longer exist (Control + E)
* Retype the name using the exact spelling of the survivor
* Just the vendor name field needs to be modified
* Click OK and a popup will ask if you want to merge
* Click yes.

This can also be used for items, customers, and accounts.

QuickBooks: Posting ATM Withdrawals

A client asked: How do I record withdrawals from an ATM machinefor petty cash?

To transfer the money in QuickBooks:

* Go to the Banking menu and click Transfer Funds.
* Click the Transfer Funds From drop-down arrow
* Click the bank account that you withdrew the money from
* Click the Transfer Funds To drop-down list
* Click your petty cash or cash drawer account.
* In the Transfer Amount field, enter the withdrawal amount
* In the Memo field, enter ATM withdrawal
* Save the transfer.

This will move your funds into petty cash.

QuickBooks: Rename Your Company

A client asked: We want to use a different name for our company reports. Is there a way to rename our company?

There are a number of ways to change your company name without affecting the integrity of the company database.

* Select Company > Company Information
* Enter the Company Name you want as the default
* To control by report > Select the Report Center
* Select Profit and Loss > Standard
* Click the Modify Report button > Click Header/Footer
* Modify the Company Name for this report

You need to select which name you want as the default name and which are related to reports based on your company's needed. However, QB Enterprise is very flexible in terms of Header information.

QuickBooks: Customer Credit Cards

A client asked: Can I have more than one credit card assigned to a customer?

No, but you can use the Job feature under the Customer Center to add multiple credit cards to a customer.

* Select the customer center and add a job.
* Name this "job" based on one of the locations of the customer.
* Edit the Job and under Payment Information, add a credit card.
* Save this job.
* Add jobs for locations or anything with a need for credit cards.

Before creating alot of jobs, make sure that this will work with the posting of payments within your company.

HEAT: Pausing an Autotask

Is there a way to stop an autotask to run a program, get information from the external program then return to HEAT to add the data?

Yes, there is an action to 'Wait for Program to Terminate" This, combined with the Run an AutoTask action, will allow you to branch to an external program, get your information from the external program and return to the same point in the autotask.

Using Goldmine as the external program, see below:

* Create a new autotask called External Information
* Click Add > select the Run a Program action
* Under Filename, browse out to the Goldmine executable
* Check the box 'Wait for the program to terminate'
* Click OK > add the Goto Call(Ticket) autotask
* Add the @Prompt function to the Call ID expression
* Add 'Enter Call ID' to the @Prompt function
* Click OK > associate with a toolbar icon > Click Close

When you run the autotask, it will run Goldmine and pause at that point in the autotask while you search Goldmine for the information you need. When you close Goldmine, the autotask will resume where it left off. Enter a Call ID in the pop-up box and add the information obtained from Goldmine.

HEAT: Automatically Create HEATBoard Issues

A client wants to know: Is there a way to automatically create HEATBoard issues when certain conditions exist?

Yes, to create a HEATBoard issue automatically, you would do the following:

* Create an autotask that checks for the condition
* Create an autotask that is executed when this condition exists
* Click Add > Provide a Name > Click Add
* Select "HEATBoard Issue" action > Complete HEATBoard form
* Click OK

This will then put up a HEATBoard Issue whenever the condition is met and the autotask branches to this action.

HEAT: Linking Call Records

A client asked: Can you link call tickets in HEAT?

Yes, there is a autotask function called Goto Call(Ticket). Use this action to search for a specific call record; performs the same action as Ctrl+G or File > Go to Call ID, enabling you to open a specific Call Record by its Call ID.

Utilizing HEAT field values, link calls by storing Call IDs in fields for a record, then execute the action using a command button, which results in the stored call record being opened.

To Create a Goto Call Action:

* Select Autotask > Manage Autotasks
* Click Add > Enter the Autotask Name
* Click Add > Select Goto Call(Ticket) > Click OK.
* The AutoTask Goto Call Action dialog box opens.
* In the Title field, type a name for the Goto Call action.
* In the Call ID expression field, type the Call ID for the call record (as
shown in the Call Status Banner).

With your cursor in the Call ID expression field, you can also click Insert to use HEAT field values, @Functions, filenames, temporary filenames, assignments, journals, or counters to configure an expression that identifies which call to go to.

HEAT: Using the Web Browse Feature

There is a feature in HEAT called Web Browse that is called by an autotask. This allow you to incorporate web-based data in your HEAT system.

To use Web Browse for MapQuest directions, do the following:

* Select Autotask > Manage Autotasks
* Select Add > Enter the Autotask name
* Click Add > Select Web Browse
* Add the Page URL >
* Click the checkbox for "Submit a Form for the PageURL"
* Click the Auto Fill button > the Mapquest web page opens in a window
* On the drop-down Select Form, select directionsForm
* The text boxes on Mapquest to be filled will flash
* Enter placeholder characters in each box (e.g. xxx) > these will be used later
* Click OK > Values will appear in the POST/GET DATA text box
* Replace the xxx's with the correct HEAT field (insert field) or a static value

Make sure to use the GET function and a state that is the two digit code, not the full state name.

Note: the data string is created by using the procedure above. No knowledge of the scripting is required.

HEAT: HTML Message Box

Did you know that you do not need to use the plain gray dialog boxes to gather information?

There is an autotask function that can be used to create a message box with multiple lines of text, inserted functions or fields and instructions to the user:

To create this message box:

* Select Autotask > Manage Autotasks
* Click Add > Enter the Autotask Name
* Click Add > Select the Display Message Box action
* Click in the Message Box Text field
* Select the font, font size, bolding, text alignment, etc
* Enter the message the you want the user to see
* Enter any HEAT fields or functions you would like in the message
* Select the message box option - either OK, OK/Cancel or Yes/No

Click OK to save the autotask. This autotask can be assigned to a toolbar icon, command button or called by another autotask. This allows the Message Box to appear at a decision point and provide the choice to continue or cancel.

Thursday, May 20, 2010

Crystal Reports : Changing Groups at Run Time

This is neat: Let us suppose we want a report to be Grouped on either Users.UserID or Users.UserTerritory, depending on a Parameter field.

So, provide a simple parameter, {?Group} and let the user set it to "UserID" or "Territory".

Now, within your report, create a formula field called {@Group1} and paste in the following code:

IF {?Group} = "Territory" THEN {USERS.USERTERRITORY}

Now, create your Group in crystal, and use {@Group1} as your Group Field.

This way, the user can select how the report should group itself, affecting all summaries, etc.

Try it!

Crystal Reports : Automatically derive dates based on "last week"

This is a nice trick for those of you that want to cut down on parameter input for your end users.

Suppose a report needs to only print records with dates from last Monday through Friday. You could of course provide a date range parameter and let the user choose for each printing of the report.

You could however, use the nifty LastFullWeek function in crystal to return these values automatically.

The first thing to keep in mind here is that LastFullWeek will actually return a Date Range, NOT a Date Value. In order to see what dates LastFullWeek is actually returning, we will need to create two formula fields. {@DateStart} and {@DateEnd}.

The code for {@DateStart} : MINIMUM(LastFullWeek)
The code for {@DateEnd} : MAXIUMUM(LastFullWeek)

Drag those two fields onto your report. You'll see that in the Crystal Universe, the LastFullWeek starts on a SUNDAY and ends on a SATURDAY. So if we just want Monday through Friday, we need to do some tweaking.

The code for {@DateStart} : MINIMUM(LastFullWeek) + 1
The code for {@DateEnd} : MAXIUMUM(LastFullWeek) - 1

Now the formulas are returning Monday to Friday and we can use them in our Selection Criteria thusly (assuming we are looking at a field called {HISTORY.ONDATE})

{HISTORY.ONDATE} IN {@DateStart} TO {@DateEnd}.

Try it!

Crystal Reports : Viewing the list inside a "multiple selection" parameter

This is a blast from the past, but came up recently during our last Crystal training presentation. How to print the values inside a parameter that allows the user to select multiple items?

Let us suppose we have a parameter field; {?UserID}, which allows the end user build a list of Users to report on.

Create a formula called {@UserList} and paste in the following code:

JOIN({?UserID}, ", ")

The JOIN function in crystal will "join" all the multiple values together into a string for us, using the second parameter as a delimiter; in this case a comma.

Try it out!

Crystal Reports : Returning rows where there aren't any

Take this example; you want to print a list of customers without history. Sounds easy, right? But you will quickly find out this is trickier than at first glance.

We have two tables, CONTACTS and HISTORY. They are joined on a field called CUSTNUM, like so; CONTACTS.CUSTNUM = HISTORY.CUSTNUM.

However, when you define your table linking in Crystal, it assumes you want an INNER JOIN, which will only return rows from CONTACTS where there is at least one corresponding row in HISTORY.

1. Go into the Visual Linking Expert and right-click on the JOIN line between your tables. Select Options.
2. Change the JOIN type to RIGHT OUTER. Ok your way out.
3. Now, provide a selection criteria (in the Select Expert) as a formula and use the following:


Which is the Crystal Syntax for HISTORY.CUSTNUM IS NULL (for those SQL folks out there).

You'll notice now that your report is only printing Customers where there are NO corresponding rows in HISTORY.