Saturday, February 27, 2010

MSCRM: Disable Sections

A client asked: I need to disable all fields with a section. I can do it manually but there must be a faster way?

To disable all fields in a section, do the following:

var section = crmForm.all.new_attribute_c.parentElement.parentElement.parentElement;

for (i = 0; i < section.all.length; i++)
{
section.all[i].Disabled = true;
}

Replace 'new_attribute' with the name of an attribute that resides inside the section you wish to disable.

Note: The _c is not a mistake, that needs to be part of the attribute name. For instance, to hide the section containing accountnumber, you would use accountnumber_c.

Though the use of the JavaScript Document Object Model ( DOM ), we get a list of all of the fields and set their Disabled property to true.

MSCRM: Finding Workflow Errors

A client asked: We are looking for a better way to diagnose errors in newly created workflows. What are the tools or methods that are available?

MSCRM 4.0 workflows can have a 'Waiting' status is they are waiting for a timer or if an error has occurred. To determine if an error has occurred, do the following:

* In the web client, go to the 'System Jobs' in the settings area
* Click Advanced find > Edit Columns
* Click Add Columns > Select columns 'Error Code' and 'Message'
* Click 'OK' twice > Click Find to execute the query
* This will bring up the workflows that have executed.

If the workflow ran into an error the error code and message column will contain details. You can find the meaning of the error codes on http://msdn.microsoft.com/en-us/library/bb930493.aspx.

MSCRM: Telemarketing Trick

A client asked: When our telemarketer converts a lead, a workflow assigns the account to a new owner. Our telemarketer can still see these accounts, however, even though he is not the owner. How can we change that?

This could be due to a system setting. Check the following:

* Select Settings > Administration
* Select System Settings > General tab
* Check 'Set whether reassigned records are shared with original owner'
* Make sure it is set to 'No'

This should stop the telemarketer from seeing the converted accounts after he converts them and the owner is changed.

MSCRM: Retrieve License Key

A client asked: I need to retrieve my license key. Where can I find this in the database?

You can use a SQL query to retrieve you MSCRM 4.0 license key. To find it, do the following:

* Open SQL Server Management Studio
* Select the MSCRM_Config database
* Click New Query
* Enter the SQL query below:

select LicenseKey from ConfigSettings

I have also seen posts that recommend using the company_MSCRM database with the query: select licensekey from License

However, this does not return a key from our database.

Friday, February 26, 2010

MSCRM: IFRAME Performance

A client asked: We have a MSCRM form with several IFRAMES contained in multiple tabs. The form is slow to load - what can we do to improve the performance?

The performance issue is at the client browser due to the frames all loading at once. One way to improve performance is to delay loading an IFRAME until the tab where it is located is clicked.

To do this, you need to create a function that sets the source of the IFRAME and then attach the function to the onclick event of the tab. Below is an IFRAME named ‘IFRAME_myiframe’ and the original url is set to ‘about:blank’. We want the IFRAME to display the Bing search engine when it’s tab is displayed.

IFRAME Load function
Setting an IFRAME source via javascript is a simple piece of code that CRM customizers should be familiar with. We’re going to embed the code within a function:

LoadFrame = function() {
crmForm.all.IFRAME_myiframe.src='http://www.bing.com';
}

This function should be placed in the OnLoad event of the form.

The Tab Element
Next, we need to identify the name of the specific tab we want. The id of any tab on a CRM form is ‘tabXTab’ where ‘X’ represents the zero-based index of the tab. So the first tab is named tab0Tab, the second is named tab1Tab, etc. The tab we want to work with is the second tab, so the element we want is named ‘tab1Tab’.

After you have identified the tab element, attach the load function:

document.getElementById('tab1Tab').onclick = LoadFrame;

Note: Place this code in the form’s OnLoad event, but make sure it comes after the load function defined above.

That's it. When you click on tab, the related IFRAME will load and the performance should be improved.

GoldMine: Populate Dear Field

A client asked: How do I automatically update the Dear field when a name is entered into the Contact field?

The lookup.ini script below will populate the Dear field when a name (with two words) is entered in the contact field.

Enter the following in your lookup.ini:

[AutoUpdate]
Contact=Dear

[Dear]
Lookup1=Contact
Otherwise=&trim(substr(contact1->contact, 1, at(" ", contact1->contact)))
Overwrite=1

* Open the lookup.ini file using Notepad
* Add the lines above to the file including the Autoupdate section.
* Save the lookup.ini file
* Restart GoldMine.

When you enter a contact, the Dear field should now contain the person's first name.

GoldMine: Email and Email Merge Codes

A client asked: Is there a way to display the contact with their email and email merge codes?

The following SQL Query will display the Primary Contact with their mailing address, email address and the associated email merge code and mail merge code.

Select c1.Accountno, c1.Company, c1.City, c1.Address1, c1.State, c1.Zip, cs.ADDRESS2as Primary_Contact, cs.CONTSUPREF as Email_Address, cs.Mergecodes as Email_Merge, c1.Mergecodes as Mail_Merge, cs.zip
From contsupp as cs, contact1 as c1
Where (cs.rectype = ‘P’ and cs.contact = ‘E-mail Address’) and c1.key1 = ‘CUSTOMER’ and substring(cs.zip,2,1)=’1′ and c1.accountno=cs.accountno

Enter this in the SQL Query pane to get the information you want.

GoldMine: Change Template Name

Did you know that you can make an Email Template name something other than the subject line?

When an creating email template, the subject line of the template will normally be the name of the template in the Document Management Center. But this does not have to be the case.

When you design your template make the subject line the default as you wish it to appear on the outgoing email, then save the template. The template will now appear in your Document Management Center with the name of the subject line as the file name.

To rename the template, click on the template once, press F2 and then click on the template name again. It will now allow you to rename the template to what ever you want.

GoldMine: Suppressing Blank Merge Fields

A client asked: Is it possible to suppress merge fields that do not contain any data?

Yes, but it is detailed so read the following instructions carefully:

* In your Word template, put the cursor at the position you want the field.
* Press CTRL + F9.
* This puts a blank field code in the documents.
* Your cursor is now between the braces.
* Type the word IF then a space then the " symbol.
* Press CTRL + F9 to insert another field inside the first one.

Now type the Goldmine codes as follows:

DDEAUTO GOLDMINE DATA CONTACT2->UFIELD \* CHARFORMAT where UFIELD is your Goldmine field.

(This will vary depending on the table and field).

* Move the cursor past the first } symbol.
* Type another " symbol.
* Type the two symbols < and > followed by a space.
* Type another " symbol.
* Press CTRL + F9 to insert another field.
* Again type DDEAUTO GOLDMINE DATA CONTACT2->UFIELD \* CHARFORMAT.
* Between the last to } symbols type " a space then two " symbols.

You should end up with a series of codes that looks like this:

{IF "{DDEAUTO GOLDMINE DATA CONTACT2->UFIELD \* CHARFORMAT}" <> "0" "{DDEAUTO GOLDMINE DATA CONTACT2->UFIELD \* CHARFORMAT}" ""}

This field is saying if the contents of UFIELD is less than or greater than 0 put the contents of UFIELD into this position otherwise put nothing in (this is the text between the last two "" symbols).

If the value is -1 it gets printed, if it’s 1 it gets printed, if it’s zero the field is not put in. Highlight what you typed, right mouse click and select Toggle Field Codes to view the result.

Note: while working on this use DDE instead of DDEAUTO. That way you can change the value in GoldMine, highlight the field in Word and press F9 to update it without having to close and reopen the document. Once you’re happy with the result, change DDE to DDEAUTO.

GoldMine: Copy Contacts to Another Database

A client asked: We have separate databases for prospects and customers. When a prospect becomes a customer, how can we move them to the Customer database?

Follow the steps below:

* Ensure you have current backups of both databases.

* In GoldMine, open the database that records will be copied or moved from.

* For older GoldMine versions, Select File > Synchronize > Copy/Move Records
* For GoldMine 8.x, Select Tools > Data Management > Copy/Move Records

* Select the target database that the records will be copied or moved to from the Select Target Contact Set drop down.

* Specify the Record Selection by selecting Current Record (to move the contact record you are on) or Group of Contact Records. This option will allow selection of a filter or contact group.

* Select the Transfer Method. Copy will move a copy of the selected records to the target contact set and leave the original in the source contact set. Move will remove the original from the source contact set and place it in the target contact set.

* Click the Go button. Note: There is no undo for this operation. It is very important to have a current backup before begining this procedure.

GoldMine: Query to Filter

A client asked: I use the following SQL query to locate accounts created in the last 90 days - select * from Contact1 where (CreateOn > { fn NOW() } - 90) order by CreateOn - is there a way I can create an activated filter from this?

To create an object (group, filter) from a SQL query, you can do the following:

You can build a Group from the results of your SQL query.

* Run the Query > at the top of the screen click on Groups.
* Click in the area of Group Name, click on New.
* Give the Group a name, click Next
* Selcte the option to build a group based on the SQL Query.
* After it is built, click on Activate.

You could also use this filter for similar results:
Contact1->CreateOn >Date() - 90

The filter can also be activated and allow you to work with this collection of contacts.

GoldMine: Managing Linked Documents

A client asked: What is the best way to manage linked documents and attachments?

There a number of GoldMine features and SQL queries you can employ to clean up your Linked Documents (or prevent them from getting messed up in the first place).

First, to avoid creating a large number of duplicate linked documents, you can select the overwrite option instead of the rename option in the Email Options Advanced Tab. If an attachment is the exact same name, it will overwrite it. The concern with this is if the attachment is logo.jpg or some common file name, you can end up with unpredictable results.

Besides GoldMine settings, you can use Windows and SQL functions to clean up your linked documents/attachments after the fact. One thing to identify is what you really want to clean up - just the links or the actual documents. To clean up the links, you can use SQL queries. To delete the actual attached files, you can use Windows.

Each step needs to be done carefully so that you do not delete needed links or files. In SQL, you can use the following queries to select and delete links to the documents/attachments.

Be careful, the code below deletes all links for all contacts. To limit that, you need to add more WHERE clauses to the Delete query.

SELECT [ACCOUNTNO]
,[RECTYPE]
,[CONTACT]
,[TITLE]
FROM [db_name].[dbo].[CONTSUPP]
WHERE [db_name].[dbo].[CONTSUPP].[RECTYPE]='L' order by [TITLE] ASC

Delete FROM [db_name].[dbo].[CONTSUPP]
WHERE [db_name].[dbo].[CONTSUPP].[RECTYPE]='L' AND [db_name].[dbo].[CONTSUPP].[TITLE]='GIF Image'

Once this is complete, go to the directory where GoldMine stores your attachments and delete the actual physical files. You may want to do this only for image and vCard files that you don't need. You may want to copy files to a separate folder before deleting.

As always, make sure the database and the folders are backed up before proceeding.

GoldMine: To Archive or Not Archive

A client asked: Is it necessary to archive contact data on a periodic basis or should I leave everything in one database? If I archive, how is it done?

The question of archiving GoldMine data has been around for some time. In the older versions of GoldMine with BDE that were be approaching the 2GB limit on the Email table, archiving of email was common.

In more current versions (without BDE), it is more common to recommend that you keep all data in a single database that shows information regarding a contact whether that data is from last month or five years ago. That's the point of a CRM program, to have everything available about a contact. You can then use Universal Search to mine the data for what you need.

Should you decide to archive your data on a yearly basis, you would do the following:

Create a totally new, and separate database each year. At the end of each year, export the entire GoldMine database to the new GoldMine database. Setup an Alias for that database in GoldMine. Once you are assured that everything is over there, you can delete all records in your Active database, and begin the year with a clean database. If you don't want to recreate all the Contact1/Contact2 information, you could export those from the Archived database into the Active database. (Both exports would been done using the SQL Management Tools). Of course, make sure you backup GoldMine before doing anything.

In general, however, the consensus is to not archive data from a GoldMine database. This older data could prove very valuable during calls or meetings with customers and you want it at your fingertips. So, unless you have a compelling reason to archive, leave the data where it is.

GoldMine: Cleaning Up Email Templates

A client asked: When I am composing an email, I click on the Subject drop-down and see email templates related to HEAT that are not in the Document Management Center. How do I remove these from the Subject drop-down list?

To locate these and delete them, you can use the following queries:

The Subject drop-down draws directly from the mailbox table where folder='X-GM-TEMPLATES', so to locate them:
select * from mailbox where folder like 'X-GM-Templates' and mailref like '%Heat%'

To delete them, you would run the following:
Delete from mailbox where folder like 'X-GM-Templates' and mailref like '%Heat%'

As with any change, you should backup your GoldMine database first.

GoldMine: Organize Your Taskbar

Does your Taskbar make sense or is it links to various items in no particular order?

One way to improve your Taskbar is by organizing links into logical groups (e.g. Marketing, Sales, Operations) instead of links to individual documents.

Here's how to do it:

* Right click on the Taskbar and select Customize Taskbar.
* Right click and add the Groups (Marketing, Sales, Operations)
* Add an item to Group and select Website.
* In the URL, add a file location (file:/// followed by the path).

Now the Taskbar will be organized into folders of documents instead of individual document links

HEAT: Speed Up Edit Sets

Do you need to speed up your edit sets? Check out the tips below.

Edit sets are interesting. If you have a 1GB HEAT system and perform several changes and then commit, the edit set could take several hours to complete. To improve this, you can make a few changes when committing the edit set and find the process to be a lot shorter.

The following are some best practices for edit sets:

Commit Locally: Try and commit the edit set on the same server the database is on as it keeps the data local. If your policy does not allow you to run the Admin tool on the SQL server, then keep the edit sets small. One table at a time, on a machine that shares the same network switch to limit the the possibility of a network failure during commit.

Consider Your Changes: The changes you make directly affect the length of time of the commit. For example, if you add a field to the CallLog table, which has 750K records, those 800K records will have to unload, then load back into the table after the commit adds the new field. If you make several changes to the CallLog table, it will only unload and load those records once. If you have many changes to make in one table, do it all at once, instead of making multiple commits. The same rules apply to all other tables.

Know How Commits Work: Edit sets commit the tables alphabetically. HEAT's main tables are Assgnmnt, CallLog, Config, Detail, Journal, Profile, and Subset. You will know that your edit set is almost done when you see the Subset loading back in.

What Type of Change is Appropriate: Adding, renaming, changing field types, and removing fields and tables require an edit set. Marking a field read-only or required, setting a field to validate or autofill, along with normal form changes only require a quick customize. A quick customize with 750K records only takes a few seconds.

Always back up your HEAT system before making any changes.

Thursday, February 25, 2010

HEAT: Reset CallID Counter

A client asked: How do I reset the Call ID counter to start at a specific number and increment from that point?

This is fairly easy to do but you must go outside the HEAT modules and use the SQL Server Management Studio. Because of this, make sure you have a current SQL backup prior to making any changes.

* Open the SQL Management Studio
* Select your HEAT database
* Select and open the HEATSeq table
* Locate the CallID field
* Change the current value to your value
* Exit the SQL Management Studio

Following this, your CallID will start from the number you entered.

HEAT: Time Tracking Methods

A client asked: Is there a way, within HEAT, to track time spent on tickets and make as many time entries per ticket as needed.

A good place to start would be the Journal tab. This has a one-to-many relationship to the Call ID and has no limitation in terms on entries. Using the Journal tab and some custom fields, you can now 1) record your time in a Journal entry (together with a description of the work performed and; 2) devise a method to roll up the time in all Journal entries for each ticket.

The rolled up time could be displayed on the ticket form or in an external report (e.g. Crystal Reports). This decision should be based on how the time spent total will be used. Ultimately, however, a time total recorded on the ticket form could also be reported on (for all tickets) in a Crystal Report. If, however, only a Crystal Report is used to record the total time by ticket, then when a need for the ticket to contain the time total arises (e.g. use by the Managers Console) the process would need to be revised or reworked.

So, it appears that totaling the time entered in the Journal entries on a HEAT form would be the best approach (with reporting methods created after the form changes are made). Below are the steps to create a total time spent field that pulls from all Journal entries for a ticket.

First, depending on your version and setup of HEAT, you may already have a custom field on the Journal tab. It is called TotalJournalTime and is a Number field type. If you do not have this field, create an edit set and add the field to the Journal table and form.

Next, add a field to your form (e.g. Call Log) to hold the total time for the Journal. Name this TotalJournalTime and then create an autotask to total the entries in the Journals for that ticket. The autotask should use the Update Call action and run the Sum Journals function. Specifically, insert this function in the CallLog TotalJournalTime field - {|@Functions.@SumJournals|(TotalJournalTime)}

The autotask can be connected to a command button or to a business rule to execute the calculation. Which method is used would depend on the need for the calculation and if it is later included in a Crystal Report. In this case, the Business Rule method may be better to ensure that the calculation is performed on a regular basis.

HEAT: Jump To Call in WebUI

A client asked: How do I jump to a specific call record when using the HEAT WebUI?

When you are using Call Logging, pressing Ctrl G will allow you to enter a Call ID and jump to that call. In the WebUI, you have other options than Ctrl G.

First, to jump to another call when using the HEAT WebUI, press Ctrl G. A small pop up box appears and allows you to enter a Call ID. This takes you to the Call ID entered.

Another option is to click the starred tab above the Call Logging toolbar. This opens a dialog box where you can enter a Call ID or a Customer ID. You can select the Customer Type, enter the Customer ID and be taken to the Customer Profile.

This dialog box also displays recent call groups, which can be opened by clicking on the dialog box link.

HEAT: Disabled Business Rules

A client asked: Why do my BPAM rules disable themselves?

First, you must follow a set routine when commmitting changes in HEAT. This is the reason why BPAM rules can become disabled. When you commit a change, the HFW file must be rebuilt before the service runs. If not, then the BPAM service stops and disables the rules as a precaution.

If you follow the steps below when committing changes, then your BPAM rules should never auto-disable themselves.

* Save the Edit Set
* Stop all services/modules (BPAM, ATG, HMC, HSS, WebUI)
* Lock all users out of the system
(Security > User Status > Select All > Lock)
* Create a HEAT Archive File (haf) file
(Database > Export)
* Re-open the Edit Set
* Pre-commit the Edit Set Verify
* Commit the Edit Set (create a second set of back ups)
* Log into Call Logging and check all changes
* Enter any necessary validation data
* Unlock users
* Restart all services an modules

If the above procedures are followed, then your rules should never auto-disable. However, if something happens, by keeping your rule list clean can make all the difference. Remove rules you don't use and keep all enabled rules at the top of the list. This way, if your rules become disabled, restarting will be simple and straightforward.

Wednesday, February 24, 2010

QuickBooks: Customize Customer Center

It is fairly easy to customize the columns in the QuickBooks Customer Center.

When reviewing information within the Customer Center (or in the the vendor or employee centers), you can right-click when hovering over the details of a customer account to see an option that says 'customize columns'.

Once you click on the "customize columns" option, a pop-up window appears. The left hand side of the window displays all the columns that are available for you to add and the right hand side of the window displays all the columns that are currently displayed.

For example, if I wanted to add the memo column so that it displays every time, I would click the word 'memo' on the left hand side and then click the 'add' button in the middle.

I could also click the 'move up' or 'move down' options in the middle of the screen to move the memo column closer to the top (left) or or bottom (right) side of the screen.

This tip can save a client countless hours of searching customer invoice history if the information they are searching for is in the internal transaction notes.

You can click the title of any of the columns (type, num, memo, etc.) and the information displayed will then sort in whatever order you have chosen. This makes it easy to track down information quickly when talking with a customer.

QuickBooks: More Report Options

Somewhat hidden in the upper corner of the Report Center screen is a button called 'Find More Reports'. This can be easily overlooked but it is very powerful option.

Clicking the 'Find More Reports' button takes you to the QuickBooks Reports Library. Here you will find a collection of new reports that have been created by QuickBooks users and QuickBooks ProAdvisors.

Within the library, you can search by the same categories used in QuickBooks, such as 'company and financial', 'customers and receivables', etc. You also have the ability to sort by the most recent, the most downloaded, and the highest rated reports using the 'sort by' button in the upper right hand corner of the screen.

How to download reports:

Once you find a report, create a folder on your computer to store the reports, click the download button and save the report in your new folder. Go to your reports folder, double-click on the report, and it will be added to your memorized reports menu.

Some reports you may find of interest:

* Payments by Vendor
* Overhead as % of Total Income and Expenses
* Unapplied Customer Payments and Credits
* Sales by State
* Cash Flow Forecast by Quarter

There are many more reports than listed above. Make sure to visit the report library and find some reports of use to your business.

QuickBooks: New Custom Fields

QuickBooks Enterprise has more custom fields with new functionality that can benefit businesses in terms of data accuracy.

The new custom fields appear in several places:

* Item list
* Customer list
* Vendor list
* Employee list

The Item List: open an item in QuickBooks Enterprise, click the 'Custom Fields' button and then click the 'Define Fields' button, and you will see a window that allows you to define additional information about information required, types of transactions required and define types of data.

Improvements to Item List custom fields include:

* Use up to 15 custom fields instead of 5
* Predetermine the type of data used in the custom field.
* You can also choose from a variety of number options
(phone number, date or create your own custom number)
* Require that the field be filled in on a transaction
* Require that the field be filled when the item is added to the list

You also get more choices of custom fields when it comes to customers, vendors, and employees. For example, when reviewing the details of a customer account, click the 'Additional Info' tab within the customer account, then click the 'Define Fields' button and a window appears that allows you to define a number of custom field attributes.

Improvements in these custom fields include:

* A total of 20 custom fields to be shared across lists.
* The same choices on the type of data as shown in the item list
* Make entry of data into these fields mandatory on a transaction
* Make entry of data mandatory when setting up a customer, vendor or employee

The new custom fields in QuickBooks Enterprise makes capturing important information easier and more reliable.

QuickBooks: Viewing Time Reports

If you are using QuickBooks to track time, you most likely are familiar with the reports available when you click Reports > Jobs, Time and Mileage.

These report include:

* Time by Job Summary
* Time by Job Detail
* Time by Name
* Time by Item

But there are many other ways to display the data on these reports. Click the "Modify Report" button the upper left hand corner of these jobs/time and mileage reports. You then have another option (under the Columns section) that says 'Display time grouped by' with several options. These options change based on the Time report you are viewing.

For example, pull up the Time by Name report and these additional grouping options appeared.

* Time by Name by Job
* Time by Name by Item
* Time by Name by Job by Item
* Time by Name by Item by Job

If you open the Time by Item report, you would see similar options, but the choices would begin with 'Time by Item'. This one feature greatly increases the usefulness of the Time reports.

QuickBooks: Modify Report Fonts

How do I change the font size and color in the QuickBooks reports?

You don't need to accept the default font settings in the QuickBooks reports. In fact, you have a great deal of control over the size, type and color of the information that appears in the various QuickBooks reports.

To change the font settings on a report, run the report of your choice, then click the 'Modify Report' button in the upper left hand corner, then click the 'Fonts and Numbers' tab.

Select the area of the report you want to change, click the 'Change Font' button, make your selection, and continue to click OK until you exit the window. Your report will then reflect your updated font preferences. Be sure to click the 'Memorize Report' button when finished so QuickBooks knows you want your own copy of the report saved with these customized settings.

You can also set the default font settings for all QuickBooks reports by following the steps below:

* In the menu at the top, click Edit, then Preferences
* Click Reports and Graphs, then the Company Preferences tab
* Click the Format button, then the Fonts and Numbers tab
* Make your changes and click OK until you exit Preferences.

To change back to the original font settings, repeat the steps above, and click the Revert button instead of the format button. Click OK until you have left the Preferences window. Your reports will return to their original fonts.

ACT: Create Custom Reports

ACT! provides many preformatted reports and these reports are also customizable. You may come to a point, however, where you need to create a report from scratch.

To create a new custom report, you will use the Report Designer and the New Report Template. The details are below.

* Click Reports > New Template > Select a Report Type
* Select from the basic templates, including Empty Contact Report
* The Report Designer screen is separated into several sections
* Sections are report and page header/footer and a detail section
* To add objects to the report, click the Field tool
* Draw a field where you want to place it, select a type
* Add fields to the Detail section to show Contact information
* Add Header and Footer objects as desired

Click Print Review to review your report and adjust spacing, sorting and formatting as needed. Save the report and provide a unique name. To run your custom report, click Reports, Other Reports and select from the list of reports.

ACT: Query Based on Changes to Data

I want to know what contacts have had no activity since the beginning of the year. Is there an easy way to search the contact history for changes (or lack of changes?)

ACT! keeps track of every change you make to a Contact record. This includes field changes, scheduled activities, opportunities or even changes to a note.
Because of this tracking, you can lookup any contact whose opportunities have changed in the last 30 days or whose record has not been touched since 2009.

* Select Lookup > Contact Activity
* Select Changed or Not Changed
* Select the Since Date to specify the date range
* Select the areas of the contact to search
* Click OK

Depending on your selections, the search checks for changes to contact records, changes to notes, changes to opportunities, new or changed history items and uncleared activities. These options can be combined in any way you choose.

ACT: Customize Activity Types

The Activity Types are very limited. Can I customize them?

Yes, but do not add Activity Types lightly. Once created, an Activity Type cannot be deleted, only edit or deactivated. You also must have Administrator or Manager roles to create a custom Activity Type.

To create a new Activity Type:

* Select Schedule > Manage > Activity Types > click Add
* Enter the Activity Type Name (e.g. Billable) > Select an Icon
* Click OK

To use a custom Activity Type:

* Open the Calendar > Right click, Schedule > Select Other
* The custom Activity Type will appear as part of 'Other'
* Select your custom Activity Type > complete the entry

Once you have created a custom Activity Type, you might want to open previously defined tasks and redefine them with the new type. If in the future, you no longer want to use the Activity Type, you can inactivate it by selecting Schedule > Manage > Activity Types > Delete.

ACT! will pop up a prompt stating that you cannot delete the Activity Type but that you can inactivate it. Once inactivated, it will no longer appear as an option when scheduling, although tasks already containing the custom Activity Type will retain it.

ACT: Rolling Over Calendar Events

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

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

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

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

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

ACT: Share Notes

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

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

To use the Notes functions:

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

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

Tuesday, February 23, 2010

Crystal Reports : Using Formulas within Formulas to streamline code

You will undoubtedly have noticed that there is no real right way to do anything in Crystal.
A Running Total could be substituted by a Summary on a Formula, and so forth.
Where technique really matters is in duplication of labor. Effective use of Formulas is one way to cut down on this:

Let us consider a calculation on an Invoice report;

{INVOICES.QUANTITY} * {INVOICES.NETPRICE}

But just to make it tricky, we're making this part of a formula, because we only want our calculation to happen for a certain year;

IF YEAR({INVOICES.ONDATE}) = 2009 THEN {INVOICES.QUANTITY} * {INVOICES.NETPRICE}

Now to make it worse, you've got dozens of these calculation fields, all specifying different criteria (i.e. Year, Month, etc). But that's fine, you create the fields normally and everything works.

Suddenly management decides they need to increase their prices by 5%, and this must be reflected in the Invoice report.

It is easy enough to change our formula code (you'll notice our Quantity and Netprice is now encapsulated in parenthesis)

IF YEAR({INVOICES.ONDATE}) = 2009 THEN
({INVOICES.QUANTITY} * {INVOICES.NETPRICE}) *.05 + ({INVOICES.QUANTITY} * {INVOICES.NETPRICE})

And this works too, but now you have a full days work ahead of you adding that .05 to each and every calculation field.

A better way:

Create a formula called {@frmExtPrice}:

({INVOICES.QUANTITY} * {INVOICES.NETPRICE}) *.05 +
({INVOICES.QUANTITY} * {INVOICES.NETPRICE})

Then change the calculation formula thusly:

IF YEAR({INVOICES.ONDATE}) = 2009 THEN
{@frmExtPrice}

This way, any changes that need to be made to the way the Invoice calculates line items can be modified in frmExtPrice once and all the calculation fields automatically "have it".

It is easy to code all your logic directly into one big formula, but keep the bigger picture in mind.

While you're at it, why not make your .05 into a parameter?

Crystal Reports : Dealing with Zero and NULL Values (Part 2)

Depending on your usage, the returning of NULL (from a formula or running total) can be more stubborn at times.

First, check what NULL conversion options the report is using:

1. Select from the top level menu File | Report Options.
2. Look for the "Convert NULL field value to default" checkbox.

If this box is checked, NULL is returned as a special "automagical" DEFAULT value.
This typically will convert NULL to zero in formulas and running totals.

Folks who are using newer versions of Crystal will notice an additional checkbox in Report Options called "Convert Other NULL Values to Default".

This checkbox refers to the NULLs being returned by objects other than database rows (i.e. formulas and running totals). Typically a combination of both of these checkboxes is required to properly fix a zero totaling problem. (Depending on back-end database and usage)

As always, try it out.

Crystal Reports : Dealing with Zero and NULL Values (Part 1)

It is inevitable; one day you will create a Crystal formula or running total and it will return NULL.
However, management typically likes to see zeroes on reports, especially where user activity is concerned. A NULL value will print as nothing (i.e. blank).

You must edit the Number formatting of the field in question.

1. Right-click your field.
2. Select "Format Field".
3. Click the "Customize" button.
4. Look for the "Show Zero Values As..." drop-down on the lower right-hand corner.
5. Select "0" (zero) from the list.