Tuesday, April 30, 2013

Crystal Reports : Formulas Working on Formulas

This keeps coming up; I see clients struggling with the ongoing administration of report logic to satisfy the requirements of management. Which, simply put, you should be designing your reports with an eye towards future changes.

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 : Using Comments

Did you know that you can save programming Comments within your Formula Code? You simply prefix each commented line with "//", like this;

//Formula to choose correct descriptions - 04/23/2012 Justin Hill
IF {CONTACT1.KEY3} = "WID" THEN "WIDGET" ELSE
IF {CONTACT1.KEY3} = "SPR" THEN "SPROCKET" ELSE
//If we can't find the code, simply return UNKNOWN
ELSE "UKNOWN"

This is a great way to comment very complex formulas, and really shines when you need to come back to them after time has passed and you might have forgotten what you did!

Crystal Reports : Date Formatting

A blog reader recently had a question regarding a very special Date formatting. He needed specific separators and day, month and year order (DD, MM, then YYYY). This can be achieved through the Field Formatting options of any Date field.

It is important to note that the field type must be Date in order for the Date Formatting options to be available.

1. Right-click on the Date field in question, select "Format Field"

2. Click the "Customize" button

3. Click the Date tab

4. From the "Format" drop downs, select the appropriate value type (numbers or letters). Do this for Day, Month and Year. In our case, we select a two digit number format.

5. Beneath the Order area (right hand side), select the appropriate value order. In our case, we want DMY (Day, Month, Year).

6. If desired, change the date separators below. In our special case, the user in question needed foreign language characters to separate the values within the date.

7. Ok your way out

This is very useful; no matter how you change the Format of the Date, it still "acts" like a Date Value, allowing all the easy math that implies. Try it!

Crystal Reports : Using a Formula for your Selection Criteria

Here is a nice technique to use when your selection criteria starts to get out of control; offload the "heavy lifting" to a formula, then just reference that formula within the Selection Expert.

For instance, let us imagine our Selection Criteria needs to test for multiple things; {CONTACT.STATE} should be either NY, PA or NJ, AND {CONTACT.SOURCE} should be "XMAS 2011" AND {CONTACT.LASTESTIMATE} is at least $100 but no more then $500.

Now, the Selection Criteria for that would look something like this;
{CONTACT.STATE} IN ['PA', 'NY'] AND {CONTACT.SOURCE} LIKE "*XMAS 2011*"
AND
({CONTACT.LASTESTIMATE} >= 100 AND {CONTACT.LASTESTIMATE} <= 500)

Note the use of enclosing parenthesis around the last two conditions; we want the LASTESTIMATE to be BOTH greater than 100 and less than 500.

But you are a Crystal Reporter, and know deep in your heart that someday, someone will need you to change this report. Here is how I would handle the same situation; I would create a separate formula for each condition, thusly;

{@frmState}
IF {CONTACT.STATE} IN ['PA', 'NY'] THEN TRUE ELSE FALSE

{@frmSource}
IF {CONTACT.SOURCE} LIKE "*XMAS 2011*" THEN TRUE ELSE FALSE

{@frmEstimate}
IF {CONTACT.LASTESTIMATE} >= 100 AND {CONTACT.LASTESTIMATE} <= 500 THEN TRUE ELSE FALSE

Now, once those formulas are created (and can be viewed right within the report itself, making troubleshooting very easy), you can use them in the Selection Criteria like this;

{@frmState} = TRUE AND {@frmSource} = TRUE AND {@frmEstimate} = TRUE

This leaves lots of room for compounding, making changes, and plugging in the eventual Parameters. Try it!

Crystal Reports : Counting Distinct 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.

Monday, April 29, 2013

GoldMine : Using GoldMine E-mail

Did you know that GoldMine has it's own "internal" e-mail? This can be really handy when you want to send very GoldMine specific things to other GoldMin users.

To send a GoldMine E-Mail;

1. Select Schedule | GoldMine E-mail from the top level menu.

2. Select the user you want to send to by dropping down the "To:" list. (GoldMine User Groups are also displayed here)

3. Note that the e-mail you are composing is connected to the Current Contact Record, so it's important to be on the "correct" record.

4. When finished, simply hit the "Send" button in the top left hand corner. After that, the e-mail message appears in the Contacts' Pending Tab and the GoldMin Users' Inbox. And like any other e-mail in GoldMine, needs to be Completed.

GoldMine : Adding Menu Items

Did you know how easy and fun it is to add menu items to GoldMine? Back in "the day" this was always a problematic procedure, but now with Premium Edition, this works fantastically well.

1. In GoldMine, click the thin "down arrow" button all the way at the "end" (the right hand side) of your toolbar.

2. From the drop down, select "Add or Remove Buttons", then "Customize..."

3. Select the "Commands" tab.

4. From the list of available items in the right-hand pane, simply click-drag an item from the list up to the toolbar where you'd like it to be. Rinse and repeat for any desired items.

5. Also, while you're here, you can remove toolbar items by click-dragging them OFF of the toolbar.

Try it!

GoldMine : Getting Control of That Process Monitor

The GoldMine Process Monitor. Love it, hate it; either way we must all deal with it. It shows, for instance, automatic e-mail retrieval history, global update and deletion logs; it is the essential window into troubleshooting the GoldMine universe.

But why is it set to auto-hide itself by default? Untouched, the Process Monitor will hide itself along the bottom edge of your GoldMine. To view it, the idea is to float your mouse cursor over the top edge and it appears like magic.

Unfortunately, this does not always happen. If you have issues viewing or manipulating the Process Monitor, simply turn it into a Tab like this;

1. Float your mouse over the Process Monitor until it reveals itself.

2. In the top gray edge of the Monitor window, right-click and select "Show as MDI window".

It's just that easy. You'll notice the Monitor is now it's own tab, supremely visible and easy to get to. Of course, you can always turn it back to auto-hide by repeating the process.

GoldMine : Customizing Tabs

One of the nicest things about GoldMine is the ability for each user to modify their Contact Record layout. Specifically, the Contact Record Tabs.

This is done on a per-user basis and can be locked down for security reasons.

1. Select Tools | Options from the top-level menu.

2. Select the "Record" tab.

3. Click the "Tabs" button.

4. In this window, you can turn Tabs On and Off. You can also re-arrange them by using the "Move Up/Down" buttons.

A lot of new users appreciate having all the extraneous Tabs removed at the least. Also, arranging the most important ones in a row can help as well. Try it!

GoldMine : Sorting your History and Pending Tabs

Did you know that you can perform all kinds of sorting and filtering within the History and Pending tabs? It's easy;

1. To Sort by a column in the History Tab, simply click on the column header. To get back to the "Default" sort, click on the Date tab until it sorts descending.

2. To Filter on a column value, click the little "down arrow" on the right of the column header. The drop down list will provide you with all the unique values available to Filter on. Simply select an item from the list. Note that unique values that do not exist are not displayed in the list. That is; if there are no Appointments in the current History Tab, Appointments will not be available from the Activity drop down.

Note: Once you select a Filter in this manner, the Tab will remain filtered until you "release" it by clicking on the little grey arrow in the bottom left hand corner of the Tab.


Wednesday, April 24, 2013

QuickBooks: Forensic Accounting



“I'm doing some forensic accounting work for a client. If there is a bill payment and no bill, I'm thinking the only way that could happen is that someone created a bill - paid it, and then deleted the bill. Do you have any other ideas on how this could happen?”
While I suspect you are correct in your assumption, you can find out exactly what happened by checking the audit trail report.

Reports -> Accountant & Taxes -> Audit Trail

It has filters like the other reports, so you can narrow the report down by name, date, amount, etc. It will show the date entered, who entered it, and all of the changes in the transaction since it was created, even if it's just adding a memo.

QuickBooks: Sales Tax Set Up



Collecting sales tax is a requirement for many of you, and as such, several things must be set up.

Enable Sales Tax

Select Edit - Preferences - Sales Tax preference – Company Preferences Tab - Click on “Yes” for the question “Do you charge sales tax”.

Pay attention to the full screen. Notice that the lower half of it has several options to review to make sure they are set properly for your business (how often you pay sales tax, and so forth).

Sales Tax Items

Sales tax is a charge that is added to an invoice and therefore must be on the item list.
You will create one or more sales tax items in the item list. You can do this by clicking on the add sales tax item button in the preferences screen, or by adding an item to the item list in the normal way.
Enter the percentage for the sales tax, and a vendor for whom you pay that sales tax. This is typically the state sales tax agency.

Multiple Jurisdictions

So what do you do if you have multiple jurisdictions? You could have a situation where you have a state tax, a county tax, a city tax or even more. When you file your reports to the state tax agency you need to split out the amounts by jurisdiction, usually.

To manage this, create a sales tax item for each jurisdiction. One for the state, one for each county you work with, one for each city. However you need to break it down for your reporting.

Then, create a sales tax group item and add each of the jurisdiction taxes:

For example – Sales Tax Group for Philadelphia/PA:  PA State Sales Tax – 6%; Philadelphia City Sales Tax – 2%
When you use this sales tax group in an invoice the rate will be 8.0%, but the program will report each tax separately.

It is important to note that if you have a large number of taxable jurisdictions it can become very complex. You need to have a sales tax item for each jurisdiction, and a sales tax group for each “nexus” or location that has different combinations. There are third party add-on products that help with sales tax tracking which may be helpful for you, especially if you have a large number of invoices to process.

QuickBooks: File Sharing Ideas



Even if you are still doing on-site bookkeeping services, it is likely that you will need to share files occasionally. Technology makes it easy to share information. As more statements, bills and other financial documentation is being sent electronically every bookkeeper should have a few reliable file sharing tools in the “office toolkit”.

Since there are many options, how do you know which is best for you? To answer that question, you need to think about situations in which you might need to use file sharing. Many people would wonder why they can’t just use email for everything. However, email can be very risky for many different reasons.

Using secure file sharing is a much better choice. Identity and financial information theft is just way too common now. So you do not want to be the one who was the weak link in the data protection chain!

Define your objective, THEN look for a solution

When choosing the tool(s) you will use, knowing your various needs will help in finding the solution. Look at how you are currently exchanging information with each client/professional, and then look for tool features that will make that exchange more convenient AND secure. Below are some of the tools used by bookkeeping businesses to exchange information with clients and CPA’s.

Tool
What it’s good for?
Cost
This works well for sharing and updating files that you want to access from multiple computers.

It can also be used as an automatic backup, and the newest features can work like a drag-and-drop “Inbox”
Free (up to 30 GB) to Paid
This is a great, tool that can be used as a 2-way “Inbox” for receiving and sending project files with clients and staff.

It sits in your system tray and you can easily drag & drop files, with instant synching.
Free (up to 18 GB) to paid
The free plan works if you have large documents that are too big for email and not sensitive.

The Pro plan is great for “certified mail” type secure file deliveries. Good for large projects, but not as streamlined as Dropbox for ongoing file sharing.

They have a desktop app that makes using their service very easy (works with both free & paid accounts)
Free to paid
This paid service works well for low-tech clients who refuse to go paperless. It’s an easy way to convert paper documents to electronic format (non-sensitive info only).

For workflow, you can setup a folder for each of your clients for “automatic” delivery in your email client
Paid (low cost)
An excellent solution for month-to-month clients who are also using QuickBooks.

Easy Inbox that also helps users convert paper to electronic format and send source documents that can attach to transactions in QuickBooks. Automatically keeps everything organized.

There is a free (unlimited time) level you can use with your first client to try it out until you outgrow the storage capacity.
Free to paid

Always remember, when you are sharing a QuickBooks data file, be sure it is password protected. Dropbox is great, and used by a large population. Since it appears to be on your desktop, it is easy to forget that it is actually a cloud based storage unit. Password protect as much as you can.

QuickBooks: Finding a Customized QuickBooks Letter Template



Sometimes there is a problem finding a template that was created or edited – often because it was not saved during the editing process and added to the letter template list. 

The following steps will guide you through the process.
·         Close any open windows in QuickBooks.
·         Select Company menu option.
·         Select Prepare Letters and Envelopes from the drop down.
·         Select the Type of letter to prepare.  (i.e., customer, vendor, etc.)
·         Review and Edit Recipients window appears.
·         Select the dates and customers you wish to create letters for. Click Next.
·         Select the button, Create or Edit a letter template.
·         Select Convert an Existing Microsoft Word Document to a Letter Template. Click next.
·         You will be at a window titled Choose an Existing Microsoft Word document. Select the browse tab and browse to the saved template.
·         You will now select the type of letter again; the default will be the last type you choose. Click Next.
·         In this window you will have an opportunity to rename the template if you choose. Select OK and Next.
·         This will open up the saved Word Document so that you can edit if needed.  Once editing is done, or no further editing needs to be done, you can close the Word Document.
·         Click on the tab Use Template and select Next.
·         This will take you back to the Review and Edit Recipients screen. After choosing the names and dates you wish to create letters select Next.
·         Your customized template will now appear in the list of Letter templates.