Monday, October 31, 2011

HEAT – What is an Edit Set

An Edit Set is a working copy of your database. More specifically, it is a working copy of your table, field, form, and grid definitions. This working copy acts as a snapshot of your system that a system administrator can work on without affecting a live system. This allows you to save and work on your Edit Sets refining them over a long period of time.

Edit Sets utilize their own interface. This interface provides a user-friendly format and several wizards/editors to help you create and modify your system to meet the specific needs of your organization.

Use an Edit Set to:

• Create, edit, and delete tables.

• Create connections to External tables.

• Create, edit, and delete Table Types.

• Create, edit, and delete Views.

• Create, edit, and delete fields

• Define field attributes (size, format, flags, Validation features, etc.).

• Create and design forms.

• Create, edit, and delete grids.

HEAT - connect to external database

External tables, are tables that exist outside the HEAT database and can be used for the purpose of retrieving information. Example: Your Asset Management system may reside in a database other than HEAT. Your Heat Administrator can configure Call Logging to validate detail screens based on information in your asset management system. You may also have a database containing customer information which you need to validate from.

HEAT External tables are intended to help reduce duplication of data across dissimilar back-end systems and allow access to company-wide information from within the HEAT Call Logging module.

Note: External tables are very similar to HEAT tables with two notable exceptions: Fields cannot be added or removed Data cannot be changed from within HEAT – in other words, you can only view the data which resides in the external database.

The Call Logging module can read data directly from any supported data source. The external data is acquired through a real-time ODBC connection, so it is always current and never needs to be synchronized.

External Connections

External connections are HEAT representations of an ODBC data source. External connections are created using the Connection Manager in the HEAT Administrator module. When defining the external connection, the system administrator can determine how users log on to the data source and how the data source is used by the Call Logging module.

HEAT - simple search defaults

Simple Search defaults let you create default lists of fields to Simple Search in Call Logging (and also in Web-based Call Logging). The search returns a group (Call Group or Customer Group) of records that meet your criteria. Creating default lists reduces the time and effort spent setting search criteria. For example, if you commonly Simple Search for Call Records logged on the same day, you could add the RecvdDate field to the list to instantly search for Call Records by date. The default lists of fields are presented in the Simple Search submenus on Call Records and Customer Records.

You can designate up to 25 fields to include in each Simple Search default list.

To Set Simple Search Defaults:

  1. In the Administrator Dashboard, click the Simple Searches link in the Set System Defaults section (or, in the Administrator main window, select Defaults>Simple Searches from the menu bar). The Simple Searches dialog box opens.
  2. Click either the Call Ticket tab (to set Simple Search fields for Call Records) or the Customer tab (to set Simple Search fields for Customer Records).
  3. In the Table Name drop-down list, select the table containing the field you want to include in the Simple Search default list. If you select a Compound table (Subset or Detail), you must also select a Table Type (otherwise a default Table Type of System is selected). 
  4. In the Field List, select the field(s) you want to appear in the Simple Search default list, then click Add. The added fields include the table name as a prefix to the field name. Fields are listed this way in the Simple Search submenu.
  5. Click OK

HEAT – Understanding HEAT Compound Table

A Compound table in HEAT, is a special System table that exists as one table in the database, but can function as multiple tables in the user interface. In other words, one physical table exists, but multiple sub-tables appear in an Edit Set and in Call Logging. Each contains its own unique combination of fields (which are acquired from the one physical Compound table), displays its own form, and uses its own security. The sub-tables, called Table Types, control what table information (fields) appears in each sub-table. Customer Types, Configuration Types and Call Types are all examples of Table Types.

For example, the Profile table is subcategorized by Customer Types. These sub-tables acquire their information from the Profile table, but they function as individual tables displaying their own combination of fields, possessing their own forms, and using their own security.

HEAT contains four Compound tables:

  • Profile - The Profile table stores information specific to a customer. It can be subcategorized by a Customer Type. Each Type appears as a separate Profile sub-table and is displayed as a separate Profile form, but the information all exists in the main Profile table. The HEAT Demo data contains several predefined Customer Types, which can be modified, and you can create your own Customer Types.
  • Subset - The Subset table stores key information specific to a call about the customer. Since the Subset table normally gets its information from the Profile table, Customer Types defined for the Profile table also control the Subset table.
  • Config - The Config table is part of the Customer Record, and contains information about other elements of the customer environment, such as computer hardware and software, training, HR Benefits, storeroom equipment, inventory, IT settings, or registrations. It can be subcategorized by a Configuration Type. The HEAT Demo data contains several predefined Configuration Types, which can be modified, and you can create your own Configuration Types.
  • Detail - The Detail table stores specific, detailed information about a call. It can be subcategorized according to a Call Type. The HEAT Demo data contains several predefined Call Types, which can be modified, and you can create your own Call Types.

Monday, October 24, 2011

QuickBooks: The Business Owner Pays for Expenses

Many times an owner pays for small expenses from their pocket while out shopping and it is likely to happen as we get nearer the holidays. So, how you handle it depends on the situation.

1) If the owner is being paid back immediately or the near future

• Write a check and use the expense tab to list all the appropriate expense accounts and their portion of the total amount.
• You can also enter into the petty cash bank register, building that debt until such time as you write a check to clear the owner’s amount and put funds back into the petty cash box. Otherwise create a bank account called owners

2) If there is no intent on being paid back

• Create a journal entry to the owner’s equity account and in the account block enter owner-name equity investment – and offset it with the expenses.

QuickBooks: Reports for ALL payments due within a date range

I recently received the following question: “Is there a simple way for me to create a report that will show all the payments due for a given date or date range for a specific days need to pay them? For example, if we have rent due, or weekly payroll, credit cards, or regular bills”

Not having seen this person’s data file, the best I could provide is a generic response – Yes - but there are assumptions.

Assumption 1: All vendor bills are entered into the accounts payable system. --- Reports -> Vendors and Payables -> A/P Aging detail.

Assumption2: Bills or Checks are memorized. -- Lists -> Memorized Transactions

QuickBooks: Turning Off the Online Payment Link in QuickBooks Invoices

While working with a client recently, I noticed the link on the invoice for the Intuit PaymentNetwork. When I asked if she subscribed to the service, she said no, but did not know how to take it off the invoice. It just “sort of showed up”.

Unfortunately, QuickBooks has a tendency to try to force this on the users, and it has been a source of complaint for many. If a client tries to click the link to make payment, and you are not subscribed to the service, they get message reading “Your vendor is not signed up to receive payments." How embarrassing is that!

How to turn it off? The simplest thing to do is to just un-check the show online payment link box in the lower left of your invoice. However, that isn’t a permanent fix. Every new invoice will have that box checked. What you need to do is to change your preference settings.

Select Edit and then Preferences. Select the Payments preference and click on the Company Preferences tab. There are two boxes to make sure that you un-check in the bottom of the screen.

New invoices will not have the links, usually. In some cases, though, the first invoice you create after making this change may still have the box checked. Watch that – uncheck it and future invoices should be OK.

Note that all existing invoices that you have will not be changed – if they were checked before, they will still be checked. So you should keep an eye on this.

QuickBooks: File Size & DB File Fragments Matter

The size of your QuickBooks file and the number of DB File Fragments determines the health of your QuickBooks company data file. Most people don’t periodically check the health of their QuickBooks file to help prevent data corruption – mainly because they don’t realize that they should or even how to go about it. However, it is important to periodically check this information just to see how healthy your file is or isn’t.

To check the size of your own QuickBooks file – with QuickBooks open press the CTRL and the 1 keys at the same time OR press the F2 key at the top of your keyboard – this will open a Product Information window.

There are three important things to review:
1) File Size for QuickBooks Pro or Premier, files around 150 MB may start to experience some problems. If your file hovers around 250 MB, it may be time to consider starting a new data file. Note that QuickBooks indicates the file size in “K” while the recommendations are in “MB”. The general conversion is to move the decimal to the left three places. So, file size 180932 K is roughly 180 MB - a fairly large file; this is confirmed by the fact that there are 17583 total transactions in the file

2) DB File Fragments – Less than 10 is recommended, but anything under 20 DB file fragments means that your file is pretty healthy; while anything over 20 means your file is sick!

3) List Limits – In QuickBooks Pro and Premier, you can only have 10,000 accounts, 14,500 total names or 14,500 items. If your company needs more list capacity, then an upgrade to QuickBooks Enterprise Solutions may be needed. Otherwise, creating a new file provides an opportunity to clean up the lists and delete things.

There are some things that you can do to try and repair you QuickBooks file yourself:
• Rebuild you company data file and check the Product Information Window after to see if the number of DB file fragments has been reduced
• Create and restore a Portable company file and check your results
• Seek the help of a professional to repair your data file or create a new one.
Keeping your QuickBooks data file healthy means that it will perform better!

QuickBooks: Billed Costs Disappear if you Void, Delete, or Change an Invoice

If you link costs or time to a customer or job, it's very easy to include those amounts when you create an invoice by selecting the add time/costs button, or directly selecting invoice by time and expenses.

However, once the costs have been linked to a saved invoice they're marked as "Billed". Changing the invoice in any way that eliminates any of the billed costs does not return the costs to a "billable" status.

If you void or delete an invoice that contains billed costs, QuickBooks displays a message. Many users misinterpret this message …thinking it means that QuickBooks will take the logical step of removing the Billed status from expenses that were on the invoice and return them to the Billable status that lets you add them to a future invoice.

Here's how to make those expense costs and time charges billable again:

For expenses/costs you linked to a customer or job, choose Reports | Jobs, Time & Mileage | Unbilled Costs by Job. The window that opens doesn't show the costs you want to recover – it may have no listings at all.

For Time charges, choose Reports | Jobs, Time & Mileage | Time by Job Summary. The window that opens displays time charges that are recorded, but doesn't differentiate between billed and unbilled charges.

1. Select the Date range that covers the dates for which costs/time were marked Billable (or select All as the Date range).
2. Choose Modify Report.
3. Move to the Filters tab.
3. Choose the Billing Status filter and change it to Billed.

When you click OK the report window displays all the Billed costs or time charges for the date range you selected. Now you can drill down into each applicable transaction and change the billing status by clicking the Billable field so it is marked with a checkmark.

The costs and/or time charges will be available when you create the next invoice.

Crystal Reports : Displaying Hours and Minutes

Let us suppose we would like to summarize the amount of minutes in a phone call report, but express the grand total in hours and minutes. This is more easily accomplished using the "Display String" of the field in question. This allows us to create the summary field as normal.

1. Create a summary field to SUM your {CALLS.MINUTES} field
2. Right click on the summary field itself
3. Click on the "X-2"button to the right of "Display String"
4. Paste in the following formula :

local numbervar Hr := Truncate(CurrentFieldValue/60);

local numbervar Mn := Remainder(CurrentFieldValue,60);

Totext(Hr,0) + "Hours " + ToText(Mn,0) + "Minutes"

Pro Tip : The CurrentFieldValue function returns the current field value of the field being formatted. This allows easy code reuse.

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:


Crystal Reports : Viewing the Report Selection Criteria

When the Selection Criteria becomes very complex for any given report, I often find it helpful to display the whole Selection Criteria right on the report.
To do this, you need to insert a "Special Field" from the Field Explorer.
Select Insert | Special Field.
Drop down the Special Fields list.
The field we want to insert is Record Selection Formula.
You will also notice that the Group Selection Formula is also available here.

Crystal Reports : Counting Unique Records

When creating a "Summary", using the Count function may not be desirable. It is often the case that a report must only return the number of unique contact records, as other tables (i.e. History) may contain multiple rows for each customer.
Select Insert | Summary.
Select the fieldname you wish to summarize.
Make sure to select Distinct Count as the Summary Operation.

Crystal Reports : Calculating Age

One of the many things you will want to do with a bona-fide date field is calculate age.
Crystal makes it easy for us here; as long as we're comparing two date type fields, we can perform simple math.
Let us suppose that the date field in question is {CONTACTS.SOURCEDATE}, which represents when a particular prospect was imported into our database.

1. We can see how many records were imported in the last seven days

{CONTACTS.SOURCEDATE} <= CurrentDate() - 7

2. Or how "old" any particular record is, expressed in days


3. To calculate the age in years, we must use the Crystal DateDiff function:

DateDiff("yyyy", {CONTACTS.SOURCEDATE}, CurrentDate())

The "yyyy" specifies we want the answer in years; "m" and "d" are also options (months and days, correspondingly)

Monday, October 17, 2011

GoldMine : Assigning a Password Policy

Another new security measure in GoldMine is the ability to set a password policy. This is done system-wide and applies to all users.

To do this, go to Tools | Configure | System Settings.
Click on the "Password Policy" tab.
You can set a minimum length, whether or not to block after three unsuccessful attempts, and also save a password history that cannot be repeated.

To set up password expiration, this is done on a per-user basis.

1. Go to Tools | Users Settings
2. Change the "Valid for Days" value.

You'll notice the "Next Change" date becomes activated after you choose x number of days.

Caveat; I once worked in a place that forced password changes every month. And as you walked around the office, on virtually every desk (usually on a stickynote) was each users password. So I guess the moral of the story is to use with care.

MSCRM 2011: Adding the LinkedIn Interface to a CRM form

In the sample below I will show you how to integrate Microsoft Dynamics CRM and LinkedIn. I found a post from Leon Tribe (an MVP for Microsoft Dynamics CRM) that explains it clearly for Microsoft Dynamics CRM 2011. Below are the detailed steps:

* Save the below html code to an html file (Copy and paste into Notepad and then do a Save As)

<script src="" type="text/javascript"> </script>
<span id="getlinkedin"> </span>
<script type="text/javascript">
var parentForm = parent.frames.document.crmForm;
new LinkedIn.CompanyInsiderBox("getlinkedin",parentForm.all.companyname.DataValue);

*Go to CRM, Settings, Customizations, Customize The System, Web Resources and Click the New button.
* Give the Web Resource a name and specify the type as a Web Page. Browse to the location where you saved your html file, select it and then press the save button.
* Customize the lead entity by going to Settings, Customizations, Customize the Solution, Entities, Leads, Form and add the Web Resource to the Main Form. Open up the Main Form, click the Insert tab and select the Web Resource that you just created.
* Save and Publish

You will now see a windows on the Lead form for the LinkedIn interface. When you change the Lead record’s Company, the LinkedIn window is adjusted to show you who you know that works there and a link to show everyone else that works there that you may have access to.

MSCRM 2011: Operators in a Workflow

There are 6 different operators that can be used in an MSCRM 2011 Workflow:

* Set to: This operator is used to set the value of the field of any data type.
* A new operator introduced in CRM 2011 – Append with: This operator can only be used with text field. It will append specified field's value or the default value to the existing value.
* Clear: This operator can be used with all data types. This will clear the field's value.
* Increment by: This operator can only be used with decimal fields. This will increment the existing value with the specified field's value or default value.
* Decrement by: This operator can only be used with decimal fields. This will decrement the existing value with the specified field's value or default value.
* Multiply by: This operator can only be used with decimal fields. This will multiply the existing value with the specified field's value or default value.

Bulk or Mass edit is a great feature in CRM that allows us to edit multiple records at a time. However, it will only allow you to set the value, you cannot perform the above operations in bulk edit. With the help of workflows, we can perform the above operations on multiple records.

GoldMine : Taking a Phone Message

For those of us who still have a receptionist or like to take messages in "person" over the phone, GoldMine can help organize this process.

Imagine I am calling into your organization and you need to take a phone message for a different sales rep to call me.

1. The first thing you do is navigate to the "Justin Hill" contact record. This is important, as virtually everything you do in GoldMine affects the "Current Contact Record".

2. Select "Message" from the GoldMine toolbar, or select Actions | Message from the top level menu.

3. You now see the "Phone Message" dialog window. You'll notice this item is already attached to the Justin Hill contact record. All you need do now is:

4. Specify the GoldMine user that should get this message from the top right drop down list.

5. Type in a simple message in the "Message" box. If more room is needed for notes, simply use the "Notes" area below. You may also choose to use the checkboxes which indicate the purpose of the call.

The assigned user will see this call on their GoldMine calendar alongside other Calls, Appointments and Actions.

GoldMine : Adding Columns to the Search Center

Did you know that you can change the columns seen in the Search Center?

1. Open up your Search Center by clicking on "Search" from the GoldMine toolbar.

2. Click on the "Columns" button within the "top strip" of the Search Center.

3. Click on the "Column Selection" button.

4. To add columns to the Search Center, you must move the appropriate fieldname from the "Available Columns" to the "Selected Columns" list.

Do so by singly selecting an item on the left, then clicking the forward arrow button. Reverse the procedure to remove columns from the Search Center view.

Pro Tip : This is a nice way to build columns for a list, as you can right-click within the Search Center and select Output To... | Excel.

MSCRM 2011: Adding CRM Modules to the Navigation Pane in Microsoft Outlook 2010

In Outlook 2010, the Solutions Module service won't let you simply drag and drop as you were able to in Outlook 2003 and 2007. Here is an alternative that uses the Shortcuts view in the Outlook Navigation Pane and allows you to quickly access your most frequently used Microsoft Dynamics CRM 2011 areas (Accounts, Contacts, Leads, etc.) within the Microsoft Dynamics CRM 2011 Outlook Client.

* Right-click on Shortcuts and Choose New Shortcut Group.
* Name the Group “CRM”.
* Right-Click on the “CRM Group” and Choose New Shortcut
* The Add to Navigation Pane will be displayed – choose the CRM Module that you wish to add to the CRM Shortcut
* Continue until all the modules you wish to add have been added.

This will allow easy access to those CRM modules that you use most frequently.

GoldMine : Using Favorite E-mail Folders

Did you know that you can add items to the "Favorite Folders" area in the GoldMine E-mail Center?

1. Hit F5 to bring up your E-mail Center (or select Go To | Mail from the top level menu)

2. Within the E-mail Center "Folder Tree" (upper left hand side), right click on the mail folder you'd like to add to your Favorites.

3. Select "Add to Favorite Folders". You'll now notice that your Folder now appears within the Favorites list.

I find it handy to add the previous months "Sent" folder to my Favorites, then remove it when the next month passes (by right-clicking the folder and selecting "Remove from favorite folders").

GoldMine : Using Custom E-mail Folders

Did you know that you can create and manage "custom" e-mail folders within the GoldMine E-mail Center?

1. Hit F5 to bring up our E-mail Center (or select Go To | Mail from the top level menu).

2. Right click on your "Inbox" folder, then select "New Inbox Subfolder".

3. Type in a new name for your Custom Folder.

Now you can drag messages from any other Mailbox folder into the new one. We have many clients that have, for instance, separate Inbox Subfolders for each "type" of E-mail (i.e. Client Related, Internal, etc).

Monday, October 3, 2011

Crystal Reports : Grouping on a Formula

One of the most effective techniques I've learned is how to group on a formula.
Think of it this way: the logic of the formula can be whatever you want it to be. So, any "high-level" corporate logic can be expressed here.
Take for example a table called HISTORY in which are listed history items per user.
Let us create a formula that will "kick-out" a value based on HISTORY.DATE.

IF {HISTORY.DATE} > CURRENTDATE - 30 THEN "History within 30 Days" ELSE
IF {HISTORY.DATE} < CURRENTDATE - 30 THEN "History beyond 30 Days" ELSE

Now this formula can be used as a Group.

You will notice that the last line is an ELSE {HISTORY.DATE}. This is because if a {HISTORY.DATE} does NOT fall into our formula criteria, then it will be "skipped". The final ELSE works as a "catch-all".

Crystal Reports : Group Tree Missing

Very recently a client asked: "where did my Group Tree go?"
They were referring to the left-hand windowpane where the Groups are displayed in a hierarchical view.
You may enable or disable the Group Tree by selecting View | Group Tree from the top level menu.

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 : Crystal Reports : Displaying Parameters with Multiple Values

When using a parameter that accepts multiple values, you will notice that you can't just drag it onto the report. We must pass it to the "Join" Crystal Function to "split" the values out.

Our parameter field is called {?UserID}, and accepts multiple values.

Create a formula with the following code:

Join({?UserID}, ", ")

This will return all UserID values within the {?UserID} parameter, separated by commas.

Crystal Reports : Displaying the starting and ending dates within a Range

If you are using a date parameter that is Ranged (i.e. a single parameter field that prompts for a starting and ending date), you will probably want to display these dates on the report. This helps prevent confusion as to what data is included in the report. To do this, we will need to create two formula fields, one for the starting date and one for the ending date. The formula fields will use the Minimum and Maximum Crystal functions.

For this example, our parameter field is called {?DateRange}

Formula field #1 (Starting Date) : Minimum({?DateRange})
(Pulls out the minimum value of our range)

Formula field #2 (Ending Date) : Maximum({?DateRange})
(Pulls out the maximum value of our range)