Monday, January 30, 2012

Crystal Reports : Using the Format Painter

In order to shorten the time it takes to make a report look nice, we can use the format painter to first, "copy" the formatting from one field, then "paste" it onto another. Formatting includes everything under the formatting options menu, including things like decimal places, currency symbol, etc.

1. Click on the object you want to "copy" the formatting of.
2. Go to Format | Format Painter on the top level menu
3. Click on the object you want to apply the formatting to

Note: Some versions of Crystal will not have this menu option, but will instead provide a format painter toolbar icon, in the form of a little paintbrush.

Crystal Reports : Creating a Verbose value from a "Code"

In many instances, you'll be reporting from a series of Codes. Let's imagine that we are Grouping a report on {ORDERS.PRODUCTCODE}, which is a three character value denoting the type of Product sold.

Values are:

- WID (Widget 5000)
- SRV (Professional Services)
- MSC (Miscellaneous)

But now let's imagine the Boss wants to see the actual Type Names on the report (so, instead of WID he wants to see "Widget 5000".

This is easily done with a simple Formula:

1. From your Field Explorer, right click on "Formulas" and select New.

2. Give your new Formula a descriptive name like {@frmProductCode}.

3. Paste in the following text:

IF {ORDERS.PRODUCTCODE} = "SRV" THEN "Professional Services" ELSE
IF {ORDERS.PRODUCTCODE} = "MSC" THEN "Miscellaneous" ELSE "Unknown"

4. Now simply Save and Exit the Formula Editor, and place the new Formula on your report. Note the final ELSE kicks out "Unknown", so if someday someone mistakenly entered in "MSV" (fat fingering the "C") for Miscellaneous, we will get an "Unknown" on the report, making it way for us to determine what happened.

Crystal Reports : Aligning Objects within the Designer

Here is an oldy but a goody;

For reports that need to look like spreadsheets, or where your fields need to line up precisely, you will want to learn the Align menu.

Here is the way it works; you can Align a group of selected Objects to share the same:

- Left hand side, Right hand side
- Midpoints, baselines, etc

To make this happen, find two fields you want to Align.

1. Select both of them using CTRL-Clicking. Select the field that NEEDS TO BE ALIGNED FIRST.

2. From the top level menu, select Format | Align | Lefts (for instance)

You'll notice that all your selected objects now share the left hand side with the LASTLY SELECTED ITEM. If you get your order screwed up, simply hit CTRL-Z to undo, then try again!

Crystal Reports : Catching Value Exceptions in Formulas

Here is a typical situation; you're working with a plain text date, by which I mean someone has just been entering in "loose" dates into a text field. So our values could be any one of these combinations of formatting:


The problem here was that I had to calculate number of days that had passed since the Date {CONTACT.CONTRACTDATE} in question, so I created a simple formula;


Which would return the number of days passed. Now, Crystal is pretty forgiving when it comes to plain text dates. It will actually "catch" all of the examples given above.

But when we started to run the report, it kept blowing up on "bad date values". Further inspection of the database revealed that users had been entering in values like:

01/01/2010 Signed
Estimate 01/2012

Which is to say they were mixing both text and date formats within the same field. So, the natural thing you'd want to do is add an exception to {@frmDateMath} like this; (kicking out 01/01/1980 whenever a date failed)

IF {CONTACT.CONTRACTDATE} LIKE "*Estimate*" THEN "01/01/1980"

...which would prevent the formula from ever trying to perform date math on a malformed date. (Assuming all malformed dates have the words "Estimate" or "Signed" in them)

ProTip: You may find yourself providing more exceptions than logic (depending on end users, in which case it is highly desirable to go to the source and start cleaning that up :)

Crystal Reports : Grouping records by the Date

I recently did a report for a client where we were summarizing (summing) Sales by Week of the Year. Remember, whenever you're summarizing values, you need to think about how those values will be Grouped; that more than half the work.

Let us imagine the database field we're Grouping on in called {ORDERS.ORDERDATE}.

So, we would first insert a Group using Insert | Group from the top level menu. Then, simply specifying {ORDERS.ORDERDATE} from the field drop down.

However, by default, Groups on Dates fall into Daily Group (or even worse, Day and Time Groups, if your software is track a time precision).

To change the Group to Weekly;

1. Select Report | Change Group Expert from the top level menu.

2. Select the Group you wish to modify, click the Options button.

3. When Crystal detects a Date Value as a Group, it displays the "The Section will be Printed" drop down. Select the appropriate interval from this list. In our case, we used "For each week".

You'll notice that now your Report is Grouped by Week (Based on {ORDERS.ORDERDATE}).

GoldMine : Setting the Calendar Time Increment

Did you know that you can specify the Increments on the Daily Calendar? By default, the Daily Calendar (Go To | Calendar) divides the day into thirty minute increments. Some businesses (especially those with a lot of Phone Call volume) may wish to set this to fifteen minutes.

To do this:

1. Select Tools | Options from the top level menu

2. Select the Calendar tab.

3. Drop down the "Increment Time By" list and select your desired increment

4. OK your way out.

Try it!

GoldMine : Auto-selecting Field Contents

Did you know that you can set GoldMine to "auto-select" the contents of a field when you click into it? Think of it like this; when you click into the Address bar of Internet Explorer or Firefox, the contents are automatically selected (highlighted), making it east to overwrite whatever is there by simply typing. It also makes it easy to Copy the contents of the field by simply hitting CTRL-C (the default Windows Copy command).

To set this up:

1. Select Tools | Options from the top level menu
2. Select the "Record" Tab
3. Check the box labeled "Select Contents of Fields"

You will need to log back into GoldMine when prompted to affect the change. Try it!

GoldMine : Viewing Filters in the Search Center

Did you know that you can view and Activate Filters from within the Search Center?

1. Open the GoldMine Search Center by clicking on the "Search" toolbar button.

2. Click on the Filters/Groups button at the top of the Search Center.

3. You'll notice that a Filters/Groups pane has opened itself up within the Search Center. Open up either the Filters or Groups tree and select the Filter/Group you wish to Activate.

4. GoldMine will prompt you to apply a Limit on the currently selected Filter/Group. You can simply click "No.". (What that actually does is bring you to the Optimize tab of the selected Filter/Group.

5. You'll see that your selected Filter/Group is now displayed as Search Results.

This is handy as you can take advantage of the Column format of the Search Center (the Preview portion in the Filters window only shows Contact and Company name).

NOTE: The last selected Filter/Group will be "Active" for the Search Center until you Close and ReOpen the Search Center.

GoldMine : Linking Documents the Easy Way

I am a big fan of Easy. Did you know that you can easily link a document to a Contact Record by dragging a file onto it from Windows Explorer?

1. Find the file you wish to link using Windows Explorer.
2. Simply drag-and-drop it into GoldMine onto the Contact Record. The Links Tab need not be open.
3. You'll see the familiar "Linked Document" window pop up. Notice that the filename is already filled in using the information from the file you just dragged into GoldMine.
4. Simply hit OK. Your Document is Linked.

ProTip: When linking documents to GoldMine, always make sure the document is linked somewhere other Users can get to it (like a Public network drive, for example).

GoldMine : Setting Tabstop Order

I am a big fan of keyboard shortcuts; take for instance the TAB key. When editing a field on the Current Contact Record in GoldMine, you can hit TAB to cycle through the other fields. But you'll probably notice, depending on how much customization you've done so far, that the order might be a little screwy.

To set the field order, simply bring up the properties of any field on the Contact Record.

1. Enter Screen Design mode by right-clicking on the Contact Record and selecting "Screen Design".
2. Double-click the field you wish to modify.
3. Select the "Layout" tab.
4. Change the "Position" number on the lower right hand side to reflect where the field should appear in your TAB cycle.

To Make it Easier: Do a print screen of your GoldMine Contact Record, and print it out using Microsoft Paint or something similar. Then mark each field on the paper with their corresponding order numbers. Only THEN go ahead and edit the TAB stops in GoldMine. The reason being is that it can be easy to get "lost" when working without a map. Try it!

QuickBooks: Two Instances of One Bank Account

I am a bookkeeper for a small business and created a bank account in QuickBooks and entered some Journal Entries with owner's contribution, his reimbursable expenses etc.. Unfortunately, it seems the owner decided to continue working on the data file and set up the bank account in QuickBooks to connect to his bank account on line so he can download the transactions. Now there are two of the same bank accounts in QuickBooks. How do I move the 'entries' I created into the account that is mapped to the bank online so that everything is in one place?

You can merge the two bank accounts. However, if the owner has downloaded transactions into QuickBooks, there could be duplicate data. When you reconcile the bank account, you will be able to delete the duplicate entries, if appropriate.

Note that in order to merge, you may need to unlink Online Banking before you merge and then re-set it up.

QuickBooks: Quick Help for Reconciliation Issue

HELP! I reconciled the previous month and everything was OK. When I started this month’s reconciliation, the beginning balance does not match last month's ending balance! Is there a quick & easy way to sniff out the problem? Or do I need to just retrace my steps - or start over for the previous month!!

Here's a neat and different trick to isolate the differential:
• Go to your reconcile window. Refer to the previous month's paper statement and enter the previous statement END DATE and ENDING BALANCE.
• Click continue.
• You'll see the difference in the bottom right hand corner and should be able to find the problem quickly (assuming it's just one or two items).
• Check off the items so that your difference is zero, and click RECONCILE NOW.

Now the numbers will be accurate to proceed with the current month. This method enables you to get back on track without having to un-reconcile first.

For your information:It is important to first understand that there are only so many ways a reconciled previous ending balance is changed as identified below:
1. A cleared transaction is changed to uncleared status
2. The date of a cleared transaction is changed to a post reconciliation date
3. The amount of a cleared transaction is changed
4. A cleared transaction is deleted
5. A new transaction is entered with a date before the previous balance date

QuickBooks: Advice For Managing Backups In QB Professional

Question: I am a CPA and have several clients who run QB Pro 2010. I also have my own copy in my own office. Normally, I take a backup of work done on site per client and then restore it to my own PC. Any tips on making this process mistake proof? What’s the actual difference between a QBW, a QBB and a QBM file? Thanks for any advice

Answer: It is not uncommon for Accounting Professionals to need to work on a client data file. However, the question is what is actually being done? That is, are they going to make changes and then give the data back to the client? In that case, they may be better off using the Accountant's Copy feature - although it does depend on what exactly you are doing with the file.

Understanding the different file types:
A QBW file is the "native" file. Uncompressed, and just the QuickBooks company file by itself.

A QBB file is a backup. Compressed a bit, and it includes other files that may be a part of the client installation, such as logo/graphics for forms, loan manager files, etc.

A QBM file is a compressed file that is more portable, usually used if you are emailing a file. It is not as complete as a QBB.

If the accounting professional takes any file back to their office other than the Accountant's Copy, and make changes that need to get back to the client – the client then cannot do any work (other than just viewing the file) on their side while the accounting professional has it. If they do, those changes will be lost when the accounting professional restores the file that they had been working on.

The Accountant's Copy process gets around that BUT there are restrictions and limitations.

Alternately, the accounting professional can choose to not bring back the file, and instead log in to their computer remotely and do your work on the original file. Then there is only one copy of the file. But that requires a good Internet connection, and a computer at their office that remains on and not in use so you can access it.

Going beyond that are more involved solutions, like using a Hosted QuickBooks installation or switching to QuickBooks Online.

QuickBooks: Locking the File after It Has Been Officially Closed

We are in the throes of preparing the data files to be reviewed by the CPA. That means all the changes or corrections that need to be made for the year should be done. When you send your data file to the CPA, consider that file officially closed for the year.

Your CPA will then work on the information provided, and they don’t want you going back in and changing OR deleting anything that relates to that year. If you change an old invoice or delete a duplicate bill, you’ve done just that.

QuickBooks provides a way to “lock” the data file to avoid making changes to a closed period. It is a feature called the “Closing Date” and it is very easy to use.

To do this task
1. Open the accounting preferences.
2. Click the Company Preferences tab.
3. In the Closing Date section, click the Set Date/Password button.
4. In the Date section, select the date through which you want your books to be closed.
5. (Optional, but recommended) Enter a password in both the Password and Confirm Password fields to limit access to the closed accounting period.
6. Click OK.

TIP: You will need to change the date in the “Closing Date” field manually at the end of each closing period. QuickBooks does not do that automatically for you. Set a Reminder (Company > Reminders) to ensure you don’t forget to update this critical box!

QuickBooks: “Sunset” of ALL 2009 Versions

It’s that time of year again – Intuit’s annual “Sunset” of older versions of QuickBooks. If you are using QuickBooks 2009 then you won’t be able to use certain services and features after May 31 2012.

In a nutshell, you lose any kind of feature that uses an online service from Intuit. Any feature that is self contained in your computer will continue to work, but if it requires any sort of updates or services via the Internet, you have to upgrade to a currently supported version. This should include the following in the US Windows versions:

-- QuickBooks Assisted, Basic, Standard or Enhanced Payroll
-- Employee Organizer
-- QuickBooks Merchant Service and Billing Solutions
-- QuickBooks Email (using their server)
-- Bill Pay and Online Banking
-- Intuit Technical Support Plans and Services

This also means that you won’t be getting any more updates to your copy of QuickBooks. No more bug fixes, no more new features. Note that this should NOT affect most third-party products that integrate with QuickBooks, but you should check with your vendor to be sure. This is a key issue in some cases. If you want to reinstall your 2009 program on a new computer, that program will not be updated automatically. You may be installing the R1 release if you install from a CD. Sometimes that creates an issue where the new install cannot access your existing QuickBooks company data if it had been used with a later release.

What Should You Do?
If you are using QuickBooks, the standard answer is “now is the time to upgrade”. Upgrading should be relatively painless going from QB 2009 to 2012, although there can sometimes be problems that range from minor annoyances, to major concerns.

As a Certified QuickBooks ProAdvisor, I will be able to help you and you should feel free to reach out.

Wednesday, January 11, 2012

GoldMine : Adding a Record to a Contact Group

As we all know, Contact Groups in GoldMine are just lists of Customers. The lists don't change unless we add to or remove from the Group.

The new GoldMine has a really handy dandy button right on the Contact Record toolbar, the "Add To" button. This allows you to very easily add single records to Contact Groups without having to actually go into the Groups window.

From the Contact Record you wish to add,

1. Click the "Add To" button located at the top of the Contact Record.
2. Select "Contact Group".
3. Choose the Group you want to add the Contact Record to.
4. If desired, type in a Reference (this is seen in the Groups window).
5. Hit OK.

It's just that easy. Try it!

GoldMine : Auto-populate Forecasted Sale amount based on Product

When Forecasting Sales in GoldMine, you can auto-populate the Amount field based on when Product you choose from the drop down.

To do this;

1. Select Schedule | Sale
2. Click on the PickList arrow for the Product field
3. To force a dollar amount for any Product in this list, click Edit and append a "//", then the dollar amount.

So, if our Product entry is "Widget", we want it to say "Widget //350.00".

Now, when you choose Widget from the Product list, an amount of $350.00 is automatically entered. Try it!

GoldMine : Keeping a Clean Inbox

This can be a huge deal; having a huge Inbox. I am one of the worst offenders. My Inbox right at this moment is at about 800. Which is actually fine, it's not until you get to 2500 or so that you start to run into problems. E-mail Center takes forever to come up, you can't see downloaded new e-mail, etc.

GoldMine wants us to deal with our Inbox. This happens in one of a few different ways;

1. When Replying to an E-mail, GoldMine automatically files the original in History, alongside your Reply.
2. When Deleting an E-mail, removes the item from the Inbox and puts it into Trash.
3. You can File e-mails manually from the E-mail center itself.

That third option is for all those e-mails that accumulate, you know, the stuff that you wanna keep, but doesn't require a reply. What to do about those?

The easiest way to deal with these is to Fast File them. This gets them out of your Inbox and into the associated Contacts History.

From your Inbox, right click on a single e-mail and select Fast File. It's just that easy. It's gone into History. You can also multiply select a range of e-mails using the SHIFT key, or add single e-mail to the list with a CTRL click. I highly recommend that if you cleaning out a bunch, do it in 100 record chunks. That way, your GoldMine isn't totally tied up.

You'll notice a snappier E-mail Center all around if you keep up with your Inbox from time to time.

GoldMine : Enabling Large Fonts

A lot of folks like to use "Large Fonts" in GoldMine. This can make a long day of pouring over Contact Records a little easier on the eyes...

1. Select Tools | Options
2. Click on the "Record" tab
3. Click the "Use Large Font" checkbox
4. Hit OK
5. Log back into GoldMine

You'll notice now that all the text in GoldMine is slightly larger. In some rare cases it may cause some general weirdness with Field Names being displayed properly, but this is purely cosmetic. I just switched mine to Large to prepare for this blog posting, and now I'm surprised to say I'll probably keep it this way. Kinda nice. Try it!

GoldMine : Storing Dates in GoldMine

This actually came up with a client today, when trying to run a Crystal Report on some date values they had been tracking in GoldMine. Most users like to track, say, a birthdate, or perhaps a Contract Signed date.

The important thing to know here is that when we need to report on these dates in the future, they will need to be Data Type Date. You may be tempted to simply use one of the default fields in GoldMine (the "Key" fields are notorious for this) and easily start inputting your dates (i.e. 01/01/2012, 04/20/92, etc.).

A better way to do it is to create a new field specifically meant to track the date.

Note: You MUST have exclusive rights to the database and a Master user account in GoldMine to do this.

1. On the Fields tab of your GoldMine, right click and select "New Field"
2. Click the "New Field" button
3. Give your new field a name (must start with the letter "U") like UCONTRACT
4. Give your new field a description, like "Contract Signed Date"
5. Select Field Type : Date
6. Hit OK.
7. You will now see your new field on the Contact Record. Move it to where you want it.
8. Click away from your new field. Hit OK in the "Field Properties" window.
9. You'll be warned that the Database needs to be rebuilt. Click Yes.
10. After GoldMine is done rebuilding, log back in.

Your new Date field should now be "open" and accepting data. You'll notice that when you bring up the picklist for this new field, it's a Calendar Control, allow the end user to easliy and consistently input date values.

And because it's being tracked as a Date Type, it's easy to (down the road) report on say, all contracts signed in 2011, etc. You'll love yourself later.

Tuesday, January 10, 2012

MSCRM 2011: How to change Icons for Custom Entities

MS Dynamics CRM 2011 provides the following feature that allows you to change the icons of a custom entity. This method is very simple and there is no coding required for this. The images need to be uploaded as web resources.
Following are the steps to upload an image file:

* Go to Settings, Customizations, Customize the System.
* Click New from the toolbar and select Web Resource from the list.

There are three types of icons to be assigned (Each should not be more than 10KB).
1. 16x16 pixels --This Icon will be placed in Web Application
2. 32x32 pixels --This Icon will be placed as the shortcut icon in the MS Office Outlook
3. 66x48 Pixels --This Icon will be placed on the entity form

The key advantage of MS Dynamics CRM 2011 is that it provides us the web resource utility to store all these Icons. So first we need to store all these Icons in the web resource so that we could reference them later. It is ideal to name each icon with some identifier. For instance to identify 16x16 pixels we could name it as myicon16.png. This will provide more readability. This is not mandatory but a suggestion.

* Enter the following Information in the New Web Resource Dialog:
* Name, Display Name and Type (PNG, JPG or GIF)
* Choose the file to be uploaded

Following are the steps to update a custom entity Icon:
* Navigate to Settings, Customizations, Customize the System.
* Expand the Entities node and then select a custom entity. You will notice the toolbar will show the Update Icons toolbar.
Note: You can only update the icons for custom entities. You cannot change the system entity icons.
* Click the Update Icons toolbar option.
* Within this window you can select the Web Resource of your uploaded icons. This window allows you to upload 3 different icons; 16×16, 32×32 and 66×48.
Note: Prerequisite with this window is that you have uploaded 3 different icons already to the Web Resource section.

There are only 2 icons that you can ACTUALLY change in CRM 2011 to custom entities. These are:
1. Icon in Web application (16×16, Less than 10kb, Programmatic Development Property = IconSmallName)
2. Icon for Entity Detail Forms (32×32, Less than 10kb, Programmatic Development Property = IconMediumName)

However the 3rd icon, the 66×48, is not used in Microsoft Dynamics CRM 2011. The application incorrectly labels this as the Icon in Entity forms. Programmatic Development Property for this is IconLargeName.

The recommendation is that you use .PNG Web resources because they support transparency and good compression with a better color fidelity than GIF.
GIF, JPG, & ICO formats are supported for backwards compatibility when organizations upgrade from Microsoft Dynamics CRM 4.0.

Following is a link to a solution that contains some free images in the correct sizes and formats. This link also describes how to upload the solution file.


MSCRM 2011: Creating Marketing Campaigns

Microsoft Dynamics CRM 2011 allows you to track marketing program information on a Campaign record. You can track the offer, type, schedule, and financial information about the campaign. For instance, you might have a campaign that coordinates the advertising activities planned for the launch of a new product.
The following fields are tracked on campaigns and campaign templates:

* Name: This field contains the title of the campaign.
* Status Reason: This denotes the status of the campaign for reporting purposes. The default statuses are Proposed, Ready to Launch, Launched, Completed, Canceled and Suspended.
* Campaign Code: This can be either a user-entered or system-generated code for the campaign.
* Campaign Type: This provides a category for the campaign, such as Advertisement, Direct Marketing, Event or Co-branding. This field is useful in reporting.
* Expected Response: This allows you to record the expected response for a campaign as a percentage from 0 to 100.
* Total Cost of Campaign Activities: In this field, Microsoft Dynamics CRM automatically totals the cost of all campaign activities.
* Miscellaneous Costs: This field records miscellaneous costs associated with the campaign.
* Total Cost of Campaign: This field contains the sum of the total cost of campaign activities and miscellaneous costs.

Create a Campaign through the following steps:
1. On the site map, click Marketing and then click Campaigns.
2. On the Campaigns tab, in the Records group, click New.
3. In the General section of the form, enter the appropriate information. The most important fields include:

* Name: This is a required field.
* Currency: If you intend to associate a price list with the campaign you must select the same currency that is specified in the price list.
* Status Reason: Click on the stage that the campaign is in the
* Campaign Type: Select the campaign type in the drop-down.
* Price List: If you select a price list, any opportunities arising from this campaign will automatically get the same price list.
* Offer: Enter a description of the actual offer you are contacting
customers about.
* Proposed Begin Date, Proposed End Date
* Actual Begin Date, Actual End Date

4. In the Financials section of the form, enter the appropriate
information and observe any noted restrictions or requirements as

* Budget Allocated
* Expected Revenue
* Miscellaneous Costs: Enter the total for any known costs of the
campaign that are not entered in the actual campaign activity

5. In the Notes section of the form, enter any information that might be
helpful to users who will view this campaign.
6. On the Campaign tab, in the Save group, click Save & Close.

* You are now able to add Planning Activities and Campaign Activities. You can also add a Marketing List at this time.

MSCRM 2011: Web Resources: Using JScript to Hide/Show Tabs

In Microsoft Dynamics CRM 2011, there are a number of Web Resources available.
They are:
*HTML Web Pages
*Style Sheets (CSS)
*Script (JScript)
*Data (XML)
*Images(PNG, JPG, GIF, or ICO)
*Silverlight (XAP)
*Style Sheets (XSL)

With the addition of the Jscript Web Resource, there is no longer a need to copy the Jscript to each of the Functions where the Jscript is needed.

In this example, we are going to create a simple JScript function which hides a tab on the Campaign Entity depending on a value in a picklist field – namely, Campaign Type. The Additional Information tab will only be show when the value in the Campaign type picklist field is set to Trade Show. (Note: Trade Show is a custom value that has been added to this picklist)

Following are the steps:

* Task 1 – Create a new JavaScript Library
* Go to Settings, Customizations, Customize the System.
* Click New from the toolbar and select Web Resource from the list.
* Enter the following Information in the New Web Resource Dialog:
* Name= “hideshowAddlInfoTabCampaign”
* Display Name= “Hide Show Addl Info Tab Campaigns”
* Type= “Script (Jscript)”
* Click the Text Editor button
* Paste the following script in to the source dialog.

Function hideshowaddlinfotabcampaigns (eventContext)
var option = Xrm.Page.getAttribute (“typecode”).getSelectedOption().text;
If (option == “Trade Show”)
Xrm.Page.ui.tabs.get (“AdditionalInfo).setVisible(true);
Xrm.Page.ui.tabs.get (“AdditionalInfo).setVisible(false);

* Click OK
* Click Save and Close

* Task 2 - Attach JScript function to onLoad event handler
* Next we need to setup the event handlers on the Campaign form
* Click on the Entities
* Select Campaign in the entity list and then click OK.
* When the Campaign appears in the list of available entities in your solution, expand the Campaign node and then expand the Forms node.
* In the forms grid, double-click Information form type Main
* Now that you are in the form editor we need to attach the event handler to both the Form onLoad and the Campaign Type OnChange events.
* Click Form Properties in the ribbon to bring up the dialog.
* Click the Form Libraries Add button.
* In the lookup dialog select the “Hide Show Addl Info Tab Campaign” web resource and Click OK
* In the Event Handler section Click Add.
* In the Handler Properties dialog do the following: Function = ” hideshowaddltabcampaigns”, Check Enabled, Check Pass execution context as first parameter
* Click OK, to close the Handler Properties dialog
* Click OK, to close the Form Properties dialog

* Task 3 – Attach Jscript function to Campaign Type onChange event handler
* Next, we need to attach the event handler to the Campaign Type picklist.
* In the form editor click to select the “Campaign Type” field
* Click Change Properties button in the ribbon.
* In the Field Properties dialog, select the Events tab.
* Click the Add button in the Event Handlers grid.
* In the Handler Properties dialog do the following:
* Function = ” hideshowaddlinfotabcampaigns”, Check Enabled, Check Pass execution context as first parameter
* Click OK, to close the Handler Properties dialog
* Click OK, to close the Field Properties dialog
* Click Save and Close

* Task4 - Publish changes and see the results
* While still in the Customizations, Click Publish All Customizations in the top toolbar of the Solution Explorer.
* Close the Solutions explorer

Go the campaigns module and click the New button. The Campaign form loads and the Additional Information tab is hidden.

Enter the following items:

*Name = “Test Campaign”
*Campaign Type= “Trade Show”
*Once the Campaign Type is “Trade Show” you will see the Additional Information Tab.

This is a simple demonstration that shows how the Java script only needs to be written once and can then be included in multiple On_Load, On-Change etc functions.

Tuesday, January 3, 2012

Crystal Reports : Using the Currency symbol for Other Things

When formatting Number Objects in Crystal Reports, it's good to know what the currency field can be leveraged to do. The most common use of this is to easily provide a percentage sign, but can be used for explicit strings as well.

Keep in this is effective on a per-field basis.

1. Right-click on the Number field you wish to modify from within the Designer.
2. Select Format Field.
3. Check the "Display Currency Symbol" checkbox.
4. Click Customize.
5. Select the Currency Symbol tab.
6. Modify the Currency Symbol by clicking into it and typing whatever you wish. For starters try a %.
7. Change the Position of the Currency Symbol to be "-123%", meaning that it will appear on the right-hand side of the Number.
8. Ok your way out.

This eliminates the need to use a textbox for the %. Notice also that you can type in pretty much whatever you want into the Currency Symbol, so the sky's pretty much the limit.

Try it!

Crystal Reports : Getting New Fields to Show in the Designer

A pretty basic problem, but worth our time. If Management is always creating new Fields and brainstorming new ways to use them, you will need to know how to "see" these new fields in existing reports.

This is as easy as selecting Database | Verify Database.

Crystal will then poll the Database for columns and indexes (among other things). After the "Database is up to Date" message appears, you should be able to see any new fields or new tables that have been added to the Database.

You will also notice that there is a "Verify on Every Print" option. There is no harm in enabling this, but this is usually considered overkill as even the most zealous Management can only add so many new fields.

ProTip: Certain versions of Crystal will "detect" that something has changed in the Database (i.e. a new field), and will prompt you on opening if you'd like to "Fix up the Report". Selecting Yes to this dialog is equivalent to Verifying the Database.

Crystal Reports : Turning Off Snap to Grid

This is something that occasionally drives me nuts. It is very typical to have a report that mimics the look of a spreadsheet (you actually just format each field to have a Border around it like cells in Excel). However, arranging precise items like individual rows and columns may not be the easiest thing in the world. What you usually get is one or two items that for whatever reason don't want to play nice and line up perfectly.

The first thing I learned to do was move Report items by using my arrow keys, not the mouse. Select any Crystal object and try it. You'll feel a lot more control over how things line up this way.

The second thing I learned to do was occasionally turn off my Snap. Simply right-click within the designer and select (de-select, actually) "Snap to Grid". When Snap is turned off, you'll find that you can now move your objects with the finest precision. Don't forget to turn Snap back on before you add any new objects to the Layout; they will behave erratically once Snap is turned back on. A good rule of thumb is to Design with Snap On, Fix with Snap Off.

These two tips can save you a lot of grief and aggravation, especially when modifying cosmetically complex reports. (Adding a column in to stack of columns, etc.)

Crystal Reports : Dealing with (and testing for) NULLS

NULLS are a pain. Because they are functionally equivalent to blanks. A NULL field and a "blank" field return the same thing; nothing. But they are technically different values and as a Crystal Reporter, you will need to know how to deal with them.

Let us consider the field {CONTACT2.USTATUS}, which tracks a customers' Service Status.

For those records with an explicit value (i.e. No Service), this is returned normally as a string.

For those records that HAD a value at some point in the past, but have since been "blanked out", these blank values are also returned normally as a blank string.

For those records where USTATUS has NEVER been written to, this will return a NULL. A NULL is "less than" a blank.

So when you propose the following Selection Criteria on USTATUS like this (selecting for anyone who doesn't have a Status);


This is only half of the battle. We must also test for NULL values as well, like this;


Unlike a SQL statement, which can simply use USTATUS IS NULL, we must use the special ISNULL() Crystal function. This function returns a BOOLEAN, which is either TRUE or FALSE.

Where your particular NULLS spring up will be determined by how well-written your front end software is. For example, they are more often found in so called "legacy" systems than newer, more modern ones.

Crystal Reports : Forcing a Page Break for Each Group

When using Groups in Crystal Reports (Insert | Group), you may wish to have each group appear on it's own page. Or at the very lest force a new page before the "next" Group prints.

This is easily accomplished through the Section Expert window.

1. Right-click in the "Sections" area to left hand side of your Designer.
2. Select "Format Section".

The Section Expert window will let you modify how each Report Section behaves.
In our case, we want to create a new page after each Group is printed.

1. On the left hand side of the Section Expert, select your Group Footer.
2. Then, on the right hand side, select "New Page After".

This will force the beginning of each Group to start on it's own Page. Another way to do it would be to use the "New Page Before" setting; you may find yourself trying each to get a feel for how they behave.

This can make the difference between a "messy" to read report and a nice, organized looking report. Try it!

Monday, January 2, 2012

QuickBooks: Pop-Up Messages - On or Off?

QuickBooks 2012 has both good and bad pop-up messages. However, it is very annoying for a pop-up ad to happen when you are working in QuickBooks, so you must decide how to handle them..... on or off?

There are actually some good pop-up messages in QuickBooks – for example:

  • those that prompt or remind you to add a class to a transaction when you’ve forgotten and you’ve turned on the class tracking feature

  • the one that warns you when you are posting a transaction directly to Retained Earnings – which is something that you really do NOT want to do

  • the warnings that appear when you accidentally select a date that is 90 days in the past or 30 days in the future

  • the warning that will appear if you enter duplicate bill numbers for the same vendor

  • the warning that will appear if you enter a check number, invoice number, estimate number, etc. that has previously been used

For the most part messages & pop-ups can be controlled through settings in the Preferences section {Edit menu -> Preferences}; where you will find both personal and company preference settings. Only the QuickBooks Administrator can make changes to the settings in the Company Preferences tab when they are logged into QuickBooks in single-user mode.

Individual users also have some control over what messages appear. Take time to look at each option - there are many! For example, experienced users often choose:

Desktop View -> My Preferences tab and uncheck the Show Getting Started Window
General -> My Preferences tab and uncheck the Bring back all one-time messages
Turn off pop-up messages for products and services, and Show ToolTips for clipped textselect

Sales & Customers -> My Preferences and uncheck the Show Payment toolbar on Receive Payments and Sales Receipts forms if your company does not wish to add Intuit credit card or eCheck processing

Service Connections -> My Preferences and uncheck Give me the option of saving a file whenever I download Web Connect data and If QuickBooks is run by my browser, don’t close it after Web Connect is done – if you do not use Online banking

Many pop-up windows and messages have a “Don’t show this again” checkbox option, checking this option instead of just clicking the OK button will rid you of the annoying box for good; so taking a minute or so each time a window pops up can help you to make informed decisions and rid you of wasted mouse clicks later. While it is “easier” at the moment to click the OK button just to get rid of the window – it is a bad habit to get into.

Some advertising messages, you just can’t get rid of, for example the Do More with QuickBooks block on the Home page, but you can close it.

In QuickBooks 2012, Intuit has added a new “advertising” message to printed Employee Pay Stubs – you’ll now find a Powered by Intuit logo on each pay stub that you print for your employees. Only the QuickBooks Administrator can remove this logo by going to the Edit menu -> Preferences-> Payroll & Employees -> Company Preferences -> Pay Stub & Voucher Printing -> and uncheck the Print Intuit logo option.


Clients often need to void checks that were written in a prior period. However, prior period income and expenses should not be changed for various reasons such as tax returns having been prepared.

The usual option is Void Check under the Edit menu. However, this takes the original transaction and changes the amount to $0. The date of the transaction is the date the change of the dollar amount of the transaction will be effective.

To prevent a change to the prior year accounting data, the voiding of a prior period check in the current period requires the entry of a deposit for the check that is to be voided.

During the bank reconciliation process, the check to be voided and the deposit are both cleared. The memo field on both the check and the deposit should reference the voided check. This will successfully void the check in the subsequent period. Differing From Accounts may want to be used on the Deposit depending on the reason for the voided check.

QuickBooks: Are There "Others"?

As you look at the informaiton posted during the past year, I hope you are not surprised to see "OTHER" as an account.

The reason is that when a chart of account has subaccounts, posting should only be made to the subaccount. You can always tell which accounts are sub accounts, because they are located under the Parent account, and are always indented.

Fix the entry:
When you see the "- Other" on a report, double click on the amount until you trace it back to the original transaction. Reclassify it to a correct sub account.

QuickBooks: It's 1099 Time Again

A year has passed, and it will soon be time to send out 1099’s to the vendors and a 1096 to the IRS. So many users hope it is a simple process in QuickBooks.

Actually, the printing is the easy part, once all the accounts are properly reviewed and mapped.

Verify your 1099 vendors
1. Go to the Reports menu, choose Vendors & Payables, and then click Vendor Phone List.
2. Click Modify Report at the top of the report.
3. In the Modify Report window, on the Display tab, select Eligible for 1099.
4. Click OK.
5. Review the report on the screen for vendors marked as "No" in the Eligible for 1099 column.

If you need to send Form 1099-MISC to any of these vendors, you must set them up as 1099 Vendors.
== If you found any missing 1099 vendors, open the vendor's record, and on the Additional Info tab, select Vendor eligible for 1099 and enter the vendor's tax ID number.

== On the Address Info tab, make sure the vendor's name and address follow postal guidelines.

Verify your 1099 accounts
1. Go to the Reports menu, choose Vendors & Payables, and then click 1099 Detail.
You see the vendors you designated as eligible for 1099-related payments and the accounts you selected for tracking 1099-related payments.
2. Click Print for a paper copy of the report in its current form.
You'll need this for comparisons.
3. Click the second 1099 Options drop-down list at the top of the report and choose All allowed accounts.
4. On your computer screen, look for accounts that are missing from the paper copy of the report.
5. If an account is missing from the paper copy of the report, and you have been using the account to track 1099-related payments, set up the account as a 1099 account.

QuickBooks: Great COA Security Trick!


Why is this trick needed? Any user with security authorization to create or modify "sensitive accounting activities" can make changes to the Chart of Accounts. Although these changes are made with the best of intentions, the results are not always optimal.

The Strategy Behind the Trick: You can prevent unapproved changes to the Chart of Accounts by using the 1099 Company Preference. Users with full rights or the administrator rights can still make changes. By following this 1099 setup, users will be unable to delete an account or change the type of account for any accounts you choose. Likewise, they will be unable to merge accounts. When trouble-shooting this issue, you can reverse a change in account type, however, merging accounts is permanent.

The Closing Date does not stop changes, and if charges are made, the affect all previous years.

Use the 1099 setup feature by selecting the Edit menu, selecting Preferences, selecting Tax:1099, and then selecting the Company tab. QuickBooks displays the list of possible 1099 categories. Select a 1099 Category that you will not be using in the normal course of business. For instance, if you never use box 13 - Excess Golden Parachute, then that is a great option for this trick.

Select the middle column, with the heading titled Account. Click on the drop-down arrow next to the category you have chosen (Box 13), QuickBooks displays the Chart of Accounts. Select Multiple Accounts and then manually select all the accounts you would like to protect.

NOTE: You may only assign an account to one 1099 Box. If an account has already been added in your 1099 setup, then that account is already protected.

Inactive Accounts do not show up in this selection window. You do not need to select Accounts Receivable and Accounts Payable as these accounts do not allow changes of types and/or merging. If a new account is added that we want to protect, then it has to also be added to the multiple accounts selected in the 1099 Preferences.

NOTE: If you would ever like to merge an account that is protected using this method, you can simply return to this preference screen and deselect those specific accounts prior to making any changes.