Saturday, August 29, 2009

Crystal Reports : Display the day of week from a date

A client asked: I would like to display the day of the week on the detail line along with the date. How do I do this?

Using the example of CREATEON in Goldmine Contact1, you would do the following:

Create a formula using the DayOfWeek function called DayPosition as follows. This will create the numerical position of the date (e.g. 1=Sun, 2=Mon, etc)

DayOfWeek ({Contact1.CREATEON})

Then create a formula called DayName. This will add the name to the report (Mon, Tue, Wed). Use the function WeekDayName and add the DayPosition as the function detail

WeekdayName ({@DatePosition})

Drag the DayName formula to the detail row next to the CREATEON date. It will list the weekday name of the CREATEON date.

Friday, August 28, 2009

GoldMine: Menu Changes

A client asked: I like the new features in Goldmine Premium but why did they change the menus? It makes it confusing for users accustomed to the original menus.

The new menus are grouped to show related GoldMine functions under the same menu. However, if you want to use the original menus, you can do the following:

* Select Tools > Options > System tab
* Under Navigation > select Use classic GoldMine menu
* Click OK

The GoldMine menus will now appear as they did in previous versions. For example, you would select Edit > Options to change this back instead of Tools > Options.

One advantge of using the Classic menus is that it can ease the transition to GMPE.

GoldMine: Change Default Search

A client recently asked: When I click the Search button, it opens the Contact Search to automatically search by Contact. Is there a way to set this default to something other than Company or Contact?

Yes, you can "memorize" a field so that it appears by default:

* Select Tools > Options > Lookup tab
* Modify Default lookup by field > select Remember Lookup By
* Open the Contact Search Ceenter > select Email as the lookup
* Lookup by email > logout of Goldmine

When you log bck in, click the Search button. Email should appear as the default Lookup By field.

GoldMine: Recently Viewed Contacts

A client asked: Under the Contact sidebar, can I expand the number of contacts recently viewed?

Yes, you can set them to show up to 99 contacts:

* Select Tools > Options > System tab
* Under Navigation > increase Recent items shown
* Logout and log back into Goldmine
* Open the Contact Search > select/open Contacts

You will see the Recently Viewed items list increase as you open new contacts.

GoldMine: Changing Toolbar Details

A client recently asked: We would like to change the 'New Record' button on the toolbar to more closely reflect our record types. Is this possible?

Yes, you can change buttons on the toolbar by following the steps below. For example, to change 'New Record' to 'New Dealer':

* Click on New Record > Right-click and select Customize
* Highlight Menu Bar in the Toolbars window
* Right-Click New Record > Change '&Record' To '&Dealer'
* Click Edit Button Image or Click Change Button Image

This will now show the New Record as New Dealer with a different icon. This is useful for a company that deals with a specialized type of contact.

Thursday, August 27, 2009

GoldMine: Remove Outlook Add-in

A client asked: We currently use GMPE 8.5 and would like to remove the Outlook add-in. Can you provide step by step instructions on removing?

Yes, you would need to do the following:

* Open Control Panel > Add or Remove Programs
* Locate the GoldMine entry > click the Change button
* Click Next > select Modify > click Next
* Expand the GoldMine Plus section
* Click the arrow next to 'For Microsoft Outlook'
* Select 'This feature will not be available'
* Click Next > click Install

This will remove the sync between Goldmine and Outlook.

HEAT: No Password for WebUI

A client recently asked: It appears that HEAT 9.02 does not allow you to require passwords. You can simply type in the user ID and see the activity. Is there a fix?

Yes, on the latest version of HEAT, allows users to log in without a password. The password field can't be selected to type the password on the login page.

In Administrator, the "Require Password" setting under Defaults > System > System Defaults controls the password setting for both Call Logging and WebUI. This setting, however, requires a workaround for HEAT 9.02:

* Open the Heat Administrator module
* Go to Defaults > Administrative Defaults
* Uncheck the box for require password
* Restart the Web UI service.
* Then recheck the box for Require passwords
* Restart the WebUI service again.

This should solve the problem and allow you to use password for WebUI

HEAT: Automated Haf Files Overnight

A client asked: I have heard that there is a way to automatically create a haf every night but I cannot find any documentation to do it. Does it exist?

One way to do this is to create a BATCH file. Call the batch file autohaf.bat and use the server Task Manager run it nightly. It renames the file to the date created so it saves all haf files created.

If you have not used batch files:
* Copy/paste the script below into NOTEPAD
* Save as autohaf with the .BAT extension
* Insert your database name (unless it is HEAT)
* Insert your Admin name and password.
* Make sure the user name has rights to export HAF files


echo off
echo This file creates a HEAT archive file from the production HEAT database
echo .
echo Creating .haf file
echo .
echo Please wait....
echo .
"C:\Program Files\HEAT\LoadDB.exe" /d "HEAT" /u "Admin" /p "password" /e "C:\HAF\backup.haf"
for /f "tokens=1-4 delims=/ " %%a in ('date /t') do (set weekday=%%a& set day=%%b& set month=%%c& set year=%%d)
echo Renaming file to include todays date of %day% %month% %year%

ren c:\haf\backup.haf "backup_%day%_%month%_%year%.haf"
echo .
del c:\haf\fcactinkcom.txt
echo Backup and renaming of file complete


ren Command Lines:
ren /d – ODBC Datasource name
ren /u – HEAT UserID that has rights to export HAF files
ren /p – Password of the HEAT UserID above
ren /e – Export directory including name of HAF file


Another option is to create a SQL backup prior to committing an edit set. This can often be quicker. However, since edit sets and haf files are interconnected, use haf files if you are making changes all the time.

HEAT: Copy and Paste Call ID

A client recently asked: Is there a way to make the Call ID into text that you can copy and paste?

A quick workaround to this is below:

* Use Administrator to create a text field on the Subset
* Create an Auto Task that copies {|CallLog.CallID} to the field
* Create a command button next to the field connects to the AutoTask

The Heat user needs to press the command button to the Call ID in the text field but now you copy and paste the Call ID.

HEAT: Creating Detail Screens with SQL

A client recently asked: Is there a way to change a call type without recreating the detail screen?

No, when a call type is changed, the associated detail screen must be recreated. This is not particularly easy using Administrator. You can copy and paste the form but the fields do not come over so they must be added manually.

If, however, you are handy with SQL and have a test environment to use, you can try the following SQL code to recreate a detail form using the SQL backend database.

A word of caution: this method is not supported by FRS so make sure you have a good backup first (haf file, sql backup). Also, it is recommended that you test it on a development server first.

However, if this works for you, it can save hundreds of hours if you have a number of detail forms to recreate. The SQL code is below:

/* Select * from HEATTbl where TableName = 'Detail' and TableType = 'Northgate housing' */
Update HEATTbl set TableType = '*new call type*' where TableName = 'Detail' and TableType = '*old call type*'

/* Select * from HEATFld where TableName = 'Detail' and TableType = 'Northgate Housing' */
Update HEATFld set TableType = '*new call type*' where TableName = 'Detail' and TableType = '*old call type*'

/* Select * from HEATCtrl where TableName = 'Detail' and TableType = 'Sales Order' */
Update HEATCtrl set TableType = '*new call type*' where TableName = 'Detail' and TableType = '*old call type*'

/* Select * from HEATForm where TableName = 'Detail' and TableType = 'Sales Order' */
Update HEATForm set TableType = '*new call type*' where TableName = 'Detail' and TableType = '*old call type*'

Note: You need to rebuild the catalog file for the clients so commit an edit set when you do this.

HEAT: Run Program Action

A client asked: Can I run remote desktop and other diagnostic software using an autotask?

Yes, there are several tools you can launch using the Run Program action:

1. Remote Desktop

Filename: c:\windows\system32\mstsc.exe
Arguments: /v:{X}

2. Ping

Filename: c:\windows\system32\ping.exe
Arguments: {X}-t

3. Search MS Knowledgebase with the Incident Description text

Filename:{|CallLog.CallDesc} &catalog=LCID%3D2057&pwt=false&title=false&kt=ALL&mdt=0&comm=1&ast=1&ast=2&ast= 3&ast=8&ast=9&mode=a&x=11&y=9

4. Launch VNC: there are various versions of vnc

Filename: c:\program files\ultra vnc\vncviewer.exe
Arguments: {X}

5. Computer Management: this runs computer management for a target PC.

Filename: c:\windows\system32\compmgmt.msc
Arguments: /computer={X}

These autotasks may vary slightly based on the version of Windows.

Wednesday, August 26, 2009

GoldMine: GM+View Prompt

A client asked: When I run a GM+View, I get the following message “Running a system command on this item may be unsafe. Do you wish to continue?” How do I disable this prompt?

Depending on the GM+View, you may receive this message for GM+Views that launch an application. To remove this warning:

* Start Internet Explorer
* Go to Tools > Internet Options
* Click on the Security tab
* Select Custom
* Go to "Launching applications and files in an IFRAME"
* Check Enable

This will allow users to run the GM+View without receiving this warning.

CRM: Working Outside the CRM SDK

A client asked: Is it possible to hide controls outside of the CRM SDK?

Yes, you can, however, this solution is technically unsupported by Microsoft as it goes outside the CRM SDK, however the customization is small and easy to reverse to get into a state where Microsoft is supported.

Below are some code samples on how to hide attributes, elements and sections. These are quick JavaScript solutions for hiding both controls on a CRM page and links in the left-hand navigation page on an Entity Form.

The code below provides three functions that can be used to show/hide specific items on a CRM form:

var HIDE = 'none';
var SHOW = 'block';

// Function to show/hide CRM controls on a CRM form
// such as text boxes, lookups, pick-lists etc.
function SetCrmControlVisible(elementName, visibility)
SetElementVisible(elementName + '_c', visibility);
SetElementVisible(elementName + '_d', visibility);

// Fuction to show/hide specific elements on a CRM form
// such as the left-hand link items (More Addresses, Workflows
// and even custom ISV links
function SetElementVisible(elementId, visibility)
var elem = document.getElementById(elementId);
if (elem != null)
{ = visibility;

// Function to show/hide Navigation "Sections" in the left-hand
// links (Such as Sales, Marketing and Service)
function SetParentElementVisible(elementId, visibility)
var elem = document.getElementById(elementId);
if (elem != null && elem.parentElement != null)
{ = visibility;

Add the above code to the Entity OnLoad andyou are ready to show/hide elements on the CRM form.

Using the IE Developer toolbar, you can retrieve the id of the element to be hidden Open an entity record (in this case a Contact), press CTRL+N to open it in a new window, and then select IE Developer Toolbar.

Using the controls, select the item you want to hide and get the element id. In this case we have selected the “Opportunities” link and the id is “navOpps”

Using this, I can call the SetElementVisible function like this to hide the Opportunities link:

SetElementVisible('navOpps', HIDE);

To hide the entire “Sales” section from the left-hand navigation pane, I can once again find the id, but this time is of the parent item which would result in the following call to hide it:

SetParentElementVisible('_NA_SFA', HIDE);

Lastly, to hide an element on the form such as the “Job Title” field, you once again should use the IE Developer Toolbar to retrieve the element id and then call the SetCrmControlVisible function:

SetCrmControlVisible('jobtitle', HIDE);

By selecting either the textbox or the label, for the Job Title, the returned id will be “jobtitle_c” or “jobtitle_d” – remove everything including and after the “_” and pass that to the SerCrmControlVisible function and you are done!

CRM: Disable Fields

A client wants to know: When creating custom solution in MSCRM is there a way to disable all the fields on a form (based on data entered)?

This is a frequent question while creating custom CRM solutions: How can I disable all or most of the fields on a CRM form based on data selected by the user.

The script below does this:

1: // disable all of the fields on the form.
2: DisableFormFields = function(onOff)
3: {
4: var iLen = crmForm.all.length;
6: for (i = 0; i < iLen; i++)
7: {
8: o = crmForm.all[i];
9: switch (o.tagName)
10: {
11: case "INPUT":
12: case "SELECT":
13: case "TEXTAREA":
* 1: // disable all of the fields on the form.
2: DisableFormFields = function(onOff)
3: {
4: var iLen = crmForm.all.length;
6: for (i = 0; i < iLen; i++)
7: {
8: o = crmForm.all[i];
9: switch (o.tagName)
10: {
11: case "INPUT":
12: case "SELECT":
13: case "TEXTAREA":
14: case "IMG":
15: case "IFRAME":
16: if ( != "leadqualitycode")
17: {
18: o.disabled = onOff;
19: }
20: break
21: default:
22: break;
23: }
24: }
25: }

This code should be placed in the form's OnLoad event. In the example, we're working with the Lead Entity. The DisableFormFields() function is actually attached to the OnChange event for the Lead Status Attribute. The value of Lead Status is checked and calls DisableFormFields(true) to disable the fields or DisableFormFields(false) to re-enable the fields.

Disabling the entire form is not good idea, so allow the user to change their mind. Looping through each of the form fields, we check the id of the field and while Lead Status is not encountered, the fields' disabled status is set to either true to disable the fields or false to re-enable them.

CRM: Changing the Default Organization

A client recently asked: How do I change the default organization in a multi-entity MSCRM installation?

You can change the default organization by opening the configuration manager. In this tool you can rightclick on the organization to set as the default and click "set as default". You'll see that the name of the organization has changed to: Orgname (default).

After you perform an iisreset you need to perform some additional steps. The default organization will not change for existing users, this change will only be applied for new users created after the change.

Each user record has stored the default organization, so you would need to change them for each one of the already created users. You have two methods. Either use the tool provided by Microsoft Support or directly modify the CRM Database to change the DefaultOrganizationId in the SystemUser table.

In either case, make a backup of the databases before making any changes.

CRM: Removing Locked Attributes

A client asked: There are attributes on CRM default forms which you cannot remove. Is there a workaround for this?

Yes, however, before using this method make sure you have a good backup. To remove attributes on default forms:

* Export customizations for the specific entity
* Modify the form in the customizations xml
* Import the modified customizations

To modify the customizations, open the file in notepad or another XML editor. In the file look for this path:

ImportExportXml - Entities - Entity - FormXml - forms - entity - form - tabs

Within tabs look for the tab with the correct name and similar for the sections. Become familiar with the section xml once you have found the correct section. You will find that there are a couple of lines that you need to remove. An example is below to remove the subjectid on the case entity:

<cell id="{a9859c32-0cdc-41b5-8e7e-3eb173cab4a8}">
<label description="Onderwerp" languagecode="1043" />
<label description="Subject" languagecode="1033" />
<control id="subjectid" classid="{270BD3DB-D9AF-4782-9025-509E298DEC0A}"
datafieldname="subjectid" />

Attributes which are locked on the form includes the following:
case - subjectid
case - contractid
case - contractdetailid

Note: Some of the attributes are used in hidden javascript codes. For instance the case contract contractid is used in the script for the customer onchange. You can also remove those scripts from the customizations file.

CRM: Customization Button Missing

A client recently asked: When I have the Outlook client open and open the web client the Customization option does not appear? Is there a fix for this?

This has been how MSCRM works for quite some time and is not considered an issue by Microsoft so it will most likely remain this way.

There is a workaround. Open the web client using the IP address and open the Outlook client using the server address. This gives each client a different cookie and the problem goes away.

Tuesday, August 25, 2009

Crystal Reports : Changing the database location

When developing a report, it's often against a development or "test" database. When it comes time to point your report to the real thing;

1. Select Database | Set Location.
2. Click the "Set Location" button.
3. This will launch the Crystal Database Explorer. Find your new database.
4. Click the "Set" button.
5. Crystal should now ask you if you want it to "Propagate Server and Database Changes across tables with the same original information?" Click yes.
6. "Ok" your way back out.

Crystal Reports : Speed Tips

Here are a few things I have found to help with report performance:

1. Select, from the menu, Database | Perform grouping on server. This helps against SQL Backends.
2. Constrain data using the Select Expert whenever possible.
3. Constrain against indexed fields whenever possible.
4. Whenever possible, Group on an actual database field, not on a formula.

Crystal Reports : Changing the decimal precision on a field

In the case of "calculated" fields, especially where division is involved, the result can sometimes be a number like "10.25" or "13.53".
Crystal will default the formatting of such a result to include the decimal places as the calculation requires.
If you only wish to see the "integer" portion of the field in question, you need only to change the field formatting.

1. Right-click on the field in question
2. Select "Format Field"
3. On the "Common" tab, click "Customize"
4. Drop down the "Decimals" list to select your desired precision.
5. "Ok" your way back out.

Note : These Field Formatting options are only available on numeric fields. If your formula is returning a string, try using the ToNumber() Crystal function to convert it.

Monday, August 10, 2009

GoldMine: Search The U.K.

A client asked: Is there a GM+View that allows you to search for addresses in countries other than the US?

Yes, enter the following to search the UK:

Select Web > Setup GM+View
Click New > Enter a Template Name
Click in the large text box > click <H>
Copy and paste in the following:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<TITLE>Multimap Query on Zipcode</TITLE>
<META content="MSHTML 6.00.2800.1458" name=GENERATOR>

<BODY leftMargin=1 topMargin=1 rightMargin=1>
<P>Getting Map Data from Multimap based on Postcode....please wait....

<SCRIPT language=javascript type=text/javascript>
// -->




Click Save > Click OK

This will search the UK for contact addresses.

Goldmine: GM+View To Cross Sell

A client asked: Is there a GM+View that allows you to prospect for new opportunities or cross sell to your contacts?

Yes, one such site is Hoover.comand the following GM+View will search the Hoover database by company name:

Select Web > Setup GM+View
Click New > Enter a Template Name
Click in the large text box > click <H>
Copy and paste in the following:



This GM+ View Template is provided as part of The Hackers Guide to GoldMine, and
the author is providing this without guarentee or additional support.

<SCRIPT language=JavaScript>

function redirect()
window.location = "<<&strtran(trim(Contact1->Company), " ", "+")>;>;&dir_top_id=7"

setTimeout("redirect();", 0000)
// -->


<STYLE type=text/css> P, UL, OL, DL, DIR, MENU, PRE { margin: 0 auto;}</STYLE>

<META content="Microsoft FrontPage 5.0" name=GENERATOR>
<BODY leftMargin=1 topMargin=1 rightMargin=1>
<FONT face=Tahoma size=2>

Click Save > Click OK

Go to a contact and select this GM+View from the drop-down menu. It will show the Hoover website and automatically populate the Hoover search with the contact name.

GoldMine: Contact Website

A client asked: Is there a GM+View that lets you fill in the contact website and open it?

Yes, you can add this GM+View doing the following:

Select Web > Setup GM+View
Click New > Enter a Template Name
Click in the large text box > click <H>
Copy and paste in the following:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

function PopUp(){
var ScreenWidth=window.screen.width;
var ScreenHeight=window.screen.height;
var movefromedge=0;
var PopUpUrl="<<&website>;>;","","width="+ScreenWidth+",height="+ScreenHeight+",toolbar=1,location=1,directories=1,status=1,scrollbars=1,menubar=1,resizable=1,left="+placementx+",top="+placementy+",screenX="+placementx+",screenY="+placementy+",");

<META content="Microsoft FrontPage 5.0" name=GENERATOR>
<BODY leftMargin=1 topMargin=1 onload=PopUp() rightMargin=1></BODY>

Click Save > Click OK

Click on contact website if it does not exist. Add the website to the popup window and it will be added to the contact's record. Select this GM+View from the drop down menu and it will display the contact's website.

GoldMine: Finding Zip Code

A client asked: We often get addresses without zip codes. Is there a GM+View for this

Yes, to lookup a zip by address, enter the following into a GM+View:

Select Web > Setup GM+View
Click New > Enter a Template Name
Click in the large text box > click <H>
Copy and paste in the following:


<meta http-equiv=REFRESH



Click Save > Click OK

Select the GM+View and you will get a form to enter an address and get the zip code

QuickBooks: Using the Account Register

A client asked: When reconciling a bank account, how do I search the Bank Reconciliation Window?

In QuickBooks 2008 and prior, the bank reconciliation window shows disbursements in document number order only and does not allow you to sort by any of the other column like the transaction date, the payee or the amount.

In all versions of QuickBooks there is no "Go To" or "Search" feature on the Bank Reconciliation window.

To workaround this problem, you can do the following:

* Select Bank Reconciliation > enter ending balance, service charges.
* Continue to the window where you clear checks and deposits.
* Minimize the window and open the check register
* Sort the check register by 'Date/Type/Number' or by 'Amount'

You can also sort by check number but the bank reconciliation window sorts by check number already. The check register simply allows you to use additional sort options.

When you locate the transactions, use the check register to designate each check as cleared – using the cleared column in the register.

* If you cannot locate a check using sort options, use “Go To”
* Search the register by payee, number, memo or amount.
* Return to the bank reconciliation window.
* Transactions cleared will show a check mark in the window.
* Clear deposits and disbursements not cleared with the register.

Sunday, August 9, 2009

QuickBooks: Required Account Numbers

A client asked: Is there a way to require account numbers? There seems only an option to turn them on or off.

There is no preference to require the use of account numbers, just the option to turn them on or off. This option tells QuickBooks to show or hide the account numbers. The account numbers remain in the file

There is way, however, to require account numbers and even let you know if any accounts do not have accounts numbers assigned.

To require account numbers, do the following:
* Select the Edit menu and then select Preferences.
* Select Accounting Company Preferences
* Select Show Lowest subaccount Only.
* This option is active when you check Use Account Numbers.
* If all accounts have account numbers assigned, click OK.

QuickBooks will then require you to use an account number for all accounts you create in the future – and will not allow you to remove the account number from existing accounts although can edit the account numbers.

If there are accounts without account numbers you will see a window:

* You must then go to the Chart of Accounts
* Assign numbers to all accounts (even the inactive accounts)
* Return to the Accounting Company Preferences
* Select the Show Lowest Subaccount Only checkbox.

QuickBooks: Memorized Reports

A client recently asked: A memorized report stopped working and I had to recreate it. Is there a way to backup memorized reports?

Yes, you can backup memorized reports and there are a couple reasons you should be doing this. First, there is a known issue with memorized reports in QuickBooks Enterprise Solutions. Memorized reports will sometimes stop working for no apparent reason. A backup process will allow you to restore the memorized report.

Second, if a user needs the ability to manage the Memorized Report list, the user can inadvertently modify or delete memorized reports on the list. A backup will allow you to restore the original memorized report settings if you need to do so.

To backup your Memorized Reports:
* Create a user called "Report Backup" with Admin access.
* Be sure to use a password that only the file Administrator knows.
* Login as the "Report Backup" user.
* Open the memorized report you want to backup.
* Select the View drop down menu and then select "Add to Icon Bar."

Use the Memorized Report name as the title of the Icon Bar shortcut.

QuickBooks: Access the Name List

A client asked: How do I access the Composite Name List without creating a check?

To create an easy way to access the Name List, perform the following:

* Open a check transaction.
* Place your cursor in the Payee field
* Press CTRL+L to display the names list
* With the list displayed, select the View menu
* Select 'Add Names List to Icon Bar'.

This will allow you quick access to the Name list in the future. This can be used for several purposes including deleting names, finding names in transactions and other funtions.

QuickBooks: Getting Error Details

A client asked: When I run Verify Data, I get an error message from QuickBooks to run the data repair utility. There is no way, however, to get details of the problem.

There is a way to find the details of the verification, but you must do the following:

* Press F2 to open the Product Information window.
* Press CTRL+2 to open the Tech Help window.
* On the tech help window, press tab that reads 'Open File'
* Highlight the file 'QBWIN.log' > click 'Open File'.
* Scroll until you see a long, double-line
* Scan the verification information for the word “error”
* Note information by the error (document number, trans type )
* Search the information in the report to find the transaction
* Delete and re-enter the transaction.

The problem will usually go away with the deleted transaction. If the verification problem is related to a list entry, it is almost always a duplicate list entry. You can solve this problem by renaming one of the two duplicate list entries and merging the two lists together.