Wednesday, March 31, 2010

MSCRM: Hide Nav Bar Items

We want to hide certain mavigational items that on CRM forms (e.g. Contact) Can this be done with JavaScript?


Yes, this is relatively easy to do. All you need is a basic understanding of HTML and javascript.

Open A Contact in Microsoft CRM
* Open a Contact, Press Ctrl + N to open the Contact in a new window
* In Internet Explorer, Click View -> Source
* When the HTML opens in Notepad do a search for Sub-Contacts
* Just before the highlighted text you will see: id="navSubConts"
* Write down stuff between the "" signs.

Add the javascript below to the OnLoad Event of the contact form:
* Open CRM in the Web Client
* Click Settings -> Customization -> Customize Entities
* Double-Click the Contact Entity
* Click Forms & Views in the left-hand pane
* When the list of Forms & Views appear, double-click Form
* In the right-hand pane, Click Form Properties
* Click OnLoad and Click Edit
* Paste the following Javascript in the box:

document.getElementById("navSubConts").style.display = "none";

* Check the Enable Event checkbox
* Click Save & Close twice and then click Publish

Test The Script:
Open a Contact in CRM, the Sub-Contacts menu option should be gone

You can use this same technique to hide other menu items in not only the Contact form within CRM but in any of the other CRM Forms as well. The main key to getting this to work is finding the right ID value in the HTML code. Most, if not all, of the navigation is stored with "div", "/div" tags.

MSCRM: Contact SSN Formatted

A client asked: We need a field to enter and automatically format the social security number. How do we do this?

* Select Customization > Customize Entities
* Select Contact > Select Attributes
* Click New > Add the 'aaa_contact1ssn' field
* The field will be named 'new_aaa_contact1ssn'
* Click Save and Close > Click Forms and Views

* Select Contact > Main Application Form
* Click Add Fields > Add new_aaa_contact1ssn
* Click the field > Change label to SSN
* Click Form Properties > OnLoad Event

Add the following to the pane > check event enabled

function formatSSN()
{
var theCount = 0;
var theString = crmForm.all.new_aaa_contact1ssn.DataValue;
var newString = "";
var myString = theString;
var theLen = myString.length;
for ( var i = 0 ; i < theLen ; i++ )
{
// Character codes for ints 1 - 9 are 48 - 57
if ( (myString.charCodeAt(i) >= 48 ) && (myString.charCodeAt(i) <= 57) )
newString = newString + myString.charAt(i);
}
// Now the validation to determine that the remaining string is 9 characters.
if (newString.length == 9 )
{
// Now the string has been stripped of other chars it can be reformatted to ###-##-####
var newLen = newString.length;
var newSSN = "";
for ( var i = 0 ; i < newLen ; i++ )
{
if ( ( i == 2 ) || ( i == 4 ) )
{
newSSN = newSSN + newString.charAt(i) + "-";
}else{
newSSN = newSSN + newString.charAt(i);
}
}
crmForm.all.new_aaa_contact1ssn.DataValue = newSSN;
return true;
}else{
alert("The Social Security Number you entered "+newString+" does not contian the correct number of digits");
//crmForm.all.new_aaa_contact1ssn.focus();
return false;
}
}

formatSSN();

* Click Save and Close > Click Publish
* Open Workplace > Open a contact to test

MSCRM: Change Default Forms

A client asked: Our CRM contacts appear under 'My Active Contacts' when we open the Contacts form. Can we change this?

* Select Customization > Customize Entities
* Select Contact > Form to make Default (e.g. 'Active Contacts')
* Click More Actions > Set Default
* Save and Close > Publish

When open the Contacts, the new form willl automatically appear (e.g. Active Contacts or a custom form you create)

MSCRM: Dynamic Title Bar

Is there any way to change the title bar or the heading section of the contact form to display another value from the contact record?

* Select Customization > Customize Entities
* Select Contact > Main Application Form
* Click Form Properties > OnLoad Event
* Add the following:

var cells = document.getElementsByTagName("span");
for (var i = 0; i < cells.length; i++) {
if (cells[i].className == "ms-crm-Form-Title") {
cells[i].innerText = "Philadelphia";
break;
}
}

* Save and Close, then publish.

Whatever static value or field data value you add to the area above will appear in the Title Bar.

MSCRM: Modified Views

Views allow you to display data in Microsoft Dynamics CRM in a customized manner, designed for the way you work. Creating them is easy and only requires a few clicks.

The types of views include system and personal. Each entity has different system views such as 'associated view', 'active accounts', etc. You can modify and create entity specific views. Not only does a view allow you to view data from the record but also any records associated to it. This is very convenient when browsing data.

* Below is an example of associating an opportunity to a custom entity
* Call it UserActivityTime.
* Next, modify the system views to show data from the opportunity record.
* Select the attributes of the record.

*In the example the Est. Revenue field and the Est. Revenue base fields get crossed on the form. So you would use the base value to see the Est.Revenue.

The modified view now shows the new columns.

ACT: Swap Data

A client asked: How can I move the data from one field in ACT! to another field?

ACT! makes this quite easy:

* Create a Look Up of the records to move the data.
* After you have a Look Up, click Edit at the top of the ACT! screen.
* Next, select Swap Field.
* A 'Swap Data' dialog box will appear.
* You will see two pull down selectors
* They are labeled 'Swap contents of - With contents of'.
* The 'Swap contents of' field is the source field
* The 'With contents of' field is the destination.
* Click the field with the data you want to move
* Select which field to send the data.

After clicking OK, a Replace/Swap/Copy dialog box will appear with this text: "This operation modifies all records in the current look up. Are you sure you want to continue?" At this point, be sure you have the correct look up. Otherwise, you'll move data you didn't intend to move. Click Yes.

* The data is now moved from one field to the other.

ACT: Creating an Opportunity

A client asked: We would like to track our Lead activity. Would we use the Opportunities tab to do this?

An opportunity is a potential sale. Tracking your opportunities in ACT! lets you use the ACT! sales processes, or your own, to manage leads from the initial request to closing the sale.

To create a sales opportunity:

From the global toolbar, click the arrow next to New, and then click Opportunity or from a Contact Detail view, click the Opportunities tab then click the New Opportunity tool.

Complete the information in the Opportunity Detail.

Some fields are completed by default, but you can change the information. For example, the stage of an opportunity determines its probability of closing as well as the value of the sale. If you think the opportunity is farther along in the sales process, you can select a different stage or change the probability. You can change the date the opportunity is opened; its estimated close date, status, and so on

Add information in the tabs as needed, such as:
* Associate contacts, groups, or companies with the opportunity.
* On the Contact, Group, or Company Detail view
* Click the Opportunities tab.
* Click Add/Remove Opportunities.

Add a product or service
* On the Opportunity Detail view, click the Products/Services tab.
* Click Add.
* Select Edit Mode to add products and services directly in the grid.
* In the Add/Edit Product dialog box, select a product or service from the Name list, or type a name in the field.

* Select the Edit Value List to add a product or service.
* You can create a list of products and services.
* Names you type in do not become part of the Product List

If you created custom product fields and want to add them to the dialog box and the Product/Services tab, click Display Fields, and then customize the columns or from the Item # list, select the item number of a product or service.

* Enter or change information in the fields as needed. Tell me more
* In the Quantity field, enter or change the number of units
* Indicate the number of hours for a service.
* Use a decimal for partial hours
* To adjust the price of a product or service
* Type the price in the Adjusted Price field.
* To discount the price of the product or service by a percentage
* Type the percentage in the Discount field.
* To add another product or service, click Add.

Note: Currency fields display only two digits to the right of a decimal. If you require more precision, you can type additional digits to the right of the decimal. To view the additional digits, click in the field or change your operating system's Regional Settings.

ACT: Create a Custom Dashboard

We would like to create a custom Dashboard for our sales staff. We plan on using the Dashboard Designer to modify an existing Dashboard. Can you provide details?

1. Create a new Dashboard.
* From the Navbar, click Dashboard to display the default.
* Click Edit.
* From the File menu in the Dashboard Designer, click Save As.
* Type Sales Team Dashboard. Click Save.

2. Replace the Activities by User bar chart with an Activity List.
* Click the Activities by User to open Component Configuration.
* On the Activities by User page, under Component tasks,
Click Select Display Type.
* On the Select Display Type page, click List View. Click Next.
* On the Edit Default Filters page, click Next.
* On the Edit Header/Footer page, select Activities by User
* Change it to Activity List.
* Click Preview to view the reconfigured component.
* Click Close. Click Finish.
* ACT! displays a warning about changing the default filters.
* Since no one is using this Dashboard, click Yes to continue.
* Click Close.
* Click the Save tool.

3. Modify Opportunity Pipeline by Stage to display sales data.
* Double-click the Opportunity Pipeline by Stage component.
* On the Opportunity Pipeline by Stage page, under Component tasks,
click Edit Default Filters.
* Under Users, hold down the CTRL key and select users. Click Next.
* Under Component tasks, click Change Totals.
* On the Change Totals page, select the Show totals check box.
* Click Finish.
* Click Yes. Click Close.
* Click the Save tool.

ACT: Record Types

A client asked: What are Record Types and how are they used?

ACT! has parent and extended-data record types.

Parent records are:
* Contacts
* Groups
* Companies
* Opportunities

Extended-data records are owned (or co-owned or shared) by parent records. Extended-data records are:
* Activities
* Notes
* Histories
* Secondary contacts (contacts only)
* Products (opportunities only)

A user's access to a parent record limits his access to its extended-data records.

Think of the information in the database as having two levels, with parent records at the top level and extended-data records at the bottom level. The record access of the parent record cascades down to any extended-data records. So, if a user has no access to a parent record, such as a contact, the user also cannot access the extended-data records, such as notes, that belong to that contact.

ACT: Exporting Databases

A client asked: We want to export our database to another ACT! database. What is the best way to do this?

There are several ways to export your ACT! database (text file, Excel file) but the best way and most comprehensive way is to export directly to an ACT! Database format. The steps are below:

Open the database to export data from.
From the File menu, click Export.
The Export Wizard opens.

On the Specify Destination page:
* From What type of file do you want to export to
* Select ACT! Database 12.
* Type a path and file name.
* Or, click Browse, select a location, and type a file name.
* Enter the user name and password, if required.
* Click Next.

On the Specify record type(s) page:
* Under What kind of records would you like to export
* Select one or more types of records.
* You can select all record types at once.
* To export products, you must select Opportunity records.
* Under Which records do you want to export?
* Select an option.
* Current lookup requires that you create a lookup before exporting.
* Click Next.

On the Contact Map page:
* To view the data in the mapped fields
* Use the arrow buttons to move between records
* To change the default field mapping
* Click in a row under To this field
* Then click the drop-down arrow to select a different field
* Or, to clear all default field mapping
* Select a mapped field for each field
* Click Clear Map.

Tip: If you have a saved custom map, click Load Map. If you want to restore the default mapping, click Reset. Click Next.

If you selected to export more that one record type (e.g., Opportunities), the Opportunity Map and Product Map pages appear. Repeat steps for each record type field map page that appears.

On the Specify Merge Options page:
To include attachments, other users' public activities, and confirm each match, select the appropriate check boxes.

To change the default settings for record merge options, click Contact (or Group, Company, or Opportunity). Click Next.

On the Completing Export Wizard page, click Finish if the information is correct. If the information is not correct, click Back until you reach the page that needs correction.

If you selected the Confirm each match option and ACT! finds duplicate records, then the Match Confirmation dialog box appears. Choose options.

Tuesday, March 30, 2010

QuickBooks: Shipping Income

How do you classify your shipping? Is it an expense or COGS?

In-bound shipping, the cost to acquire the items or material you use to make items, is part of the cost of the items. But what about out-bound shipping?

When you charge your customer for shipping, that is an income to the business. When you pay to actually ship the items to the customer, that is an expense. The shipping income and the shipping expense then offset each other in the P&L.

But there can be exceptions for out-bound shipping.

For example, you sell your items to a store and as part of the sales price you agree to pay shipping. If the sale is final, not on consignment, then I would include the shipping cost in COGS for that sale.

But if you could be asked to pull your items off the store's shelves at your cost then the cost to ship them to the store cannot be considered COGS, because the sale was not final.

As always, ask your accountant regarding your specific situation.

QuickBooks: Disassemble an Assembly

A client asked: How do you disassemble an assembly? Is this possible?

To disassemble an assembly:

* Find the assembly on the item list
* Right click and select quick report
* Double click on the assembly build to change.

* That brings the build up on the screen
* Click in the number built and change it.
* Click save and yes to save the changes.

This lowers the build and returns unused parts to inventory. Note: this process can vary based on the version of QuickBooks.

Monday, March 29, 2010

Crystal Reports : Using MID() and INSTR() together

Sooooo, if we were able to write a dynamic MID function, one that could determine where the Lastname started no matter what it was, that would be pretty sweet, right? Because it is in the nature of a human name to have a space between Firstname and Lastname, we can do just that by unleashing our INSTR() "in string" function.

The INSTR() function searches a string for another string. It takes two parameters, like this:

INSTR(string to search, what to search for)

We are going to use it to search for the space in our {USERS.NAME} field.

So, going back to our "Justin Hill" example,

INSTR({USERS.NAME}, " ") = 7 - finds the space between first and lastname

So going back to our original problem, cutting out the Lastname, we can now substitute the starting position in our MID() function with the INSTR() from above like this:

MID({USERS.NAME}, INSTR({USERS.NAME}, " ")) returns " Hill" (note the leading space).

What you really need to do is increment our starting position by 1, because the Lastname starts one character position after the space. Like this:

MID({USERS.NAME}, INSTR({USERS.NAME}, " ") + 1) returns "Hill"

Because the MID function is always being passed the location of the space by INSTR it should reliably "kick out" the Lastname.

Then it is a simple matter to make this code into a Crystal Formula, then use that as your sort field in your Sort Expert.

Crystal Reports : Currency values continued

So, what we are saying here is that if we have no cases, then don't bother doing the division, just return a simple 0. A zero-divide error will stop the report in it's tracks, and NOTHING frustrates management more than plugging a billion parameters into a report that dies 1000 records into itself. Trust me on this one.

You will also notice that by omitting quotation marks around the 0, the formula is returning a Number instead of a String. This is important if you have other Formulas or Running Totals that are acting upon the returned value.

But I'll tell you honestly, that "hard-coded" 1000000 doesn't sit well with the Crystal Reporter within me. I'd rather create a {@DIVISOR} formula as a "constant" and then use {@DIVISOR} within {@AmountInMillions} Formula like this:

First we create {@DIVISOR} as simply 1000000.

Then substitute within {@AmountInMillions} like this:

IF {#CASESAMOUNT} = 0 THEN 0 ELSE {#CASESAMOUNT} / {@DIVISOR}

It may seem superfluous now, but down the road you may be happy you can just change your divisor in just one place and not a billion. Keep in mind that by building modularity into a report, you extend both it's and your longevity.

QuickBooks: Inventory Bin Locations

A client asked: We would like to use bin location in our inventory. Where do we setup bins>

QuickBooks does not handle bin locations for inventory parts. To create a workaround, you can do is create a custom field and use that. Once you create the custom field it will be show for all items in inventory.

* Open an inventory item under Lists
* Click Custom Fields
* Add Bin Location
* Click OK

Once you have the field created:
* Bring up the item list > right click on the column titles
* Select customize columns, find the bin field on the left side list
* Highlight it > click Add > move it up the list to where you want it

QuickBooks: Converting Non-Inventory

A client asked: How do I change non-inventory items to inventory items?

The answer is: Don't do this.

When you change a non-inventory part to inventory, QuickBooks goes back and changes every transaction that used that part, if the part was always an inventory item.

This means that prior years financial reports will change, since this changes inventory asset totals and COGS.

Keep in mind that COGS is handled differently for the two parts:
For the non-inventory part, when you bought and sold it, the actual cost was expensed immediately (to COGS)

When it becomes an inventory part the cost to purchase is averaged and that average cost is what is sent to COGS. In addition the cost of an inventory item is held in inventory asset, so your inventory value changes.

Instead of changing the item, rename it (e.g. add a suffix to the part name)and mark it as being inactive. Then create a new inventory part with the old name and use that item from that day forward.

QuickBooks: Computing Commisions by Invoice

A client asked: I pay an 8% commission on a specific service I offer. The commission is paid semi-annually so I want to accumulate a payable automatically. They can also be several vendors that are paid a commission. How do I do this?

The process is as follows:

* Setup a service item called XYZ at your price
* Point this at the sales income account
* Setup an Other Charge as 'XYZ Commission' at 8%
* Point this at an Other Current Liability- XYZ Other
* Setup a COGS Account called COGS-XYZ
* Setup a Service item called XYZ Fee
* Pointed this at COGS-XYZ at -8% with a sales price of zero

Then create a group, and add the following:
* On the first line add the service XYZ
* On the second line add the other charge (XYZ Other)
* On the third line add the service item (XYZ Fee)
* Type the description in the group description line.

Set the group to not print all lines so the customer does not see the commission.

When you sell the service use the group item. On screen you will see the fee computed, but when you print or print preview the invoice, the fees will not be shown.

HEAT: SupportMail Revisited

A client asked: I would like to use SupportMail for all my emails but I need html formatting. Is there a way to create html emails using SupportMail?

In HEAT version 9.5, html formatting has been added. HTML formatting and other SupportMail features are listed below.

Using SupportMail, you can:

* Send email to either internal HEAT users or external addressees.

* Use the Cc, Bcc and Forward functions. These are added by clicking the Fields button.

* If you know the address of the recipient, you can type it in, click Address and add from the Address Book of whatever email protocol has been defined (including POP, MAPI, etc)

* Select the Message Format: either HTML or Plain Text. If you select HTML, you have access to message formatting options in the formatting toolbar

* You can add a hyperlink or a picture to a message

* If the email is external, you can attach files. Click the Attach button to locate and attach one or more files to the email

* If you want to automatically forward the email, type an email address in the Forward If Not Read field or click Forward.

* If the email is internal, you can attach a Call Record. Select the Attach Call check box.

* Click Save to Outbox if you want a copy of the email saved to your Outbox.

* The Read Receipt option is only available if you are emailing to an external addressee. Select this option if you want a return email when the recipient reads your message.

* Select Create Journal if you want to import the email to a Journal entry for the currently active Call Record. If there are files attached to the email, you are asked if you also want to attach the files to the Call Record.

Sunday, March 28, 2010

HEAT: New Tabbed Forms

In Call Logging, is there a way to make items more visible? There are many functions (e.g. Call Groups, Support Mail, Attachments) that are buried under menus.

In HEAT 9.5, the latest version, the Display preferences allow you to create more tabs than in previous versions of HEAT. Also, some items have been added to the Call Logging form as tabs that were previously hidden behind the form.

To add new tabs to the Call Logging form:
* Select Edit > User Preferences
* Select the Display tab > Select Tabbed layout
* Click OK

This will add Call Groups, SupportMail, and My Hot List to the top of the Call Logging form as tabs. In addition, the new Attachments tab has been added to the Call Logging form. These additions provide visibility of a greater number of functions without sorting through numerous menu items.

HEAT: Dashboard Changes

I like the Dashboard in Call Logging but it would be more useful if it could be configured. Is there a way to change the Dashboard display?

This has been added to HEAT version 9.5. On the Dashboard that hovers above Call Logging, you can select which metrics are shown and also add your own.

Any of the views and metrics can be removed from display on the Dashboard, so you can display on the Dashboard only the information you need to see.

To modify the metrics shown:
* Click the Settings down arrow at the bottom left of the Dashboard
* Deselect the check box for the item to remove from the display

To prevent the dashboard from opening when you open Call Logging:
* Select Edit > User Preferences.
* Click the Display tab.
* Deselect the Show Dashboard at Startup check box.
* Click OK.

Note that you can still reopen the Dashboard at any time from the View menu.

HEAT: HSS Banner

We use our HSS banner for general announcements. Is there a better way to utilize this part of the HSS form?

The banner sits right at the top of the HSS screen and can be used for several purposes to help the service desk deal with call load, call duration and decreasing the cost of the service desk.

* Prevent calls from being made. If the end-user population is trained to check HSS before calling the service desk or if the HSS form is a part of the corporate website, then the banner can display the network status. For example, it could state that "all systems are up and running" Or, when there is a problem, it could state that "The email server will be down under 11:00am". This provides information that could prevent a large surge of calls when a server is down.

* Add the pager number or cell phone number of the emergency on-call technician. Make this part of the banner (as opposed to the Contact Us section of HSS) so it is more visible and easier to change.

* Add notifications of events that will be occurring tomorrow. This will provide advance notice so that changes or maintenance that will be done will not disrupt the end-user's plans and result in a call to the service desk.

Remember that the banner is in a prime location and does not require any additional clicking of links to get to the information, so keep the information important and timely.

HEAT: HEAT Self Service Advertising

We have tracked the cost of resolving end-user problems and requests and found that HSS is the least expensive, but least used, method. Any ideas on increasing the use of HSS?

As you point out, HEAT Self Service (HSS) is the least expensive method to address end-user issues. What this means then, is that you can afford to "advertise" a bit to increase HSS usage. Some ideas are below:

* Use your hold message. When you put together a hold message for end-users calling the service desk, try something like this: "The average wait time is 15 minutes. For non-urgent requests, try HEAT Self Service at www.hss.net". This should direct some users to the HSS site and reduce the call load.

* Send an email blast. When using HSS for internal, end-user support, send an email blast letting users know about HSS for non-urgent calls, to check the status of a ticket, to open tickets after hours and to reach the FAQ section of HSS.

* Use the ticket closed message. It is customary for service desks to send a BPAM email once a ticket has been closed. Add an HSS tag to this email touting the benefits of HSS.

* Increase end-user confidence in HSS. This is a particularly sneaky method of getting end-users to try HSS. Setup HSS to assign a higher priority to all HSS tickets created. End users can be skeptical of automated systems, so when a technician arrives at their desk quicker than usual, they will be converted to HSS users.

These techniques should increase the use of HSS and have end-users saying "I entered a ticket in HEAT Self Service and the technician was at my desk in 5 minutes". Once HSS takes hold, it can dramatically reduce your call times, call load and the cost of servicing end-users.

GoldMine: Schedule a Group Meeting

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: The 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: Using the Knowledge Base

We would like a way to provide additional information or attached documents for new GoldMine users. This information needs to be grouped into logical sections.

GoldMine's Knowledge Base provides books, folders and pages that allow you to organize content and files for any GoldMine user to view. GoldMine users with master rights can add items or modify the Knowledge Base content or sections.

To define GoldMine Knowledge Base items:
* Select Go To > Knowledge Base > Knowledge Base window appears.
* All users can potentially view all Knowledge Base topics
* Users with master rights can add or edit Knowledge Base items.

To create a book:
* Click the New Book icon
* In in the left pane, type a name.
* You can add text in the right pane or attach a file
* You can also add a TOC at the book level instead of text

To add a folder:
* Select a book > click the New Folder icon.
* The new folder appears under the selected book.
* Type in the name of the folder. Click in the right pane
* Add text or click the Attach icon to add an attachment.

To add a topic page:
* Select a book or a folder
* Click the New Topic Page icon > a new page appears
* Type the name of the topic page
* Click in the right page to add text

Use the Book > Folder > Page sections to group information into logical sections. You can also add a Table of Contents to any section to further group the information.

Thursday, March 25, 2010

GoldMine: Combine Groups

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: Copy/Move Has Moved

We have two databases, Prospects and Customers. We previously used File > Synchronize > Copy/Move Records to move from one database to the other. This function no longer exists in GoldMine Premium 8.5 so what can we do?

Things have changed for version 8.0 and above. The function still exists but in a different place. Here is how you do it:

* Open the database from which the contact is to be moved or copied.
* Select Tools > Data Management > Copy/Move Records.
* Click 'Select Target Contact Set' > pick the database to copy to.
* Use Record Selection > Current Record to transfer one contact.
* Use Record Selection > Group of Contact Records to move several.
* Select the Transfer Method (Copy or Move).
* Click on the Go button.

Note: Make sure you have current backups of both databases before you begin this process.

GoldMine: Incomplete Installation

When we run a workstation installation for GoldMine Premium, we receive the following error: "Another GoldMine installation is running". What causes this and how can it be corrected?

If you cancel a workstation install before it is completed, you can get this error. To be able to run the complete workstation install, you need to do the following:

* Select Start > Run > Regedit > Backup the registry
* Select HKEY_LOCAL_MACHINE > SOFTWARE > FrontRange Solutions
* Delete the folder GMinProgress
* Exit Regedit.

You can now retry the workstation installation and should be able to complete the installation without an error. Note: If you can’t find the FrontRange Solutions registry key, search for it by clicking on Edit > Find, in the Registry Editor.

GoldMine: Changing Labels

A client asked: We want to change the tab names in GoldMine 8.5 but modifying the username.ini or gm.ini does not work. Is there some other way?

Yes, you can use the following method:

* Open GoldMine > select a Contact
* Click the arrow left of the tabs
* Click Customize > Check a box next to a label
* Click Rename > enter the new tab label
* Click "Customize tab labels globally" to affect everyone
* Click OK

This method works in GoldMine Premium 8.5 and has the same effect as modifying the username.ini (for user level changes) and the gm.ini (for global changes).

Wednesday, March 24, 2010

Crystal Reports : Using MID() to cut out a substring

I run into this a lot; management needs to sort a report by an employees' last name. Sounds easy until you realize the name itself is stored "normally", that is to say we have a field called NAME
which looks like this:

Corey Babka
Gene Marks
Justin Hill

A possible solution could be to actually store Lastname and Firstname in separate fields, but as reporters, we typically don't have this kind of control over our sources.

So before we can sort on the Lastname, we need to "cut" it out of our NAME field.

Our example field is called {USERS.NAME}.

Now the way we cut strings out of other strings is with the MID() function. The MID function takes three parameters. The string on which to operate, where to start and the length of the string to return.

MID(string, starting character position, length of string to return)

Supposing that {USERS.NAME} is going to return Justin Hill, we can see the result of various MID statements:

MID({USERS.NAME}, 1, 3) = "Jus"
MID({USERS.NAME}, 2, 3) = "ust"
MID({USERS.NAME}, 3, 5) = "stin "

Also note that you may omit the final parameter (length) to return "the rest" of the string, like this:

MID({USERS.NAME}, 1) = "Justin Hill"
MID({USERS.NAME}, 2) = "ustin Hill"

Crystal Reports : Display currency values as millions of dollars

Let us suppose we are summarizing very large dollar values. This is common in the financial advice and investment industry, where "cases" are typically measured in millions of dollars.
It is very typical to show these values simply in millions of dollars. So, for instance, the number 325,000,000 becomes 325. 1,200,000 becomes 1.2. This can help also on a report that is starved for "real estate" (i.e. available blank space on the report).

So, our millions of dollars Running Total Field is called {#CASESAMOUNT}.

It would be a simple feat to create a Formula called {@AmountInMillions} thusly:

{#CASESAMOUNT} / 1000000

Tuesday, March 9, 2010

GoldMine: Bcc in GoldMine Emails

A client asked: How do you add a blind copy (Bcc:) to a GoldMine email?

To add a Bcc: email address to a GoldMine email, you would do the following:

* Click the Email icon > Enter your primary email address
* Under Additional Recipients > click on the Cc: icon
* Select Bcc: as the type > enter the blind copy email address

Fill in the remaining email content or select a template from the Subject line. You can also enter Cc: email addresses in the Additional Recipients area.

GoldMine: Illegal Database

A client asked: When trying to create a new database, we get the error "Failed to create database! Illegal database name or database exists". Tried in both GoldMine and SQL. The database name does not exist so what might be the problem?

There could be a number of reasons. First, check that you do not have an illegal character in the database name (i.e. / ? < > \ : * | ”). Also, some names are reserved under Windows (e.g. com1, lpt1, con, nul, prn). But assuming that the database does not exist and you are not using any illegal characters, then you get the following error in SQL:

SQL error when trying to create new db: SQL Server Error: 5118

This problem occurs because the DefaultData and DefaultLog registry values that correspond to the default data directory and to the default log directory exist, but the folder paths that are mentioned in the registry values are either blank or do not exist. Set the default data directory value and the default log directory value to valid folder paths.

You can set the default data directory value and the default log directory value by using either SQL Server Enterprise Manager or Registry Editor.

Using SQL Server Enterprise Manager

* Click Start, point to Programs, point to Microsoft SQL Server
* Click Enterprise Manager > Right-click the instance of SQL Server
* Click Properties > In SQL Server Properties (Configure)
* Click the Database Settings tab

In the New database default location section, type a valid folder path in the Default data directory box and in the Default log directory box

* Click OK
* Stop your instance of SQL Server, and then restart your instance of SQL Server.

(Note: this also works with SQL Server Management Studio, just go directly to Database Settings from SQL Server Properties).

GoldMine: Search Phone Fields

A client asked: We need to search the Phone fields to quickly locate contacts. How do you search all three Phone fields?

One way to do this is by using a SQL query. Follow the steps below:

* Select Tools > SQL Query > Copy and paste the following:

select Accountno, Company, Phone1, Phone2, Phone3 from contact1 where coalesce(phone1,'') + '|' + coalesce(phone2, '') + '|' + coalesce(phone3, '') like '%(888)224-0649%'

* Enter a Name > Click Save (New Query)
* Click Query > Review results

Note that the format of the phone number is the format that is used by GoldMine when a number is entered. If you use a different phone format, make sure this is used consistently and is part of the SQL Query.

This will locate the phone number entered, however, to make it easier to use, you should develop a macro to enter the phone number (as opposed to opening and modifying the SQL query each time).