Thursday, October 29, 2009

GoldMine: Make Sure to Follow Up

A client asked: I often forget to schedule follow up calls in Goldmine. Is there a way to make sure that follow ups are always scheduled?

When I tell someone that I'll give them a call tomorrow, I always go right to Goldmine to schedule the call so I actually remember to do it. Sure and if you believe that one...

Unfortunately, there is no way to make sure you or I always schedule that follow up call but we can make the process quicker (which greatly increases the chances of adding the followup to Goldmine).

Here is what I do: In the Date field when scheduling the call (or any type of activity), instead of typing in a date, or using the lookup to bring up a calendar, type in 1D.

Then, when you tab or click, to the next field the date will be set for 1 day from today. Also, when you want to schedule the activity 2 days from now, type in 2D, for 3 days 3D.

D works for Days, W for Weeks, and M for Months.

GoldMine: Methods To Send Holiday Cards

A client wants to know: I have added Merge Codes for holiday cards. Can you tell me how to generate a filter or group to create a list?

Yes, however, the method you use depends in part on the type of contact. For example, if you are only working with primary contacts I recommend using a filter structured as follows:

The key is to use the Contains operator, as there may be multiple values in the field. Once you’ve built your filter you can see the list from the Filter’s Preview tab as well as from the Contact Search Center!

Then, you can export from the search center, use GoldMine’s report’s to print labels, and send the holiday cards.

You can also use GoldMine’s Export Wizard available under File Import/Export Export Contact Records. The wizard gives you a variety of options of the export format (dBASE, ASCII, etc.) as well as whether you want to export primary and/or secondary contacts as well as limiting the export to a merge code.

Once exported, you can use Word to print labels and send the cards. The only problem with this approach is that if the additional contact doesn’t have an address listed, the Export wizard won’t automatically pick up the address of the primary contact.

So, to export a list for all contact types, you need to use the SQL Query feature. You will need to run three SQL queries as shown below:

One for primary contacts:

select company, contact, title, address1, address2, address3, city, state, zip, phone1, contact1.accountno from contact1 where mergecodes like ‘%HC%’ order by company, contact

One for secondary contacts without an address (using the address from the primary contact):

select contact1.company, contsupp.contact, contsupp.title, contact1.address1, contact1.address2, contact1.address3, contact1.city, contact1.state, contact1.zip, contsupp.phone, contact1.accountno from contact1, contsupp where rectype = ‘C’ and (contsupp.address1 is null or contsupp.address1 <= ”) and contact1.accountno = contsupp.accountno and contsupp.mergecodes like ‘%HC%’ order by company, contsupp.contact

One for secondary contacts with an address:

select contact1.company, contsupp.contact, contsupp.title, contsupp.address1, contsupp.address2, contsupp.address3, contsupp.city, contsupp.state, contsupp.zip, contsupp.phone, contact1.accountno from contact1, contsupp where rectype = ‘C’ and contsupp.address1 > ‘ ‘ and contact1.accountno = contsupp.accountno and contsupp.mergecodes like ‘%HC%’order by company, contsupp.contact

Just change the ‘HC’ in each query to the mergecode you’re using. After running each query you can Right-Click > Output to Excel and export a list for sending the holiday cards.

GoldMine: Tagging Contacts for Holiday Cards

A client asked: Can you provide a way to filter contacts that should be getting a holiday card?

Sure, there are several different ways of doing this, but the following is easy and reliable. This method uses the Merge field and can be used for both primary and secondary contacts.

To configure the merge field for Holiday cards, we have to change the lookup values for the primary contact and the same merge codes will automatically be used for secondary contacts.

Within the Merge field, use the F2 key to view the Lookup list.
Left-Click on the New button to add different merge codes.
You may want single or multiple codes (for cards and gifts)

For example, you can use
HC for Holiday Card and HG for Holiday Gift.

If you use the format: HC;//Holiday Card (Code, semi-colon, two slashes, and then description) then only the code will be placed in the field.

With this format you can fit up to 5 codes in the merge code field. Select one code, as you normally would when using the lookup list, then select a second in the same manner. GoldMine will automatically place a comma and space between the two instead of replacing one code with another.

You can use any number of letters or numbers for the code but all codes should have the same number of characters. Once the codes are added, you can use a SQL query and the export wizard to produce a list

Wednesday, October 28, 2009

QuickBooks: Clean Up Lists

A client asked: We need to clean up our lists. Can you give us some easy ways to reduce duplicate and inactive names?

There are a number of things you can do. First, you can merge duplicate customers, vendors and items. You can also make a list item inactive so that it is hidden. These clean up methods are outlined below.

The following procedure will show you how to merge two vendors All the historical transactions that contained the predecessor name will be modified to contain the survivors name. This procedure can also be used for items, customers, and accounts.

From your Lists Menu Click on Vendors
Determine which vendor name will be the survivor.
(Make a note of the exact spelling.)
Determine which vendor will be merged off the list
Edit the vendor that will no longer exist
(Control + E, or right click on the name > select edit vendor)
Retype the name using the exact spelling of the survivor
Click OK and a popup will ask if you want to merge
Click Yes.

When a list name is no longer used but cannot be deleted because of prior activity you can hide it from sight by making it inactive.

Highlight the list item (Customer, Vendor or Account)
From the edit menu, select Make inactive or Ctrl+E to edit
Click on the option make inactiveor
When at least one name is set to inactive - Click on show all
In the column to the left of the list name click to add an "X”.
Remove the show all checkmark when done

All list items/names (including inactive) will continue to show up in the reports for any given period as long as there was a balance in that period, except inventory items, so be sure to print and save all end of period inventory reports before inactivating inventory items.

QuickBooks: Problems With "Write Check"

A client recently asked: A 'write check' window was used to pay a bill that had also been entered into QuickBooks with an enter bills window. How do I mark the bill that is still showing as open on the accounts payable report as paid?

Two steps will correct the situation:
1. Find the 'write check' transaction. Change the account to 'accounts payable' and enter the vendor name under the 'customer:job' field. Save and close

2. To link the payment to the vendor bill, From the Vendor menu > Pay bills > place a checkmark next to the open vendor bill > Click on set credits and then done. Save and close the pay bill window.

This solution is better than simply deleting the original 'bill' transaction. The dates used on forms in QuickBooks determines the month the transaction will show up on reports. The date used on the bill transaction will keep the expense in the month it was incurred. If you delete the 'bill' the date on the check would be the date that the expense shows up in reports.

QuickBooks: Overpayment by Customer

A client asked: What do I need to do when a customer overpays?

First, determine if you need to issue a refund check or if the funds will be retained on account to be used against other outstanding invoices or future sales to the customer.

Here are examples of both options:

A customer, XYZ, sends you a check for $48.00 to pay for two invoices The receive payments window only shows invoice 42 with $30.00 unpaid.

Run a search on $18.00 (Edit menu > Advanced find)
Select the filter amount > click on the button "="
Enter $18.00 > click Find
You find XYZ has previously paid the $18.00 with another check

Option One: Refund the overpayment:

Enter the check number in the receive payment window
Enter an amount of $20.00 and apply to invoice #42.
In the lower left, click on "group with undeposited funds".
Save and close.

From the banking menu, click on Make deposits
Select all the checks that apply to the deposit
Click OK.

On the 2nd window, click on the next line under "received from"
Enter the customer name XYZ. Tab to the next column
Type "Customer refunds" as the account name.

You will be required to set up this account, select type = "income".
In the memo field, put a note to describe the transaction
Enter the amount in the far right corner.
Save and Close

From the "write checks" window, create a check payable to XYZ
Code to the account "Customer refunds".
Enter description into memo field.
Save and print the check.

The customer refunds account now has a zero balance. The account's only purpose is to clear the overpayment deposit and return payment to the customer.

Option 2: Apply the overpayment "on account":

Enter the check number in the receive payment window
Enter in the amount of the payment received $48.00.
Place a check next to the invoice #42 $30.00
Do not place a checkmark next to any other invoice.
Clicking save and close will leave the $18.00 as a credit on the account.

A pop up window will remind you that this amount will be left open (as a credit) to be used against future invoices entered into the system. Consider mailing the customer a new statement (Customer menu > Create Statements) which will notify the customer of the new credit on his account.

QuickBooks: You Can't Memorize That

A client recently asked: Are there transactions that can't be memorized?

Yes, transactions that cannot be memorized include payroll checks, time records, bill payments, sales tax payments or receipts or deposits of payments. There are a lot of transactions, however, that can be memorized to save time and increase efficiency.

Some of these are:
Loan or lease payments, recurring bills, recurring checks, monthly health insurance bills, monthly electronic withdrawals from checking, or business insurance installment payments

Other items include:
Rent, recurring sales, rent collections, monthly installments payments due from customers, repeating tuition invoices, recurring purchases orders, purchase orders that are repeated with many of the same items, recurring journal entries, allocation of overhead to other departments and recurring estimates

To memorize a transaction from the transaction window:

Enter the transaction as you would like it memorized
Control + M or from the edit menu select “memorize”
Enter a name that will help you recognize the transaction
Complete: How often, next date to enter, number of remaining payments
Retrieve and use the memorized transactions from the list menu.
O use the shortcut Control key+ T

The transaction template you used to create the memorized transaction can be saved as a valid transaction, or cleared

QuickBooks: Correcting Memorized Transactions

A client asked: How do I correct a memorized transaction?

To correct a memorized transaction, you need to do the following:

Double-click on the memorized transaction like writing a check
This will also work for a bill or invoice
Make the correction, then re-memorize (control + M)
Select "replace the previous transaction"
Cancel the open transaction by closing the window

To modify the frequency:
From the memorized transaction list, highlight the transaction
Press Ctrl + E (to edit right click and select edit).
Change the scheduling of the transaction and click OK.

Tuesday, October 27, 2009

CRM: Auto-Fill for Empty Fields

A client asked: We have a field that is required but if left blank on save we want it to autopopulate with the combination of two fields that exist on the form. Can this be done?

Yes, this can be done using client-side javascript.
First, you'll need to open the form for the record that you want to update:

* Select Settings Customization Customize Entities
* Choose the entity you want this customization to work for 00
* Double Click Click the Forms and Views item
* Double Click on the Form.

The two fields you want to use to create the third field, and the third field itself, should be on the same form. If not, add them to the form.

* Update the OnSave Event: Click Form Properties (right-side of the form, bottom)
* Select OnSave and then Edit Make sure you check "Event is enabled"

Use the sample code below as a starting point to develop your code. Once it is complete, click Save and Close then Publish.

[code]

if (crmForm.all.[VarCharFieldName].DataValue == null) {

// Retrieve the value of the lookup field
// If it is null, then return an empty string
var lkupfield = crmForm.all.[LookupFieldName].DataValue;
if (lkupfield[0] != null) {
var lkupvalue = lkupfield[0].name;
}
else
{
var lkupvalue = "";
}

// Return the value for the Picklist item
var picklistvalue = crmForm.all.[PickListFieldName].SelectedText;

// Concatenate the two values into a third value - a varchar attribute
crmForm.all.[VarCharFieldName].DataValue = lkupvalue + " " + picklistvalue;

}

[/code]

CRM: Changing Checkbox Execution

A client recently asked: When I click a checkbox on a form, an action associated with the checkbox is not executed until I leave the checkbox. Can this be changed to execute immediately upon checking the box?

Every CRM Form field has an OnChange event associated with it that allows you to execute JavaScript when the user changes the value of the attribute. This event is fired when you change the attribute’s value and you leave the field – by clicking or using the Tab key. In certain instances, bit attributes on the CRM Form which are formatted to display as a Checkbox.

When an action is associated with a checkbox, it would be better for the JavaScript (added to the OnChange event) to be executed immediately when clicking the checkbox and changing the value, not when the user leaves the field.

Using the following code, you can add that functionality:

function ClickMe(){ crmForm.all.new_checkboxfield.FireOnChange();} crmForm.all.new_checkboxfield.attachEvent('onclick',ClickMe, false);

Here is how it works

In the Form’s OnLoad event:

* Create a small JavaScript function called ClickMe that does nothing more than call the OnChange event for the bit attribute we’re working with.

* Use the JavaScript function attachEvent to attach the ClickMe function to the onclick event of the attribute.

Once this code is published, any time the user clicks the checkbox, it will execute the OnChange code for the attribute.

CRM: OnSave Issues

A client recently had this problem: When converting an opportunity to an order, the opportunity was saved despite the fact that custom validations were not completed. What can be done to prevent this?

When working with a customer project, it is often necessary to control the OnSave event. Despite adding logic to prevent the user from saving if certain form conditions exist, the opportunity was saved anyway. When tested, however, we found that Save events were coming from a variety of places and overriding the logic we had put in place.

In the Opportunity Entity, there are several related entities connected to the entity. Clicking on the Orders link on the Opportunity allows you to review or create new associated Orders.

When you click the New Order button on the Orders pane, one of the first actions it will perform is to save the parent Opportunity with an internal call that looks something like this:
crmForm.SubmitCrmForm(21, true, true, false);

This system-originated save caused a problem in our solution because we had hidden the Save and Save and Close buttons until our approval processes had been completed. This system originated save broke our validation processes.

If you review the OnSave event documentation on MSDN, you’ll see that you can actually determine from where the save originated. The property event.Mode will contain the origination point for the save activity.

In our case, we only wanted the OnSave event to fire if the event.Mode was 1 ( Save ), or 2 ( Save and Close ). The code looks like this: if (event.Mode == 1 event.Mode == 2){}

Finally, if it turns out that you need to watch for a specific save event, the first parameter of the crmForm.SubmitCrmForm will contain value that will be used to set the event.Mode.

As you will see in the SDK documentation, you can actually stop the save from occurring, using the following code:

event.returnValue = false;

This is useful should you need to inspect the form state and only allow the process to complete if all checks pass.

CRM: Calculating Field Values

A client asked : Is there an easy way to calculate the value of a field based on the value of two other fields on the form?

Yes, a simple way to calculate values is by using JavaScript. The value of the field is updated whenever the value of one of the two other fields is changed. For example, use the Opportunity form and add a custom field that shows the total value of the opportunity after discount.

Use the following fields for reference in the example:
Field1 (Custom attribute) = Total_Estimated_Value
Field2 (Custom attribute) = Total_Discount
Field3 (Standard attribute)= Est. Booking (estimatedvalue)

Steps to implement the change: Create the two custom attributes that you need by going to:

Settings > Customization > Customize Entities > open Opportunity
Select Attributes > Create two money attributes > Save the changes.
Open the Opportunity form and add the two fields to the form.

Double-click Est Booking Value (estimatedvalue) on the form
Add the following line of script to OnChange event of the field:
crmForm.all. Total_Estimated_Value.FireOnChange();

Open the Total_Discount field on the form
Add the same script to the OnChange event of the field crmForm.all.Total_Estimated_Value.FireOnChange();

Open the Total_Estimated_Value field on the form
Add the following script to the OnChange event of the field crmForm.all.Total_Estimated_Value.DataValue=crmForm.all.estimatedvalue.DataValue - crmForm.all.Total_Discount.DataValue

Save your changes and test using the Create or Update Form feature from the Customization Tool. The end result will be that whenever the Total_Discount or Est. Booking field is updated the Total_Estimated_Value will update by subtracting the Total_Discount from the Est. Booking amount.

Please note that this example assumes there is always a value in the Est. Booking and Discount field when one of those fields are updated. If there is no value (null), then you will have to set the value to 0 (zero). Checking and setting the default value of the fields to 0 can be done in the Form properties OnLoad event.

CRM: Unsatisfied Customers

A client asked: We would like a way to quickly identify unsatisfied customers in the Account grid view. Is it possible to change the color of the customer name to Red?

Yes, it is possible but difficult. The only clean solution would be an asp.net page using an external customizable grid and then inserting your new page with the color applied to account name.

A better solution would be to create a picklist (which would allow a greater number of choices than a checkbox) with Satisfied, Unsatisfied and Very Unsatisfied.

From this picklist, create a view to show all unsatisfied accounts (which is better than scrolling the entire list to find the Red accounts). They are all in one place and can be easily reviewed and contacted. This picklist could also be used to create a campaign for Satisfied customers or sending an email to all unsatisfied accounts.

The details of creating the picklist and view are below:

PickList:
Select Settings > Customization > Customize Entities
Select Attributes > Click New > Name the Attribute
Select Type (Picklist) > Add Picklist values > Save and Close
Select Account > Select Forms and Views > Main Application Form
Select Add Field > Add the new attribute > Save and Close
Select the Advanced Find View > Add attribute using Add Columns
Save and Close > Publish the Customization

This will add a new field to the Main Form that can be selected by users to indicate the satisfaction level of the customer. To make sure the field is used, you can make it Required.

View:
Open the CRM Web client > Select Accounts
Open Accounts > Select Satisfaction Level > Save and Close
Click Advanced Find > Select Account > Clear Details
Click Select > Select Satisfaction field > Select Values to view
Click Save As > Provide a Name for the View

Monday, October 26, 2009

HEAT: Recover From An Edit Set Crash

A client asked: We had a crash during the commit of an edit set. How do we clear the error message?

When an edit set crashes, performing a LoadDB from your backup may not work because a data flag inside a system table of HEAT is blocking you from loading. It's there to block others from logging into HEAT until the commit is finished.

However, when a commit crashes and the flag data is still inside the field, this field must be cleared if you want to reload your .haf file or open Call Logging to get back to normal.

To clear it, have a database administrator go to the HEATDB table and find the field DBStatus and clear its contents. It should have the same wording as the error message:

"Building Commit files - started at 07:07:07 by Admin"

Once this message is cleared from DBStatus, it's okay to reload your .haf file.

HEAT: Change Call History Grid

A client asked: How can we add, change or remove fields in the grid order when we click on the Call History button?

First, you should know what order you would like your fields to be in and be sure any pending Edit Sets have been committed. Then use the following steps to change the Call History grid.

* Open the Administrator tool > Start a Quick Customize.
* Highlight the CallLog table > Select the table type > Click Open Grid.
* In the next window move fields from the left and right sides.
* When you are finished arranging fields Apply the Quick Customize
* Log into Call Logging.

Now when you go to a customer's Call History you will see your new grid order.

HEAT: Automate HSS Login

A client asked: Is there a way to automatically enable an HSS login for a new employee instead of going into their Profile and adding the information?

There are many ways to enable a login for HEAT Self Service (HSS). One way is to run an SQL script that will do the work for you. Another way is to use the tools offered in Call Logging and BPAM.

Before discussing automatically creating a HSS login, I will review the manual method:

* From a profile record, click on View > Customer Login Information
* Click Enable Login and give that user the same CustID for a login.
* A corresponding record is created inside a table called HEAT CAI
* A few fields are filled in and HEAT maintains the link

To have HEAT create an HSS login automatically, do the following:

* Create an AutoTask that will use the Create Customer Login action.
* The autotask will run whenever a specific profile record is targeted.
* Create a BPAM rule using When a Customer Profile is Created template
* Have the rule run the AutoTask.

Now when a profile is created, BPAM will automatically enable a login for that user.

HEAT: Using Counters For Customer IDs

A client asked: What is a counter used for in HEAT?

A counter is a function in HEAT that provides you with a way to generate a specific unique number that can never be reused. For example, you may want to generate a specific asset tag or new Customer IDs. You can set up a counter to start at any number and then whenever a HEAT user generates another number the counter provides the next available number

To define a counter, follow these steps:

Start the Administrator > Click on Defaults > Counter Setup.
Click Add and a new window appears.
Give the counter a Name, a starting Value and select the Increment.
The increment can be 1, 2, or any number.

If you would like to have the counter reset itself, check the box next to "Should the counter automatically reset itself?" and then select How Often and enter the Initial value after reset.

Now set up an AutoTask using an Update Call action that will call the counter and write to any field that supports the size and type of the counter value. Right-click on the field, Insert Counter, then choose the Counter you created.

This can work for almost anything including generating new Customer IDs in the Profile table.

HEAT: Change Label Colors

A client wants to know: How can I change the color of a field label based on the contents of the field?

You would need to use the Administrator module and create an Edit Set. If the field is, for example, Call Status you would do the following:

* Select the CallLog table > Open Form > Select the Status label
* Right click, pick Attributes > Click Advanced > Connect to Call Status
* Right click and select Color > Select Advanced Color
* Add the Status value and related color (e.g. Red equals Open)

Commit the edit set and open Call Logging. The field label should now change color based on the entries in Advanced Color.

GoldMine: Side By Side Contacts

A client recently asked: Is there a way to compare two contacts without having to alternate back and forth between the two contacts?

By default, GoldMine displays only one contact at a time. But, you can program it to open other contact records simultaneously.

Here is the method:

* Select Window > New Contact Window > Opens the contact list.
* From the contact list, select the second contact you wish to see
* Double-click the second contact > This opens the second contact window.
* You will see a second record tab at the top of your screen.
* Select Window > Tile horizontally for a side by side view
* For a Vista-style view > Select Cascade Windows

This method will save time by eliminating the need to alternate back and forth between two records.

GoldMine: Convert Email To Case

A client asked: Is there a way to convert an incoming email into a Service Center case?

Yes, you can do the following to add to a New Case or link to an Existing Case:

* Click the Oppty/Proj button on the email
* Select the option to create a New Case
* The email will be linked to the Case.

For an Existing Case, you can link the Email to an Existing Case using the same option

GoldMine: Clearing Out The Inbox

A client wanted to know: I have thousands of email in an Inbox. Is there an easy way to clear them out?

There are a number of ways. If you have the latest version of GoldMine (8.5.1.12), then you can clear out an inbox when deleting a user by selecting "Delete the user's pending activities and unfiled e-mails."

If you are not clearing the Inbox in conjunction with deleting a user, then you can use Goldbox or use the following queries:

First, backup your SQL Database. The following examples assume that the user's name is "DAVE", and that your database is named "Goldmine".

From the SQL Server Management Studio run the following query:
Select * from goldmine.dbo.mailbox where Folder = 'X-GM-INBOX' and userid = 'DAVE'

If you like the results, then run:
Delete from goldmine.dbo.mailbox where Folder='X-GM-INBOX' and userid = 'DAVE'

Then you can clear out the CAL table:
Select * from goldmine.dbo.cal where rectype = 'M' and userid = 'DAVE'

Just be sure to backup your database before doing any of this. Note: if you are using the Goldmine Sync functions, Goldbox is sync-aware and would the best choice.

Crystal Reports : Rotating Text

Often, when creating a report with graphs or indeed embedded images, we may want to change the direction or rotation of a field.

1. Right-click on the field in question within the Crystal Designer.
2. Select "Format Text" or "Format Field" (depending on the field type).
3. In the middle of the first formatting page, select a different Rotation. (i.e. 90 or 270).

Crystal Reports : Changing JOIN Types

Within the Visual Linking Expert (Database | Visual Linking Expert), we can create JOINS between the tables that make up our report.
For those of us that have more complex needs or a more modular (relational) database (i.e. many tables) may need to use different kinds of JOINS. The "default" JOIN type is INNER.

1. Within the Visual Linking Expert, right click on a "link" (right on the line).
2. Select "Options".
3. At the bottom right of the window, you may select a JOIN type. All the expected types are listed (i.e. LEFT OUTER, RIGHT OUTER, etc).

Note: Changing this will impact your expected recordcounts. It is advisable to be familiar with all the JOIN types before modifying.

Crystal Reports : Dynamic Parameters

Note : Only applies to Crystal Reports XI or higher.

A Dynamic Parameter is one that "refreshes" it's values list every time the report prompts the user for parameter input. This can be very handy, especially in the instance of our user table, which sees a lot of updates as turnover has been high lately.

When creating your parameter, select Dynamic List of Values.
You may then select a fieldname to use as a list source, which in our case would be something like {USERS.USERNAME}.

ProTip: You may also use a pure SQL query to specify the values list.

Crystal Reports : Caldulating the Week "Number"

Most reports use some kind of date math or date grouping (i.e dollars per month, week, etc).
Let us suppose we need to return the week "number" within any given year based upon a simple date. (Our fieldname will be {SALES.SALEDATE})

The DATEPART() function is what we need here:

DATEPART("ww", {SALES.SALEDATE}, crSunday)

1. The "ww" specifies that we want the "week number" returned.
2. {SALES.SALEDATE} is the date field to evaluate.
3. The final parameter, crSunday, specifies on what day the week starts.

GoldMine: Changing Zip Label

A client asked: I need to change a field label color (Zip) if the field is empty and if another field (Country) is either 'UK' or empty.

You can do this a number of ways. Below is an expression to do this:

* Right click and select Screen Design
* Double-click on the Zip field > Select the Color tab
* Enter the following in the (Label) Expression area:

IIF(Empty(Contact1->Zip).AND.(Contact1->Country="UK".OR.Empty(Contact1->Country)), 255, 0)

This will turn the Zip field to Red when the conditions in the expression are met.

Crystal Reports : The Exclusive "OR"

Most of you will have been comfortable using just "AND" and "OR" as boolean operators when creating crystal formulas. Once in awhile, however, we will need to use the "XOR" operator. This "exclusive" OR will only return TRUE if either of the conditions is true, yet return FALSE if both or none of the conditions is true.
Let us imagine a user database that tracks software class certifications. There are two fields, {USERS.CLASS1} AND {USERS.CLASS2}, which represent two classes any one user may have taken. To determine which users have completed just ONE (i.e. field is not null) class, but not BOTH or NONE, use the "XOR" operator thus:

ISNULL({USERS.CLASS1}) = TRUE XOR ISNULL({USERS.CLASS2}

GoldMine: Finding Contacts With an Email Address

A client recently asked: II have a query that finds contacts without emails but I need to find all contacts with emails. How do I set up the query?

There are a few ways, depending on the version of Goldmine. I have found the queries below to be accurate:

For Goldmine Premium 8.5, take the query below for No Email and convert the "NOT IN" to "IN"

SELECT C1.CONTACT, C1.COMPANY, C1.ADDRESS1, C1.ADDRESS2, C1.CITY, C1.STATE, C1.ZIP, C1.PHONE1, C1.PHONE2, C1.PHONE3 FROM CONTACT1 AS C1 WHERE C1.ACCOUNTNO NOT IN (SELECT DISTINCT ACCOUNTNO FROM CONTSUPP WHERE(CONTSUPP.RECTYPE = 'P' AND CONTSUPP.CONTACT = 'E-MAIL ADDRESS')) ORDER BY C1.CONTACT

Query where Contact has an Email Address:

SELECT C1.CONTACT, C1.COMPANY, C1.ADDRESS1, C1.ADDRESS2, C1.CITY, C1.STATE, C1.ZIP, C1.PHONE1, C1.PHONE2, C1.PHONE3 FROM CONTACT1 AS C1 WHERE C1.ACCOUNTNO IN (SELECT DISTINCT ACCOUNTNO FROM CONTSUPP WHERE (CONTSUPP.RECTYPE = 'P' AND CONTSUPP.CONTACT = 'E-MAIL ADDRESS')) ORDER BY C1.CONTACT

If you are using an older version of Goldmine, this query works well:

Select distinct Contact1.AccountNo, Contact1.Company, Contact1.Contact, ContSupp.ContSupRef+ContSupp.Address1 as Email
from Contact1,ContSupp where Contact1.AccountNo=ContSupp.AccountNo and ContSupp.Contact='E-mail Address' and ContSupp.Zip like '_1__'

GoldMine: Finding Referrals

A client asked: Is there a query that will show all referrals entered in Goldmine?

Yes, this can be done within GoldMine:

First, select the SQL Query
Enter the following:

Select * from ContSupp where RecType = 'R'

This will return two records for each referral (Referred To / Referred From) so the order of the query results are important. You can use any of the following depending on your reason for finding the referrals:

Order by Contact field; by the Contsupref field; or by the Accountno field.

The query would then look like the following:

Select * from ContSupp where RecType = 'R'
Order by Contact

GoldMine: Revise Phone Number

A client asked: I have a number of records that were incorrectly created with the US phone format. How can I globally correct these records?

I have a number of records that were incorrectly created with the US phone format. How can I globally correct these records?

First, backup your database. Then do the following:* Create a group of the records with the non-numeric characters
* Select Tools > Data Management > Global Replace
* Select Update a field using advanced options
* Select the Phone1 field as the Update Field
* Select Evaluate Value as dBASE expression
* Enter the following the Expression field

StrTran(Contact1->Phone1, [(], [])
* Select Next > Next > Select your Group
* Select Finish

This expression will remove the left parentheses. You will need to repeat this process for the other characters - change [(] to [)] and then to [-] to remove all non-numeric characters for the phone number

Note: If you have GoldBox, you can use PhoneStrip("Contact1->Phone1") to get all characters with one pass.

Friday, October 2, 2009

CRM: About IFRAMES

A client asked: Why would I get an error message when I load a form with an IFRAME. The message is "Do you want to view only the webpage content that was delivered securely?"

This error is caused by using the very common method of defaulting the url to "about:blank" for your IFRAME and then using javascript to set the actual url. This works well until you start serving CRM over HTTPS. Then you get this security warning.

The problem here is that when your CRM form it is being served over HTTPS. The CRM form (e.g. Account) loads and the IFRAME that calls “about:blank” loads in a different Internet Explorer Zone. Most likely CRM is loading under the Trusted Site zone and About:Blank is loading in Restricted Sites.

The workaround is to use a better practice. Instead of using about:blank when you default your IFRAME to nothing, use /_static/blank.htm. The path is relative so it will work whether your CRM is hosted as https: or http:

This practice also conforms with current security settings and the heightened concern about viruses and malware.

CRM: Get Values From the Parent

A client recently asked: Is it possible to create an order from within the Account and add the Account to the order automatically?

Yes, it is not uncommon to pull data for from the parent entity of the current record. In many cases, this child entity is only created from its parent so we can reference the object to get to the parent form.

An example is creating a new Order from inside the Account form. The solution below is dynamic and can be called from the Form OnLoad event.
function GetParentFormFieldValue(fieldName)
{
var retVar = null;
if (
(window.opener != null) &&
(window.opener.parent != null) &&
(window.opener.parent.document != null) &&
(window.opener.parent.document.crmForm != null)
)
{
eval("retVar = window.opener.parent.document.crmForm.all." + fieldName + ".DataValue");
}
return retVar;
}


To use this code, add the following:
crmForm.all.name.DataValue = "New order for " + GetParentFormFieldValue("name");

CRM: Customization Testing 101

A client asked: We are making a lot of our own custom changes to CRM. Should we have a process in place to test these changes before they reach the end-users?

Yes, since CRM is very easy to customize, there are a lot of end-users making their own changes (including javascript) without any testing before the customized CRM is placed in production.

Basic Testing
Ideally you should have a production CRM database and a development CRM database. You develop customizations on the development database and once your testing is complete, you move them (using export customizations) to the production database.

Test Users
You should have at least one test user for each role in which normal users exist (normal being non-CRM Administrators). This will allow you to test the functionality of your customizations and custom solutions as each type of user.

While this may seem like a lot of extra work to you, keep in mind that CRM changes the environment and the user interface based on the user’s security. This means that sometimes users will not see things as you expect them to or they will have permissions issues where you do not.

How to Test
Prior to Windows Vista and Server 2008, you could simply right-click on the Internet Explorer icon, select Run As and supply the credentials of the test user you want to use. That changes with the new environments and does not work.

A possible work-around is using the ShellRunAs commandlet (which can be downloaded) or should nothing else work, you can just Switch Users and log into the machine as each test user.

What to Test
Here are the usual items for testing customizations within CRM:
* The Site Map ( left-hand navigation )
* ISV.Config ( buttons and menus )
* JavaScript ( does your custom JavaScript work with all users )
* Custom Solutions ( ASP.NET code written and added to CRM )
* Processes. If you have a process that moves data through the system, test it from start to finish as the particular user or users who actually perform the work to make sure you’re covering the whole process as a “normal” user and experiencing what they experience.

Summary
This is the very least amount of testing you need to perform on a customized system. You can get as comprehensive and complex as you like as your testing needs increase.

If you will document your testing procedures and repeat those steps each time a change is made, you should be able to identify problems and create solutions before your changes reach the end-users.

Thursday, October 1, 2009

CRM: Creative Quick Search

A client asked: We like the quick search for finding account numbers or addresses without a company, but is there a way that the quick search will look in both active and inactive accounts?

Yes, but since CRM 4.0 does not have a method for including inactive records and the customization window does not allow modifying the filter criteria of the view, you need to use a workaround:

Click on Settings > Customization > Export Customizations.

Select Account and click on Export Selected Customizations.

Click OK to the warning window.

Click Save on the File Download window. Save the file on your desktop.

Extract the Customizations.zip file and then open the Customizations.xml file in NotePad.

Search for “Quick Find Active.”

Remove the filter condition shown below (it is separated from the other code).

- <fetchxml>
- <fetch version="1.0" output-format="xml-platform" mapping="logical">
- <entity name="account">
<attribute name="name" />
<attribute name="accountnumber" />
<attribute name="primarycontactid" />
<attribute name="address1_city" />
<attribute name="telephone1" />
<attribute name="emailaddress1" />
<attribute name="accountid" />
<order attribute="name" descending="false" />

- <filter type="and">
<condition attribute="statecode" operator="eq" value="0" />
</filter>

- <filter type="or" isquickfindfields="1">
<condition attribute="name" operator="like" value="{0}" />
<condition attribute="accountnumber" operator="like" value="{0}" />
<condition attribute="emailaddress1" operator="like" value="{0}" />
</filter>
</entity>
</fetch>
</fetchxml>
- <LocalizedNames>
<LocalizedName description="Quick Find Active Accounts" languagecode="1033" />
</LocalizedNames>
</savedquery>
- <savedquery>
<iscustomizable name="No">0</iscustomizable>
<isquickfindquery name="No">0</isquickfindquery>
<isuserdefined name="Yes">1</isuserdefined>
<isprivate>0</isprivate>
<isdefault name="No">0</isdefault

Save the XML file. In CRM, click on Settings > Customization > Import Customizations.

Browse to the modified XML file and click on Upload. This will read the XML file to determine what customizations are contained in it. Make sure Account is selected and choose Import Selected Customizations.

To publish the changes:
Click on Settings > Customization > Customize Entities.
Select Account and click on Publish.

This is an unsupported method; however, it will work on any of the entities including custom entities.

CRM: Upper and Lowercase Javascript

A client asked: I need a way to populate a field in Account when I enter a specific valule. Can this be done?

Yes, this can be done using javascript as follows:

Select Settings > Select Customization > Select Customize Entities
Open the Account entity > Select Forms and Views
Select Main Application Form > Double click SValue > Select Events
Open OnChange > Click "Event is Enabled" > Copy the script below

var svalue = null;
switch(crmForm.all.picklistField1.SelectedText) {

case "Nine":
case "Eight":
svalue = "Acceptable";
break;

case "Six":
svalue = "Unacceptable";
break;
}
crmForm.all.textField1.DataValue = svalue;

If you have problems with people entering upper or lower case, change the script to the following:

var svalue = null;

switch(crmForm.all.picklistField1.SelectedText.toLowerCase()) {
case "Nine":
case "Eight":
svalue = "Acceptable";
break;

case "Six":
svalue = "Unacceptable";
break;
}
crmForm.all.textField1.DataValue = svalue;

Publish the changes and try it out.

HEAT: Alert Monitor Fields

A client recently asked: How do I change the fields displayed in Alert Monitor and the order they appear? I have tried changing the Call Log grid with Quick Customize but it has no effect.

The Alert Monitor grid view is part of Alert Monitor and is somewhat hidden:

Open Alert Monitor > Stop Polling > Click Define > Call Group Alerts
Select a Call Group > Select Edit > Click Grid
The fields displayed in Alert Monitor and their order are shown.
Use the functions here to remove or add fields and change the order.

When you are done, exit the window and the columns will immediately change. This needs to be done for each Call Group in Alert Monitor. Don't forget to save your alr file before you exit Alert Monitor.

HEAT: Alert Monitor Pop-Ups

A client asked: After setting up Alert Monitor, I now get a pop-up window that opens every minute showing my calls. How can I stop this?

First, identify why this is occurring. Use the steps below:

Open Alert Monitor > Stop Polling > Select Define > Call Group Alerts
Select a Group > Click Edit > Review the setup
If "Any Calls are Found" is checked, uncheck it
Save the revised alr file > Resume polling

The pop-ups each minute should stop. You should now get a pop-up only when a new call is created.

HEAT: How Do I Create a DAT file?

A client asked: When I try to use the import/export function of HEAT Administrator, it asks for a DAT file to import. What is this?

A DAT (.dat) file is a text file formatted in a specific way. They can be opened with Notepad or other text editors. To begin the process of creating a DAT file to import, first export the existing table as a DAT file to review the structure.

Select Export > Select Category > Browse to a folder > Export
Open Category.dat with Notepad > you will see the format below

'Name''Age''City''State'

This structure is a delimited text file using a single quote and a pipe symbol (). To create this type of file from an Excel file, you would do the following:

Save your Excel file as a "csv" file type
Open the file (category.csv) in Word
Use Find and Replace to convert the commas to ''
Create a macro to add a single quote in the beginning of each row
Create a macro to add ' at the end of each row

Save the file as a text file and give it the extension .dat. This can now be used to import into the HEAT category table using Administrator.