A client asked: We want to create a Call Group that will shows tickets that have assignments to the "Help Desk" and not to any other Assignment Group. If there is an assignment to another group, we do not want that ticket to appear in the Call Group. This group would be used for sending surveys on the Help Desk.
Try the two expressions below in your Call Group. They should return a "Help Desk Only" Call Group:
Option 1:
{Asgnmnt.GroupName} = 'Help Desk' AND {Asgnmnt.CallID} not in (select callid from asgnmnt where groupname <> 'Help Desk')
Option 2:
({CallLog.CallStatus} = "Open" and ({Asgnmnt.GroupName} = "Helpdesk" and {CallLog.CallSource} <> "HeatWEB" and {Asgnmnt.Resolution} <> "Completed") or ({CallLog.CallSource} = "HeatWEB" and {CallLog.CallStatus} = "Open" and ({Asgnmnt.GroupName} = "Helpdesk" and IsNull ({Asgnmnt.Resolution}))
Option 2 covers a number of alternatives and may provide better results depending on your Survey requirements.
Monday, November 9, 2009
Sunday, November 8, 2009
HEAT: Removing Select Calls From Groups
A client asked: How can I temporarily remove calls from a Call Group without changing the Boolean logic?
Of all the different things that can be done inside of Call Logging, one of the more popular actions is the ability to remove a Call Record from a Call Group without actually excluding it from the Call Group Boolean expression.
Why do this? A number of reasons. Maybe you want to run an AutoTask against an entire Call Group, with the exception of a few calls in that Group. Maybe you want to do a clean up by deleting all calls in a Call Group, with that same exception.
To temporarily remove a Call from a Call Group:
* Run the Call Group and open the Call Record to exclude
* Click on File > Remove Call from Group.
* This will temporarily remove the call from the open Group
The call will re-appear within the Call Group, once you refresh the Call Group, or restart Call Logging.
Of all the different things that can be done inside of Call Logging, one of the more popular actions is the ability to remove a Call Record from a Call Group without actually excluding it from the Call Group Boolean expression.
Why do this? A number of reasons. Maybe you want to run an AutoTask against an entire Call Group, with the exception of a few calls in that Group. Maybe you want to do a clean up by deleting all calls in a Call Group, with that same exception.
To temporarily remove a Call from a Call Group:
* Run the Call Group and open the Call Record to exclude
* Click on File > Remove Call from Group.
* This will temporarily remove the call from the open Group
The call will re-appear within the Call Group, once you refresh the Call Group, or restart Call Logging.
QuickBooks: Using IIF Files
A client asked: I would like to import various Lists into QuickBooks from another installation. Is the IIF file the best way to do this?
The IIF file format is a Generic data format used by Intuit QuickBooks for exporting financial data; saved in a text-based tab-delimited format that can be imported by QuickBooks and most spreadsheet programs. Follow the steps below
* Open the file in a spreadsheet program.
* Insert a column to the left so that the first column is blank.
* Insert a row at the top so that the first row is blank.
* Type the List Type in the first column and the first row.
* The types are shown in the next section.
* Fill in the remaining cells of the first column
* Add the column headings to the first row.
Note: You can get information about the column headings from the Reference guide to import files available in the QuickBooks Help file.
* Click the appropriate list type in the Help window.
* Verify that the column headings match the column information.
* The spreadsheet columns might require modification.
* Save your spreadsheet as a tab- or comma-delimited file.
* In QuickBooks, open the company to import the lists into.
* From the File menu, choose Utilities, click Import
* Then choose IIF Files from the submenu.
* Select the appropriate file to import in the Import window.
Click Open to run the import.
The IIF file format is a Generic data format used by Intuit QuickBooks for exporting financial data; saved in a text-based tab-delimited format that can be imported by QuickBooks and most spreadsheet programs. Follow the steps below
* Open the file in a spreadsheet program.
* Insert a column to the left so that the first column is blank.
* Insert a row at the top so that the first row is blank.
* Type the List Type in the first column and the first row.
* The types are shown in the next section.
* Fill in the remaining cells of the first column
* Add the column headings to the first row.
Note: You can get information about the column headings from the Reference guide to import files available in the QuickBooks Help file.
* Click the appropriate list type in the Help window.
* Verify that the column headings match the column information.
* The spreadsheet columns might require modification.
* Save your spreadsheet as a tab- or comma-delimited file.
* In QuickBooks, open the company to import the lists into.
* From the File menu, choose Utilities, click Import
* Then choose IIF Files from the submenu.
* Select the appropriate file to import in the Import window.
Click Open to run the import.
QuickBooks: Update Memorized Reports
A client asked: I use the filtering and formatting features of QuickBooks reports a lot. However, recently some of my reports are not producing accurate results. Any ideas?
Yes, the report features in QuickBooks are easier to use than most software packages and have flexibility, filtering, and formatting features that often exceed the functionality of more expensive accounting packages.
Normally, when you find a report that works, you memorize the report for quick access. You need to be careful, however, if you have memorized a report that has been filtered for more than one account, or name (e.g. job name), or item, or customer, job, vendor types
Your current problem could be that you need to use the feature you selected and add to the filter any new accounts or names or items that have been set-up since the report was memorized.
For example: A job detail transaction report has been memorized for a construction job labeled "Smith Project". Since the job started, the business added a new account to chart of accounts labeled "Permits".
The memorized job report may need to be modified to include this new account if the bookkeeper used "Selected Accounts" to create the report.
Yes, the report features in QuickBooks are easier to use than most software packages and have flexibility, filtering, and formatting features that often exceed the functionality of more expensive accounting packages.
Normally, when you find a report that works, you memorize the report for quick access. You need to be careful, however, if you have memorized a report that has been filtered for more than one account, or name (e.g. job name), or item, or customer, job, vendor types
Your current problem could be that you need to use the feature you selected and add to the filter any new accounts or names or items that have been set-up since the report was memorized.
For example: A job detail transaction report has been memorized for a construction job labeled "Smith Project". Since the job started, the business added a new account to chart of accounts labeled "Permits".
The memorized job report may need to be modified to include this new account if the bookkeeper used "Selected Accounts" to create the report.
QuickBooks: Employee Loans
A client recently asked: How do I setup an employee loan in QuickBooks and, also, a payroll deduction to pay it back?
To do this you need to setup payroll items and account items:
* Set up a payroll item, type=deduction, called employee loans.
* Set up an other current asset account called "employee loans".
* Use "write checks" to loan the money > code to "employee loans".
Edit employee information > add the payroll item "employee loans" to the "additions, deductions and company contributions" box. Enter the per pay period amount to withhold from each paycheck and the total amount of the loan.
Note: The loan deduction limit is a calendar year limit. If the loan is not repaid by December 31st, you will need to make adjustments. On January 1st, QuickBooks forgets the withdrawals made against the loan in prior years and begins all over again.
Therefore, in December, for each employee that has a loan balance -before the first paycheck of the new year- adjust the amount to repay for the new year. If the loan is repaid in full, delete the "employee loan item" from the employee information window.
To do this you need to setup payroll items and account items:
* Set up a payroll item, type=deduction, called employee loans.
* Set up an other current asset account called "employee loans".
* Use "write checks" to loan the money > code to "employee loans".
Edit employee information > add the payroll item "employee loans" to the "additions, deductions and company contributions" box. Enter the per pay period amount to withhold from each paycheck and the total amount of the loan.
Note: The loan deduction limit is a calendar year limit. If the loan is not repaid by December 31st, you will need to make adjustments. On January 1st, QuickBooks forgets the withdrawals made against the loan in prior years and begins all over again.
Therefore, in December, for each employee that has a loan balance -before the first paycheck of the new year- adjust the amount to repay for the new year. If the loan is repaid in full, delete the "employee loan item" from the employee information window.
QuickBooks: Inventory Valuation Difference
A client asked: Why does the inventory valuation report differ from inventory value in the Balance Sheet?
Items can be entered using any of the following transaction types:
* To increase: bill, credit card, check, inventory adjustment.
* To reduce: invoice, sales receipt, statement charges.
* To decrease: adjustment, bill credits, credit refund, credit memo.
* To record a purchase from a vendor for inventory, the quantity and inventory item is entered on the "item tab" of the transaction.
Any of these transaction types, that have used the "item tab" and also have an "inventory item" will appear on the inventory valuation report
The inventory valuation report will not include inactive items, so be sure to print all period end inventory valuation reports before inactivating them. On the other hand, the balance sheet account inventory can include a much broader range of transactions.
Transactions that used the expense tab not the item tab with a posting account of inventory will appear in the balance sheet reports on inventory. But as mentioned, will be omitted from the inventory valuation report due to no item tab used.
To discover the differences between the two reports, produce the following report:
* From the reports menu > Select Company & Financial
* Select balance sheet report > drill down on the inventory account
* Change the dates to the period you are reviewing
* From the report button bar, choose "type" from the "sort by" box
* Also from the report button bar, click on modify
* The lower left of the display tab has a window named columns
* Place an additional checkmark next to item > click OK.
Look for any of the following problems: Do you see any transactions missing an item name? You may discover that you used a transaction type that does not allow for entering "items" or "quantities".
Do you see transaction types without an item, but when you drill down to the original transaction, you have correctly used the item tab and see an inventory item was entered.
Do you see inventory adjustment transactions without an item? When using the transaction type "inventory adjustment", QuickBooks handles the posting to the account "inventory" behind the scenes. Do not use the account "inventory" on these transactions,; instead, select an expense or cost of goods sold account.
Was an inventory item made inactive while it still had a quantity and value? The value will appear in the balance sheet inventory report but not on the valuation report.
Items can be entered using any of the following transaction types:
* To increase: bill, credit card, check, inventory adjustment.
* To reduce: invoice, sales receipt, statement charges.
* To decrease: adjustment, bill credits, credit refund, credit memo.
* To record a purchase from a vendor for inventory, the quantity and inventory item is entered on the "item tab" of the transaction.
Any of these transaction types, that have used the "item tab" and also have an "inventory item" will appear on the inventory valuation report
The inventory valuation report will not include inactive items, so be sure to print all period end inventory valuation reports before inactivating them. On the other hand, the balance sheet account inventory can include a much broader range of transactions.
Transactions that used the expense tab not the item tab with a posting account of inventory will appear in the balance sheet reports on inventory. But as mentioned, will be omitted from the inventory valuation report due to no item tab used.
To discover the differences between the two reports, produce the following report:
* From the reports menu > Select Company & Financial
* Select balance sheet report > drill down on the inventory account
* Change the dates to the period you are reviewing
* From the report button bar, choose "type" from the "sort by" box
* Also from the report button bar, click on modify
* The lower left of the display tab has a window named columns
* Place an additional checkmark next to item > click OK.
Look for any of the following problems: Do you see any transactions missing an item name? You may discover that you used a transaction type that does not allow for entering "items" or "quantities".
Do you see transaction types without an item, but when you drill down to the original transaction, you have correctly used the item tab and see an inventory item was entered.
Do you see inventory adjustment transactions without an item? When using the transaction type "inventory adjustment", QuickBooks handles the posting to the account "inventory" behind the scenes. Do not use the account "inventory" on these transactions,; instead, select an expense or cost of goods sold account.
Was an inventory item made inactive while it still had a quantity and value? The value will appear in the balance sheet inventory report but not on the valuation report.
Saturday, November 7, 2009
QuickBooks: Deposit Report
A client asked: When I am doing a Bank Reconciliation, I would like a report of deposits that I can total. How can I do that?
Yes, to create a totaled list of deposits do the following:
* Select the reports menu > Select custom transaction detail report
* Click on the 2nd tab titled "filters" > select the filter "account"
* Place a check next to your business checking account
* Add a second filter for transaction type and select "deposit".
The credit column on this report will never contain data so remove it from the report. Find the diamond icon to the right of the word "credit", click and hold, drag it to the left side of credit
To utilize this report every month, give it a title and memorize for future use.
* From the report button bar, click on the modify report button
* Click on the Header/Footer tab and change the report title
* Click OK. Use the memorize button on the button bar to save
Memorized reports are available under the reports menu located at the very top of the QuickBooks window. Change the date range the next time you use this report.
Yes, to create a totaled list of deposits do the following:
* Select the reports menu > Select custom transaction detail report
* Click on the 2nd tab titled "filters" > select the filter "account"
* Place a check next to your business checking account
* Add a second filter for transaction type and select "deposit".
The credit column on this report will never contain data so remove it from the report. Find the diamond icon to the right of the word "credit", click and hold, drag it to the left side of credit
To utilize this report every month, give it a title and memorize for future use.
* From the report button bar, click on the modify report button
* Click on the Header/Footer tab and change the report title
* Click OK. Use the memorize button on the button bar to save
Memorized reports are available under the reports menu located at the very top of the QuickBooks window. Change the date range the next time you use this report.
HEAT Plus Mobile Released
FrontRange just announced the launch of HEAT Plus Mobile that enables field-based technicians to access and update critical service management information through a BlackBerry smartphone.
Using HEAT Plus Mobile, not only can technicians be alerted to new problem tickets, they can also update and close calls as soon as the work is done, no matter where they are. They can even open new tickets while on-site. This offers a significant time and cost saving over having to wait for engineers to update tickets from their desk.We'll be doing a whitepaper on this shortly.
Please email me if you'd like more information.
Using HEAT Plus Mobile, not only can technicians be alerted to new problem tickets, they can also update and close calls as soon as the work is done, no matter where they are. They can even open new tickets while on-site. This offers a significant time and cost saving over having to wait for engineers to update tickets from their desk.We'll be doing a whitepaper on this shortly.
Please email me if you'd like more information.
FrontRange Has Just Released HEAT+ HSS 9.0
Last month we did a webinar showing the features of the new release. If you'd like to setup a personal review please let us know.
We have four documents pertaining to this release:
-A Marks Group Whitepaper "5 Cool Things We Like About HEAT 9.0"
-What's New In HEAT 9.0
-HEAT 9.0 Administrators Guide
-Call Logging vs. Calling Web UI in 9.0
If you'd like any or all of these documents (no-charge) please email gene@marksgroup.net
We have four documents pertaining to this release:
-A Marks Group Whitepaper "5 Cool Things We Like About HEAT 9.0"
-What's New In HEAT 9.0
-HEAT 9.0 Administrators Guide
-Call Logging vs. Calling Web UI in 9.0
If you'd like any or all of these documents (no-charge) please email gene@marksgroup.net
New HEAT Whitepaper From The Marks Group
We just wrote "10 Things You BETTER Be Doing With Your HEAT System!". Many of our clients are holding off on upgrading their systems or adding new stuff because of budget limitations. A lot of non-clients are holding off their HEAT implementations for the same reasons. But if you already have HEAT, this whitepaper lists out the features that you already own and should be using.
For a free copy, please email me at Gene@marksgroup.net
For a free copy, please email me at Gene@marksgroup.net
SPECIAL ANNOUNCEMENT: HEAT USER GROUP AND FREE LIVE HEAT TRAINING
"What's new...and some free training too!"
Hosted by The Marks Group PC and FrontRange Solutions.
Open to Clients and Invited Guests (including prospective users).
Tuesday, December 1, 2009
8:30 AM - 4 PM
Register Online or call
888-224-0649, x801
The beginning of the day will be the HEAT User Group and in the afternoon is free training on implementing SLA's in HEAT.
The Courtyard Marriott at The Philadelphia Airport
8900 Bartram Avenue
Philadelphia, PA 19153
Agenda
8:30 AM - Noon
Welcome and Introductions / Breakfast
FrontRange Technology Update
What's New in HEAT
9.0 and Beyond / HEAT Roadmap
Customer Implementation Overview
Noon - 1 PM
Lunch and Networking
1 - 4 PM
Free Training: Implementing SLA's in HEAT
The purpose of implementing Service Level Management (SLA) is to transform a typical reactive service department to that of a proactive department which is managed effectively through the Service Level Management processes.
In this three hour session, participants will be trained step-by-step how to:
-Create and configure fields necessary to track SLA information in HEAT;
-Design custom forms and views using the fields created;
-Create and configure critical Alerts for notifying SLA tracking status;
-Create and configure necessary Rules for ensuring an automatic and meaningful SLA process;
-Configure HEAT's Messaging Center to accept communications related to existing SLAs;
-Use HEAT's Answer Wizard reports and Manager's Console to quantify SLA results;
Documentation will be provided. This class is for both beginners and administrators.
Hosted by The Marks Group PC and FrontRange Solutions.
Open to Clients and Invited Guests (including prospective users).
Tuesday, December 1, 2009
8:30 AM - 4 PM
Register Online or call
888-224-0649, x801
The beginning of the day will be the HEAT User Group and in the afternoon is free training on implementing SLA's in HEAT.
The Courtyard Marriott at The Philadelphia Airport
8900 Bartram Avenue
Philadelphia, PA 19153
Agenda
8:30 AM - Noon
Welcome and Introductions / Breakfast
FrontRange Technology Update
What's New in HEAT
9.0 and Beyond / HEAT Roadmap
Customer Implementation Overview
Noon - 1 PM
Lunch and Networking
1 - 4 PM
Free Training: Implementing SLA's in HEAT
The purpose of implementing Service Level Management (SLA) is to transform a typical reactive service department to that of a proactive department which is managed effectively through the Service Level Management processes.
In this three hour session, participants will be trained step-by-step how to:
-Create and configure fields necessary to track SLA information in HEAT;
-Design custom forms and views using the fields created;
-Create and configure critical Alerts for notifying SLA tracking status;
-Create and configure necessary Rules for ensuring an automatic and meaningful SLA process;
-Configure HEAT's Messaging Center to accept communications related to existing SLAs;
-Use HEAT's Answer Wizard reports and Manager's Console to quantify SLA results;
Documentation will be provided. This class is for both beginners and administrators.
More Technologies For Your Company
The Marks Group sells Microsoft CRM, GoldMine and ZohoCRM to help companies improve their sales, marketing and service processes.
We're also certified QuickBooks and Microsoft Dynamics-GP partners and our CPAs have helped hundreds of companies improve their financial systems.
And of course, we do a bunch of Crystal Reporting projects too!
We're also certified QuickBooks and Microsoft Dynamics-GP partners and our CPAs have helped hundreds of companies improve their financial systems.
And of course, we do a bunch of Crystal Reporting projects too!
Get CRM and Help Desk Training For FREE
November 16, 2009 - Customizing Microsoft CRM8:30 AM EST - 12:00 PMMicrosoft Training Center - Malvern, PAClick here for more details and to register
December 1, 2009 - Implementing Service Level Agreements1:00 PM EST - 4:00 PM ESTMarriott Courtyard Philadelphia AirportClick here for more details and to register
December 1, 2009 - Implementing Service Level Agreements1:00 PM EST - 4:00 PM ESTMarriott Courtyard Philadelphia AirportClick here for more details and to register
Upcoming Webinars of Interest
November 24th, 2009
Crystal Reports Tips and Tricks
2:00PM EST (30 minutes)
HEAT Software Tips and Tricks
1:00PM EST (30 minutes)
Microsoft CRM Tips and Tricks
10:15AM EST (30 minutes)
GoldMine Tips and Tricks
9:30AM EST (30 minutes)
QuickBooks Tips and Tricks
11:00AM EST (30 minutes)
January 27th, 2009
Quicker! Better! Wiser! Best Help Desk Technologies For 2009 11:00 AM EST (60 minutes)
Quicker! Better! Wiser! Best CRM Technologies For 2009
2:00PM EST (45 minutes)
Quicker! Better! Wiser! Which QuickBooks Makes Sense?3:30 PM EST (30 minutes)
To register for any of the above webinars, click HERE
Crystal Reports Tips and Tricks
2:00PM EST (30 minutes)
HEAT Software Tips and Tricks
1:00PM EST (30 minutes)
Microsoft CRM Tips and Tricks
10:15AM EST (30 minutes)
GoldMine Tips and Tricks
9:30AM EST (30 minutes)
QuickBooks Tips and Tricks
11:00AM EST (30 minutes)
January 27th, 2009
Quicker! Better! Wiser! Best Help Desk Technologies For 2009 11:00 AM EST (60 minutes)
Quicker! Better! Wiser! Best CRM Technologies For 2009
2:00PM EST (45 minutes)
Quicker! Better! Wiser! Which QuickBooks Makes Sense?3:30 PM EST (30 minutes)
To register for any of the above webinars, click HERE
The Marks Group Integrates Constant Contact With Microsoft CRM
Ready to send professional bulk e-mail messages to customers and contacts? The Marks Group has entered into a business partnership with the #1 e-mail marketing company Constant Contact If you sign up with Constant Contact through us (prices are the same) we'll support and help you integrate with Microsoft CRM at no charge! Please click here for more information.
SPECIAL ANNOUNCEMENT: FREE, LIVE MICROSOFT CRM TRAINING IN MALVERN, PA
A Dynamics - CRM Morning
Free to Clients and Invited Guests
November 16, 2009
8:30 AM - Noon
Register Online or call
888-224-0649, x801
Customizing Microsoft Dynamics - CRM
Hosted by The Marks Group PC
Agenda:
8:30 - 8:45 - Check In
8:45 - 12:00 - Training - Customization
-Basics of form, field and view customizations
-Creating, designing and modifying custom entities
-Building "on-load" events and other tricks to reduce mouse clicks
-Modifying form behavior to better align with your processes
-Q&A and Open Forum
No prior experience necessary. This session is for beginners and moderate users of Microsoft Dynamics - CRM who want to learn how to customize the application.
The Microsoft Training Center
Great Valley Corporate Center
45 Liberty Blvd, Ste 210
Malvern, PA 19355
Free to Clients and Invited Guests
November 16, 2009
8:30 AM - Noon
Register Online or call
888-224-0649, x801
Customizing Microsoft Dynamics - CRM
Hosted by The Marks Group PC
Agenda:
8:30 - 8:45 - Check In
8:45 - 12:00 - Training - Customization
-Basics of form, field and view customizations
-Creating, designing and modifying custom entities
-Building "on-load" events and other tricks to reduce mouse clicks
-Modifying form behavior to better align with your processes
-Q&A and Open Forum
No prior experience necessary. This session is for beginners and moderate users of Microsoft Dynamics - CRM who want to learn how to customize the application.
The Microsoft Training Center
Great Valley Corporate Center
45 Liberty Blvd, Ste 210
Malvern, PA 19355
NetSeminars
This month's GoldMine NetSeminars are scheduled for November 12, 2009: GoldMine - Filters, Groups and SQL Queries (11AM EST) GoldMine - E-Mail Management (1PM EST) GoldMine - Customizing (3PM EST)
This month's QuickBooks NetSeminars are scheduled for
November 13, 2009 QuickBooks - Making Sales and Getting Paid (11AM EST) QuickBooks - Paying Bills (1PM EST) QuickBooks- Essential Tasks (3PM EST)All NetSeminars are one hour long and cost $50.More information can be found here
This month's QuickBooks NetSeminars are scheduled for
November 13, 2009 QuickBooks - Making Sales and Getting Paid (11AM EST) QuickBooks - Paying Bills (1PM EST) QuickBooks- Essential Tasks (3PM EST)All NetSeminars are one hour long and cost $50.More information can be found here
New Whitepaper: How GoldMine Can Cut Your Costs
We all know GoldMine is a great tool for selling, marketing and servicing your customers. But if implemented correctly, it can also help reduce your overhead and cut other costs. Want some examples? Then please email me and I'll send you our latest free whitepaper.
The Marks Group Integrates Constant Contact And GoldMine
Ready to send professional bulk e-mail messages to customers and contacts? The Marks Group has entered into a business partnership with the #1 e-mail marketing company Constant Contact. If you sign up with Constant Contact through us (prices are the same) we'll support and help you integrate with GoldMine at no charge! Please click here for more information.
Invite Gene To Personally Train Your Sales, Marketing, and Service Groups
Gene's "Selling to Michael Bolton - A Quicker Better Wiser Approach To Finding, Closing and Servicing Customers" has been a hit with our clients across the country. Gene's worked with smaller organizations and larger companies like Intuit, Symantec, Citrix and Bank of America.
If you'd like more information about this three-part training program for your sales, marketing or service team, to learn more visit here
If you'd like more information about this three-part training program for your sales, marketing or service team, to learn more visit here
Thursday, October 29, 2009
GoldMine: Make Sure to Follow Up
A client asked: I often forget to schedule follow up calls in Goldmine. Is there a way to make sure that follow ups are always scheduled?
When I tell someone that I'll give them a call tomorrow, I always go right to Goldmine to schedule the call so I actually remember to do it. Sure and if you believe that one...
Unfortunately, there is no way to make sure you or I always schedule that follow up call but we can make the process quicker (which greatly increases the chances of adding the followup to Goldmine).
Here is what I do: In the Date field when scheduling the call (or any type of activity), instead of typing in a date, or using the lookup to bring up a calendar, type in 1D.
Then, when you tab or click, to the next field the date will be set for 1 day from today. Also, when you want to schedule the activity 2 days from now, type in 2D, for 3 days 3D.
D works for Days, W for Weeks, and M for Months.
When I tell someone that I'll give them a call tomorrow, I always go right to Goldmine to schedule the call so I actually remember to do it. Sure and if you believe that one...
Unfortunately, there is no way to make sure you or I always schedule that follow up call but we can make the process quicker (which greatly increases the chances of adding the followup to Goldmine).
Here is what I do: In the Date field when scheduling the call (or any type of activity), instead of typing in a date, or using the lookup to bring up a calendar, type in 1D.
Then, when you tab or click, to the next field the date will be set for 1 day from today. Also, when you want to schedule the activity 2 days from now, type in 2D, for 3 days 3D.
D works for Days, W for Weeks, and M for Months.
GoldMine: Methods To Send Holiday Cards
A client wants to know: I have added Merge Codes for holiday cards. Can you tell me how to generate a filter or group to create a list?
Yes, however, the method you use depends in part on the type of contact. For example, if you are only working with primary contacts I recommend using a filter structured as follows:
The key is to use the Contains operator, as there may be multiple values in the field. Once you’ve built your filter you can see the list from the Filter’s Preview tab as well as from the Contact Search Center!
Then, you can export from the search center, use GoldMine’s report’s to print labels, and send the holiday cards.
You can also use GoldMine’s Export Wizard available under File Import/Export Export Contact Records. The wizard gives you a variety of options of the export format (dBASE, ASCII, etc.) as well as whether you want to export primary and/or secondary contacts as well as limiting the export to a merge code.
Once exported, you can use Word to print labels and send the cards. The only problem with this approach is that if the additional contact doesn’t have an address listed, the Export wizard won’t automatically pick up the address of the primary contact.
So, to export a list for all contact types, you need to use the SQL Query feature. You will need to run three SQL queries as shown below:
One for primary contacts:
select company, contact, title, address1, address2, address3, city, state, zip, phone1, contact1.accountno from contact1 where mergecodes like ‘%HC%’ order by company, contact
One for secondary contacts without an address (using the address from the primary contact):
select contact1.company, contsupp.contact, contsupp.title, contact1.address1, contact1.address2, contact1.address3, contact1.city, contact1.state, contact1.zip, contsupp.phone, contact1.accountno from contact1, contsupp where rectype = ‘C’ and (contsupp.address1 is null or contsupp.address1 <= ”) and contact1.accountno = contsupp.accountno and contsupp.mergecodes like ‘%HC%’ order by company, contsupp.contact
One for secondary contacts with an address:
select contact1.company, contsupp.contact, contsupp.title, contsupp.address1, contsupp.address2, contsupp.address3, contsupp.city, contsupp.state, contsupp.zip, contsupp.phone, contact1.accountno from contact1, contsupp where rectype = ‘C’ and contsupp.address1 > ‘ ‘ and contact1.accountno = contsupp.accountno and contsupp.mergecodes like ‘%HC%’order by company, contsupp.contact
Just change the ‘HC’ in each query to the mergecode you’re using. After running each query you can Right-Click > Output to Excel and export a list for sending the holiday cards.
Yes, however, the method you use depends in part on the type of contact. For example, if you are only working with primary contacts I recommend using a filter structured as follows:
The key is to use the Contains operator, as there may be multiple values in the field. Once you’ve built your filter you can see the list from the Filter’s Preview tab as well as from the Contact Search Center!
Then, you can export from the search center, use GoldMine’s report’s to print labels, and send the holiday cards.
You can also use GoldMine’s Export Wizard available under File Import/Export Export Contact Records. The wizard gives you a variety of options of the export format (dBASE, ASCII, etc.) as well as whether you want to export primary and/or secondary contacts as well as limiting the export to a merge code.
Once exported, you can use Word to print labels and send the cards. The only problem with this approach is that if the additional contact doesn’t have an address listed, the Export wizard won’t automatically pick up the address of the primary contact.
So, to export a list for all contact types, you need to use the SQL Query feature. You will need to run three SQL queries as shown below:
One for primary contacts:
select company, contact, title, address1, address2, address3, city, state, zip, phone1, contact1.accountno from contact1 where mergecodes like ‘%HC%’ order by company, contact
One for secondary contacts without an address (using the address from the primary contact):
select contact1.company, contsupp.contact, contsupp.title, contact1.address1, contact1.address2, contact1.address3, contact1.city, contact1.state, contact1.zip, contsupp.phone, contact1.accountno from contact1, contsupp where rectype = ‘C’ and (contsupp.address1 is null or contsupp.address1 <= ”) and contact1.accountno = contsupp.accountno and contsupp.mergecodes like ‘%HC%’ order by company, contsupp.contact
One for secondary contacts with an address:
select contact1.company, contsupp.contact, contsupp.title, contsupp.address1, contsupp.address2, contsupp.address3, contsupp.city, contsupp.state, contsupp.zip, contsupp.phone, contact1.accountno from contact1, contsupp where rectype = ‘C’ and contsupp.address1 > ‘ ‘ and contact1.accountno = contsupp.accountno and contsupp.mergecodes like ‘%HC%’order by company, contsupp.contact
Just change the ‘HC’ in each query to the mergecode you’re using. After running each query you can Right-Click > Output to Excel and export a list for sending the holiday cards.
GoldMine: Tagging Contacts for Holiday Cards
A client asked: Can you provide a way to filter contacts that should be getting a holiday card?
Sure, there are several different ways of doing this, but the following is easy and reliable. This method uses the Merge field and can be used for both primary and secondary contacts.
To configure the merge field for Holiday cards, we have to change the lookup values for the primary contact and the same merge codes will automatically be used for secondary contacts.
Within the Merge field, use the F2 key to view the Lookup list.
Left-Click on the New button to add different merge codes.
You may want single or multiple codes (for cards and gifts)
For example, you can use
HC for Holiday Card and HG for Holiday Gift.
If you use the format: HC;//Holiday Card (Code, semi-colon, two slashes, and then description) then only the code will be placed in the field.
With this format you can fit up to 5 codes in the merge code field. Select one code, as you normally would when using the lookup list, then select a second in the same manner. GoldMine will automatically place a comma and space between the two instead of replacing one code with another.
You can use any number of letters or numbers for the code but all codes should have the same number of characters. Once the codes are added, you can use a SQL query and the export wizard to produce a list
Sure, there are several different ways of doing this, but the following is easy and reliable. This method uses the Merge field and can be used for both primary and secondary contacts.
To configure the merge field for Holiday cards, we have to change the lookup values for the primary contact and the same merge codes will automatically be used for secondary contacts.
Within the Merge field, use the F2 key to view the Lookup list.
Left-Click on the New button to add different merge codes.
You may want single or multiple codes (for cards and gifts)
For example, you can use
HC for Holiday Card and HG for Holiday Gift.
If you use the format: HC;//Holiday Card (Code, semi-colon, two slashes, and then description) then only the code will be placed in the field.
With this format you can fit up to 5 codes in the merge code field. Select one code, as you normally would when using the lookup list, then select a second in the same manner. GoldMine will automatically place a comma and space between the two instead of replacing one code with another.
You can use any number of letters or numbers for the code but all codes should have the same number of characters. Once the codes are added, you can use a SQL query and the export wizard to produce a list
Wednesday, October 28, 2009
QuickBooks: Clean Up Lists
A client asked: We need to clean up our lists. Can you give us some easy ways to reduce duplicate and inactive names?
There are a number of things you can do. First, you can merge duplicate customers, vendors and items. You can also make a list item inactive so that it is hidden. These clean up methods are outlined below.
The following procedure will show you how to merge two vendors All the historical transactions that contained the predecessor name will be modified to contain the survivors name. This procedure can also be used for items, customers, and accounts.
From your Lists Menu Click on Vendors
Determine which vendor name will be the survivor.
(Make a note of the exact spelling.)
Determine which vendor will be merged off the list
Edit the vendor that will no longer exist
(Control + E, or right click on the name > select edit vendor)
Retype the name using the exact spelling of the survivor
Click OK and a popup will ask if you want to merge
Click Yes.
When a list name is no longer used but cannot be deleted because of prior activity you can hide it from sight by making it inactive.
Highlight the list item (Customer, Vendor or Account)
From the edit menu, select Make inactive or Ctrl+E to edit
Click on the option make inactiveor
When at least one name is set to inactive - Click on show all
In the column to the left of the list name click to add an "X”.
Remove the show all checkmark when done
All list items/names (including inactive) will continue to show up in the reports for any given period as long as there was a balance in that period, except inventory items, so be sure to print and save all end of period inventory reports before inactivating inventory items.
There are a number of things you can do. First, you can merge duplicate customers, vendors and items. You can also make a list item inactive so that it is hidden. These clean up methods are outlined below.
The following procedure will show you how to merge two vendors All the historical transactions that contained the predecessor name will be modified to contain the survivors name. This procedure can also be used for items, customers, and accounts.
From your Lists Menu Click on Vendors
Determine which vendor name will be the survivor.
(Make a note of the exact spelling.)
Determine which vendor will be merged off the list
Edit the vendor that will no longer exist
(Control + E, or right click on the name > select edit vendor)
Retype the name using the exact spelling of the survivor
Click OK and a popup will ask if you want to merge
Click Yes.
When a list name is no longer used but cannot be deleted because of prior activity you can hide it from sight by making it inactive.
Highlight the list item (Customer, Vendor or Account)
From the edit menu, select Make inactive or Ctrl+E to edit
Click on the option make inactiveor
When at least one name is set to inactive - Click on show all
In the column to the left of the list name click to add an "X”.
Remove the show all checkmark when done
All list items/names (including inactive) will continue to show up in the reports for any given period as long as there was a balance in that period, except inventory items, so be sure to print and save all end of period inventory reports before inactivating inventory items.
QuickBooks: Problems With "Write Check"
A client recently asked: A 'write check' window was used to pay a bill that had also been entered into QuickBooks with an enter bills window. How do I mark the bill that is still showing as open on the accounts payable report as paid?
Two steps will correct the situation:
1. Find the 'write check' transaction. Change the account to 'accounts payable' and enter the vendor name under the 'customer:job' field. Save and close
2. To link the payment to the vendor bill, From the Vendor menu > Pay bills > place a checkmark next to the open vendor bill > Click on set credits and then done. Save and close the pay bill window.
This solution is better than simply deleting the original 'bill' transaction. The dates used on forms in QuickBooks determines the month the transaction will show up on reports. The date used on the bill transaction will keep the expense in the month it was incurred. If you delete the 'bill' the date on the check would be the date that the expense shows up in reports.
Two steps will correct the situation:
1. Find the 'write check' transaction. Change the account to 'accounts payable' and enter the vendor name under the 'customer:job' field. Save and close
2. To link the payment to the vendor bill, From the Vendor menu > Pay bills > place a checkmark next to the open vendor bill > Click on set credits and then done. Save and close the pay bill window.
This solution is better than simply deleting the original 'bill' transaction. The dates used on forms in QuickBooks determines the month the transaction will show up on reports. The date used on the bill transaction will keep the expense in the month it was incurred. If you delete the 'bill' the date on the check would be the date that the expense shows up in reports.
QuickBooks: Overpayment by Customer
A client asked: What do I need to do when a customer overpays?
First, determine if you need to issue a refund check or if the funds will be retained on account to be used against other outstanding invoices or future sales to the customer.
Here are examples of both options:
A customer, XYZ, sends you a check for $48.00 to pay for two invoices The receive payments window only shows invoice 42 with $30.00 unpaid.
Run a search on $18.00 (Edit menu > Advanced find)
Select the filter amount > click on the button "="
Enter $18.00 > click Find
You find XYZ has previously paid the $18.00 with another check
Option One: Refund the overpayment:
Enter the check number in the receive payment window
Enter an amount of $20.00 and apply to invoice #42.
In the lower left, click on "group with undeposited funds".
Save and close.
From the banking menu, click on Make deposits
Select all the checks that apply to the deposit
Click OK.
On the 2nd window, click on the next line under "received from"
Enter the customer name XYZ. Tab to the next column
Type "Customer refunds" as the account name.
You will be required to set up this account, select type = "income".
In the memo field, put a note to describe the transaction
Enter the amount in the far right corner.
Save and Close
From the "write checks" window, create a check payable to XYZ
Code to the account "Customer refunds".
Enter description into memo field.
Save and print the check.
The customer refunds account now has a zero balance. The account's only purpose is to clear the overpayment deposit and return payment to the customer.
Option 2: Apply the overpayment "on account":
Enter the check number in the receive payment window
Enter in the amount of the payment received $48.00.
Place a check next to the invoice #42 $30.00
Do not place a checkmark next to any other invoice.
Clicking save and close will leave the $18.00 as a credit on the account.
A pop up window will remind you that this amount will be left open (as a credit) to be used against future invoices entered into the system. Consider mailing the customer a new statement (Customer menu > Create Statements) which will notify the customer of the new credit on his account.
First, determine if you need to issue a refund check or if the funds will be retained on account to be used against other outstanding invoices or future sales to the customer.
Here are examples of both options:
A customer, XYZ, sends you a check for $48.00 to pay for two invoices The receive payments window only shows invoice 42 with $30.00 unpaid.
Run a search on $18.00 (Edit menu > Advanced find)
Select the filter amount > click on the button "="
Enter $18.00 > click Find
You find XYZ has previously paid the $18.00 with another check
Option One: Refund the overpayment:
Enter the check number in the receive payment window
Enter an amount of $20.00 and apply to invoice #42.
In the lower left, click on "group with undeposited funds".
Save and close.
From the banking menu, click on Make deposits
Select all the checks that apply to the deposit
Click OK.
On the 2nd window, click on the next line under "received from"
Enter the customer name XYZ. Tab to the next column
Type "Customer refunds" as the account name.
You will be required to set up this account, select type = "income".
In the memo field, put a note to describe the transaction
Enter the amount in the far right corner.
Save and Close
From the "write checks" window, create a check payable to XYZ
Code to the account "Customer refunds".
Enter description into memo field.
Save and print the check.
The customer refunds account now has a zero balance. The account's only purpose is to clear the overpayment deposit and return payment to the customer.
Option 2: Apply the overpayment "on account":
Enter the check number in the receive payment window
Enter in the amount of the payment received $48.00.
Place a check next to the invoice #42 $30.00
Do not place a checkmark next to any other invoice.
Clicking save and close will leave the $18.00 as a credit on the account.
A pop up window will remind you that this amount will be left open (as a credit) to be used against future invoices entered into the system. Consider mailing the customer a new statement (Customer menu > Create Statements) which will notify the customer of the new credit on his account.
QuickBooks: You Can't Memorize That
A client recently asked: Are there transactions that can't be memorized?
Yes, transactions that cannot be memorized include payroll checks, time records, bill payments, sales tax payments or receipts or deposits of payments. There are a lot of transactions, however, that can be memorized to save time and increase efficiency.
Some of these are:
Loan or lease payments, recurring bills, recurring checks, monthly health insurance bills, monthly electronic withdrawals from checking, or business insurance installment payments
Other items include:
Rent, recurring sales, rent collections, monthly installments payments due from customers, repeating tuition invoices, recurring purchases orders, purchase orders that are repeated with many of the same items, recurring journal entries, allocation of overhead to other departments and recurring estimates
To memorize a transaction from the transaction window:
Enter the transaction as you would like it memorized
Control + M or from the edit menu select “memorize”
Enter a name that will help you recognize the transaction
Complete: How often, next date to enter, number of remaining payments
Retrieve and use the memorized transactions from the list menu.
O use the shortcut Control key+ T
The transaction template you used to create the memorized transaction can be saved as a valid transaction, or cleared
Yes, transactions that cannot be memorized include payroll checks, time records, bill payments, sales tax payments or receipts or deposits of payments. There are a lot of transactions, however, that can be memorized to save time and increase efficiency.
Some of these are:
Loan or lease payments, recurring bills, recurring checks, monthly health insurance bills, monthly electronic withdrawals from checking, or business insurance installment payments
Other items include:
Rent, recurring sales, rent collections, monthly installments payments due from customers, repeating tuition invoices, recurring purchases orders, purchase orders that are repeated with many of the same items, recurring journal entries, allocation of overhead to other departments and recurring estimates
To memorize a transaction from the transaction window:
Enter the transaction as you would like it memorized
Control + M or from the edit menu select “memorize”
Enter a name that will help you recognize the transaction
Complete: How often, next date to enter, number of remaining payments
Retrieve and use the memorized transactions from the list menu.
O use the shortcut Control key+ T
The transaction template you used to create the memorized transaction can be saved as a valid transaction, or cleared
QuickBooks: Correcting Memorized Transactions
A client asked: How do I correct a memorized transaction?
To correct a memorized transaction, you need to do the following:
Double-click on the memorized transaction like writing a check
This will also work for a bill or invoice
Make the correction, then re-memorize (control + M)
Select "replace the previous transaction"
Cancel the open transaction by closing the window
To modify the frequency:
From the memorized transaction list, highlight the transaction
Press Ctrl + E (to edit right click and select edit).
Change the scheduling of the transaction and click OK.
To correct a memorized transaction, you need to do the following:
Double-click on the memorized transaction like writing a check
This will also work for a bill or invoice
Make the correction, then re-memorize (control + M)
Select "replace the previous transaction"
Cancel the open transaction by closing the window
To modify the frequency:
From the memorized transaction list, highlight the transaction
Press Ctrl + E (to edit right click and select edit).
Change the scheduling of the transaction and click OK.
Tuesday, October 27, 2009
CRM: Auto-Fill for Empty Fields
A client asked: We have a field that is required but if left blank on save we want it to autopopulate with the combination of two fields that exist on the form. Can this be done?
Yes, this can be done using client-side javascript.
First, you'll need to open the form for the record that you want to update:
* Select Settings Customization Customize Entities
* Choose the entity you want this customization to work for 00
* Double Click Click the Forms and Views item
* Double Click on the Form.
The two fields you want to use to create the third field, and the third field itself, should be on the same form. If not, add them to the form.
* Update the OnSave Event: Click Form Properties (right-side of the form, bottom)
* Select OnSave and then Edit Make sure you check "Event is enabled"
Use the sample code below as a starting point to develop your code. Once it is complete, click Save and Close then Publish.
[code]
if (crmForm.all.[VarCharFieldName].DataValue == null) {
// Retrieve the value of the lookup field
// If it is null, then return an empty string
var lkupfield = crmForm.all.[LookupFieldName].DataValue;
if (lkupfield[0] != null) {
var lkupvalue = lkupfield[0].name;
}
else
{
var lkupvalue = "";
}
// Return the value for the Picklist item
var picklistvalue = crmForm.all.[PickListFieldName].SelectedText;
// Concatenate the two values into a third value - a varchar attribute
crmForm.all.[VarCharFieldName].DataValue = lkupvalue + " " + picklistvalue;
}
[/code]
Yes, this can be done using client-side javascript.
First, you'll need to open the form for the record that you want to update:
* Select Settings Customization Customize Entities
* Choose the entity you want this customization to work for 00
* Double Click Click the Forms and Views item
* Double Click on the Form.
The two fields you want to use to create the third field, and the third field itself, should be on the same form. If not, add them to the form.
* Update the OnSave Event: Click Form Properties (right-side of the form, bottom)
* Select OnSave and then Edit Make sure you check "Event is enabled"
Use the sample code below as a starting point to develop your code. Once it is complete, click Save and Close then Publish.
[code]
if (crmForm.all.[VarCharFieldName].DataValue == null) {
// Retrieve the value of the lookup field
// If it is null, then return an empty string
var lkupfield = crmForm.all.[LookupFieldName].DataValue;
if (lkupfield[0] != null) {
var lkupvalue = lkupfield[0].name;
}
else
{
var lkupvalue = "";
}
// Return the value for the Picklist item
var picklistvalue = crmForm.all.[PickListFieldName].SelectedText;
// Concatenate the two values into a third value - a varchar attribute
crmForm.all.[VarCharFieldName].DataValue = lkupvalue + " " + picklistvalue;
}
[/code]
CRM: Changing Checkbox Execution
A client recently asked: When I click a checkbox on a form, an action associated with the checkbox is not executed until I leave the checkbox. Can this be changed to execute immediately upon checking the box?
Every CRM Form field has an OnChange event associated with it that allows you to execute JavaScript when the user changes the value of the attribute. This event is fired when you change the attribute’s value and you leave the field – by clicking or using the Tab key. In certain instances, bit attributes on the CRM Form which are formatted to display as a Checkbox.
When an action is associated with a checkbox, it would be better for the JavaScript (added to the OnChange event) to be executed immediately when clicking the checkbox and changing the value, not when the user leaves the field.
Using the following code, you can add that functionality:
function ClickMe(){ crmForm.all.new_checkboxfield.FireOnChange();} crmForm.all.new_checkboxfield.attachEvent('onclick',ClickMe, false);
Here is how it works
In the Form’s OnLoad event:
* Create a small JavaScript function called ClickMe that does nothing more than call the OnChange event for the bit attribute we’re working with.
* Use the JavaScript function attachEvent to attach the ClickMe function to the onclick event of the attribute.
Once this code is published, any time the user clicks the checkbox, it will execute the OnChange code for the attribute.
Every CRM Form field has an OnChange event associated with it that allows you to execute JavaScript when the user changes the value of the attribute. This event is fired when you change the attribute’s value and you leave the field – by clicking or using the Tab key. In certain instances, bit attributes on the CRM Form which are formatted to display as a Checkbox.
When an action is associated with a checkbox, it would be better for the JavaScript (added to the OnChange event) to be executed immediately when clicking the checkbox and changing the value, not when the user leaves the field.
Using the following code, you can add that functionality:
function ClickMe(){ crmForm.all.new_checkboxfield.FireOnChange();} crmForm.all.new_checkboxfield.attachEvent('onclick',ClickMe, false);
Here is how it works
In the Form’s OnLoad event:
* Create a small JavaScript function called ClickMe that does nothing more than call the OnChange event for the bit attribute we’re working with.
* Use the JavaScript function attachEvent to attach the ClickMe function to the onclick event of the attribute.
Once this code is published, any time the user clicks the checkbox, it will execute the OnChange code for the attribute.
CRM: OnSave Issues
A client recently had this problem: When converting an opportunity to an order, the opportunity was saved despite the fact that custom validations were not completed. What can be done to prevent this?
When working with a customer project, it is often necessary to control the OnSave event. Despite adding logic to prevent the user from saving if certain form conditions exist, the opportunity was saved anyway. When tested, however, we found that Save events were coming from a variety of places and overriding the logic we had put in place.
In the Opportunity Entity, there are several related entities connected to the entity. Clicking on the Orders link on the Opportunity allows you to review or create new associated Orders.
When you click the New Order button on the Orders pane, one of the first actions it will perform is to save the parent Opportunity with an internal call that looks something like this:
crmForm.SubmitCrmForm(21, true, true, false);
This system-originated save caused a problem in our solution because we had hidden the Save and Save and Close buttons until our approval processes had been completed. This system originated save broke our validation processes.
If you review the OnSave event documentation on MSDN, you’ll see that you can actually determine from where the save originated. The property event.Mode will contain the origination point for the save activity.
In our case, we only wanted the OnSave event to fire if the event.Mode was 1 ( Save ), or 2 ( Save and Close ). The code looks like this: if (event.Mode == 1 event.Mode == 2){}
Finally, if it turns out that you need to watch for a specific save event, the first parameter of the crmForm.SubmitCrmForm will contain value that will be used to set the event.Mode.
As you will see in the SDK documentation, you can actually stop the save from occurring, using the following code:
event.returnValue = false;
This is useful should you need to inspect the form state and only allow the process to complete if all checks pass.
When working with a customer project, it is often necessary to control the OnSave event. Despite adding logic to prevent the user from saving if certain form conditions exist, the opportunity was saved anyway. When tested, however, we found that Save events were coming from a variety of places and overriding the logic we had put in place.
In the Opportunity Entity, there are several related entities connected to the entity. Clicking on the Orders link on the Opportunity allows you to review or create new associated Orders.
When you click the New Order button on the Orders pane, one of the first actions it will perform is to save the parent Opportunity with an internal call that looks something like this:
crmForm.SubmitCrmForm(21, true, true, false);
This system-originated save caused a problem in our solution because we had hidden the Save and Save and Close buttons until our approval processes had been completed. This system originated save broke our validation processes.
If you review the OnSave event documentation on MSDN, you’ll see that you can actually determine from where the save originated. The property event.Mode will contain the origination point for the save activity.
In our case, we only wanted the OnSave event to fire if the event.Mode was 1 ( Save ), or 2 ( Save and Close ). The code looks like this: if (event.Mode == 1 event.Mode == 2){}
Finally, if it turns out that you need to watch for a specific save event, the first parameter of the crmForm.SubmitCrmForm will contain value that will be used to set the event.Mode.
As you will see in the SDK documentation, you can actually stop the save from occurring, using the following code:
event.returnValue = false;
This is useful should you need to inspect the form state and only allow the process to complete if all checks pass.
CRM: Calculating Field Values
A client asked : Is there an easy way to calculate the value of a field based on the value of two other fields on the form?
Yes, a simple way to calculate values is by using JavaScript. The value of the field is updated whenever the value of one of the two other fields is changed. For example, use the Opportunity form and add a custom field that shows the total value of the opportunity after discount.
Use the following fields for reference in the example:
Field1 (Custom attribute) = Total_Estimated_Value
Field2 (Custom attribute) = Total_Discount
Field3 (Standard attribute)= Est. Booking (estimatedvalue)
Steps to implement the change: Create the two custom attributes that you need by going to:
Settings > Customization > Customize Entities > open Opportunity
Select Attributes > Create two money attributes > Save the changes.
Open the Opportunity form and add the two fields to the form.
Double-click Est Booking Value (estimatedvalue) on the form
Add the following line of script to OnChange event of the field:
crmForm.all. Total_Estimated_Value.FireOnChange();
Open the Total_Discount field on the form
Add the same script to the OnChange event of the field crmForm.all.Total_Estimated_Value.FireOnChange();
Open the Total_Estimated_Value field on the form
Add the following script to the OnChange event of the field crmForm.all.Total_Estimated_Value.DataValue=crmForm.all.estimatedvalue.DataValue - crmForm.all.Total_Discount.DataValue
Save your changes and test using the Create or Update Form feature from the Customization Tool. The end result will be that whenever the Total_Discount or Est. Booking field is updated the Total_Estimated_Value will update by subtracting the Total_Discount from the Est. Booking amount.
Please note that this example assumes there is always a value in the Est. Booking and Discount field when one of those fields are updated. If there is no value (null), then you will have to set the value to 0 (zero). Checking and setting the default value of the fields to 0 can be done in the Form properties OnLoad event.
Yes, a simple way to calculate values is by using JavaScript. The value of the field is updated whenever the value of one of the two other fields is changed. For example, use the Opportunity form and add a custom field that shows the total value of the opportunity after discount.
Use the following fields for reference in the example:
Field1 (Custom attribute) = Total_Estimated_Value
Field2 (Custom attribute) = Total_Discount
Field3 (Standard attribute)= Est. Booking (estimatedvalue)
Steps to implement the change: Create the two custom attributes that you need by going to:
Settings > Customization > Customize Entities > open Opportunity
Select Attributes > Create two money attributes > Save the changes.
Open the Opportunity form and add the two fields to the form.
Double-click Est Booking Value (estimatedvalue) on the form
Add the following line of script to OnChange event of the field:
crmForm.all. Total_Estimated_Value.FireOnChange();
Open the Total_Discount field on the form
Add the same script to the OnChange event of the field crmForm.all.Total_Estimated_Value.FireOnChange();
Open the Total_Estimated_Value field on the form
Add the following script to the OnChange event of the field crmForm.all.Total_Estimated_Value.DataValue=crmForm.all.estimatedvalue.DataValue - crmForm.all.Total_Discount.DataValue
Save your changes and test using the Create or Update Form feature from the Customization Tool. The end result will be that whenever the Total_Discount or Est. Booking field is updated the Total_Estimated_Value will update by subtracting the Total_Discount from the Est. Booking amount.
Please note that this example assumes there is always a value in the Est. Booking and Discount field when one of those fields are updated. If there is no value (null), then you will have to set the value to 0 (zero). Checking and setting the default value of the fields to 0 can be done in the Form properties OnLoad event.
CRM: Unsatisfied Customers
A client asked: We would like a way to quickly identify unsatisfied customers in the Account grid view. Is it possible to change the color of the customer name to Red?
Yes, it is possible but difficult. The only clean solution would be an asp.net page using an external customizable grid and then inserting your new page with the color applied to account name.
A better solution would be to create a picklist (which would allow a greater number of choices than a checkbox) with Satisfied, Unsatisfied and Very Unsatisfied.
From this picklist, create a view to show all unsatisfied accounts (which is better than scrolling the entire list to find the Red accounts). They are all in one place and can be easily reviewed and contacted. This picklist could also be used to create a campaign for Satisfied customers or sending an email to all unsatisfied accounts.
The details of creating the picklist and view are below:
PickList:
Select Settings > Customization > Customize Entities
Select Attributes > Click New > Name the Attribute
Select Type (Picklist) > Add Picklist values > Save and Close
Select Account > Select Forms and Views > Main Application Form
Select Add Field > Add the new attribute > Save and Close
Select the Advanced Find View > Add attribute using Add Columns
Save and Close > Publish the Customization
This will add a new field to the Main Form that can be selected by users to indicate the satisfaction level of the customer. To make sure the field is used, you can make it Required.
View:
Open the CRM Web client > Select Accounts
Open Accounts > Select Satisfaction Level > Save and Close
Click Advanced Find > Select Account > Clear Details
Click Select > Select Satisfaction field > Select Values to view
Click Save As > Provide a Name for the View
Yes, it is possible but difficult. The only clean solution would be an asp.net page using an external customizable grid and then inserting your new page with the color applied to account name.
A better solution would be to create a picklist (which would allow a greater number of choices than a checkbox) with Satisfied, Unsatisfied and Very Unsatisfied.
From this picklist, create a view to show all unsatisfied accounts (which is better than scrolling the entire list to find the Red accounts). They are all in one place and can be easily reviewed and contacted. This picklist could also be used to create a campaign for Satisfied customers or sending an email to all unsatisfied accounts.
The details of creating the picklist and view are below:
PickList:
Select Settings > Customization > Customize Entities
Select Attributes > Click New > Name the Attribute
Select Type (Picklist) > Add Picklist values > Save and Close
Select Account > Select Forms and Views > Main Application Form
Select Add Field > Add the new attribute > Save and Close
Select the Advanced Find View > Add attribute using Add Columns
Save and Close > Publish the Customization
This will add a new field to the Main Form that can be selected by users to indicate the satisfaction level of the customer. To make sure the field is used, you can make it Required.
View:
Open the CRM Web client > Select Accounts
Open Accounts > Select Satisfaction Level > Save and Close
Click Advanced Find > Select Account > Clear Details
Click Select > Select Satisfaction field > Select Values to view
Click Save As > Provide a Name for the View
Monday, October 26, 2009
HEAT: Recover From An Edit Set Crash
A client asked: We had a crash during the commit of an edit set. How do we clear the error message?
When an edit set crashes, performing a LoadDB from your backup may not work because a data flag inside a system table of HEAT is blocking you from loading. It's there to block others from logging into HEAT until the commit is finished.
However, when a commit crashes and the flag data is still inside the field, this field must be cleared if you want to reload your .haf file or open Call Logging to get back to normal.
To clear it, have a database administrator go to the HEATDB table and find the field DBStatus and clear its contents. It should have the same wording as the error message:
"Building Commit files - started at 07:07:07 by Admin"
Once this message is cleared from DBStatus, it's okay to reload your .haf file.
When an edit set crashes, performing a LoadDB from your backup may not work because a data flag inside a system table of HEAT is blocking you from loading. It's there to block others from logging into HEAT until the commit is finished.
However, when a commit crashes and the flag data is still inside the field, this field must be cleared if you want to reload your .haf file or open Call Logging to get back to normal.
To clear it, have a database administrator go to the HEATDB table and find the field DBStatus and clear its contents. It should have the same wording as the error message:
"Building Commit files - started at 07:07:07 by Admin"
Once this message is cleared from DBStatus, it's okay to reload your .haf file.
HEAT: Change Call History Grid
A client asked: How can we add, change or remove fields in the grid order when we click on the Call History button?
First, you should know what order you would like your fields to be in and be sure any pending Edit Sets have been committed. Then use the following steps to change the Call History grid.
* Open the Administrator tool > Start a Quick Customize.
* Highlight the CallLog table > Select the table type > Click Open Grid.
* In the next window move fields from the left and right sides.
* When you are finished arranging fields Apply the Quick Customize
* Log into Call Logging.
Now when you go to a customer's Call History you will see your new grid order.
First, you should know what order you would like your fields to be in and be sure any pending Edit Sets have been committed. Then use the following steps to change the Call History grid.
* Open the Administrator tool > Start a Quick Customize.
* Highlight the CallLog table > Select the table type > Click Open Grid.
* In the next window move fields from the left and right sides.
* When you are finished arranging fields Apply the Quick Customize
* Log into Call Logging.
Now when you go to a customer's Call History you will see your new grid order.
HEAT: Automate HSS Login
A client asked: Is there a way to automatically enable an HSS login for a new employee instead of going into their Profile and adding the information?
There are many ways to enable a login for HEAT Self Service (HSS). One way is to run an SQL script that will do the work for you. Another way is to use the tools offered in Call Logging and BPAM.
Before discussing automatically creating a HSS login, I will review the manual method:
* From a profile record, click on View > Customer Login Information
* Click Enable Login and give that user the same CustID for a login.
* A corresponding record is created inside a table called HEAT CAI
* A few fields are filled in and HEAT maintains the link
To have HEAT create an HSS login automatically, do the following:
* Create an AutoTask that will use the Create Customer Login action.
* The autotask will run whenever a specific profile record is targeted.
* Create a BPAM rule using When a Customer Profile is Created template
* Have the rule run the AutoTask.
Now when a profile is created, BPAM will automatically enable a login for that user.
There are many ways to enable a login for HEAT Self Service (HSS). One way is to run an SQL script that will do the work for you. Another way is to use the tools offered in Call Logging and BPAM.
Before discussing automatically creating a HSS login, I will review the manual method:
* From a profile record, click on View > Customer Login Information
* Click Enable Login and give that user the same CustID for a login.
* A corresponding record is created inside a table called HEAT CAI
* A few fields are filled in and HEAT maintains the link
To have HEAT create an HSS login automatically, do the following:
* Create an AutoTask that will use the Create Customer Login action.
* The autotask will run whenever a specific profile record is targeted.
* Create a BPAM rule using When a Customer Profile is Created template
* Have the rule run the AutoTask.
Now when a profile is created, BPAM will automatically enable a login for that user.
HEAT: Using Counters For Customer IDs
A client asked: What is a counter used for in HEAT?
A counter is a function in HEAT that provides you with a way to generate a specific unique number that can never be reused. For example, you may want to generate a specific asset tag or new Customer IDs. You can set up a counter to start at any number and then whenever a HEAT user generates another number the counter provides the next available number
To define a counter, follow these steps:
Start the Administrator > Click on Defaults > Counter Setup.
Click Add and a new window appears.
Give the counter a Name, a starting Value and select the Increment.
The increment can be 1, 2, or any number.
If you would like to have the counter reset itself, check the box next to "Should the counter automatically reset itself?" and then select How Often and enter the Initial value after reset.
Now set up an AutoTask using an Update Call action that will call the counter and write to any field that supports the size and type of the counter value. Right-click on the field, Insert Counter, then choose the Counter you created.
This can work for almost anything including generating new Customer IDs in the Profile table.
A counter is a function in HEAT that provides you with a way to generate a specific unique number that can never be reused. For example, you may want to generate a specific asset tag or new Customer IDs. You can set up a counter to start at any number and then whenever a HEAT user generates another number the counter provides the next available number
To define a counter, follow these steps:
Start the Administrator > Click on Defaults > Counter Setup.
Click Add and a new window appears.
Give the counter a Name, a starting Value and select the Increment.
The increment can be 1, 2, or any number.
If you would like to have the counter reset itself, check the box next to "Should the counter automatically reset itself?" and then select How Often and enter the Initial value after reset.
Now set up an AutoTask using an Update Call action that will call the counter and write to any field that supports the size and type of the counter value. Right-click on the field, Insert Counter, then choose the Counter you created.
This can work for almost anything including generating new Customer IDs in the Profile table.
HEAT: Change Label Colors
A client wants to know: How can I change the color of a field label based on the contents of the field?
You would need to use the Administrator module and create an Edit Set. If the field is, for example, Call Status you would do the following:
* Select the CallLog table > Open Form > Select the Status label
* Right click, pick Attributes > Click Advanced > Connect to Call Status
* Right click and select Color > Select Advanced Color
* Add the Status value and related color (e.g. Red equals Open)
Commit the edit set and open Call Logging. The field label should now change color based on the entries in Advanced Color.
You would need to use the Administrator module and create an Edit Set. If the field is, for example, Call Status you would do the following:
* Select the CallLog table > Open Form > Select the Status label
* Right click, pick Attributes > Click Advanced > Connect to Call Status
* Right click and select Color > Select Advanced Color
* Add the Status value and related color (e.g. Red equals Open)
Commit the edit set and open Call Logging. The field label should now change color based on the entries in Advanced Color.
GoldMine: Side By Side Contacts
A client recently asked: Is there a way to compare two contacts without having to alternate back and forth between the two contacts?
By default, GoldMine displays only one contact at a time. But, you can program it to open other contact records simultaneously.
Here is the method:
* Select Window > New Contact Window > Opens the contact list.
* From the contact list, select the second contact you wish to see
* Double-click the second contact > This opens the second contact window.
* You will see a second record tab at the top of your screen.
* Select Window > Tile horizontally for a side by side view
* For a Vista-style view > Select Cascade Windows
This method will save time by eliminating the need to alternate back and forth between two records.
By default, GoldMine displays only one contact at a time. But, you can program it to open other contact records simultaneously.
Here is the method:
* Select Window > New Contact Window > Opens the contact list.
* From the contact list, select the second contact you wish to see
* Double-click the second contact > This opens the second contact window.
* You will see a second record tab at the top of your screen.
* Select Window > Tile horizontally for a side by side view
* For a Vista-style view > Select Cascade Windows
This method will save time by eliminating the need to alternate back and forth between two records.
GoldMine: Convert Email To Case
A client asked: Is there a way to convert an incoming email into a Service Center case?
Yes, you can do the following to add to a New Case or link to an Existing Case:
* Click the Oppty/Proj button on the email
* Select the option to create a New Case
* The email will be linked to the Case.
For an Existing Case, you can link the Email to an Existing Case using the same option
Yes, you can do the following to add to a New Case or link to an Existing Case:
* Click the Oppty/Proj button on the email
* Select the option to create a New Case
* The email will be linked to the Case.
For an Existing Case, you can link the Email to an Existing Case using the same option
GoldMine: Clearing Out The Inbox
A client wanted to know: I have thousands of email in an Inbox. Is there an easy way to clear them out?
There are a number of ways. If you have the latest version of GoldMine (8.5.1.12), then you can clear out an inbox when deleting a user by selecting "Delete the user's pending activities and unfiled e-mails."
If you are not clearing the Inbox in conjunction with deleting a user, then you can use Goldbox or use the following queries:
First, backup your SQL Database. The following examples assume that the user's name is "DAVE", and that your database is named "Goldmine".
From the SQL Server Management Studio run the following query:
Select * from goldmine.dbo.mailbox where Folder = 'X-GM-INBOX' and userid = 'DAVE'
If you like the results, then run:
Delete from goldmine.dbo.mailbox where Folder='X-GM-INBOX' and userid = 'DAVE'
Then you can clear out the CAL table:
Select * from goldmine.dbo.cal where rectype = 'M' and userid = 'DAVE'
Just be sure to backup your database before doing any of this. Note: if you are using the Goldmine Sync functions, Goldbox is sync-aware and would the best choice.
There are a number of ways. If you have the latest version of GoldMine (8.5.1.12), then you can clear out an inbox when deleting a user by selecting "Delete the user's pending activities and unfiled e-mails."
If you are not clearing the Inbox in conjunction with deleting a user, then you can use Goldbox or use the following queries:
First, backup your SQL Database. The following examples assume that the user's name is "DAVE", and that your database is named "Goldmine".
From the SQL Server Management Studio run the following query:
Select * from goldmine.dbo.mailbox where Folder = 'X-GM-INBOX' and userid = 'DAVE'
If you like the results, then run:
Delete from goldmine.dbo.mailbox where Folder='X-GM-INBOX' and userid = 'DAVE'
Then you can clear out the CAL table:
Select * from goldmine.dbo.cal where rectype = 'M' and userid = 'DAVE'
Just be sure to backup your database before doing any of this. Note: if you are using the Goldmine Sync functions, Goldbox is sync-aware and would the best choice.
Crystal Reports : Rotating Text
Often, when creating a report with graphs or indeed embedded images, we may want to change the direction or rotation of a field.
1. Right-click on the field in question within the Crystal Designer.
2. Select "Format Text" or "Format Field" (depending on the field type).
3. In the middle of the first formatting page, select a different Rotation. (i.e. 90 or 270).
1. Right-click on the field in question within the Crystal Designer.
2. Select "Format Text" or "Format Field" (depending on the field type).
3. In the middle of the first formatting page, select a different Rotation. (i.e. 90 or 270).
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.
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 : Dynamic Parameters
Note : Only applies to Crystal Reports XI or higher.
A Dynamic Parameter is one that "refreshes" it's values list every time the report prompts the user for parameter input. This can be very handy, especially in the instance of our user table, which sees a lot of updates as turnover has been high lately.
When creating your parameter, select Dynamic List of Values.
You may then select a fieldname to use as a list source, which in our case would be something like {USERS.USERNAME}.
ProTip: You may also use a pure SQL query to specify the values list.
A Dynamic Parameter is one that "refreshes" it's values list every time the report prompts the user for parameter input. This can be very handy, especially in the instance of our user table, which sees a lot of updates as turnover has been high lately.
When creating your parameter, select Dynamic List of Values.
You may then select a fieldname to use as a list source, which in our case would be something like {USERS.USERNAME}.
ProTip: You may also use a pure SQL query to specify the values list.
Crystal Reports : Caldulating the Week "Number"
Most reports use some kind of date math or date grouping (i.e dollars per month, week, etc).
Let us suppose we need to return the week "number" within any given year based upon a simple date. (Our fieldname will be {SALES.SALEDATE})
The DATEPART() function is what we need here:
DATEPART("ww", {SALES.SALEDATE}, crSunday)
1. The "ww" specifies that we want the "week number" returned.
2. {SALES.SALEDATE} is the date field to evaluate.
3. The final parameter, crSunday, specifies on what day the week starts.
Let us suppose we need to return the week "number" within any given year based upon a simple date. (Our fieldname will be {SALES.SALEDATE})
The DATEPART() function is what we need here:
DATEPART("ww", {SALES.SALEDATE}, crSunday)
1. The "ww" specifies that we want the "week number" returned.
2. {SALES.SALEDATE} is the date field to evaluate.
3. The final parameter, crSunday, specifies on what day the week starts.
GoldMine: Changing Zip Label
A client asked: I need to change a field label color (Zip) if the field is empty and if another field (Country) is either 'UK' or empty.
You can do this a number of ways. Below is an expression to do this:
* Right click and select Screen Design
* Double-click on the Zip field > Select the Color tab
* Enter the following in the (Label) Expression area:
IIF(Empty(Contact1->Zip).AND.(Contact1->Country="UK".OR.Empty(Contact1->Country)), 255, 0)
This will turn the Zip field to Red when the conditions in the expression are met.
You can do this a number of ways. Below is an expression to do this:
* Right click and select Screen Design
* Double-click on the Zip field > Select the Color tab
* Enter the following in the (Label) Expression area:
IIF(Empty(Contact1->Zip).AND.(Contact1->Country="UK".OR.Empty(Contact1->Country)), 255, 0)
This will turn the Zip field to Red when the conditions in the expression are met.
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:
ISNULL({USERS.CLASS1}) = TRUE XOR ISNULL({USERS.CLASS2}
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:
ISNULL({USERS.CLASS1}) = TRUE XOR ISNULL({USERS.CLASS2}
GoldMine: Finding Contacts With an Email Address
A client recently asked: II have a query that finds contacts without emails but I need to find all contacts with emails. How do I set up the query?
There are a few ways, depending on the version of Goldmine. I have found the queries below to be accurate:
For Goldmine Premium 8.5, take the query below for No Email and convert the "NOT IN" to "IN"
SELECT C1.CONTACT, C1.COMPANY, C1.ADDRESS1, C1.ADDRESS2, C1.CITY, C1.STATE, C1.ZIP, C1.PHONE1, C1.PHONE2, C1.PHONE3 FROM CONTACT1 AS C1 WHERE C1.ACCOUNTNO NOT IN (SELECT DISTINCT ACCOUNTNO FROM CONTSUPP WHERE(CONTSUPP.RECTYPE = 'P' AND CONTSUPP.CONTACT = 'E-MAIL ADDRESS')) ORDER BY C1.CONTACT
Query where Contact has an Email Address:
SELECT C1.CONTACT, C1.COMPANY, C1.ADDRESS1, C1.ADDRESS2, C1.CITY, C1.STATE, C1.ZIP, C1.PHONE1, C1.PHONE2, C1.PHONE3 FROM CONTACT1 AS C1 WHERE C1.ACCOUNTNO IN (SELECT DISTINCT ACCOUNTNO FROM CONTSUPP WHERE (CONTSUPP.RECTYPE = 'P' AND CONTSUPP.CONTACT = 'E-MAIL ADDRESS')) ORDER BY C1.CONTACT
If you are using an older version of Goldmine, this query works well:
Select distinct Contact1.AccountNo, Contact1.Company, Contact1.Contact, ContSupp.ContSupRef+ContSupp.Address1 as Email
from Contact1,ContSupp where Contact1.AccountNo=ContSupp.AccountNo and ContSupp.Contact='E-mail Address' and ContSupp.Zip like '_1__'
There are a few ways, depending on the version of Goldmine. I have found the queries below to be accurate:
For Goldmine Premium 8.5, take the query below for No Email and convert the "NOT IN" to "IN"
SELECT C1.CONTACT, C1.COMPANY, C1.ADDRESS1, C1.ADDRESS2, C1.CITY, C1.STATE, C1.ZIP, C1.PHONE1, C1.PHONE2, C1.PHONE3 FROM CONTACT1 AS C1 WHERE C1.ACCOUNTNO NOT IN (SELECT DISTINCT ACCOUNTNO FROM CONTSUPP WHERE(CONTSUPP.RECTYPE = 'P' AND CONTSUPP.CONTACT = 'E-MAIL ADDRESS')) ORDER BY C1.CONTACT
Query where Contact has an Email Address:
SELECT C1.CONTACT, C1.COMPANY, C1.ADDRESS1, C1.ADDRESS2, C1.CITY, C1.STATE, C1.ZIP, C1.PHONE1, C1.PHONE2, C1.PHONE3 FROM CONTACT1 AS C1 WHERE C1.ACCOUNTNO IN (SELECT DISTINCT ACCOUNTNO FROM CONTSUPP WHERE (CONTSUPP.RECTYPE = 'P' AND CONTSUPP.CONTACT = 'E-MAIL ADDRESS')) ORDER BY C1.CONTACT
If you are using an older version of Goldmine, this query works well:
Select distinct Contact1.AccountNo, Contact1.Company, Contact1.Contact, ContSupp.ContSupRef+ContSupp.Address1 as Email
from Contact1,ContSupp where Contact1.AccountNo=ContSupp.AccountNo and ContSupp.Contact='E-mail Address' and ContSupp.Zip like '_1__'
GoldMine: Finding Referrals
A client asked: Is there a query that will show all referrals entered in Goldmine?
Yes, this can be done within GoldMine:
First, select the SQL Query
Enter the following:
Select * from ContSupp where RecType = 'R'
This will return two records for each referral (Referred To / Referred From) so the order of the query results are important. You can use any of the following depending on your reason for finding the referrals:
Order by Contact field; by the Contsupref field; or by the Accountno field.
The query would then look like the following:
Select * from ContSupp where RecType = 'R'
Order by Contact
Yes, this can be done within GoldMine:
First, select the SQL Query
Enter the following:
Select * from ContSupp where RecType = 'R'
This will return two records for each referral (Referred To / Referred From) so the order of the query results are important. You can use any of the following depending on your reason for finding the referrals:
Order by Contact field; by the Contsupref field; or by the Accountno field.
The query would then look like the following:
Select * from ContSupp where RecType = 'R'
Order by Contact
GoldMine: Revise Phone Number
A client asked: I have a number of records that were incorrectly created with the US phone format. How can I globally correct these records?
I have a number of records that were incorrectly created with the US phone format. How can I globally correct these records?
First, backup your database. Then do the following:* Create a group of the records with the non-numeric characters
* Select Tools > Data Management > Global Replace
* Select Update a field using advanced options
* Select the Phone1 field as the Update Field
* Select Evaluate Value as dBASE expression
* Enter the following the Expression field
StrTran(Contact1->Phone1, [(], [])
* Select Next > Next > Select your Group
* Select Finish
This expression will remove the left parentheses. You will need to repeat this process for the other characters - change [(] to [)] and then to [-] to remove all non-numeric characters for the phone number
Note: If you have GoldBox, you can use PhoneStrip("Contact1->Phone1") to get all characters with one pass.
I have a number of records that were incorrectly created with the US phone format. How can I globally correct these records?
First, backup your database. Then do the following:* Create a group of the records with the non-numeric characters
* Select Tools > Data Management > Global Replace
* Select Update a field using advanced options
* Select the Phone1 field as the Update Field
* Select Evaluate Value as dBASE expression
* Enter the following the Expression field
StrTran(Contact1->Phone1, [(], [])
* Select Next > Next > Select your Group
* Select Finish
This expression will remove the left parentheses. You will need to repeat this process for the other characters - change [(] to [)] and then to [-] to remove all non-numeric characters for the phone number
Note: If you have GoldBox, you can use PhoneStrip("Contact1->Phone1") to get all characters with one pass.
Friday, October 2, 2009
CRM: About IFRAMES
A client asked: Why would I get an error message when I load a form with an IFRAME. The message is "Do you want to view only the webpage content that was delivered securely?"
This error is caused by using the very common method of defaulting the url to "about:blank" for your IFRAME and then using javascript to set the actual url. This works well until you start serving CRM over HTTPS. Then you get this security warning.
The problem here is that when your CRM form it is being served over HTTPS. The CRM form (e.g. Account) loads and the IFRAME that calls “about:blank” loads in a different Internet Explorer Zone. Most likely CRM is loading under the Trusted Site zone and About:Blank is loading in Restricted Sites.
The workaround is to use a better practice. Instead of using about:blank when you default your IFRAME to nothing, use /_static/blank.htm. The path is relative so it will work whether your CRM is hosted as https: or http:
This practice also conforms with current security settings and the heightened concern about viruses and malware.
This error is caused by using the very common method of defaulting the url to "about:blank" for your IFRAME and then using javascript to set the actual url. This works well until you start serving CRM over HTTPS. Then you get this security warning.
The problem here is that when your CRM form it is being served over HTTPS. The CRM form (e.g. Account) loads and the IFRAME that calls “about:blank” loads in a different Internet Explorer Zone. Most likely CRM is loading under the Trusted Site zone and About:Blank is loading in Restricted Sites.
The workaround is to use a better practice. Instead of using about:blank when you default your IFRAME to nothing, use /_static/blank.htm. The path is relative so it will work whether your CRM is hosted as https: or http:
This practice also conforms with current security settings and the heightened concern about viruses and malware.
CRM: Get Values From the Parent
A client recently asked: Is it possible to create an order from within the Account and add the Account to the order automatically?
Yes, it is not uncommon to pull data for from the parent entity of the current record. In many cases, this child entity is only created from its parent so we can reference the object to get to the parent form.
An example is creating a new Order from inside the Account form. The solution below is dynamic and can be called from the Form OnLoad event.
Yes, it is not uncommon to pull data for from the parent entity of the current record. In many cases, this child entity is only created from its parent so we can reference the object to get to the parent form.
An example is creating a new Order from inside the Account form. The solution below is dynamic and can be called from the Form OnLoad event.
function GetParentFormFieldValue(fieldName)
{
var retVar = null;
if (
(window.opener != null) &&
(window.opener.parent != null) &&
(window.opener.parent.document != null) &&
(window.opener.parent.document.crmForm != null)
)
{
eval("retVar = window.opener.parent.document.crmForm.all." + fieldName + ".DataValue");
}
return retVar;
}
To use this code, add the following:
crmForm.all.name.DataValue = "New order for " + GetParentFormFieldValue("name");
CRM: Customization Testing 101
A client asked: We are making a lot of our own custom changes to CRM. Should we have a process in place to test these changes before they reach the end-users?
Yes, since CRM is very easy to customize, there are a lot of end-users making their own changes (including javascript) without any testing before the customized CRM is placed in production.
Basic Testing
Ideally you should have a production CRM database and a development CRM database. You develop customizations on the development database and once your testing is complete, you move them (using export customizations) to the production database.
Test Users
You should have at least one test user for each role in which normal users exist (normal being non-CRM Administrators). This will allow you to test the functionality of your customizations and custom solutions as each type of user.
While this may seem like a lot of extra work to you, keep in mind that CRM changes the environment and the user interface based on the user’s security. This means that sometimes users will not see things as you expect them to or they will have permissions issues where you do not.
How to Test
Prior to Windows Vista and Server 2008, you could simply right-click on the Internet Explorer icon, select Run As and supply the credentials of the test user you want to use. That changes with the new environments and does not work.
A possible work-around is using the ShellRunAs commandlet (which can be downloaded) or should nothing else work, you can just Switch Users and log into the machine as each test user.
What to Test
Here are the usual items for testing customizations within CRM:
* The Site Map ( left-hand navigation )
* ISV.Config ( buttons and menus )
* JavaScript ( does your custom JavaScript work with all users )
* Custom Solutions ( ASP.NET code written and added to CRM )
* Processes. If you have a process that moves data through the system, test it from start to finish as the particular user or users who actually perform the work to make sure you’re covering the whole process as a “normal” user and experiencing what they experience.
Summary
This is the very least amount of testing you need to perform on a customized system. You can get as comprehensive and complex as you like as your testing needs increase.
If you will document your testing procedures and repeat those steps each time a change is made, you should be able to identify problems and create solutions before your changes reach the end-users.
Yes, since CRM is very easy to customize, there are a lot of end-users making their own changes (including javascript) without any testing before the customized CRM is placed in production.
Basic Testing
Ideally you should have a production CRM database and a development CRM database. You develop customizations on the development database and once your testing is complete, you move them (using export customizations) to the production database.
Test Users
You should have at least one test user for each role in which normal users exist (normal being non-CRM Administrators). This will allow you to test the functionality of your customizations and custom solutions as each type of user.
While this may seem like a lot of extra work to you, keep in mind that CRM changes the environment and the user interface based on the user’s security. This means that sometimes users will not see things as you expect them to or they will have permissions issues where you do not.
How to Test
Prior to Windows Vista and Server 2008, you could simply right-click on the Internet Explorer icon, select Run As and supply the credentials of the test user you want to use. That changes with the new environments and does not work.
A possible work-around is using the ShellRunAs commandlet (which can be downloaded) or should nothing else work, you can just Switch Users and log into the machine as each test user.
What to Test
Here are the usual items for testing customizations within CRM:
* The Site Map ( left-hand navigation )
* ISV.Config ( buttons and menus )
* JavaScript ( does your custom JavaScript work with all users )
* Custom Solutions ( ASP.NET code written and added to CRM )
* Processes. If you have a process that moves data through the system, test it from start to finish as the particular user or users who actually perform the work to make sure you’re covering the whole process as a “normal” user and experiencing what they experience.
Summary
This is the very least amount of testing you need to perform on a customized system. You can get as comprehensive and complex as you like as your testing needs increase.
If you will document your testing procedures and repeat those steps each time a change is made, you should be able to identify problems and create solutions before your changes reach the end-users.
Thursday, October 1, 2009
CRM: Creative Quick Search
A client asked: We like the quick search for finding account numbers or addresses without a company, but is there a way that the quick search will look in both active and inactive accounts?
Yes, but since CRM 4.0 does not have a method for including inactive records and the customization window does not allow modifying the filter criteria of the view, you need to use a workaround:
Click on Settings > Customization > Export Customizations.
Select Account and click on Export Selected Customizations.
Click OK to the warning window.
Click Save on the File Download window. Save the file on your desktop.
Extract the Customizations.zip file and then open the Customizations.xml file in NotePad.
Search for “Quick Find Active.”
Remove the filter condition shown below (it is separated from the other code).
- <fetchxml>
- <fetch version="1.0" output-format="xml-platform" mapping="logical">
- <entity name="account">
<attribute name="name" />
<attribute name="accountnumber" />
<attribute name="primarycontactid" />
<attribute name="address1_city" />
<attribute name="telephone1" />
<attribute name="emailaddress1" />
<attribute name="accountid" />
<order attribute="name" descending="false" />
- <filter type="and">
<condition attribute="statecode" operator="eq" value="0" />
</filter>
- <filter type="or" isquickfindfields="1">
<condition attribute="name" operator="like" value="{0}" />
<condition attribute="accountnumber" operator="like" value="{0}" />
<condition attribute="emailaddress1" operator="like" value="{0}" />
</filter>
</entity>
</fetch>
</fetchxml>
- <LocalizedNames>
<LocalizedName description="Quick Find Active Accounts" languagecode="1033" />
</LocalizedNames>
</savedquery>
- <savedquery>
<iscustomizable name="No">0</iscustomizable>
<isquickfindquery name="No">0</isquickfindquery>
<isuserdefined name="Yes">1</isuserdefined>
<isprivate>0</isprivate>
<isdefault name="No">0</isdefault
Save the XML file. In CRM, click on Settings > Customization > Import Customizations.
Browse to the modified XML file and click on Upload. This will read the XML file to determine what customizations are contained in it. Make sure Account is selected and choose Import Selected Customizations.
To publish the changes:
Click on Settings > Customization > Customize Entities.
Select Account and click on Publish.
This is an unsupported method; however, it will work on any of the entities including custom entities.
Yes, but since CRM 4.0 does not have a method for including inactive records and the customization window does not allow modifying the filter criteria of the view, you need to use a workaround:
Click on Settings > Customization > Export Customizations.
Select Account and click on Export Selected Customizations.
Click OK to the warning window.
Click Save on the File Download window. Save the file on your desktop.
Extract the Customizations.zip file and then open the Customizations.xml file in NotePad.
Search for “Quick Find Active.”
Remove the filter condition shown below (it is separated from the other code).
- <fetchxml>
- <fetch version="1.0" output-format="xml-platform" mapping="logical">
- <entity name="account">
<attribute name="name" />
<attribute name="accountnumber" />
<attribute name="primarycontactid" />
<attribute name="address1_city" />
<attribute name="telephone1" />
<attribute name="emailaddress1" />
<attribute name="accountid" />
<order attribute="name" descending="false" />
- <filter type="and">
<condition attribute="statecode" operator="eq" value="0" />
</filter>
- <filter type="or" isquickfindfields="1">
<condition attribute="name" operator="like" value="{0}" />
<condition attribute="accountnumber" operator="like" value="{0}" />
<condition attribute="emailaddress1" operator="like" value="{0}" />
</filter>
</entity>
</fetch>
</fetchxml>
- <LocalizedNames>
<LocalizedName description="Quick Find Active Accounts" languagecode="1033" />
</LocalizedNames>
</savedquery>
- <savedquery>
<iscustomizable name="No">0</iscustomizable>
<isquickfindquery name="No">0</isquickfindquery>
<isuserdefined name="Yes">1</isuserdefined>
<isprivate>0</isprivate>
<isdefault name="No">0</isdefault
Save the XML file. In CRM, click on Settings > Customization > Import Customizations.
Browse to the modified XML file and click on Upload. This will read the XML file to determine what customizations are contained in it. Make sure Account is selected and choose Import Selected Customizations.
To publish the changes:
Click on Settings > Customization > Customize Entities.
Select Account and click on Publish.
This is an unsupported method; however, it will work on any of the entities including custom entities.
CRM: Upper and Lowercase Javascript
A client asked: I need a way to populate a field in Account when I enter a specific valule. Can this be done?
Yes, this can be done using javascript as follows:
Select Settings > Select Customization > Select Customize Entities
Open the Account entity > Select Forms and Views
Select Main Application Form > Double click SValue > Select Events
Open OnChange > Click "Event is Enabled" > Copy the script below
var svalue = null;
switch(crmForm.all.picklistField1.SelectedText) {
case "Nine":
case "Eight":
svalue = "Acceptable";
break;
case "Six":
svalue = "Unacceptable";
break;
}
crmForm.all.textField1.DataValue = svalue;
If you have problems with people entering upper or lower case, change the script to the following:
var svalue = null;
switch(crmForm.all.picklistField1.SelectedText.toLowerCase()) {
case "Nine":
case "Eight":
svalue = "Acceptable";
break;
case "Six":
svalue = "Unacceptable";
break;
}
crmForm.all.textField1.DataValue = svalue;
Publish the changes and try it out.
Yes, this can be done using javascript as follows:
Select Settings > Select Customization > Select Customize Entities
Open the Account entity > Select Forms and Views
Select Main Application Form > Double click SValue > Select Events
Open OnChange > Click "Event is Enabled" > Copy the script below
var svalue = null;
switch(crmForm.all.picklistField1.SelectedText) {
case "Nine":
case "Eight":
svalue = "Acceptable";
break;
case "Six":
svalue = "Unacceptable";
break;
}
crmForm.all.textField1.DataValue = svalue;
If you have problems with people entering upper or lower case, change the script to the following:
var svalue = null;
switch(crmForm.all.picklistField1.SelectedText.toLowerCase()) {
case "Nine":
case "Eight":
svalue = "Acceptable";
break;
case "Six":
svalue = "Unacceptable";
break;
}
crmForm.all.textField1.DataValue = svalue;
Publish the changes and try it out.
HEAT: Alert Monitor Fields
A client recently asked: How do I change the fields displayed in Alert Monitor and the order they appear? I have tried changing the Call Log grid with Quick Customize but it has no effect.
The Alert Monitor grid view is part of Alert Monitor and is somewhat hidden:
Open Alert Monitor > Stop Polling > Click Define > Call Group Alerts
Select a Call Group > Select Edit > Click Grid
The fields displayed in Alert Monitor and their order are shown.
Use the functions here to remove or add fields and change the order.
When you are done, exit the window and the columns will immediately change. This needs to be done for each Call Group in Alert Monitor. Don't forget to save your alr file before you exit Alert Monitor.
The Alert Monitor grid view is part of Alert Monitor and is somewhat hidden:
Open Alert Monitor > Stop Polling > Click Define > Call Group Alerts
Select a Call Group > Select Edit > Click Grid
The fields displayed in Alert Monitor and their order are shown.
Use the functions here to remove or add fields and change the order.
When you are done, exit the window and the columns will immediately change. This needs to be done for each Call Group in Alert Monitor. Don't forget to save your alr file before you exit Alert Monitor.
HEAT: Alert Monitor Pop-Ups
A client asked: After setting up Alert Monitor, I now get a pop-up window that opens every minute showing my calls. How can I stop this?
First, identify why this is occurring. Use the steps below:
Open Alert Monitor > Stop Polling > Select Define > Call Group Alerts
Select a Group > Click Edit > Review the setup
If "Any Calls are Found" is checked, uncheck it
Save the revised alr file > Resume polling
The pop-ups each minute should stop. You should now get a pop-up only when a new call is created.
First, identify why this is occurring. Use the steps below:
Open Alert Monitor > Stop Polling > Select Define > Call Group Alerts
Select a Group > Click Edit > Review the setup
If "Any Calls are Found" is checked, uncheck it
Save the revised alr file > Resume polling
The pop-ups each minute should stop. You should now get a pop-up only when a new call is created.
HEAT: How Do I Create a DAT file?
A client asked: When I try to use the import/export function of HEAT Administrator, it asks for a DAT file to import. What is this?
A DAT (.dat) file is a text file formatted in a specific way. They can be opened with Notepad or other text editors. To begin the process of creating a DAT file to import, first export the existing table as a DAT file to review the structure.
Select Export > Select Category > Browse to a folder > Export
Open Category.dat with Notepad > you will see the format below
'Name''Age''City''State'
This structure is a delimited text file using a single quote and a pipe symbol (). To create this type of file from an Excel file, you would do the following:
Save your Excel file as a "csv" file type
Open the file (category.csv) in Word
Use Find and Replace to convert the commas to ''
Create a macro to add a single quote in the beginning of each row
Create a macro to add ' at the end of each row
Save the file as a text file and give it the extension .dat. This can now be used to import into the HEAT category table using Administrator.
A DAT (.dat) file is a text file formatted in a specific way. They can be opened with Notepad or other text editors. To begin the process of creating a DAT file to import, first export the existing table as a DAT file to review the structure.
Select Export > Select Category > Browse to a folder > Export
Open Category.dat with Notepad > you will see the format below
'Name''Age''City''State'
This structure is a delimited text file using a single quote and a pipe symbol (). To create this type of file from an Excel file, you would do the following:
Save your Excel file as a "csv" file type
Open the file (category.csv) in Word
Use Find and Replace to convert the commas to ''
Create a macro to add a single quote in the beginning of each row
Create a macro to add ' at the end of each row
Save the file as a text file and give it the extension .dat. This can now be used to import into the HEAT category table using Administrator.
Wednesday, September 30, 2009
Crystal Reports : Easily copying formatting between fields
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.
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 : Using MID() to cut out a substring
Let us suppose that we are reporting on history, and our field {HISTORY.REF} has some annoying characters padded into the beginning of the string, like this:
"oc: Justin Hill - Appointment"
"oc: Gene Marks - Phone Call"
"oc: Corey Babka - Next Action"
For cosmetic purposes, we will create a formula to strip out the "oc:" with the following code:
Mid({HISTORY.REF}, 5)
This will return the entire string, starting at character position five.
"oc: Justin Hill - Appointment"
"oc: Gene Marks - Phone Call"
"oc: Corey Babka - Next Action"
For cosmetic purposes, we will create a formula to strip out the "oc:" with the following code:
Mid({HISTORY.REF}, 5)
This will return the entire string, starting at character position five.
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.
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 : 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
CurrentDate() - {CONTACTS.SOURCEDATE}
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)
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
CurrentDate() - {CONTACTS.SOURCEDATE}
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)
HEAT: Import Excel to HEAT tables
A client wants to know: We have alot of data for our new HEAT system in Excel worksheets used to export from our previous help desk software. How do we get it into HEAT?
As an example, let's use Call Type as an example. There are a number of ways to move data from Excel to SQL tables; 1) HEAT's import/export function; 2) SQL DTS function and 3) an Access database. We will cover the Access database method:
First, as an backup of the Call Type table, open Administrator
Select Database > Export > Select Call Type > Browse to a folder
Save as Call_Type_ Export > the Call Type table is now saved *********************************************************
Open Access > Create a blank database
Select Tables > File > Get External Data > Import
Browse to your Excel worksheet > Select the Call Type worksheet
Click OK > Your Excel worksheet will become a table in Access
Select Tables > File > Get External Data > Link Tables
Change the File Type to ODBC Databases() > Select HEAT DSN
Select the Call Type table > this becomes a linked table in HEAT
*********************************************************
Click Query in Access > Select New Query > Design View
Add the CallType worksheet > close table window
Select Query > Change to Append query > Apppend to dbo.CallType
Match the Excel Fields to the Call Type fields below
Close and save the query >Select the query
Click Open > it will tell you that is going to append x records
Close the Access database after the append is complete
*********************************************************
Open Call Logging and select Category. The contents from the Excel worksheet should now be in the Call Type drop down.
As an example, let's use Call Type as an example. There are a number of ways to move data from Excel to SQL tables; 1) HEAT's import/export function; 2) SQL DTS function and 3) an Access database. We will cover the Access database method:
First, as an backup of the Call Type table, open Administrator
Select Database > Export > Select Call Type > Browse to a folder
Save as Call_Type_ Export > the Call Type table is now saved *********************************************************
Open Access > Create a blank database
Select Tables > File > Get External Data > Import
Browse to your Excel worksheet > Select the Call Type worksheet
Click OK > Your Excel worksheet will become a table in Access
Select Tables > File > Get External Data > Link Tables
Change the File Type to ODBC Databases() > Select HEAT DSN
Select the Call Type table > this becomes a linked table in HEAT
*********************************************************
Click Query in Access > Select New Query > Design View
Add the CallType worksheet > close table window
Select Query > Change to Append query > Apppend to dbo.CallType
Match the Excel Fields to the Call Type fields below
Close and save the query >Select the query
Click Open > it will tell you that is going to append x records
Close the Access database after the append is complete
*********************************************************
Open Call Logging and select Category. The contents from the Excel worksheet should now be in the Call Type drop down.
HEAT: Working from the Assignment tab
A client recently asked: Can we setup HEAT so that technicians can work from the Assignment tab to perform all the tasks they need to do in HEAT?
Yes, this involves adding some command buttons and autotasks but it not very complicated. To add a command button:
Open Administrator > Select Quick Customize
Select the Asgnmnt table > Click Open Form
If the Toolbox does not appear, click View > Controls Toolbox
Select the Command Button > Drag to under the Resolve button
Right-click the button > Change text to Save and Close
*********************************************************
Open Call Logging > Create an Autotask called Assignment Button
This autotask should include the following actions:
-Update Call > Call Log:Close Desc > Insert Field > Assignment:Notes
-Update Call > Call Log:Cause > Insert Function > Validate Prompt()
-Check the Save Call and Close Call checkboxes
Save the Autotask
*********************************************************
In Administrator, select the Command Button > click Control
Click Connect > Click Autotask > Browse to Assignment Button
Apply the Quick changes
*********************************************************
Open Call Logging > it should create a new Catalog
Open the Assignment tab > Create an Assignment
Acknowledge and Resolve the Assignment
Enter the Assignment details in Notes
Click the Save and Close button
Enter Cause the from Prompt > Notes will be copied to Close Desc
The autotask will save and close the call
That's it - you can then add IF statements to check if the Assignment has been Acknowledged and Resolved before the Save and Close function. You can also test that the Notes field is not empty before it is copied to Close Desc.
Yes, this involves adding some command buttons and autotasks but it not very complicated. To add a command button:
Open Administrator > Select Quick Customize
Select the Asgnmnt table > Click Open Form
If the Toolbox does not appear, click View > Controls Toolbox
Select the Command Button > Drag to under the Resolve button
Right-click the button > Change text to Save and Close
*********************************************************
Open Call Logging > Create an Autotask called Assignment Button
This autotask should include the following actions:
-Update Call > Call Log:Close Desc > Insert Field > Assignment:Notes
-Update Call > Call Log:Cause > Insert Function > Validate Prompt()
-Check the Save Call and Close Call checkboxes
Save the Autotask
*********************************************************
In Administrator, select the Command Button > click Control
Click Connect > Click Autotask > Browse to Assignment Button
Apply the Quick changes
*********************************************************
Open Call Logging > it should create a new Catalog
Open the Assignment tab > Create an Assignment
Acknowledge and Resolve the Assignment
Enter the Assignment details in Notes
Click the Save and Close button
Enter Cause the from Prompt > Notes will be copied to Close Desc
The autotask will save and close the call
That's it - you can then add IF statements to check if the Assignment has been Acknowledged and Resolved before the Save and Close function. You can also test that the Notes field is not empty before it is copied to Close Desc.
Sunday, September 27, 2009
QuickBooks: Using Transaction Classes
A client asked: What are Classes and how are they used in QuickBooks?
You can use classes in QuickBooks to segment your financial statements and to filter many of your QuickBooks summary reports and detail reports for specific segments of your business. However, certain entries may not apply to any of the classes on the list or you may need to allocate the entry across multiple classes.
For example, if you use QuickBooks to track locations, entries for company-wide expenses like insurance policies and advertising do not apply to a specific location. You have two options when working with this type of entry:
1. You can create a class called “Overhead” and you can post the entry to that class.
2. You can split the detail of the transaction so that the single transaction (e.g. check, bill or invoice) applies to multiple classes.
Whatever method you use, the bottom line is to always use a class on each and every transaction. If you do not include a class, you will:
* Create an Unclassified column on the Profit & Loss by Class report. Doing this does not allow you to track transactions that should have posted to a specific class but where the user failed to enter the class.
If you use a Class called “Overhead,” you can use the “Unclassified” column to track transactions that are missing a class.
* Receive a warning message that the transaction does not include a class. (This message will show only if the “Prompt to Assign Class” box is check in the Accounting Company Preferences.)
If you exclude the class on selected posts only (e.g. posts that are overhead or posts to Balance Sheet accounts), the benefit of this warning is diminished. Over time, users will ignore the message.
For this reason it is best to use the Overhead Class on posts to Balance Sheet accounts, even though QuickBooks does not allow you to filter the Balance Sheet by Class.
You can use classes in QuickBooks to segment your financial statements and to filter many of your QuickBooks summary reports and detail reports for specific segments of your business. However, certain entries may not apply to any of the classes on the list or you may need to allocate the entry across multiple classes.
For example, if you use QuickBooks to track locations, entries for company-wide expenses like insurance policies and advertising do not apply to a specific location. You have two options when working with this type of entry:
1. You can create a class called “Overhead” and you can post the entry to that class.
2. You can split the detail of the transaction so that the single transaction (e.g. check, bill or invoice) applies to multiple classes.
Whatever method you use, the bottom line is to always use a class on each and every transaction. If you do not include a class, you will:
* Create an Unclassified column on the Profit & Loss by Class report. Doing this does not allow you to track transactions that should have posted to a specific class but where the user failed to enter the class.
If you use a Class called “Overhead,” you can use the “Unclassified” column to track transactions that are missing a class.
* Receive a warning message that the transaction does not include a class. (This message will show only if the “Prompt to Assign Class” box is check in the Accounting Company Preferences.)
If you exclude the class on selected posts only (e.g. posts that are overhead or posts to Balance Sheet accounts), the benefit of this warning is diminished. Over time, users will ignore the message.
For this reason it is best to use the Overhead Class on posts to Balance Sheet accounts, even though QuickBooks does not allow you to filter the Balance Sheet by Class.
QuickBooks: The Ask Advisor Equity Account
A client asked: Since every transaction requires assignment to an account, what is the best practice for handling tricky transactions that I am not sure about?
The best approach is to create an “Ask Advisor” Equity Account. Depending on your level of accounting and QuickBooks experience, you may often need to record transactions that you are unsure of which account you should use. In fact, to record the transaction you might even need to create a new account.
Since you cannot record the transaction without assigning an account, you need to choose something in order to continue with the data entry. However, if you guess and post to an incorrect account, the transaction becomes buried in the General Ledger and will appear on account-based transaction detail reports.
Instead, create a new account with an Equity type called “Ask Advisor” You can choose whatever type of account you like, but it is best to select an account with a Balance Sheet type (like Equity) so you can use the account register to easily edit the transactions in the future to assign the correct account.
Arrange regular meetings with your QuickBooks Advisor to help you to clear the balance in this account. The transactions you post to “Ask Advisor” could have a significant impact on your financial reports, affecting the way QuickBooks measures your financial performance and how you manage your company, so don't wait under the end of the year to meet with your QuickBooks Advisor.
The best approach is to create an “Ask Advisor” Equity Account. Depending on your level of accounting and QuickBooks experience, you may often need to record transactions that you are unsure of which account you should use. In fact, to record the transaction you might even need to create a new account.
Since you cannot record the transaction without assigning an account, you need to choose something in order to continue with the data entry. However, if you guess and post to an incorrect account, the transaction becomes buried in the General Ledger and will appear on account-based transaction detail reports.
Instead, create a new account with an Equity type called “Ask Advisor” You can choose whatever type of account you like, but it is best to select an account with a Balance Sheet type (like Equity) so you can use the account register to easily edit the transactions in the future to assign the correct account.
Arrange regular meetings with your QuickBooks Advisor to help you to clear the balance in this account. The transactions you post to “Ask Advisor” could have a significant impact on your financial reports, affecting the way QuickBooks measures your financial performance and how you manage your company, so don't wait under the end of the year to meet with your QuickBooks Advisor.
QuickBooks: QuickBooks on Terminal Services
A client asked: Does QuickBooks run on Terminal Services?
Yes, it does and it runs very well. It provides a faster and more stable connection to QuickBooks for each user.
Yes, it does and it runs very well. It provides a faster and more stable connection to QuickBooks for each user.
First, there is a difference in setup and licensing. Terminal Services allows each workstation to share the server’s desktop. When you use Terminal Services, you still need a QuickBooks license for each unique QuickBooks user, but you do not install QuickBooks on each workstation. Instead, you install one license of QuickBooks on the server and you store the data file on the server.
Then, all QuickBooks users on the network run the QuickBooks application stored on the server, up to 30 users running a single installation of QuickBooks for QuickBooks Enterprise. QuickBooks will perform significantly better and each user's connection to the QuickBooks data file is much more stable.
In a Terminal Services environment the processing power is focused on the Terminal Server instead of the workstations, so the faster the server the better. You also may want to consider a separate server just for the QuickBooks program.
QuickBooks: Networks and Databases
A client asked: Can my network configuration cause problems with the QuickBooks database?
The condition of your Local Area Network (LAN) can have a significant impact on the possibility of QuickBooks data file corruption. Consider the following:
1) Wireless Networks
Wireless networks are the worst enemy of the QuickBooks data file. The QuickBooks file is sensitive to unexpected disconnections and wireless networks have a much greater propensity to have connection interruptions than connections via Ethernet cable.
Strongly consider not using QuickBooks with wireless network connections. If the network has a wireless router at all, consider that some computers may have both Ethernet and wireless network adapters. To ensure the computer uses the Ethernet connection and not the wireless connection, you should either turn off the wireless signal or take steps to prevent the computers with wireless network adapters from connecting to the wireless router.
2) General Network Maintenance
Substandard, obsolete or poorly configured routers can impact both the performance of QuickBooks in a multiuser environment and can increase the risk of file corruption.
The concept here is the similar to wireless connections. If the configuration of the server or the condition of the hardware causes periodic interruptions in a user's connection, this interruption can cause a QuickBooks file corruption.
The condition of your Local Area Network (LAN) can have a significant impact on the possibility of QuickBooks data file corruption. Consider the following:
1) Wireless Networks
Wireless networks are the worst enemy of the QuickBooks data file. The QuickBooks file is sensitive to unexpected disconnections and wireless networks have a much greater propensity to have connection interruptions than connections via Ethernet cable.
Strongly consider not using QuickBooks with wireless network connections. If the network has a wireless router at all, consider that some computers may have both Ethernet and wireless network adapters. To ensure the computer uses the Ethernet connection and not the wireless connection, you should either turn off the wireless signal or take steps to prevent the computers with wireless network adapters from connecting to the wireless router.
2) General Network Maintenance
Substandard, obsolete or poorly configured routers can impact both the performance of QuickBooks in a multiuser environment and can increase the risk of file corruption.
The concept here is the similar to wireless connections. If the configuration of the server or the condition of the hardware causes periodic interruptions in a user's connection, this interruption can cause a QuickBooks file corruption.
Examples include connection issues with the network router, hardware or driver problems on workstation network adapters, operating system/resource issues on the server that cause QuickBooks or Windows to enter a non-responding mode.
QuickBooks: Reduce Data Corruption
A client asked: What should I be doing to prevent data corruption in my QuickBooks Enterprise database?
There are a number of things that you can do yourself or in conjunction your QuickBooks Advisor:
To reduce the potential for data file corruption, take precautions to make sure the database is as stable as possible. You can do the following to reduce the likelihood of data file corruptions or other types of data integrity issues.
1) Interpreting the Product Information Window
The QuickBooks Product Information window shows valuable information about the QuickBooks program When analyzing your QuickBooks files this information is extremely important. You need to ensure your clients installed the most recent QuickBooks update, that the size of the QuickBooks file is reasonable and that the client’s lists are not nearing the 28,000 limitation for Items and the 14,000 limitation for customers, vendors, employees and other names).
2) Track Historical Database Size
A way to track your historical data use is to open a backup copy of your data file from about one year ago and note the number of transactions in the file and the size of the file. You can then note the same information using the current file. Using this information, compute the number of transactions entered over the past 12 months to project the file growth over the coming year or even over multiple years. When factoring file growth, consider not only the historical growth of the file, but the projected growth of the company and any changes the company intends to make in the use of QuickBooks. To access the Product Information window, open the QuickBooks data file and then press F2.
3) Monitor File Size
The size of the file is a major factor in corruption prevention. Though larger files do not necessarily become corrupted, the larger the file the greater the risk. The increase in risk escalates quickly as the file size reaches about 500MB for QuickBooks Pro and Premier and about 1GB for QuickBooks Enterprise Solutions. You should monitor the size of QuickBooks files and keep the sizes at or below these levels if at all possible.
There are numerous ways you can reduce the size client's data files. As one quick (and strongly recommended) option you can use the Portable Company File process to reduce file size.
4) Monitor Database Fragments
The Product Information window also shows you the number of database fragments for any given file. If possible you should have no more than 9 database fragments in the file. If there are 10 or more database fragments you can perform the following steps to reduce the fragments:
* Resort the lists in the file. Use the "Resort List" selection in the list menu to resort the lists. Resorting the lists using the list headers does not have the same reorganization impact on the file. Note: Some lists do not have a "Resort List" selection.
* Rebuild the File. Select the File menu, select Utilities and then select "Rebuild Data." You can also use the Rebuild Data utility to repair file corruptions, but the focus in this context is around the reduction of database fragments to reduce the risk of corruption.
* Create a Portable Company File and then restore the Portable Company File. This process re-indexes the database and is one of the most effective ways to reduce database fragments. You can also use the Portable Company File utility to repair some data file corruptions, but the focus in this context is around the reduction of database fragments to reduce the risk of corruption.
* Run the Windows Defragmentation utility on the drive on which the QuickBooks data file is stored (e.g. the server). The fragmentation level of the computer's hard drive can impact the fragmentation level of the QuickBooks database.
Note: According to Intuit's data repair department, running the Rebuild Data utility too frequently can actually increase your risk of file corruption. You should limit the prevention intended rebuilds to a handful per year.
There are a number of things that you can do yourself or in conjunction your QuickBooks Advisor:
To reduce the potential for data file corruption, take precautions to make sure the database is as stable as possible. You can do the following to reduce the likelihood of data file corruptions or other types of data integrity issues.
1) Interpreting the Product Information Window
The QuickBooks Product Information window shows valuable information about the QuickBooks program When analyzing your QuickBooks files this information is extremely important. You need to ensure your clients installed the most recent QuickBooks update, that the size of the QuickBooks file is reasonable and that the client’s lists are not nearing the 28,000 limitation for Items and the 14,000 limitation for customers, vendors, employees and other names).
2) Track Historical Database Size
A way to track your historical data use is to open a backup copy of your data file from about one year ago and note the number of transactions in the file and the size of the file. You can then note the same information using the current file. Using this information, compute the number of transactions entered over the past 12 months to project the file growth over the coming year or even over multiple years. When factoring file growth, consider not only the historical growth of the file, but the projected growth of the company and any changes the company intends to make in the use of QuickBooks. To access the Product Information window, open the QuickBooks data file and then press F2.
3) Monitor File Size
The size of the file is a major factor in corruption prevention. Though larger files do not necessarily become corrupted, the larger the file the greater the risk. The increase in risk escalates quickly as the file size reaches about 500MB for QuickBooks Pro and Premier and about 1GB for QuickBooks Enterprise Solutions. You should monitor the size of QuickBooks files and keep the sizes at or below these levels if at all possible.
There are numerous ways you can reduce the size client's data files. As one quick (and strongly recommended) option you can use the Portable Company File process to reduce file size.
4) Monitor Database Fragments
The Product Information window also shows you the number of database fragments for any given file. If possible you should have no more than 9 database fragments in the file. If there are 10 or more database fragments you can perform the following steps to reduce the fragments:
* Resort the lists in the file. Use the "Resort List" selection in the list menu to resort the lists. Resorting the lists using the list headers does not have the same reorganization impact on the file. Note: Some lists do not have a "Resort List" selection.
* Rebuild the File. Select the File menu, select Utilities and then select "Rebuild Data." You can also use the Rebuild Data utility to repair file corruptions, but the focus in this context is around the reduction of database fragments to reduce the risk of corruption.
* Create a Portable Company File and then restore the Portable Company File. This process re-indexes the database and is one of the most effective ways to reduce database fragments. You can also use the Portable Company File utility to repair some data file corruptions, but the focus in this context is around the reduction of database fragments to reduce the risk of corruption.
* Run the Windows Defragmentation utility on the drive on which the QuickBooks data file is stored (e.g. the server). The fragmentation level of the computer's hard drive can impact the fragmentation level of the QuickBooks database.
Note: According to Intuit's data repair department, running the Rebuild Data utility too frequently can actually increase your risk of file corruption. You should limit the prevention intended rebuilds to a handful per year.
Saturday, September 26, 2009
GoldMine: Record Activity Time
A client asked: Is there a way to automatically record the time spent on an activity and save it in the history of the contact?
Yes, there is a timer that can be configured in GoldMine preferences to time your activities. Here is how you set it up:
* Select Edit > Preferences from the main menu
* In the 'Preferences' dialog box > click the 'Schedule' tab.
* Check the “Start timer when completing activities” option.
* Click OK.
Now, when you view the completed activity in the history tab, the duration will be recorded.
Yes, there is a timer that can be configured in GoldMine preferences to time your activities. Here is how you set it up:
* Select Edit > Preferences from the main menu
* In the 'Preferences' dialog box > click the 'Schedule' tab.
* Check the “Start timer when completing activities” option.
* Click OK.
Now, when you view the completed activity in the history tab, the duration will be recorded.
GoldMine: Quick Scheduling
A client recently asked: It seems like a lot of steps to create an activity in Goldmine. Is there a quicker way?
There are several shortcuts to create an activity in Goldmine. My favorite method is below:
* Look up the contact that you would like to schedule
* Highlight the contact > Press F7 to open the calendar
* Pick a date > Click the Schedule drop-down > pick Activity type
* The Schedule Activity window opens with the contact filled in
Enter the remaining information about the activity and click Save. You're done.
There are several shortcuts to create an activity in Goldmine. My favorite method is below:
* Look up the contact that you would like to schedule
* Highlight the contact > Press F7 to open the calendar
* Pick a date > Click the Schedule drop-down > pick Activity type
* The Schedule Activity window opens with the contact filled in
Enter the remaining information about the activity and click Save. You're done.
GoldMine: SMS Text Message
A client asked: Can I use text messaging in Goldmine 8.5? If so, do replies go to my Blackberry?
Yes, SMS text messaging is possible in Goldmine. To send a text message, you would do the following:
* Select Contact > Send SMS/Text Message
* Enter the number to text > enter the provider
* You can type your message in the large text box
* Click Send
Any reply to the text message would be to the email address setup in your Goldmine email preferences.
Yes, SMS text messaging is possible in Goldmine. To send a text message, you would do the following:
* Select Contact > Send SMS/Text Message
* Enter the number to text > enter the provider
* You can type your message in the large text box
* Click Send
Any reply to the text message would be to the email address setup in your Goldmine email preferences.
Friday, September 25, 2009
GoldMine: Controlling the Process Monitor
A client asked: Is there a way to control the Process Monitor window? It seems to just pop up anywhere when you send an email or run an automated process.
Yes, there are number of ways to control the Process Monitor window. Try the methods below:
* An easy way is to drag the Process Monitor window to the bottom of the screen. It should dock to the bottom of the form. Then find and click the push-pin icon. This will cause the process monitor window to auto-hide and only appear when you mouse over the 'GoldMine Process Monitor' tab in the lower left corner
* Another way to manage the Process Monitor is change the settings in your user.ini file. Find the section below and change MdiState to 1.
[ProcMon]
MdiState=1
The window then behaves as you tell it and does not interfer with user activity. It will stay in the shape, size and position that you set it to.
Yes, there are number of ways to control the Process Monitor window. Try the methods below:
* An easy way is to drag the Process Monitor window to the bottom of the screen. It should dock to the bottom of the form. Then find and click the push-pin icon. This will cause the process monitor window to auto-hide and only appear when you mouse over the 'GoldMine Process Monitor' tab in the lower left corner
* Another way to manage the Process Monitor is change the settings in your user.ini file. Find the section below and change MdiState to 1.
[ProcMon]
MdiState=1
The window then behaves as you tell it and does not interfer with user activity. It will stay in the shape, size and position that you set it to.
GoldMine: Beyond the Documentation
A client asked: I have never seen a list of undocumented tips for GoldMine. Can you provide a list of some tips and tricks not included in the documentation?
Sure, there are a lot of tips that are discovered by users and then passed on by word of mouth. A few are listed below:
* When scheduling an activity for a contact, you can automatically schedule three weeks out by typing 3W in the date field (or 4W, 5W)
* Schedule by priority (instead of Time) Establish a series of codes for your priorities (A, B, C), then place them in the Time field instead of the time.
This will change the field to read "Priority" and all calls will be placed on the View Activity lists sorted by priorities. These calls will show up on the daily calendar under To-Do's.
* In the Contact Search Center, click the Columns button to add fields to your view. Choose the Column Selection tab and double click the field you want added
* Right click in any list view and select the 'Summary' option to get a count of the currently displayed items (the count is displayed in a textbox on the bottom-left).
These are just some of the tips and tricks that exist for GoldMine. If you know of others, free feel to send them on for inclusion in a newsletter.
Sure, there are a lot of tips that are discovered by users and then passed on by word of mouth. A few are listed below:
* When scheduling an activity for a contact, you can automatically schedule three weeks out by typing 3W in the date field (or 4W, 5W)
* Schedule by priority (instead of Time) Establish a series of codes for your priorities (A, B, C), then place them in the Time field instead of the time.
This will change the field to read "Priority" and all calls will be placed on the View Activity lists sorted by priorities. These calls will show up on the daily calendar under To-Do's.
* In the Contact Search Center, click the Columns button to add fields to your view. Choose the Column Selection tab and double click the field you want added
* Right click in any list view and select the 'Summary' option to get a count of the currently displayed items (the count is displayed in a textbox on the bottom-left).
These are just some of the tips and tricks that exist for GoldMine. If you know of others, free feel to send them on for inclusion in a newsletter.
GoldMine: Accept, Decline or Propose a New Time
A client wants to know: When you use GoldMine to send a meeting request email to a contacts can you request that the contact Accept, Decline or Propose a New Time for the meeting?
Yes, you need to do the following:
* Configure SMTP settings in GoldMine to send emails.
* Select Edit > Preferences > Internet to do it.
* When scheduling an activity, check "Send a request with the Appt."
* GoldMine sends an iCal meeting request to the contact.
* The contact can choose any of Accept, Decline, Propose a New Time.
* Their email program will automatically send a response back.
This allows for meeting confirmations using emails with the option to Accept, Decline or Propose a New Time.
Yes, you need to do the following:
* Configure SMTP settings in GoldMine to send emails.
* Select Edit > Preferences > Internet to do it.
* When scheduling an activity, check "Send a request with the Appt."
* GoldMine sends an iCal meeting request to the contact.
* The contact can choose any of Accept, Decline, Propose a New Time.
* Their email program will automatically send a response back.
This allows for meeting confirmations using emails with the option to Accept, Decline or Propose a New Time.
GoldMine: Team Meetings
A client recently asked: I use GoldMine to schedule meetings with customers. Can I also use it to schedule Team meetings?
Yes, you can easily use GoldMine to send meeting requests to your team members (i.e. GoldMine users). Here's how you do it:
* Select Schedule > Events from the GoldMine Menu.
* The Schedule An Event window appears.
* In the Detail tab, fill in details about the meeting
* Select time and duration of the meeting.
* In the same tab, Check "Send a request with the event to" option
* From the drop-down below it select Users.
* From the Users tab, select the Users to send the request.
* If the meeting is a regular activity, click on the Recurring tab
* Select the date range, frequency and occurrence of the activity.
* Hit Schedule to complete.
Now, GoldMine will automatically send the meeting request to your team.
Yes, you can easily use GoldMine to send meeting requests to your team members (i.e. GoldMine users). Here's how you do it:
* Select Schedule > Events from the GoldMine Menu.
* The Schedule An Event window appears.
* In the Detail tab, fill in details about the meeting
* Select time and duration of the meeting.
* In the same tab, Check "Send a request with the event to" option
* From the drop-down below it select Users.
* From the Users tab, select the Users to send the request.
* If the meeting is a regular activity, click on the Recurring tab
* Select the date range, frequency and occurrence of the activity.
* Hit Schedule to complete.
Now, GoldMine will automatically send the meeting request to your team.
GoldMine: Maximize Your Sales Calls
A client asked: When I visit a city on a sales call, I would like to visit any other contact that resides in the city I am visiting. Is there a report for this?
This can be easily done using a filter. Use the GoldMine Contact Search Center to find this information:
* Click on the Search button.
* In the drop down box next to ‘Search by’, choose State.
* In the next box choose ‘Equal To’
* In the last box choose the State you are going to.
* Click on the plus sign at the end of the row
* Another search line will appear below
* Choose ‘And’ in the first drop down box.
* In the Search box, choose City.
* In the next box again choose ‘Equal To’
* In the last box type in the city you are visiting.
You will now see contacts in your GoldMine database that are from that particular city and state. You can then send the results to a printer or to Excel.
This can be easily done using a filter. Use the GoldMine Contact Search Center to find this information:
* Click on the Search button.
* In the drop down box next to ‘Search by’, choose State.
* In the next box choose ‘Equal To’
* In the last box choose the State you are going to.
* Click on the plus sign at the end of the row
* Another search line will appear below
* Choose ‘And’ in the first drop down box.
* In the Search box, choose City.
* In the next box again choose ‘Equal To’
* In the last box type in the city you are visiting.
You will now see contacts in your GoldMine database that are from that particular city and state. You can then send the results to a printer or to Excel.
GoldMine: Delete Attachments with Email
A client asked: When I delete an email in GoldMine, the attachments are not removed. Is there a way to delete both emails and attachments?
Yes, since email attachments are not be removed by default, you need to do the following to also delete the attachments with the email:
* Select Tools > Options from the menu.
* Select the E-mail tab.
* Click on 'More Options' to open the 'E-mail Preferences' dialog box.
* Select the 'Advanced' tab.
* In 'Message', check 'Delete attachments when deleting the mail'.
Now whenever you delete an email, the email attachments will also be deleted.
Yes, since email attachments are not be removed by default, you need to do the following to also delete the attachments with the email:
* Select Tools > Options from the menu.
* Select the E-mail tab.
* Click on 'More Options' to open the 'E-mail Preferences' dialog box.
* Select the 'Advanced' tab.
* In 'Message', check 'Delete attachments when deleting the mail'.
Now whenever you delete an email, the email attachments will also be deleted.
GoldMine: Pictures of Contacts
A client asked: I would like to include a picture of each contact in a Goldmine field. Is there an easy way to do this?
This could be done using a GM+View. The GM+View tab allows you to embed rich HTML data into your customer records, including photos, graphic images or sound files.
Below is how you can display images relevant to your contact:
* Select Web > Configure GM+View.
* The GM+View Tab Settings dialog box appears.
* Click on the New button and enter a template name.
* To insert the picture, click the Insert Image icon
* Browse to the image > add any text or borders you want
* Save the template
You can make this the default GM+View if you want it to appear whenever you select the contact. You can use this image for visual recognition of contacts or to print name badges.
This could be done using a GM+View. The GM+View tab allows you to embed rich HTML data into your customer records, including photos, graphic images or sound files.
Below is how you can display images relevant to your contact:
* Select Web > Configure GM+View.
* The GM+View Tab Settings dialog box appears.
* Click on the New button and enter a template name.
* To insert the picture, click the Insert Image icon
* Browse to the image > add any text or borders you want
* Save the template
You can make this the default GM+View if you want it to appear whenever you select the contact. You can use this image for visual recognition of contacts or to print name badges.
Saturday, August 29, 2009
Crystal Reports : Display the day of week from a date
A client asked: I would like to display the day of the week on the detail line along with the date. How do I do this?
Using the example of CREATEON in Goldmine Contact1, you would do the following:
Create a formula using the DayOfWeek function called DayPosition as follows. This will create the numerical position of the date (e.g. 1=Sun, 2=Mon, etc)
DayOfWeek ({Contact1.CREATEON})
Then create a formula called DayName. This will add the name to the report (Mon, Tue, Wed). Use the function WeekDayName and add the DayPosition as the function detail
WeekdayName ({@DatePosition})
Drag the DayName formula to the detail row next to the CREATEON date. It will list the weekday name of the CREATEON date.
Using the example of CREATEON in Goldmine Contact1, you would do the following:
Create a formula using the DayOfWeek function called DayPosition as follows. This will create the numerical position of the date (e.g. 1=Sun, 2=Mon, etc)
DayOfWeek ({Contact1.CREATEON})
Then create a formula called DayName. This will add the name to the report (Mon, Tue, Wed). Use the function WeekDayName and add the DayPosition as the function detail
WeekdayName ({@DatePosition})
Drag the DayName formula to the detail row next to the CREATEON date. It will list the weekday name of the CREATEON date.
Friday, August 28, 2009
GoldMine: Menu Changes
A client asked: I like the new features in Goldmine Premium but why did they change the menus? It makes it confusing for users accustomed to the original menus.
The new menus are grouped to show related GoldMine functions under the same menu. However, if you want to use the original menus, you can do the following:
* Select Tools > Options > System tab
* Under Navigation > select Use classic GoldMine menu
* Click OK
The GoldMine menus will now appear as they did in previous versions. For example, you would select Edit > Options to change this back instead of Tools > Options.
One advantge of using the Classic menus is that it can ease the transition to GMPE.
The new menus are grouped to show related GoldMine functions under the same menu. However, if you want to use the original menus, you can do the following:
* Select Tools > Options > System tab
* Under Navigation > select Use classic GoldMine menu
* Click OK
The GoldMine menus will now appear as they did in previous versions. For example, you would select Edit > Options to change this back instead of Tools > Options.
One advantge of using the Classic menus is that it can ease the transition to GMPE.
GoldMine: Change Default Search
A client recently asked: When I click the Search button, it opens the Contact Search to automatically search by Contact. Is there a way to set this default to something other than Company or Contact?
Yes, you can "memorize" a field so that it appears by default:
* Select Tools > Options > Lookup tab
* Modify Default lookup by field > select Remember Lookup By
* Open the Contact Search Ceenter > select Email as the lookup
* Lookup by email > logout of Goldmine
When you log bck in, click the Search button. Email should appear as the default Lookup By field.
Yes, you can "memorize" a field so that it appears by default:
* Select Tools > Options > Lookup tab
* Modify Default lookup by field > select Remember Lookup By
* Open the Contact Search Ceenter > select Email as the lookup
* Lookup by email > logout of Goldmine
When you log bck in, click the Search button. Email should appear as the default Lookup By field.
GoldMine: Recently Viewed Contacts
A client asked: Under the Contact sidebar, can I expand the number of contacts recently viewed?
Yes, you can set them to show up to 99 contacts:
* Select Tools > Options > System tab
* Under Navigation > increase Recent items shown
* Logout and log back into Goldmine
* Open the Contact Search > select/open Contacts
You will see the Recently Viewed items list increase as you open new contacts.
Yes, you can set them to show up to 99 contacts:
* Select Tools > Options > System tab
* Under Navigation > increase Recent items shown
* Logout and log back into Goldmine
* Open the Contact Search > select/open Contacts
You will see the Recently Viewed items list increase as you open new contacts.
GoldMine: Changing Toolbar Details
A client recently asked: We would like to change the 'New Record' button on the toolbar to more closely reflect our record types. Is this possible?
Yes, you can change buttons on the toolbar by following the steps below. For example, to change 'New Record' to 'New Dealer':
* Click on New Record > Right-click and select Customize
* Highlight Menu Bar in the Toolbars window
* Right-Click New Record > Change '&Record' To '&Dealer'
* Click Edit Button Image or Click Change Button Image
This will now show the New Record as New Dealer with a different icon. This is useful for a company that deals with a specialized type of contact.
Yes, you can change buttons on the toolbar by following the steps below. For example, to change 'New Record' to 'New Dealer':
* Click on New Record > Right-click and select Customize
* Highlight Menu Bar in the Toolbars window
* Right-Click New Record > Change '&Record' To '&Dealer'
* Click Edit Button Image or Click Change Button Image
This will now show the New Record as New Dealer with a different icon. This is useful for a company that deals with a specialized type of contact.
Thursday, August 27, 2009
GoldMine: Remove Outlook Add-in
A client asked: We currently use GMPE 8.5 and would like to remove the Outlook add-in. Can you provide step by step instructions on removing?
Yes, you would need to do the following:
* Open Control Panel > Add or Remove Programs
* Locate the GoldMine entry > click the Change button
* Click Next > select Modify > click Next
* Expand the GoldMine Plus section
* Click the arrow next to 'For Microsoft Outlook'
* Select 'This feature will not be available'
* Click Next > click Install
This will remove the sync between Goldmine and Outlook.
Yes, you would need to do the following:
* Open Control Panel > Add or Remove Programs
* Locate the GoldMine entry > click the Change button
* Click Next > select Modify > click Next
* Expand the GoldMine Plus section
* Click the arrow next to 'For Microsoft Outlook'
* Select 'This feature will not be available'
* Click Next > click Install
This will remove the sync between Goldmine and Outlook.
HEAT: No Password for WebUI
A client recently asked: It appears that HEAT 9.02 does not allow you to require passwords. You can simply type in the user ID and see the activity. Is there a fix?
Yes, on the latest version of HEAT, allows users to log in without a password. The password field can't be selected to type the password on the login page.
In Administrator, the "Require Password" setting under Defaults > System > System Defaults controls the password setting for both Call Logging and WebUI. This setting, however, requires a workaround for HEAT 9.02:
* Open the Heat Administrator module
* Go to Defaults > Administrative Defaults
* Uncheck the box for require password
* Restart the Web UI service.
* Then recheck the box for Require passwords
* Restart the WebUI service again.
This should solve the problem and allow you to use password for WebUI
Yes, on the latest version of HEAT, allows users to log in without a password. The password field can't be selected to type the password on the login page.
In Administrator, the "Require Password" setting under Defaults > System > System Defaults controls the password setting for both Call Logging and WebUI. This setting, however, requires a workaround for HEAT 9.02:
* Open the Heat Administrator module
* Go to Defaults > Administrative Defaults
* Uncheck the box for require password
* Restart the Web UI service.
* Then recheck the box for Require passwords
* Restart the WebUI service again.
This should solve the problem and allow you to use password for WebUI
HEAT: Automated Haf Files Overnight
A client asked: I have heard that there is a way to automatically create a haf every night but I cannot find any documentation to do it. Does it exist?
One way to do this is to create a BATCH file. Call the batch file autohaf.bat and use the server Task Manager run it nightly. It renames the file to the date created so it saves all haf files created.
If you have not used batch files:
* Copy/paste the script below into NOTEPAD
* Save as autohaf with the .BAT extension
* Insert your database name (unless it is HEAT)
* Insert your Admin name and password.
* Make sure the user name has rights to export HAF files
===========================================================
echo off
cls
echo This file creates a HEAT archive file from the production HEAT database
echo .
echo Creating .haf file
echo .
echo Please wait....
echo .
"C:\Program Files\HEAT\LoadDB.exe" /d "HEAT" /u "Admin" /p "password" /e "C:\HAF\backup.haf"
for /f "tokens=1-4 delims=/ " %%a in ('date /t') do (set weekday=%%a& set day=%%b& set month=%%c& set year=%%d)
echo Renaming file to include todays date of %day% %month% %year%
ren c:\haf\backup.haf "backup_%day%_%month%_%year%.haf"
echo .
del c:\haf\fcactinkcom.txt
echo Backup and renaming of file complete
pause
ren Command Lines:
ren /d – ODBC Datasource name
ren /u – HEAT UserID that has rights to export HAF files
ren /p – Password of the HEAT UserID above
ren /e – Export directory including name of HAF file
===========================================================
Another option is to create a SQL backup prior to committing an edit set. This can often be quicker. However, since edit sets and haf files are interconnected, use haf files if you are making changes all the time.
One way to do this is to create a BATCH file. Call the batch file autohaf.bat and use the server Task Manager run it nightly. It renames the file to the date created so it saves all haf files created.
If you have not used batch files:
* Copy/paste the script below into NOTEPAD
* Save as autohaf with the .BAT extension
* Insert your database name (unless it is HEAT)
* Insert your Admin name and password.
* Make sure the user name has rights to export HAF files
===========================================================
echo off
cls
echo This file creates a HEAT archive file from the production HEAT database
echo .
echo Creating .haf file
echo .
echo Please wait....
echo .
"C:\Program Files\HEAT\LoadDB.exe" /d "HEAT" /u "Admin" /p "password" /e "C:\HAF\backup.haf"
for /f "tokens=1-4 delims=/ " %%a in ('date /t') do (set weekday=%%a& set day=%%b& set month=%%c& set year=%%d)
echo Renaming file to include todays date of %day% %month% %year%
ren c:\haf\backup.haf "backup_%day%_%month%_%year%.haf"
echo .
del c:\haf\fcactinkcom.txt
echo Backup and renaming of file complete
pause
ren Command Lines:
ren /d – ODBC Datasource name
ren /u – HEAT UserID that has rights to export HAF files
ren /p – Password of the HEAT UserID above
ren /e – Export directory including name of HAF file
===========================================================
Another option is to create a SQL backup prior to committing an edit set. This can often be quicker. However, since edit sets and haf files are interconnected, use haf files if you are making changes all the time.
HEAT: Copy and Paste Call ID
A client recently asked: Is there a way to make the Call ID into text that you can copy and paste?
A quick workaround to this is below:
* Use Administrator to create a text field on the Subset
* Create an Auto Task that copies {|CallLog.CallID} to the field
* Create a command button next to the field connects to the AutoTask
The Heat user needs to press the command button to the Call ID in the text field but now you copy and paste the Call ID.
A quick workaround to this is below:
* Use Administrator to create a text field on the Subset
* Create an Auto Task that copies {|CallLog.CallID} to the field
* Create a command button next to the field connects to the AutoTask
The Heat user needs to press the command button to the Call ID in the text field but now you copy and paste the Call ID.
HEAT: Creating Detail Screens with SQL
A client recently asked: Is there a way to change a call type without recreating the detail screen?
No, when a call type is changed, the associated detail screen must be recreated. This is not particularly easy using Administrator. You can copy and paste the form but the fields do not come over so they must be added manually.
If, however, you are handy with SQL and have a test environment to use, you can try the following SQL code to recreate a detail form using the SQL backend database.
A word of caution: this method is not supported by FRS so make sure you have a good backup first (haf file, sql backup). Also, it is recommended that you test it on a development server first.
However, if this works for you, it can save hundreds of hours if you have a number of detail forms to recreate. The SQL code is below:
/* Select * from HEATTbl where TableName = 'Detail' and TableType = 'Northgate housing' */
Update HEATTbl set TableType = '*new call type*' where TableName = 'Detail' and TableType = '*old call type*'
/* Select * from HEATFld where TableName = 'Detail' and TableType = 'Northgate Housing' */
Update HEATFld set TableType = '*new call type*' where TableName = 'Detail' and TableType = '*old call type*'
/* Select * from HEATCtrl where TableName = 'Detail' and TableType = 'Sales Order' */
Update HEATCtrl set TableType = '*new call type*' where TableName = 'Detail' and TableType = '*old call type*'
/* Select * from HEATForm where TableName = 'Detail' and TableType = 'Sales Order' */
Update HEATForm set TableType = '*new call type*' where TableName = 'Detail' and TableType = '*old call type*'
Note: You need to rebuild the catalog file for the clients so commit an edit set when you do this.
No, when a call type is changed, the associated detail screen must be recreated. This is not particularly easy using Administrator. You can copy and paste the form but the fields do not come over so they must be added manually.
If, however, you are handy with SQL and have a test environment to use, you can try the following SQL code to recreate a detail form using the SQL backend database.
A word of caution: this method is not supported by FRS so make sure you have a good backup first (haf file, sql backup). Also, it is recommended that you test it on a development server first.
However, if this works for you, it can save hundreds of hours if you have a number of detail forms to recreate. The SQL code is below:
/* Select * from HEATTbl where TableName = 'Detail' and TableType = 'Northgate housing' */
Update HEATTbl set TableType = '*new call type*' where TableName = 'Detail' and TableType = '*old call type*'
/* Select * from HEATFld where TableName = 'Detail' and TableType = 'Northgate Housing' */
Update HEATFld set TableType = '*new call type*' where TableName = 'Detail' and TableType = '*old call type*'
/* Select * from HEATCtrl where TableName = 'Detail' and TableType = 'Sales Order' */
Update HEATCtrl set TableType = '*new call type*' where TableName = 'Detail' and TableType = '*old call type*'
/* Select * from HEATForm where TableName = 'Detail' and TableType = 'Sales Order' */
Update HEATForm set TableType = '*new call type*' where TableName = 'Detail' and TableType = '*old call type*'
Note: You need to rebuild the catalog file for the clients so commit an edit set when you do this.
HEAT: Run Program Action
A client asked: Can I run remote desktop and other diagnostic software using an autotask?
Yes, there are several tools you can launch using the Run Program action:
1. Remote Desktop
Filename: c:\windows\system32\mstsc.exe
Arguments: /v:{X}
2. Ping
Filename: c:\windows\system32\ping.exe
Arguments: {X}-t
3. Search MS Knowledgebase with the Incident Description text
Filename: http://support.microsoft.com/search/default.aspx?spid=global&query={|CallLog.CallDesc} &catalog=LCID%3D2057&pwt=false&title=false&kt=ALL&mdt=0&comm=1&ast=1&ast=2&ast= 3&ast=8&ast=9&mode=a&x=11&y=9
4. Launch VNC: there are various versions of vnc
Filename: c:\program files\ultra vnc\vncviewer.exe
Arguments: {X}
5. Computer Management: this runs computer management for a target PC.
Filename: c:\windows\system32\compmgmt.msc
Arguments: /computer={X}
These autotasks may vary slightly based on the version of Windows.
Yes, there are several tools you can launch using the Run Program action:
1. Remote Desktop
Filename: c:\windows\system32\mstsc.exe
Arguments: /v:{X}
2. Ping
Filename: c:\windows\system32\ping.exe
Arguments: {X}-t
3. Search MS Knowledgebase with the Incident Description text
Filename: http://support.microsoft.com/search/default.aspx?spid=global&query={|CallLog.CallDesc} &catalog=LCID%3D2057&pwt=false&title=false&kt=ALL&mdt=0&comm=1&ast=1&ast=2&ast= 3&ast=8&ast=9&mode=a&x=11&y=9
4. Launch VNC: there are various versions of vnc
Filename: c:\program files\ultra vnc\vncviewer.exe
Arguments: {X}
5. Computer Management: this runs computer management for a target PC.
Filename: c:\windows\system32\compmgmt.msc
Arguments: /computer={X}
These autotasks may vary slightly based on the version of Windows.
Wednesday, August 26, 2009
GoldMine: GM+View Prompt
A client asked: When I run a GM+View, I get the following message “Running a system command on this item may be unsafe. Do you wish to continue?” How do I disable this prompt?
Depending on the GM+View, you may receive this message for GM+Views that launch an application. To remove this warning:
* Start Internet Explorer
* Go to Tools > Internet Options
* Click on the Security tab
* Select Custom
* Go to "Launching applications and files in an IFRAME"
* Check Enable
This will allow users to run the GM+View without receiving this warning.
Depending on the GM+View, you may receive this message for GM+Views that launch an application. To remove this warning:
* Start Internet Explorer
* Go to Tools > Internet Options
* Click on the Security tab
* Select Custom
* Go to "Launching applications and files in an IFRAME"
* Check Enable
This will allow users to run the GM+View without receiving this warning.
CRM: Working Outside the CRM SDK
A client asked: Is it possible to hide controls outside of the CRM SDK?
Yes, you can, however, this solution is technically unsupported by Microsoft as it goes outside the CRM SDK, however the customization is small and easy to reverse to get into a state where Microsoft is supported.
Below are some code samples on how to hide attributes, elements and sections. These are quick JavaScript solutions for hiding both controls on a CRM page and links in the left-hand navigation page on an Entity Form.
The code below provides three functions that can be used to show/hide specific items on a CRM form:
var HIDE = 'none';
var SHOW = 'block';
// Function to show/hide CRM controls on a CRM form
// such as text boxes, lookups, pick-lists etc.
function SetCrmControlVisible(elementName, visibility)
{
SetElementVisible(elementName + '_c', visibility);
SetElementVisible(elementName + '_d', visibility);
}
// Fuction to show/hide specific elements on a CRM form
// such as the left-hand link items (More Addresses, Workflows
// and even custom ISV links
function SetElementVisible(elementId, visibility)
{
var elem = document.getElementById(elementId);
if (elem != null)
{
elem.style.display = visibility;
}
}
// Function to show/hide Navigation "Sections" in the left-hand
// links (Such as Sales, Marketing and Service)
function SetParentElementVisible(elementId, visibility)
{
var elem = document.getElementById(elementId);
if (elem != null && elem.parentElement != null)
{
elem.parentElement.style.display = visibility;
}
}
Add the above code to the Entity OnLoad andyou are ready to show/hide elements on the CRM form.
Using the IE Developer toolbar, you can retrieve the id of the element to be hidden Open an entity record (in this case a Contact), press CTRL+N to open it in a new window, and then select IE Developer Toolbar.
Using the controls, select the item you want to hide and get the element id. In this case we have selected the “Opportunities” link and the id is “navOpps”
Using this, I can call the SetElementVisible function like this to hide the Opportunities link:
SetElementVisible('navOpps', HIDE);
To hide the entire “Sales” section from the left-hand navigation pane, I can once again find the id, but this time is of the parent item which would result in the following call to hide it:
SetParentElementVisible('_NA_SFA', HIDE);
Lastly, to hide an element on the form such as the “Job Title” field, you once again should use the IE Developer Toolbar to retrieve the element id and then call the SetCrmControlVisible function:
SetCrmControlVisible('jobtitle', HIDE);
By selecting either the textbox or the label, for the Job Title, the returned id will be “jobtitle_c” or “jobtitle_d” – remove everything including and after the “_” and pass that to the SerCrmControlVisible function and you are done!
Yes, you can, however, this solution is technically unsupported by Microsoft as it goes outside the CRM SDK, however the customization is small and easy to reverse to get into a state where Microsoft is supported.
Below are some code samples on how to hide attributes, elements and sections. These are quick JavaScript solutions for hiding both controls on a CRM page and links in the left-hand navigation page on an Entity Form.
The code below provides three functions that can be used to show/hide specific items on a CRM form:
var HIDE = 'none';
var SHOW = 'block';
// Function to show/hide CRM controls on a CRM form
// such as text boxes, lookups, pick-lists etc.
function SetCrmControlVisible(elementName, visibility)
{
SetElementVisible(elementName + '_c', visibility);
SetElementVisible(elementName + '_d', visibility);
}
// Fuction to show/hide specific elements on a CRM form
// such as the left-hand link items (More Addresses, Workflows
// and even custom ISV links
function SetElementVisible(elementId, visibility)
{
var elem = document.getElementById(elementId);
if (elem != null)
{
elem.style.display = visibility;
}
}
// Function to show/hide Navigation "Sections" in the left-hand
// links (Such as Sales, Marketing and Service)
function SetParentElementVisible(elementId, visibility)
{
var elem = document.getElementById(elementId);
if (elem != null && elem.parentElement != null)
{
elem.parentElement.style.display = visibility;
}
}
Add the above code to the Entity OnLoad andyou are ready to show/hide elements on the CRM form.
Using the IE Developer toolbar, you can retrieve the id of the element to be hidden Open an entity record (in this case a Contact), press CTRL+N to open it in a new window, and then select IE Developer Toolbar.
Using the controls, select the item you want to hide and get the element id. In this case we have selected the “Opportunities” link and the id is “navOpps”
Using this, I can call the SetElementVisible function like this to hide the Opportunities link:
SetElementVisible('navOpps', HIDE);
To hide the entire “Sales” section from the left-hand navigation pane, I can once again find the id, but this time is of the parent item which would result in the following call to hide it:
SetParentElementVisible('_NA_SFA', HIDE);
Lastly, to hide an element on the form such as the “Job Title” field, you once again should use the IE Developer Toolbar to retrieve the element id and then call the SetCrmControlVisible function:
SetCrmControlVisible('jobtitle', HIDE);
By selecting either the textbox or the label, for the Job Title, the returned id will be “jobtitle_c” or “jobtitle_d” – remove everything including and after the “_” and pass that to the SerCrmControlVisible function and you are done!
CRM: Disable Fields
A client wants to know: When creating custom solution in MSCRM is there a way to disable all the fields on a form (based on data entered)?
This is a frequent question while creating custom CRM solutions: How can I disable all or most of the fields on a CRM form based on data selected by the user.
The script below does this:
1: // disable all of the fields on the form.
2: DisableFormFields = function(onOff)
3: {
4: var iLen = crmForm.all.length;
5:
6: for (i = 0; i < iLen; i++)
7: {
8: o = crmForm.all[i];
9: switch (o.tagName)
10: {
11: case "INPUT":
12: case "SELECT":
13: case "TEXTAREA":
* 1: // disable all of the fields on the form.
2: DisableFormFields = function(onOff)
3: {
4: var iLen = crmForm.all.length;
5:
6: for (i = 0; i < iLen; i++)
7: {
8: o = crmForm.all[i];
9: switch (o.tagName)
10: {
11: case "INPUT":
12: case "SELECT":
13: case "TEXTAREA":
14: case "IMG":
15: case "IFRAME":
16: if (o.id != "leadqualitycode")
17: {
18: o.disabled = onOff;
19: }
20: break
21: default:
22: break;
23: }
24: }
25: }
This code should be placed in the form's OnLoad event. In the example, we're working with the Lead Entity. The DisableFormFields() function is actually attached to the OnChange event for the Lead Status Attribute. The value of Lead Status is checked and calls DisableFormFields(true) to disable the fields or DisableFormFields(false) to re-enable the fields.
Disabling the entire form is not good idea, so allow the user to change their mind. Looping through each of the form fields, we check the id of the field and while Lead Status is not encountered, the fields' disabled status is set to either true to disable the fields or false to re-enable them.
This is a frequent question while creating custom CRM solutions: How can I disable all or most of the fields on a CRM form based on data selected by the user.
The script below does this:
1: // disable all of the fields on the form.
2: DisableFormFields = function(onOff)
3: {
4: var iLen = crmForm.all.length;
5:
6: for (i = 0; i < iLen; i++)
7: {
8: o = crmForm.all[i];
9: switch (o.tagName)
10: {
11: case "INPUT":
12: case "SELECT":
13: case "TEXTAREA":
* 1: // disable all of the fields on the form.
2: DisableFormFields = function(onOff)
3: {
4: var iLen = crmForm.all.length;
5:
6: for (i = 0; i < iLen; i++)
7: {
8: o = crmForm.all[i];
9: switch (o.tagName)
10: {
11: case "INPUT":
12: case "SELECT":
13: case "TEXTAREA":
14: case "IMG":
15: case "IFRAME":
16: if (o.id != "leadqualitycode")
17: {
18: o.disabled = onOff;
19: }
20: break
21: default:
22: break;
23: }
24: }
25: }
This code should be placed in the form's OnLoad event. In the example, we're working with the Lead Entity. The DisableFormFields() function is actually attached to the OnChange event for the Lead Status Attribute. The value of Lead Status is checked and calls DisableFormFields(true) to disable the fields or DisableFormFields(false) to re-enable the fields.
Disabling the entire form is not good idea, so allow the user to change their mind. Looping through each of the form fields, we check the id of the field and while Lead Status is not encountered, the fields' disabled status is set to either true to disable the fields or false to re-enable them.
CRM: Changing the Default Organization
A client recently asked: How do I change the default organization in a multi-entity MSCRM installation?
You can change the default organization by opening the configuration manager. In this tool you can rightclick on the organization to set as the default and click "set as default". You'll see that the name of the organization has changed to: Orgname (default).
After you perform an iisreset you need to perform some additional steps. The default organization will not change for existing users, this change will only be applied for new users created after the change.
Each user record has stored the default organization, so you would need to change them for each one of the already created users. You have two methods. Either use the tool provided by Microsoft Support or directly modify the CRM Database to change the DefaultOrganizationId in the SystemUser table.
In either case, make a backup of the databases before making any changes.
You can change the default organization by opening the configuration manager. In this tool you can rightclick on the organization to set as the default and click "set as default". You'll see that the name of the organization has changed to: Orgname (default).
After you perform an iisreset you need to perform some additional steps. The default organization will not change for existing users, this change will only be applied for new users created after the change.
Each user record has stored the default organization, so you would need to change them for each one of the already created users. You have two methods. Either use the tool provided by Microsoft Support or directly modify the CRM Database to change the DefaultOrganizationId in the SystemUser table.
In either case, make a backup of the databases before making any changes.
CRM: Removing Locked Attributes
A client asked: There are attributes on CRM default forms which you cannot remove. Is there a workaround for this?
Yes, however, before using this method make sure you have a good backup. To remove attributes on default forms:
* Export customizations for the specific entity
* Modify the form in the customizations xml
* Import the modified customizations
To modify the customizations, open the file in notepad or another XML editor. In the file look for this path:
ImportExportXml - Entities - Entity - FormXml - forms - entity - form - tabs
Within tabs look for the tab with the correct name and similar for the sections. Become familiar with the section xml once you have found the correct section. You will find that there are a couple of lines that you need to remove. An example is below to remove the subjectid on the case entity:
<cell id="{a9859c32-0cdc-41b5-8e7e-3eb173cab4a8}">
<labels>
<label description="Onderwerp" languagecode="1043" />
<label description="Subject" languagecode="1033" />
</labels>
<control id="subjectid" classid="{270BD3DB-D9AF-4782-9025-509E298DEC0A}"
datafieldname="subjectid" />
</cell>
Attributes which are locked on the form includes the following:
case - subjectid
case - contractid
case - contractdetailid
Note: Some of the attributes are used in hidden javascript codes. For instance the case contract contractid is used in the script for the customer onchange. You can also remove those scripts from the customizations file.
Yes, however, before using this method make sure you have a good backup. To remove attributes on default forms:
* Export customizations for the specific entity
* Modify the form in the customizations xml
* Import the modified customizations
To modify the customizations, open the file in notepad or another XML editor. In the file look for this path:
ImportExportXml - Entities - Entity - FormXml - forms - entity - form - tabs
Within tabs look for the tab with the correct name and similar for the sections. Become familiar with the section xml once you have found the correct section. You will find that there are a couple of lines that you need to remove. An example is below to remove the subjectid on the case entity:
<cell id="{a9859c32-0cdc-41b5-8e7e-3eb173cab4a8}">
<labels>
<label description="Onderwerp" languagecode="1043" />
<label description="Subject" languagecode="1033" />
</labels>
<control id="subjectid" classid="{270BD3DB-D9AF-4782-9025-509E298DEC0A}"
datafieldname="subjectid" />
</cell>
Attributes which are locked on the form includes the following:
case - subjectid
case - contractid
case - contractdetailid
Note: Some of the attributes are used in hidden javascript codes. For instance the case contract contractid is used in the script for the customer onchange. You can also remove those scripts from the customizations file.
CRM: Customization Button Missing
A client recently asked: When I have the Outlook client open and open the web client the Customization option does not appear? Is there a fix for this?
This has been how MSCRM works for quite some time and is not considered an issue by Microsoft so it will most likely remain this way.
There is a workaround. Open the web client using the IP address and open the Outlook client using the server address. This gives each client a different cookie and the problem goes away.
This has been how MSCRM works for quite some time and is not considered an issue by Microsoft so it will most likely remain this way.
There is a workaround. Open the web client using the IP address and open the Outlook client using the server address. This gives each client a different cookie and the problem goes away.
Tuesday, August 25, 2009
Crystal Reports : Changing the database location
When developing a report, it's often against a development or "test" database. When it comes time to point your report to the real thing;
1. Select Database | Set Location.
2. Click the "Set Location" button.
3. This will launch the Crystal Database Explorer. Find your new database.
4. Click the "Set" button.
5. Crystal should now ask you if you want it to "Propagate Server and Database Changes across tables with the same original information?" Click yes.
6. "Ok" your way back out.
1. Select Database | Set Location.
2. Click the "Set Location" button.
3. This will launch the Crystal Database Explorer. Find your new database.
4. Click the "Set" button.
5. Crystal should now ask you if you want it to "Propagate Server and Database Changes across tables with the same original information?" Click yes.
6. "Ok" your way back out.
Crystal Reports : Speed Tips
Here are a few things I have found to help with report performance:
1. Select, from the menu, Database | Perform grouping on server. This helps against SQL Backends.
2. Constrain data using the Select Expert whenever possible.
3. Constrain against indexed fields whenever possible.
4. Whenever possible, Group on an actual database field, not on a formula.
1. Select, from the menu, Database | Perform grouping on server. This helps against SQL Backends.
2. Constrain data using the Select Expert whenever possible.
3. Constrain against indexed fields whenever possible.
4. Whenever possible, Group on an actual database field, not on a formula.
Crystal Reports : Changing the decimal precision on a field
In the case of "calculated" fields, especially where division is involved, the result can sometimes be a number like "10.25" or "13.53".
Crystal will default the formatting of such a result to include the decimal places as the calculation requires.
If you only wish to see the "integer" portion of the field in question, you need only to change the field formatting.
1. Right-click on the field in question
2. Select "Format Field"
3. On the "Common" tab, click "Customize"
4. Drop down the "Decimals" list to select your desired precision.
5. "Ok" your way back out.
Note : These Field Formatting options are only available on numeric fields. If your formula is returning a string, try using the ToNumber() Crystal function to convert it.
Crystal will default the formatting of such a result to include the decimal places as the calculation requires.
If you only wish to see the "integer" portion of the field in question, you need only to change the field formatting.
1. Right-click on the field in question
2. Select "Format Field"
3. On the "Common" tab, click "Customize"
4. Drop down the "Decimals" list to select your desired precision.
5. "Ok" your way back out.
Note : These Field Formatting options are only available on numeric fields. If your formula is returning a string, try using the ToNumber() Crystal function to convert it.
Monday, August 10, 2009
GoldMine: Search The U.K.
A client asked: Is there a GM+View that allows you to search for addresses in countries other than the US?
Yes, enter the following to search the UK:
Select Web > Setup GM+View
Click New > Enter a Template Name
Click in the large text box > click <H>
Copy and paste in the following:
---------------------------------------------------------------------
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<TITLE>Multimap Query on Zipcode</TITLE>
<META content="MSHTML 6.00.2800.1458" name=GENERATOR>
</HEAD>
<BODY leftMargin=1 topMargin=1 rightMargin=1>
<P>Getting Map Data from Multimap based on Postcode....please wait....
<SCRIPT language=javascript type=text/javascript>
<!--
window.location="http://www.multimap.co.uk/map/browse.cgi?client=public&db=pc&addr1=&client=public&addr2=&advanced=&addr3=&pc=<<&zip>>&quicksearch=<<&zip>>";
// -->
</SCRIPT>
</P>
</BODY>
</HTML>
---------------------------------------------------------------------
Click Save > Click OK
This will search the UK for contact addresses.
Yes, enter the following to search the UK:
Select Web > Setup GM+View
Click New > Enter a Template Name
Click in the large text box > click <H>
Copy and paste in the following:
---------------------------------------------------------------------
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<TITLE>Multimap Query on Zipcode</TITLE>
<META content="MSHTML 6.00.2800.1458" name=GENERATOR>
</HEAD>
<BODY leftMargin=1 topMargin=1 rightMargin=1>
<P>Getting Map Data from Multimap based on Postcode....please wait....
<SCRIPT language=javascript type=text/javascript>
<!--
window.location="http://www.multimap.co.uk/map/browse.cgi?client=public&db=pc&addr1=&client=public&addr2=&advanced=&addr3=&pc=<<&zip>>&quicksearch=<<&zip>>";
// -->
</SCRIPT>
</P>
</BODY>
</HTML>
---------------------------------------------------------------------
Click Save > Click OK
This will search the UK for contact addresses.
Goldmine: GM+View To Cross Sell
A client asked: Is there a GM+View that allows you to prospect for new opportunities or cross sell to your contacts?
Yes, one such site is Hoover.comand the following GM+View will search the Hoover database by company name:
Select Web > Setup GM+View
Click New > Enter a Template Name
Click in the large text box > click <H>
Copy and paste in the following:
---------------------------------------------------------------------
<HTML>
<HEAD>
<!--
This GM+ View Template is provided as part of The Hackers Guide to GoldMine, and
the author is providing this without guarentee or additional support.
-->
<SCRIPT language=JavaScript>
<!--
function redirect()
{
window.location = "http://search.hoovers.com/cgi-bin/hol_search?siteid=HBN&which=company&query_string=<<&strtran(trim(Contact1->Company), " ", "+")>;>;&dir_top_id=7"
}
setTimeout("redirect();", 0000)
// -->
</SCRIPT>
<STYLE type=text/css> P, UL, OL, DL, DIR, MENU, PRE { margin: 0 auto;}</STYLE>
<META content="Microsoft FrontPage 5.0" name=GENERATOR>
</HEAD>
<BODY leftMargin=1 topMargin=1 rightMargin=1>
<FONT face=Tahoma size=2>
</FONT>
</BODY>
</HTML>
---------------------------------------------------------------------
Click Save > Click OK
Go to a contact and select this GM+View from the drop-down menu. It will show the Hoover website and automatically populate the Hoover search with the contact name.
Yes, one such site is Hoover.comand the following GM+View will search the Hoover database by company name:
Select Web > Setup GM+View
Click New > Enter a Template Name
Click in the large text box > click <H>
Copy and paste in the following:
---------------------------------------------------------------------
<HTML>
<HEAD>
<!--
This GM+ View Template is provided as part of The Hackers Guide to GoldMine, and
the author is providing this without guarentee or additional support.
-->
<SCRIPT language=JavaScript>
<!--
function redirect()
{
window.location = "http://search.hoovers.com/cgi-bin/hol_search?siteid=HBN&which=company&query_string=<<&strtran(trim(Contact1->Company), " ", "+")>;>;&dir_top_id=7"
}
setTimeout("redirect();", 0000)
// -->
</SCRIPT>
<STYLE type=text/css> P, UL, OL, DL, DIR, MENU, PRE { margin: 0 auto;}</STYLE>
<META content="Microsoft FrontPage 5.0" name=GENERATOR>
</HEAD>
<BODY leftMargin=1 topMargin=1 rightMargin=1>
<FONT face=Tahoma size=2>
</FONT>
</BODY>
</HTML>
---------------------------------------------------------------------
Click Save > Click OK
Go to a contact and select this GM+View from the drop-down menu. It will show the Hoover website and automatically populate the Hoover search with the contact name.
GoldMine: Contact Website
A client asked: Is there a GM+View that lets you fill in the contact website and open it?
Yes, you can add this GM+View doing the following:
Select Web > Setup GM+View
Click New > Enter a Template Name
Click in the large text box > click <H>
Copy and paste in the following:
---------------------------------------------------------------------
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<SCRIPT>
function PopUp(){
var ScreenWidth=window.screen.width;
var ScreenHeight=window.screen.height;
var movefromedge=0;
placementx=0;
placementy=0;
var PopUpUrl="<<&website>;>;"
WinPop=window.open(PopUpUrl,"","width="+ScreenWidth+",height="+ScreenHeight+",toolbar=1,location=1,directories=1,status=1,scrollbars=1,menubar=1,resizable=1,left="+placementx+",top="+placementy+",screenX="+placementx+",screenY="+placementy+",");
}
</SCRIPT>
<META content="Microsoft FrontPage 5.0" name=GENERATOR>
</HEAD>
<BODY leftMargin=1 topMargin=1 onload=PopUp() rightMargin=1></BODY>
</HTML>
---------------------------------------------------------------------
Click Save > Click OK
Click on contact website if it does not exist. Add the website to the popup window and it will be added to the contact's record. Select this GM+View from the drop down menu and it will display the contact's website.
Yes, you can add this GM+View doing the following:
Select Web > Setup GM+View
Click New > Enter a Template Name
Click in the large text box > click <H>
Copy and paste in the following:
---------------------------------------------------------------------
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<SCRIPT>
function PopUp(){
var ScreenWidth=window.screen.width;
var ScreenHeight=window.screen.height;
var movefromedge=0;
placementx=0;
placementy=0;
var PopUpUrl="<<&website>;>;"
WinPop=window.open(PopUpUrl,"","width="+ScreenWidth+",height="+ScreenHeight+",toolbar=1,location=1,directories=1,status=1,scrollbars=1,menubar=1,resizable=1,left="+placementx+",top="+placementy+",screenX="+placementx+",screenY="+placementy+",");
}
</SCRIPT>
<META content="Microsoft FrontPage 5.0" name=GENERATOR>
</HEAD>
<BODY leftMargin=1 topMargin=1 onload=PopUp() rightMargin=1></BODY>
</HTML>
---------------------------------------------------------------------
Click Save > Click OK
Click on contact website if it does not exist. Add the website to the popup window and it will be added to the contact's record. Select this GM+View from the drop down menu and it will display the contact's website.
GoldMine: Finding Zip Code
A client asked: We often get addresses without zip codes. Is there a GM+View for this
Yes, to lookup a zip by address, enter the following into a GM+View:
Select Web > Setup GM+View
Click New > Enter a Template Name
Click in the large text box > click <H>
Copy and paste in the following:
---------------------------------------------------------------------
<HTML>
<HEAD>
<meta http-equiv=REFRESH
content="0;url=http://www.usps.com/zip4/welcome.htm?address1=<<contact1->;address1>;>;&address2=<<contact1->;address2>;>;&zip_city=<<contact1->;city>;>;&state=<<contact1->;state>;>;&zip=<<contact1->;zip>;>;">
</HEAD>
<BODY>
</body>
</HTML>
---------------------------------------------------------------------
Click Save > Click OK
Select the GM+View and you will get a form to enter an address and get the zip code
Yes, to lookup a zip by address, enter the following into a GM+View:
Select Web > Setup GM+View
Click New > Enter a Template Name
Click in the large text box > click <H>
Copy and paste in the following:
---------------------------------------------------------------------
<HTML>
<HEAD>
<meta http-equiv=REFRESH
content="0;url=http://www.usps.com/zip4/welcome.htm?address1=<<contact1->;address1>;>;&address2=<<contact1->;address2>;>;&zip_city=<<contact1->;city>;>;&state=<<contact1->;state>;>;&zip=<<contact1->;zip>;>;">
</HEAD>
<BODY>
</body>
</HTML>
---------------------------------------------------------------------
Click Save > Click OK
Select the GM+View and you will get a form to enter an address and get the zip code
QuickBooks: Using the Account Register
A client asked: When reconciling a bank account, how do I search the Bank Reconciliation Window?
In QuickBooks 2008 and prior, the bank reconciliation window shows disbursements in document number order only and does not allow you to sort by any of the other column like the transaction date, the payee or the amount.
In all versions of QuickBooks there is no "Go To" or "Search" feature on the Bank Reconciliation window.
To workaround this problem, you can do the following:
* Select Bank Reconciliation > enter ending balance, service charges.
* Continue to the window where you clear checks and deposits.
* Minimize the window and open the check register
* Sort the check register by 'Date/Type/Number' or by 'Amount'
You can also sort by check number but the bank reconciliation window sorts by check number already. The check register simply allows you to use additional sort options.
When you locate the transactions, use the check register to designate each check as cleared – using the cleared column in the register.
* If you cannot locate a check using sort options, use “Go To”
* Search the register by payee, number, memo or amount.
* Return to the bank reconciliation window.
* Transactions cleared will show a check mark in the window.
* Clear deposits and disbursements not cleared with the register.
In QuickBooks 2008 and prior, the bank reconciliation window shows disbursements in document number order only and does not allow you to sort by any of the other column like the transaction date, the payee or the amount.
In all versions of QuickBooks there is no "Go To" or "Search" feature on the Bank Reconciliation window.
To workaround this problem, you can do the following:
* Select Bank Reconciliation > enter ending balance, service charges.
* Continue to the window where you clear checks and deposits.
* Minimize the window and open the check register
* Sort the check register by 'Date/Type/Number' or by 'Amount'
You can also sort by check number but the bank reconciliation window sorts by check number already. The check register simply allows you to use additional sort options.
When you locate the transactions, use the check register to designate each check as cleared – using the cleared column in the register.
* If you cannot locate a check using sort options, use “Go To”
* Search the register by payee, number, memo or amount.
* Return to the bank reconciliation window.
* Transactions cleared will show a check mark in the window.
* Clear deposits and disbursements not cleared with the register.
Sunday, August 9, 2009
QuickBooks: Required Account Numbers
A client asked: Is there a way to require account numbers? There seems only an option to turn them on or off.
There is no preference to require the use of account numbers, just the option to turn them on or off. This option tells QuickBooks to show or hide the account numbers. The account numbers remain in the file
There is way, however, to require account numbers and even let you know if any accounts do not have accounts numbers assigned.
To require account numbers, do the following:
* Select the Edit menu and then select Preferences.
* Select Accounting Company Preferences
* Select Show Lowest subaccount Only.
* This option is active when you check Use Account Numbers.
* If all accounts have account numbers assigned, click OK.
QuickBooks will then require you to use an account number for all accounts you create in the future – and will not allow you to remove the account number from existing accounts although can edit the account numbers.
If there are accounts without account numbers you will see a window:
* You must then go to the Chart of Accounts
* Assign numbers to all accounts (even the inactive accounts)
* Return to the Accounting Company Preferences
* Select the Show Lowest Subaccount Only checkbox.
There is no preference to require the use of account numbers, just the option to turn them on or off. This option tells QuickBooks to show or hide the account numbers. The account numbers remain in the file
There is way, however, to require account numbers and even let you know if any accounts do not have accounts numbers assigned.
To require account numbers, do the following:
* Select the Edit menu and then select Preferences.
* Select Accounting Company Preferences
* Select Show Lowest subaccount Only.
* This option is active when you check Use Account Numbers.
* If all accounts have account numbers assigned, click OK.
QuickBooks will then require you to use an account number for all accounts you create in the future – and will not allow you to remove the account number from existing accounts although can edit the account numbers.
If there are accounts without account numbers you will see a window:
* You must then go to the Chart of Accounts
* Assign numbers to all accounts (even the inactive accounts)
* Return to the Accounting Company Preferences
* Select the Show Lowest Subaccount Only checkbox.
QuickBooks: Memorized Reports
A client recently asked: A memorized report stopped working and I had to recreate it. Is there a way to backup memorized reports?
Yes, you can backup memorized reports and there are a couple reasons you should be doing this. First, there is a known issue with memorized reports in QuickBooks Enterprise Solutions. Memorized reports will sometimes stop working for no apparent reason. A backup process will allow you to restore the memorized report.
Second, if a user needs the ability to manage the Memorized Report list, the user can inadvertently modify or delete memorized reports on the list. A backup will allow you to restore the original memorized report settings if you need to do so.
To backup your Memorized Reports:
* Create a user called "Report Backup" with Admin access.
* Be sure to use a password that only the file Administrator knows.
* Login as the "Report Backup" user.
* Open the memorized report you want to backup.
* Select the View drop down menu and then select "Add to Icon Bar."
Use the Memorized Report name as the title of the Icon Bar shortcut.
Yes, you can backup memorized reports and there are a couple reasons you should be doing this. First, there is a known issue with memorized reports in QuickBooks Enterprise Solutions. Memorized reports will sometimes stop working for no apparent reason. A backup process will allow you to restore the memorized report.
Second, if a user needs the ability to manage the Memorized Report list, the user can inadvertently modify or delete memorized reports on the list. A backup will allow you to restore the original memorized report settings if you need to do so.
To backup your Memorized Reports:
* Create a user called "Report Backup" with Admin access.
* Be sure to use a password that only the file Administrator knows.
* Login as the "Report Backup" user.
* Open the memorized report you want to backup.
* Select the View drop down menu and then select "Add to Icon Bar."
Use the Memorized Report name as the title of the Icon Bar shortcut.
QuickBooks: Access the Name List
A client asked: How do I access the Composite Name List without creating a check?
To create an easy way to access the Name List, perform the following:
* Open a check transaction.
* Place your cursor in the Payee field
* Press CTRL+L to display the names list
* With the list displayed, select the View menu
* Select 'Add Names List to Icon Bar'.
This will allow you quick access to the Name list in the future. This can be used for several purposes including deleting names, finding names in transactions and other funtions.
To create an easy way to access the Name List, perform the following:
* Open a check transaction.
* Place your cursor in the Payee field
* Press CTRL+L to display the names list
* With the list displayed, select the View menu
* Select 'Add Names List to Icon Bar'.
This will allow you quick access to the Name list in the future. This can be used for several purposes including deleting names, finding names in transactions and other funtions.
QuickBooks: Getting Error Details
A client asked: When I run Verify Data, I get an error message from QuickBooks to run the data repair utility. There is no way, however, to get details of the problem.
There is a way to find the details of the verification, but you must do the following:
* Press F2 to open the Product Information window.
* Press CTRL+2 to open the Tech Help window.
* On the tech help window, press tab that reads 'Open File'
* Highlight the file 'QBWIN.log' > click 'Open File'.
* Scroll until you see a long, double-line
* Scan the verification information for the word “error”
* Note information by the error (document number, trans type )
* Search the information in the report to find the transaction
* Delete and re-enter the transaction.
The problem will usually go away with the deleted transaction. If the verification problem is related to a list entry, it is almost always a duplicate list entry. You can solve this problem by renaming one of the two duplicate list entries and merging the two lists together.
There is a way to find the details of the verification, but you must do the following:
* Press F2 to open the Product Information window.
* Press CTRL+2 to open the Tech Help window.
* On the tech help window, press tab that reads 'Open File'
* Highlight the file 'QBWIN.log' > click 'Open File'.
* Scroll until you see a long, double-line
* Scan the verification information for the word “error”
* Note information by the error (document number, trans type )
* Search the information in the report to find the transaction
* Delete and re-enter the transaction.
The problem will usually go away with the deleted transaction. If the verification problem is related to a list entry, it is almost always a duplicate list entry. You can solve this problem by renaming one of the two duplicate list entries and merging the two lists together.
Thursday, July 30, 2009
GoldMine: Check Out Your Customers
A client asked: We use a number of websites to check out customers when we first sign them up. Is there a way to organize these sites with Goldmine?
Yes, you can use the GM+View feature in Goldmine to list the sites you want to check when you first take on a customer. The steps are below:
* Select Web > Setup GM+View
* Click New > Add a Template Name
* Click the large text area > click the <H> in the right corner
* Position the cursor after the </style> html tag entry
* Copy the html below over the remaining text in the GM+View
<META content="MSHTML 6.00.6000.16825" name=GENERATOR></HEAD>
<BODY leftMargin=1 topMargin=1 rightMargin=1>
<P><FONT face="Microsoft Sans Serif" size=2></B><STRONG>Use these links to check out your prospects: </STRONG></FONT></P>
<P><FONT face="Microsoft Sans Serif" size=2> </P>
<DIV><A href="http://www.whois.org/"><FONT color=#0000ff>Whois</FONT></A></DIV></FONT></A>
<DIV><A href="http://www.yellowpages.com/"><FONT color=#0000ff>Yellowpages</FONT></A></DIV></FONT></A>
<DIV><A href="http://www.weather.com/"><FONT color=#0000ff>Weather.com</FONT></A></DIV></FONT></A>
This will give you three web sites you can click on to find out information about your client's area, domain, and weather. You can add other sites by copying one of the sections and adding your own site to search.
Then click Save (Yellow button) and exit. Select the GM+View from the drop-down menu at the right.
Yes, you can use the GM+View feature in Goldmine to list the sites you want to check when you first take on a customer. The steps are below:
* Select Web > Setup GM+View
* Click New > Add a Template Name
* Click the large text area > click the <H> in the right corner
* Position the cursor after the </style> html tag entry
* Copy the html below over the remaining text in the GM+View
<META content="MSHTML 6.00.6000.16825" name=GENERATOR></HEAD>
<BODY leftMargin=1 topMargin=1 rightMargin=1>
<P><FONT face="Microsoft Sans Serif" size=2></B><STRONG>Use these links to check out your prospects: </STRONG></FONT></P>
<P><FONT face="Microsoft Sans Serif" size=2> </P>
<DIV><A href="http://www.whois.org/"><FONT color=#0000ff>Whois</FONT></A></DIV></FONT></A>
<DIV><A href="http://www.yellowpages.com/"><FONT color=#0000ff>Yellowpages</FONT></A></DIV></FONT></A>
<DIV><A href="http://www.weather.com/"><FONT color=#0000ff>Weather.com</FONT></A></DIV></FONT></A>
This will give you three web sites you can click on to find out information about your client's area, domain, and weather. You can add other sites by copying one of the sections and adding your own site to search.
Then click Save (Yellow button) and exit. Select the GM+View from the drop-down menu at the right.
GoldMine: Track By Color
A client asked: Is there an easy way to change labels and data fields based on the contents?
Yes, there are at least two ways. One is to the Goldmine Record Typing function. I have found this to be complicated to use if you only want to change field color. The second way is to use a simple dbase expression in the properties of the field you want to track (e.g. KEY1).
See the example below. It changes the KEY1 label and data field based on the contents. To use it:
* Right click on the Goldmine form > select Screen Design
* Double click on the KEY1 field > select the Color tab
* Enter the following expression in both the label and data areas:
IIF(KEY1='First', 139, IIF(KEY1='Second', 10526303, IIF(KEY1='Third', 25600, 0)))
Change the sample data (First, Second, Third) to your KEY1 data and you have an easy way to track fields by color.
Yes, there are at least two ways. One is to the Goldmine Record Typing function. I have found this to be complicated to use if you only want to change field color. The second way is to use a simple dbase expression in the properties of the field you want to track (e.g. KEY1).
See the example below. It changes the KEY1 label and data field based on the contents. To use it:
* Right click on the Goldmine form > select Screen Design
* Double click on the KEY1 field > select the Color tab
* Enter the following expression in both the label and data areas:
IIF(KEY1='First', 139, IIF(KEY1='Second', 10526303, IIF(KEY1='Third', 25600, 0)))
Change the sample data (First, Second, Third) to your KEY1 data and you have an easy way to track fields by color.
GoldMine: Google For Goldmine
A client asked: Is there a site that searches just for Goldmine error codes? When I use a search engine I get too many unrelated entries.
No, but you can create one. Google now provides a way to create your own focused, community driven search engine for a specific subject area. I took the liberty of creating a GoldMine one. It can be found here: Google for GoldMine Tech Stuff
Also like other search wiki's, this search engine can be customized by the users! If you see something in your results that has nothing to do with CRM software or GoldMine, tell the engine and it will respect that the next time you (or anyone else) searches for the same thing!
The search box can even be embedded on web sites or e-mails. To create one, go to this site: http://www.google.com/coop/cse/
* Click create custom search
* Fill in a search engine name (e.g. Google For Goldmine)
* Enter a description
* Leave language as English
* Leave sites to search as "Only sites I select"
* Add sites to search with each on one line
* For example: www.djhunt.us, support.frontrange.com
* Select the Standard option (the other starts at $100)
* Click Next
Test a search in the search box on the next page. An email will be sent to you with the site address (it is saved on google.com so it available to others). There will also be instructions with the email on modifying the site, adding sites, etc.
To see one created by The Marks Group, paste the following into Internet Explorer:
http://www.google.com/coop/cse?cx=006246694855219629252:lfwbaxvxp7q
Add this url to your favorites so when you have a Goldmine error, it will only search Goldmine specific sites.
No, but you can create one. Google now provides a way to create your own focused, community driven search engine for a specific subject area. I took the liberty of creating a GoldMine one. It can be found here: Google for GoldMine Tech Stuff
Also like other search wiki's, this search engine can be customized by the users! If you see something in your results that has nothing to do with CRM software or GoldMine, tell the engine and it will respect that the next time you (or anyone else) searches for the same thing!
The search box can even be embedded on web sites or e-mails. To create one, go to this site: http://www.google.com/coop/cse/
* Click create custom search
* Fill in a search engine name (e.g. Google For Goldmine)
* Enter a description
* Leave language as English
* Leave sites to search as "Only sites I select"
* Add sites to search with each on one line
* For example: www.djhunt.us, support.frontrange.com
* Select the Standard option (the other starts at $100)
* Click Next
Test a search in the search box on the next page. An email will be sent to you with the site address (it is saved on google.com so it available to others). There will also be instructions with the email on modifying the site, adding sites, etc.
To see one created by The Marks Group, paste the following into Internet Explorer:
http://www.google.com/coop/cse?cx=006246694855219629252:lfwbaxvxp7q
Add this url to your favorites so when you have a Goldmine error, it will only search Goldmine specific sites.
Goldmine: Display Multiple Contacts Simultaneously
A client wants to know: How can you view multiple GoldMine records simultaneously?
By default, GoldMine displays one contact record at a time. If you also want to open other contact windows simultaneously, follow these steps:
* Select Window > New Contact Window. This will bring up the Contact List.
* From the contact list, select the second contact you wish to see and double click on that contact.
This opens the second contact window. You will see a second record tab at the top of your screen. Then, select Window > Tile Horizontally for a side by side view or Cascade Windows for a Vista-style window display
This option is particularly useful when you wish to compare two contact records in GoldMine. It saves time by eliminating the need to alternate back and forth between the two records.
By default, GoldMine displays one contact record at a time. If you also want to open other contact windows simultaneously, follow these steps:
* Select Window > New Contact Window. This will bring up the Contact List.
* From the contact list, select the second contact you wish to see and double click on that contact.
This opens the second contact window. You will see a second record tab at the top of your screen. Then, select Window > Tile Horizontally for a side by side view or Cascade Windows for a Vista-style window display
This option is particularly useful when you wish to compare two contact records in GoldMine. It saves time by eliminating the need to alternate back and forth between the two records.
Goldmine: Searching Notes
Did you know that you can search through the notes in GoldMine?
You can search your notes in GoldMine using the Contact Search Center.
* Open the Contact Search Center.
* Select Search By > select Notes from the drop down list.
* Contains is autofilled > enter the text to search for
* As you enter text in the search field, it will bring up contacts
The result will list all contacts whose notes contain the word(s) you have searched for.
Note: this will also search for words that are anywhere in the Note, so if you are looking for the word 'hired' in Notes, it would show "Dave was hired in 2001", "He was hired 60 days after the interview", etc.
You can search your notes in GoldMine using the Contact Search Center.
* Open the Contact Search Center.
* Select Search By > select Notes from the drop down list.
* Contains is autofilled > enter the text to search for
* As you enter text in the search field, it will bring up contacts
The result will list all contacts whose notes contain the word(s) you have searched for.
Note: this will also search for words that are anywhere in the Note, so if you are looking for the word 'hired' in Notes, it would show "Dave was hired in 2001", "He was hired 60 days after the interview", etc.
GoldMine: Scheduling for Non-Goldmine Contacts
A client asked: When I have someone on the phone, I do not want to take the time to add a contact to Goldmine before I schedule a meeting. How do I create an activity for new contact when the contact is not in Goldmine?
You can actually create a new Contact while scheduling an activity:
This is useful when you realize that the contact that you want to schedule the activity for does not exist in your GoldMine. So, instead of canceling the scheduling activity, you can create a new contact in GoldMine on the fly.
Here is how you do it:
* Select Schedule > Appointment to open 'Schedule an Appointment'.
* Fill in all the details and set the alarm.
* Click on the button to the right of the 'Contact' field
* From the options, select 'Create a new contact'.
* This will open a New Company and Contact dialog box.
* Fill out the contact information and select OK.
A new contact is now created in GoldMine along with the activity scheduled for that contact.
You can actually create a new Contact while scheduling an activity:
This is useful when you realize that the contact that you want to schedule the activity for does not exist in your GoldMine. So, instead of canceling the scheduling activity, you can create a new contact in GoldMine on the fly.
Here is how you do it:
* Select Schedule > Appointment to open 'Schedule an Appointment'.
* Fill in all the details and set the alarm.
* Click on the button to the right of the 'Contact' field
* From the options, select 'Create a new contact'.
* This will open a New Company and Contact dialog box.
* Fill out the contact information and select OK.
A new contact is now created in GoldMine along with the activity scheduled for that contact.
GoldMine: Scheduling a Team
A client recently asked: Our company schedules activities for a team, not an individual. Can GoldMine schedule activities to a team?
If you have more than one person in your sales team working on the same account, you can use GoldMine to schedule tasks to everyone involved at the same time. Here's how you can do it:
* Select the contact that you would like to schedule.
* Select Schedule and choose the type of activity.
* In the next window, click on the Users tab
* Select the users you wish to add
* Click on the user name in the left-hand column
* Use the Select button to add the user to the right-hand column.
* Use the Free/Busy tab to check scheduling conflicts for the users.
* Once you have added all the sales people, click on Schedule.
This will add the activity (appointments, calls, etc.) to each GoldMine user’s calendar and activity list.
If you have more than one person in your sales team working on the same account, you can use GoldMine to schedule tasks to everyone involved at the same time. Here's how you can do it:
* Select the contact that you would like to schedule.
* Select Schedule and choose the type of activity.
* In the next window, click on the Users tab
* Select the users you wish to add
* Click on the user name in the left-hand column
* Use the Select button to add the user to the right-hand column.
* Use the Free/Busy tab to check scheduling conflicts for the users.
* Once you have added all the sales people, click on Schedule.
This will add the activity (appointments, calls, etc.) to each GoldMine user’s calendar and activity list.
GoldMine: Multiple Websites
A client wants to know: Is it possible to add multiple websites to a single contact record?
If you have contacts that have more than one website or a customer that added another website and you want to retain both the websites, GoldMine has an option that allows you to enter additional websites for the same contact record.
* Open the contact record, for which you would like to add an additional website, and click to the right of the Web Site field. The 'Web sites' window will open and you can type in the web address.
* This window allows you to add multiple web sites, and to update the URL as needed without losing the original URL. Click OK to exit the window.
* Another option is the Details tab. You will find your contact's website listed under the Details tab. Right-click on it, select New and type in the additional web site URL.
This feature allows you to enter as many websites for a contact record as you want.
If you have contacts that have more than one website or a customer that added another website and you want to retain both the websites, GoldMine has an option that allows you to enter additional websites for the same contact record.
* Open the contact record, for which you would like to add an additional website, and click to the right of the Web Site field. The 'Web sites' window will open and you can type in the web address.
* This window allows you to add multiple web sites, and to update the URL as needed without losing the original URL. Click OK to exit the window.
* Another option is the Details tab. You will find your contact's website listed under the Details tab. Right-click on it, select New and type in the additional web site URL.
This feature allows you to enter as many websites for a contact record as you want.
GoldMine: Recently Viewed Contacts
A client asked: How do I change the number of recently viewed contacts in GoldMine
In GoldMine Premium, you set the number of recently viewed contacts to display. Just select the following:
Tools | Options | System
Change recent items shown to an appropriate value.
Remember that having a high value, could effect the performance of your GoldMine system (i.e. it might make it slower).
In GoldMine Premium, you set the number of recently viewed contacts to display. Just select the following:
Tools | Options | System
Change recent items shown to an appropriate value.
Remember that having a high value, could effect the performance of your GoldMine system (i.e. it might make it slower).
GoldMine: Major Activity Types
A client asked: When logging my activities I use the same Activity Type (Next Action) for everything. Is there a better way to organize my schedule?
Yes, many GoldMine users find the nine different activity types a bit confusing. Here is a brief activity type description and when to use each. They are broken down into four Major types which you’ll use all the time and then the rest.
Four Major Activity Types:
Calls – Phone calls (not a sales call when you go visit someone), follow up phone calls, introductory phone calls, conference calls, and other call types.
Next Action – This is used as a “To Do” related to a prospect or customer such as writing up a quote, preparing for a demo, doing research or following up via email.
Appointment – Face to face meeting could be for sales, service, or an internal meeting.
Sale – Forecasted Sales are to help you develop and keep track of your sales pipeline up to date so you know what deals you have in your sales funnel.
The Remaining Types:
Literature Request – Seldom used portion of GoldMine that lets your delegate the task of sending our literature.
Other Action – Activities that can’t be categorized into one of the other categories.
Events – Multiple day activity such as sales conference or sales meeting. These don’t show up in the timed portion of your calendar in the Day and Week view so it can be easy to schedule over them.
To Do – A generic to do. These don’t have due dates associated with them so I generally prefer to use Next Actions for anything that’s time sensitive. Also, To Dos are not, by default, linked to a contact record.
GoldMine Email – An email sent to another GoldMine user via it’s internal email center. These won’t go do Outlook so unless you’re using GoldMine for your incoming and outgoing back you’ll be better off not using these as the message might not get read.
The GoldMine calendar will show a small icon indicating which activity type you’ve scheduled and will be organized by the four Major activity types.
Yes, many GoldMine users find the nine different activity types a bit confusing. Here is a brief activity type description and when to use each. They are broken down into four Major types which you’ll use all the time and then the rest.
Four Major Activity Types:
Calls – Phone calls (not a sales call when you go visit someone), follow up phone calls, introductory phone calls, conference calls, and other call types.
Next Action – This is used as a “To Do” related to a prospect or customer such as writing up a quote, preparing for a demo, doing research or following up via email.
Appointment – Face to face meeting could be for sales, service, or an internal meeting.
Sale – Forecasted Sales are to help you develop and keep track of your sales pipeline up to date so you know what deals you have in your sales funnel.
The Remaining Types:
Literature Request – Seldom used portion of GoldMine that lets your delegate the task of sending our literature.
Other Action – Activities that can’t be categorized into one of the other categories.
Events – Multiple day activity such as sales conference or sales meeting. These don’t show up in the timed portion of your calendar in the Day and Week view so it can be easy to schedule over them.
To Do – A generic to do. These don’t have due dates associated with them so I generally prefer to use Next Actions for anything that’s time sensitive. Also, To Dos are not, by default, linked to a contact record.
GoldMine Email – An email sent to another GoldMine user via it’s internal email center. These won’t go do Outlook so unless you’re using GoldMine for your incoming and outgoing back you’ll be better off not using these as the message might not get read.
The GoldMine calendar will show a small icon indicating which activity type you’ve scheduled and will be organized by the four Major activity types.
Wednesday, July 29, 2009
HEAT: Error When Adding Call
A client asked: When trying to log new call, the following ODBC error appears: 'Incorrect syntax near the keyword with'. What can I do to resolve this?
This error occurs because the "with" statement was added to lock the Heatseq table while other users were accessing it, thereby creating a scenario that would eliminate duplicate Call IDs.
The full error is:
Incorrect syntax near the keyword with Last SQL String:
Update HEATSeq With (Serializable,TabLockX) Set SeqValue=SeqValue+1 where SeqKey=CallID Last ODBC Command:
Function: SQLExecDirect(Update HEATSeq With (Serializable,TabLockX)
Set SeqValue=SeqValue+1 where SeqKey=CallID)
To get around the error, create a file called Winheat.ini in the System folder, create an [OPTIONS] section and then add the following command: NOSQLSERVERLOCK=1
This should allow you to add new calls without the error.
This error occurs because the "with" statement was added to lock the Heatseq table while other users were accessing it, thereby creating a scenario that would eliminate duplicate Call IDs.
The full error is:
Incorrect syntax near the keyword with Last SQL String:
Update HEATSeq With (Serializable,TabLockX) Set SeqValue=SeqValue+1 where SeqKey=CallID Last ODBC Command:
Function: SQLExecDirect(Update HEATSeq With (Serializable,TabLockX)
Set SeqValue=SeqValue+1 where SeqKey=CallID)
To get around the error, create a file called Winheat.ini in the System folder, create an [OPTIONS] section and then add the following command: NOSQLSERVERLOCK=1
This should allow you to add new calls without the error.
HEAT: Differences Between Call Logging and WebUI
A client wants to know: HEAT 9.0 has two Call Logging interfaces. Are there major differences between Call Logging and Call Log WebUI?
HEAT 9.0 features the normal Call Log client (CallLog.exe) and a new Web User Interface. The Web User Interface offers most of the functionality of Call Logging, but there are differences. Listed below are: 1)Features in Call Logging, not in WebUI; 2) Features in WebUI, not in Call Logging:
1) In Call Logging, Not in WebUI
--------------------------------
Place Calls On Hold
Create and View Broadcast Messages
Customized Toolbars
SupportMail
HEAT Alarms
View Activity Log, Create Activity Log
Call Logging Dashboard
Table Maintenance
Asset Configuration Window
Call Transfer
Crystal Reports
Create AutoTask Actions
Jump to Control
Create Profile, Update Profile
Create Contact, Update Contact
Create Configuration, Update Configuration
Create Customer Login, Update Customer Login
Send A Broadcast
If Field is Empty
Display Message box
Goto Call(Ticket)
Create HEATBoard Issue
Web Browse
Run A Program to Launch Other Files
Write A File To Disk
Print Information
Create Activity Log Record
Execute External Service Request
Perform Dynamic Data Exchange
Create An Alarm
Execute SLA with ITSM
DateAdjust - Custom Calendar
DateDiff - Custom Calendar
TimeAdd - Custom Calendar
TimeAdjust - Custom Calendar
TimeDiff - Custom Calendar
StrConcat, Strlower, StrUpper, StrSub
StrTrimL, StrTrimR
DDERequest
IF
ReadFile
Move back one field Shift+Tab
Undo Ctrl+Z
Alarms Shift+F11
Assignment, Acknowledge Shift+F2
Assignment, Resolve Ctrl+Shift+F2
Call, First Shift+F7, Call, Last Shift+F8
Call, Next F8, Call, Previous F7
Call Group, Close Ctrl+F4
Call Group, Open Existing Ctrl+O
Call Map, Go to Ctrl+Alt+M
Call on Hold, Place Ctrl+H
Call Record Browse, Display Ctrl+B
Call Ticket Report, Run Ctrl+Alt+R
Clear a Date or Time Field F2
Crystal Decisions Crystal Reports Ctrl+D
Customer Types, Change Ctrl+T
Display Calendar in Date Field F4
Fill in System Date/Time F3
First Level Support (FLS) Ctrl+F
HEATBoard Current Call Information Ctrl+Alt+C
HEATBoard, Go to Ctrl+Alt+B
Help F1
InfoCenter, Show Ctrl+Alt+I
Moves between Call Groups Ctrl+Ta
My Hot List, Add Call Ctrl+M
Next Assignment/ Journal Ctrl+Page Down
Previous Assignment/ Journal Ctrl+Page Up
Print Active Call Group Ctrl+Shift+P
Print Call Ticket Ctrl+P
Profile F5
Refresh Call Record Ctrl+F5
Refreshes Active Call Group Ctrl+E
Run Crystal Report Ctrl+Shift+D
Run/Edit Report Shortcut Ctrl+Alt+D
Search, Perform Ctrl+Shift+S
Spell Check Shift+F9
Subset, Autosize F12
SupportMail F11
Un/Lock Call Record Ctrl+L
Module Integration
FrontRange IT Service ManagementYesNo
2) In WebUI, Not in Call Logging
-------------------------------
Create Assignment from Call Log Short Cut
Create Journal from Call Log Short Cut
Add Attachment from Call Log Short Cut
Tabbed Browsing
HEAT 9.0 features the normal Call Log client (CallLog.exe) and a new Web User Interface. The Web User Interface offers most of the functionality of Call Logging, but there are differences. Listed below are: 1)Features in Call Logging, not in WebUI; 2) Features in WebUI, not in Call Logging:
1) In Call Logging, Not in WebUI
--------------------------------
Place Calls On Hold
Create and View Broadcast Messages
Customized Toolbars
SupportMail
HEAT Alarms
View Activity Log, Create Activity Log
Call Logging Dashboard
Table Maintenance
Asset Configuration Window
Call Transfer
Crystal Reports
Create AutoTask Actions
Jump to Control
Create Profile, Update Profile
Create Contact, Update Contact
Create Configuration, Update Configuration
Create Customer Login, Update Customer Login
Send A Broadcast
If Field is Empty
Display Message box
Goto Call(Ticket)
Create HEATBoard Issue
Web Browse
Run A Program to Launch Other Files
Write A File To Disk
Print Information
Create Activity Log Record
Execute External Service Request
Perform Dynamic Data Exchange
Create An Alarm
Execute SLA with ITSM
DateAdjust - Custom Calendar
DateDiff - Custom Calendar
TimeAdd - Custom Calendar
TimeAdjust - Custom Calendar
TimeDiff - Custom Calendar
StrConcat, Strlower, StrUpper, StrSub
StrTrimL, StrTrimR
DDERequest
IF
ReadFile
Move back one field Shift+Tab
Undo Ctrl+Z
Alarms Shift+F11
Assignment, Acknowledge Shift+F2
Assignment, Resolve Ctrl+Shift+F2
Call, First Shift+F7, Call, Last Shift+F8
Call, Next F8, Call, Previous F7
Call Group, Close Ctrl+F4
Call Group, Open Existing Ctrl+O
Call Map, Go to Ctrl+Alt+M
Call on Hold, Place Ctrl+H
Call Record Browse, Display Ctrl+B
Call Ticket Report, Run Ctrl+Alt+R
Clear a Date or Time Field F2
Crystal Decisions Crystal Reports Ctrl+D
Customer Types, Change Ctrl+T
Display Calendar in Date Field F4
Fill in System Date/Time F3
First Level Support (FLS) Ctrl+F
HEATBoard Current Call Information Ctrl+Alt+C
HEATBoard, Go to Ctrl+Alt+B
Help F1
InfoCenter, Show Ctrl+Alt+I
Moves between Call Groups Ctrl+Ta
My Hot List, Add Call Ctrl+M
Next Assignment/ Journal Ctrl+Page Down
Previous Assignment/ Journal Ctrl+Page Up
Print Active Call Group Ctrl+Shift+P
Print Call Ticket Ctrl+P
Profile F5
Refresh Call Record Ctrl+F5
Refreshes Active Call Group Ctrl+E
Run Crystal Report Ctrl+Shift+D
Run/Edit Report Shortcut Ctrl+Alt+D
Search, Perform Ctrl+Shift+S
Spell Check Shift+F9
Subset, Autosize F12
SupportMail F11
Un/Lock Call Record Ctrl+L
Module Integration
FrontRange IT Service ManagementYesNo
2) In WebUI, Not in Call Logging
-------------------------------
Create Assignment from Call Log Short Cut
Create Journal from Call Log Short Cut
Add Attachment from Call Log Short Cut
Tabbed Browsing
HEAT: Crystal Quick Report
A client asked: Is there a simple way to create Crystal Reports without a lot of training or experience?
Yes, the report creation process can be simplified by using a single report to show whatever data you want to see. This can be done by passing a Call Group into an unrestricted report.
To create this universal report, you would:
* Create a single Crystal Report without parameters.
* Add the fields you want to see and nothing else.
* You can use an Answer Wizard report or start scratch.
* Create a report shortcut within Call Logging.
* Pick the Selection Formula tab > Limit to current call group at runtime.
* Create and run a call group and then run the report you just created.
This technique is good a particular printout to view their assignments in a simple grid.
Yes, the report creation process can be simplified by using a single report to show whatever data you want to see. This can be done by passing a Call Group into an unrestricted report.
To create this universal report, you would:
* Create a single Crystal Report without parameters.
* Add the fields you want to see and nothing else.
* You can use an Answer Wizard report or start scratch.
* Create a report shortcut within Call Logging.
* Pick the Selection Formula tab > Limit to current call group at runtime.
* Create and run a call group and then run the report you just created.
This technique is good a particular printout to view their assignments in a simple grid.
HEAT: Securing ODBC
A client asked: Is ODBC and SQL authentication secure? Could this allow someone to breach our database security?
Locking down ODBC is very important. There are reasons which require SQL Authentication, for example using modules like HEAT Self Service. However, if your HEAT database uses MS SQL and everyone logs in via Microsoft Active Directory, and you continue to use SQL Authentication, then consider the alternative below:
* Create an Active Directory group called HEAT Users and another called HEAT Admins.
* Place Active Directory Users inside their respective groups.
* Go into SQL Studio Manager and create an SQL user that points to the HEAT Users group and another that points to the HEAT Admins group.
* Give HEAT Users public, db reader and db writer rights and HEAT Admins database owner rights.
* Update all of your HEAT Users' ODBC connections to use Windows Authentication, instead of SQL Authentication. Be sure to place whoever commits edit sets into the HEAT Admins group.
Following these steps will improve security and solve the problem of always having to enter an SQL password when logging into HEAT. Remember to include any HEAT services that use a service account into at least the HEAT Users group so that it may continue to log into the database.
Also, HEAT Web UI and HEAT Self Service will continue to need SQL Authentication. If you are using either of these modules, it may be a good idea to tighten security for that SQL user so that it only has normal public rights to the database.
Locking down ODBC is very important. There are reasons which require SQL Authentication, for example using modules like HEAT Self Service. However, if your HEAT database uses MS SQL and everyone logs in via Microsoft Active Directory, and you continue to use SQL Authentication, then consider the alternative below:
* Create an Active Directory group called HEAT Users and another called HEAT Admins.
* Place Active Directory Users inside their respective groups.
* Go into SQL Studio Manager and create an SQL user that points to the HEAT Users group and another that points to the HEAT Admins group.
* Give HEAT Users public, db reader and db writer rights and HEAT Admins database owner rights.
* Update all of your HEAT Users' ODBC connections to use Windows Authentication, instead of SQL Authentication. Be sure to place whoever commits edit sets into the HEAT Admins group.
Following these steps will improve security and solve the problem of always having to enter an SQL password when logging into HEAT. Remember to include any HEAT services that use a service account into at least the HEAT Users group so that it may continue to log into the database.
Also, HEAT Web UI and HEAT Self Service will continue to need SQL Authentication. If you are using either of these modules, it may be a good idea to tighten security for that SQL user so that it only has normal public rights to the database.
HEAT: Speed Up Edit Sets
A client recently asked: Whenever we commit an edit set, it takes hours to commit. Is there a way to speed up the process?
Understanding how an edit set works can shorten the time to commit. For example if you have a 800MB, 500K record HEAT system and perform several changes on it, this could take several hours to commit. To shorten the time, make fewer changes in each edit set and the process will be a lot shorter.
No matter the size of your HEAT system, the following will speed up changes:
Use the HEAT Server: Try and commit the edit set on the same server the database is on as it keeps the data local. If your security 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 router.
Know Your Changes: The changes you make directly affect the length of time of your commit. For example, if you add a field to the CallLog table, which has 500K records, those 500K records will have to unload first, 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 edit sets. This applies to all other tables.
How Edit Sets Commit: 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.
Know What To Use: 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 500K records only takes a few seconds.
As always, back up your HEAT system before making any changes
Understanding how an edit set works can shorten the time to commit. For example if you have a 800MB, 500K record HEAT system and perform several changes on it, this could take several hours to commit. To shorten the time, make fewer changes in each edit set and the process will be a lot shorter.
No matter the size of your HEAT system, the following will speed up changes:
Use the HEAT Server: Try and commit the edit set on the same server the database is on as it keeps the data local. If your security 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 router.
Know Your Changes: The changes you make directly affect the length of time of your commit. For example, if you add a field to the CallLog table, which has 500K records, those 500K records will have to unload first, 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 edit sets. This applies to all other tables.
How Edit Sets Commit: 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.
Know What To Use: 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 500K records only takes a few seconds.
As always, back up your HEAT system before making any changes
CRM: Restrict Direct Email Functions
A client asked: A user accidentally emailed every contact in our database. Is there a way to restrict the use of the Direct Email button?
When you open the Account or Contacts entity, you will see an email button on the toolbar. This button allows you to select a group of contacts or accounts via a view or advanced find, and send them an email. This functionality can be a very useful but it can also be dangerous, as a user can easily email every contact or account in your database.
It is best to restrict access to Direct Email to only those users who need to have access.
Restricting access to Direct Email, however, is not an obvious process. There is no permission in CRM security roles for “Direct EMail” or “Bulk Email.” There is a permission option for Email; however, you won’t want to restrict users from creating emails. If you do, they will not be able to track their email communications in CRM, which is a major reason to use CRM.
The answer is in the way that Direct Email works. Direct Email is dependent on templates — you can’t just hit bulk email and type a message. You have to first create an email template, and then select that template from the Direct Email form.
So, to restrict Direct Email functionality from a group of users, use the following process:
* Select Settings > Administration > Security Roles
* Open the Security role of the group of users restrict
* Click the Core Records tab
* Remove permissions for Email templates
* Save and close the Security Role.
Now when the users with this role log in to CRM, they will no longer see the Direct Email button on the toolbar
When you open the Account or Contacts entity, you will see an email button on the toolbar. This button allows you to select a group of contacts or accounts via a view or advanced find, and send them an email. This functionality can be a very useful but it can also be dangerous, as a user can easily email every contact or account in your database.
It is best to restrict access to Direct Email to only those users who need to have access.
Restricting access to Direct Email, however, is not an obvious process. There is no permission in CRM security roles for “Direct EMail” or “Bulk Email.” There is a permission option for Email; however, you won’t want to restrict users from creating emails. If you do, they will not be able to track their email communications in CRM, which is a major reason to use CRM.
The answer is in the way that Direct Email works. Direct Email is dependent on templates — you can’t just hit bulk email and type a message. You have to first create an email template, and then select that template from the Direct Email form.
So, to restrict Direct Email functionality from a group of users, use the following process:
* Select Settings > Administration > Security Roles
* Open the Security role of the group of users restrict
* Click the Core Records tab
* Remove permissions for Email templates
* Save and close the Security Role.
Now when the users with this role log in to CRM, they will no longer see the Direct Email button on the toolbar
Tuesday, July 28, 2009
CRM: Replace Primary Entity Lookup
A client recently asked: Can I display a related entity field in a CRM Lookup instead of the primary entity field?
Yes, but it requires some javascript. A CRM lookup only displays its related entity primary field. This cannot be changed using customizations, however, there are occasions where you want to display another information in order to avoid opening a related entity form.
The javadcript function below will display another attibute inside CRM lookup.
Example:
On a contact form you want to display the accountnumber field, inside the parent customer lookup, instead of primary field of the account entity.
Select Settings > Customizations > Customize Entities
Select Account > open the Main Application Form
On the form select the lookup > select Events > select Onload
Step 1:
In the onload event, add this line of code and change LookupSchemaName to the schema name of your lookup.
crmForm.all.LookupSchemaName.FireOnChange();
Step 2
On the onchange event of the lookup, copy the following code:
---------------------------------------------------------------
Fields to add:
fieldToDisplay = the name of the attribute that you want to display in the lookup.
Take the schema name of the attribute in the linked entity.
fieldToDisplayIsText:
true if you want to display a nvarchar field.
false if you want to display a picklist or a lookup field.
organizationName = name of your organization (without spaces).
---------------------------------------------------------------
Javascript code:
var fieldToDisplay = 'accountnumber';
var fieldToDisplayIsText = true;
var organizationName = 'MyOrganizationName';
var lookupData = new Array();
var lookupItem= new Object();
var lookup = event.srcElement.DataValue;
if (typeof(lookup) != 'undefined' && lookup != null && lookup[0] != null)
{
var myValue = GetAttributeValueFromID(lookup[0].typename,lookup[0].id,fieldToDisplay,fieldToDisplayIsText);
if(myValue != '')
{
lookupItem.id = lookup[0].id;
lookupItem.typename = lookup[0].typename;
lookupItem.name = myValue;
lookupData[0] = lookupItem;
crmForm.all[event.srcElement.id].DataValue = lookupData;
}
}
function GetAttributeValueFromID(sEntityName, sGUID, sAttributeName, isTextField)
{
var xml = "" +
"" +
"" +
GenerateAuthenticationHeader() +
"" +
"" +
"" +
"" +
"" + sEntityName + " " +
"" + sGUID + " " +
" " +
"" +
"" +
"" + sAttributeName + " " +
" " +
" " +
" " +
" " +
" " +
" " +
"";
var xmlHttpRequest = new ActiveXObject("Msxml2.XMLHTTP");
xmlHttpRequest.Open("POST", "/mscrmservices/2007/CrmService.asmx", false);
xmlHttpRequest.setRequestHeader("SOAPAction","http://schemas.microsoft.com/crm/2007/WebServices/Execute");
xmlHttpRequest.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
xmlHttpRequest.setRequestHeader("Content-Length", xml.length);
xmlHttpRequest.send(xml);
var result = null;
if(isTextField){
result = xmlHttpRequest.responseXML.selectSingleNode("//q1:" + sAttributeName).text;
}
else
{
result = xmlHttpRequest.responseXML.selectSingleNode("//q1:" + sAttributeName).getAttribute('name');
}
if (result == null)
{
return '';
}
else
return result;
}
Step 3:
Save and publish the entity
Note: This code be used for other lookups, the account is just provided as an example
Yes, but it requires some javascript. A CRM lookup only displays its related entity primary field. This cannot be changed using customizations, however, there are occasions where you want to display another information in order to avoid opening a related entity form.
The javadcript function below will display another attibute inside CRM lookup.
Example:
On a contact form you want to display the accountnumber field, inside the parent customer lookup, instead of primary field of the account entity.
Select Settings > Customizations > Customize Entities
Select Account > open the Main Application Form
On the form select the lookup > select Events > select Onload
Step 1:
In the onload event, add this line of code and change LookupSchemaName to the schema name of your lookup.
crmForm.all.LookupSchemaName.FireOnChange();
Step 2
On the onchange event of the lookup, copy the following code:
---------------------------------------------------------------
Fields to add:
fieldToDisplay = the name of the attribute that you want to display in the lookup.
Take the schema name of the attribute in the linked entity.
fieldToDisplayIsText:
true if you want to display a nvarchar field.
false if you want to display a picklist or a lookup field.
organizationName = name of your organization (without spaces).
---------------------------------------------------------------
Javascript code:
var fieldToDisplay = 'accountnumber';
var fieldToDisplayIsText = true;
var organizationName = 'MyOrganizationName';
var lookupData = new Array();
var lookupItem= new Object();
var lookup = event.srcElement.DataValue;
if (typeof(lookup) != 'undefined' && lookup != null && lookup[0] != null)
{
var myValue = GetAttributeValueFromID(lookup[0].typename,lookup[0].id,fieldToDisplay,fieldToDisplayIsText);
if(myValue != '')
{
lookupItem.id = lookup[0].id;
lookupItem.typename = lookup[0].typename;
lookupItem.name = myValue;
lookupData[0] = lookupItem;
crmForm.all[event.srcElement.id].DataValue = lookupData;
}
}
function GetAttributeValueFromID(sEntityName, sGUID, sAttributeName, isTextField)
{
var xml = "" +
"" +
"
GenerateAuthenticationHeader() +
"
"
"
"
"
"
"
"
"
"
"
"
"
"
"
"
"";
var xmlHttpRequest = new ActiveXObject("Msxml2.XMLHTTP");
xmlHttpRequest.Open("POST", "/mscrmservices/2007/CrmService.asmx", false);
xmlHttpRequest.setRequestHeader("SOAPAction","http://schemas.microsoft.com/crm/2007/WebServices/Execute");
xmlHttpRequest.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
xmlHttpRequest.setRequestHeader("Content-Length", xml.length);
xmlHttpRequest.send(xml);
var result = null;
if(isTextField){
result = xmlHttpRequest.responseXML.selectSingleNode("//q1:" + sAttributeName).text;
}
else
{
result = xmlHttpRequest.responseXML.selectSingleNode("//q1:" + sAttributeName).getAttribute('name');
}
if (result == null)
{
return '';
}
else
return result;
}
Step 3:
Save and publish the entity
Note: This code be used for other lookups, the account is just provided as an example
CRM: Instant Messaging in MSCRM
A client asked: Is there a way to build Instant Messaging into the Contact profile so it can be used to communicate with them?
Yes, it is often a problem to arrange a meeting, respond to a service request, or obtain some critical information to move forward on a request Phone calls and email often are not replyed and hold up communication with your customer.
One way to rectify this delay in communication is to use your Instant Messaging program. You can add IM as a option, within MSCRM, like phone call or email.
* Open a Contact view like Active Contacts in CRM.
* Find the Contact you would like to add > don’t open the record.
* Select the small circle icon in front of the Contact name.
* Right click the circle icon and you will see several options.
* One of the options is to add the Contact to your IM program.
Select this option and the Windows Live wizard will open to allow you to add the contact to Windows Live Messenger and will send the Contact an invite to install Windows Live Messenger if they do not have the application installed.
After you complete the wizard, select the circle icon next to the Contact Name. The “Reply with Instant Message” option will be available and you can now send the Contact an instant message so that you can move the business items forward without the delays of phone calls or email. Additionally, the circle icon will turn Green if the Customer is online and available.
This feature can really come in handy for customer satisfaction items like arranging a quick meeting to discuss new features of CRM or directions to the closest Starbucks.
Yes, it is often a problem to arrange a meeting, respond to a service request, or obtain some critical information to move forward on a request Phone calls and email often are not replyed and hold up communication with your customer.
One way to rectify this delay in communication is to use your Instant Messaging program. You can add IM as a option, within MSCRM, like phone call or email.
* Open a Contact view like Active Contacts in CRM.
* Find the Contact you would like to add > don’t open the record.
* Select the small circle icon in front of the Contact name.
* Right click the circle icon and you will see several options.
* One of the options is to add the Contact to your IM program.
Select this option and the Windows Live wizard will open to allow you to add the contact to Windows Live Messenger and will send the Contact an invite to install Windows Live Messenger if they do not have the application installed.
After you complete the wizard, select the circle icon next to the Contact Name. The “Reply with Instant Message” option will be available and you can now send the Contact an instant message so that you can move the business items forward without the delays of phone calls or email. Additionally, the circle icon will turn Green if the Customer is online and available.
This feature can really come in handy for customer satisfaction items like arranging a quick meeting to discuss new features of CRM or directions to the closest Starbucks.
CRM: Clear Manager
A client had this problem: When a Manager is assigned to a User, I cannot delete it. How do I clear the Manager field and leave it blank?
To clear the Manager field for a User, you need to:
* Select Settings > Administration > Users
* Open the User > select Actions > select Change Manager
* Leave the New Manager field blank > click OK
* The Manager field in the User settings will be cleared
You cannot remove the Manager by clicking in the Manager field and pressing Delete. You must use the Actions > Change Manager option.
To clear the Manager field for a User, you need to:
* Select Settings > Administration > Users
* Open the User > select Actions > select Change Manager
* Leave the New Manager field blank > click OK
* The Manager field in the User settings will be cleared
You cannot remove the Manager by clicking in the Manager field and pressing Delete. You must use the Actions > Change Manager option.
CRM: Viewing Custom Entities
A client asked: I created some custom entities which appear in the workplace toolbar. To see them, the users need to have the System Customizer role. Is there another way?
Yes, you need to do the following for the users to see your custom entities:
* Select Settings > Administration > Security Roles
* Create a new Role (or copy their existing Role)
* Select the 'Custom Entities' tab > assign Permissions
This will allow the users, with this new Role, to see your custom entities but not make changes to them.
Yes, you need to do the following for the users to see your custom entities:
* Select Settings > Administration > Security Roles
* Create a new Role (or copy their existing Role)
* Select the 'Custom Entities' tab > assign Permissions
This will allow the users, with this new Role, to see your custom entities but not make changes to them.
Monday, July 27, 2009
QuickBooks: Sort To Find Problems
A client asked: When we have a problem with a list report it often takes hours to reconcile. Is there a trick to finding problems quicker in long list reports?
Yes, sorting the list report can make finding problems much easier. Finding problems with list entries can be like looking for a needle in a haystack, especially when the lists are very long (e.g. an item list for a company that uses inventory items).
This method assumes that most of the information in the lists is correct, and if so, you are looking for irregularities. Perform the following steps for this trick:
1. Create a list report (available for customers, vendors, employees, items, terms, to do notes and the memorized transaction list). For this example create an Item Listing report to locate items that have the incorrect account assigned to the Income account field.
2. Make sure the income account field shows as a column on the report. The column will read simply Account as opposed to the COGS Account and Expense Account columns.
3. Sort the report by the Account column. First check the top and bottom of the column for anything that is not an income account. Then, scan up and down the list and check each place in the report where the value in the Account column changes (e.g. from one income account to another).
With this method you don’t need to look at each item on the list – just the items that present something usual or out of the ordinary in the Account column.
Then sort the report by other columns (adding columns as necessary) to proof the other fields in the item setup windows.
Yes, sorting the list report can make finding problems much easier. Finding problems with list entries can be like looking for a needle in a haystack, especially when the lists are very long (e.g. an item list for a company that uses inventory items).
This method assumes that most of the information in the lists is correct, and if so, you are looking for irregularities. Perform the following steps for this trick:
1. Create a list report (available for customers, vendors, employees, items, terms, to do notes and the memorized transaction list). For this example create an Item Listing report to locate items that have the incorrect account assigned to the Income account field.
2. Make sure the income account field shows as a column on the report. The column will read simply Account as opposed to the COGS Account and Expense Account columns.
3. Sort the report by the Account column. First check the top and bottom of the column for anything that is not an income account. Then, scan up and down the list and check each place in the report where the value in the Account column changes (e.g. from one income account to another).
With this method you don’t need to look at each item on the list – just the items that present something usual or out of the ordinary in the Account column.
Then sort the report by other columns (adding columns as necessary) to proof the other fields in the item setup windows.
QuickBooks: Customer and Job Types
A client recently asked: How can I get custom fields to appear on all QuickBooks reports, not just on list reports?
When you create custom fields for customers and jobs, the custom fields will impact list reports only (e.g. the customer contact list) unless you include the custom field on a sales form template (e.g. an invoice template).
For example, if a garage has a custom field called Car Model they will not be able to run sales reports by car model unless they include the Car Model custom field on a Sales Receipt or Invoice template.
The field does not have to show on the printed form, but it does have to show on the screen and it has to be populated with the correct information. For example, the Invoice on the screen would have to show the Car Model field with the word Dodge Viper
The customer type and job type fields are much more powerful and global in impact. If you use the customer type field to track Car Model, you do not have to modify any sales forms to create sales reports around this information.
In fact, showing the customer or job type on a sales form template is not even a possibility. Just having this information in the customer or job record allows you to filter and sub-total reports based on the fields.
Also, these fields will impact all historical transactions on which you used the customer or job (e.g. historical invoices). The custom field will affect future invoices only – unless you edit the historical invoices to enter Dodge Viper into the Car Model field.
When you create custom fields for customers and jobs, the custom fields will impact list reports only (e.g. the customer contact list) unless you include the custom field on a sales form template (e.g. an invoice template).
For example, if a garage has a custom field called Car Model they will not be able to run sales reports by car model unless they include the Car Model custom field on a Sales Receipt or Invoice template.
The field does not have to show on the printed form, but it does have to show on the screen and it has to be populated with the correct information. For example, the Invoice on the screen would have to show the Car Model field with the word Dodge Viper
The customer type and job type fields are much more powerful and global in impact. If you use the customer type field to track Car Model, you do not have to modify any sales forms to create sales reports around this information.
In fact, showing the customer or job type on a sales form template is not even a possibility. Just having this information in the customer or job record allows you to filter and sub-total reports based on the fields.
Also, these fields will impact all historical transactions on which you used the customer or job (e.g. historical invoices). The custom field will affect future invoices only – unless you edit the historical invoices to enter Dodge Viper into the Car Model field.
QuickBooks: Tracking Sent Invoices
A client asked: How can I create a report to show invoices that have been sent and when they were sent?
QuickBooks does not have any standard reports to track invoices, sales receipts and credit memos by sent status or method. In other words, you cannot get a list of invoices broken down by those that were emailed, those that were mailed or those that were sent both methods.
You have two options for tracking this information:
Use a custom field called Sent Method and a second custom field called Sent Status. You can make these fields to show on the screen but not to show on the printed invoice. Then, you can filter a list of invoices (e.g. on a custom transaction detail report filtered by Transaction type – Invoice) by the data in this custom field.
Since these reports will only be as accurate as the data entered into the custom fields, you may want to create a custom field with a drop down menu that has three selections: Emailed, Mailed, and Both.
Create a unique template for each sent method. Duplicate an existing sales form template, name the new templates: Emailed, Mailed, and Both. If you prefer, you can create have a fourth template called “Not Sent.”
When you filter an invoice report by Template you can get a list of invoices that you have emailed, mailed or both as well as those you have entered into QuickBooks but have not yet mailed or emailed. When you toggle from one template to another, no information on the screen will change, but you will change the memorized reports that you filtered by template.
Since the template list has a built in drop down menu, you solve the issue of data entry accuracy that you might have around a custom field. You also have the ability to modify the sales forms slightly depending on which method you use to send the invoice.
For example, it might be critical for the addresses on a mailed invoice to line up in a window envelope, but not on the template you sent via email.
QuickBooks does not have any standard reports to track invoices, sales receipts and credit memos by sent status or method. In other words, you cannot get a list of invoices broken down by those that were emailed, those that were mailed or those that were sent both methods.
You have two options for tracking this information:
Use a custom field called Sent Method and a second custom field called Sent Status. You can make these fields to show on the screen but not to show on the printed invoice. Then, you can filter a list of invoices (e.g. on a custom transaction detail report filtered by Transaction type – Invoice) by the data in this custom field.
Since these reports will only be as accurate as the data entered into the custom fields, you may want to create a custom field with a drop down menu that has three selections: Emailed, Mailed, and Both.
Create a unique template for each sent method. Duplicate an existing sales form template, name the new templates: Emailed, Mailed, and Both. If you prefer, you can create have a fourth template called “Not Sent.”
When you filter an invoice report by Template you can get a list of invoices that you have emailed, mailed or both as well as those you have entered into QuickBooks but have not yet mailed or emailed. When you toggle from one template to another, no information on the screen will change, but you will change the memorized reports that you filtered by template.
Since the template list has a built in drop down menu, you solve the issue of data entry accuracy that you might have around a custom field. You also have the ability to modify the sales forms slightly depending on which method you use to send the invoice.
For example, it might be critical for the addresses on a mailed invoice to line up in a window envelope, but not on the template you sent via email.
Saturday, July 25, 2009
Crystal Reports : Using an "all" choice within a parameter
Let us consider our {?UserID} parameter, which accepts multiple values.
Let us further consider that the end-user will need to occasionally print the report for all UserID's. They could simply add each UserID to the parameter before printing the report, but in cases of many UserID's, this becomes cumbersome.
A better solution is to give an "all" option in the parameter choices, then use the following code:
if {?UserID} not like "*all*" then {SALES.USERID} = {?UserID} else true
Here is what's happening:
IF our parameter DOES NOT have he word "all" within it, we execute the Selection Criteria normally. The trick here is that our ELSE clause ends in TRUE, which means that we "skip over" this part of the Selection Criteria, therefore returning ALL UserID's.
Let us further consider that the end-user will need to occasionally print the report for all UserID's. They could simply add each UserID to the parameter before printing the report, but in cases of many UserID's, this becomes cumbersome.
A better solution is to give an "all" option in the parameter choices, then use the following code:
if {?UserID} not like "*all*" then {SALES.USERID} = {?UserID} else true
Here is what's happening:
IF our parameter DOES NOT have he word "all" within it, we execute the Selection Criteria normally. The trick here is that our ELSE clause ends in TRUE, which means that we "skip over" this part of the Selection Criteria, therefore returning ALL UserID's.
Crystal Reports : Using Notes Fields
When inserting "notes" fields into a Crystal Report, some things must be considered:
- After inserting the field, you may specify whether or not it "can grow" (meaning vertically) by going to the fields' Formatting page and selecting "Can Grow". You may also specify how many lines it "can grow" by.
- Certain databases keep their notes in HTML format, in which case the output will look garbled. You may, again, go to the fields' Formatting page and go to the Paragraph Formatting tab, then select a "Text Interpretation". Choices include HTML and RTF (Rich Text Format).
- After inserting the field, you may specify whether or not it "can grow" (meaning vertically) by going to the fields' Formatting page and selecting "Can Grow". You may also specify how many lines it "can grow" by.
- Certain databases keep their notes in HTML format, in which case the output will look garbled. You may, again, go to the fields' Formatting page and go to the Paragraph Formatting tab, then select a "Text Interpretation". Choices include HTML and RTF (Rich Text Format).
Crystal Reports : Using Parameters with Multiple Values
Let us consider a parameter named {?UserID} within our Sales Report.
We can allow the end-user to select multiple UserID's by creating our {?UserID} parameter to "Allow Multiple Values". This is a checkbox on the main parameter property page.
This will allow the end-user to add however many UserID's to the report output they want. The "Allow Multiple Values" functionality is also transparent to the Report Selection Criteria.
Example:
{SALES.USERID} = {?UserID} will return every userid the end user has selected.
We can allow the end-user to select multiple UserID's by creating our {?UserID} parameter to "Allow Multiple Values". This is a checkbox on the main parameter property page.
This will allow the end-user to add however many UserID's to the report output they want. The "Allow Multiple Values" functionality is also transparent to the Report Selection Criteria.
Example:
{SALES.USERID} = {?UserID} will return every userid the end user has selected.
Crystal Reports : Eliminating Zero Divide Errors
When division within calculated fields, there is always the risk of getting the dreaded "zero divide error". This happens, obviously, when a calculation tries to divide te number zero.
And since we all know that underlying data cannot be trusted 100%, it is necessary to test for these zero divide errors within our Formula Field.
Example: The formula field called {frmSalesPerDay} is a simple division of {NumSales} by {NumDays}.
So our {frmSalesPerDay} code should look like this:
if {NumSales} = 0 then 0 else {NumSales} / {NumDays}
So, if {NumSales} equals zero, then we simply return a zero and exit the formula. Otherwise, we let it perform the division.
And since we all know that underlying data cannot be trusted 100%, it is necessary to test for these zero divide errors within our Formula Field.
Example: The formula field called {frmSalesPerDay} is a simple division of {NumSales} by {NumDays}.
So our {frmSalesPerDay} code should look like this:
if {NumSales} = 0 then 0 else {NumSales} / {NumDays}
So, if {NumSales} equals zero, then we simply return a zero and exit the formula. Otherwise, we let it perform the division.
Monday, July 20, 2009
QuickBooks: Grouping Credits/Payments by Invoice
A client asked: How do we group invoices and payments to show which payments are posted to which invoices? The standard reports do not permit this.
QuickBooks links transactions together and allows you to create a limited set of reports based on those links. QuickBooks also uses the links to change the paid status of transactions when Invoices are linked to Customer Payments and Bills are linked to Bill Payments.
The problem is that when you create a Customer Balance Detail report, QuickBooks shows both Invoices and Payments, but the report does not group the invoices and payments to show you which payments post to which Invoices.
The same limitation applies to the Unpaid Bills Detail report. This makes it difficult to tell which payments are applied to which invoices when speaking with customers.
The workaround is as follows: Since the Customer Balance Detail report groups transactions by job, you can use jobs to group Payments and Credit Memos with the Invoices to which they are applied.
Perform the following steps to use this workaround:
Create a unique Job name for each new Invoice you create for the Customer. If you already use jobs and you will have more than one invoice for the job, use a separate sub-job for each Invoice. Include the invoice number in the job name.
When recording Credit Memos for the customer, refer to the applicable job (with Invoice number reference). If the Credit Memos applies to multiple jobs/invoice, record multiple Credit Memos to divide the total credit amount across multiple jobs.
When recording Payments, refer to the applicable job (with Invoice number reference). Payment transactions allow you to enter the customer’s name only and apply the payment across multiple jobs.
However, to use this workaround you must enter both the customer and job name (with Invoice reference). If the payment applies to multiple jobs/invoices you will need to enter multiple payment transactions to divide the total payment amount across multiple
jobs/invoices.
When you create a Customer Balance Detail report, QuickBooks groups all AR activity by the Invoice number/job number as shown in the report below.
Note: This workaround has a downside. You lose the ability to apply a single payment from a customer across multiple invoices. You must decide which is more important for you – to quickly show and report on the Invoices by the payments and credits applied to the invoice or the ability to apply the customer’s payment across multiple jobs. You can apply this workaround to a select group of customers if doing so makes it more practical.
QuickBooks links transactions together and allows you to create a limited set of reports based on those links. QuickBooks also uses the links to change the paid status of transactions when Invoices are linked to Customer Payments and Bills are linked to Bill Payments.
The problem is that when you create a Customer Balance Detail report, QuickBooks shows both Invoices and Payments, but the report does not group the invoices and payments to show you which payments post to which Invoices.
The same limitation applies to the Unpaid Bills Detail report. This makes it difficult to tell which payments are applied to which invoices when speaking with customers.
The workaround is as follows: Since the Customer Balance Detail report groups transactions by job, you can use jobs to group Payments and Credit Memos with the Invoices to which they are applied.
Perform the following steps to use this workaround:
Create a unique Job name for each new Invoice you create for the Customer. If you already use jobs and you will have more than one invoice for the job, use a separate sub-job for each Invoice. Include the invoice number in the job name.
When recording Credit Memos for the customer, refer to the applicable job (with Invoice number reference). If the Credit Memos applies to multiple jobs/invoice, record multiple Credit Memos to divide the total credit amount across multiple jobs.
When recording Payments, refer to the applicable job (with Invoice number reference). Payment transactions allow you to enter the customer’s name only and apply the payment across multiple jobs.
However, to use this workaround you must enter both the customer and job name (with Invoice reference). If the payment applies to multiple jobs/invoices you will need to enter multiple payment transactions to divide the total payment amount across multiple
jobs/invoices.
When you create a Customer Balance Detail report, QuickBooks groups all AR activity by the Invoice number/job number as shown in the report below.
Note: This workaround has a downside. You lose the ability to apply a single payment from a customer across multiple invoices. You must decide which is more important for you – to quickly show and report on the Invoices by the payments and credits applied to the invoice or the ability to apply the customer’s payment across multiple jobs. You can apply this workaround to a select group of customers if doing so makes it more practical.
QuickBooks: Tracking Worker's Compensation Expiration
A client recently asked: We would like to track Worker's Comp Expiration Dates for vendors. Do we need to purchase the QuickBooks Contractor Edition to do this?
The Contractor Edition of QuickBooks includes special fields in the Vendor setup window to track Worker’s Compensation expiration dates. However, if you use other QuickBooks versions, you don’t have access to these fields. There is a workaround, however, outlined below:
Use these steps to track expiration dates in the Vendor window:
* Open the Edit Vendor window for any vendor
* On Additional Information, add a field: Worker’s Comp Expiration.
* In the Vendor Type field, create Vendor Type “Subcontractor”.
* Add the Worker’s Compensation date to each vendor (sub-contractors)
* Set each vendor’s Vendor Type to “Subcontractor.”
Note: You must enter the date using a two digit year then a two digit month and then a two digit day. If you do not use this format, the report you create will not sort by date.
Create a Custom Report to track the expiration dates as follows:
* Select Reports > select List Reports > select Vendor Phone List.
* On the Display tab, add a column for Worker’s Comp Expiration.
* On the Display tab, sort by Worker’s Comp Expiration.
* On the Filters tab, filter by Vendor Type for “Subcontractors.”
* On the Header/Footer tab, assign an appropriate name and memorize.
This will allow you to track the Worker's Comp expiration date. This process can be used for other vendor expiration dates such as Liability Insurance.
The Contractor Edition of QuickBooks includes special fields in the Vendor setup window to track Worker’s Compensation expiration dates. However, if you use other QuickBooks versions, you don’t have access to these fields. There is a workaround, however, outlined below:
Use these steps to track expiration dates in the Vendor window:
* Open the Edit Vendor window for any vendor
* On Additional Information, add a field: Worker’s Comp Expiration.
* In the Vendor Type field, create Vendor Type “Subcontractor”.
* Add the Worker’s Compensation date to each vendor (sub-contractors)
* Set each vendor’s Vendor Type to “Subcontractor.”
Note: You must enter the date using a two digit year then a two digit month and then a two digit day. If you do not use this format, the report you create will not sort by date.
Create a Custom Report to track the expiration dates as follows:
* Select Reports > select List Reports > select Vendor Phone List.
* On the Display tab, add a column for Worker’s Comp Expiration.
* On the Display tab, sort by Worker’s Comp Expiration.
* On the Filters tab, filter by Vendor Type for “Subcontractors.”
* On the Header/Footer tab, assign an appropriate name and memorize.
This will allow you to track the Worker's Comp expiration date. This process can be used for other vendor expiration dates such as Liability Insurance.
Monday, June 29, 2009
Goldmine: Activity Tab Names
A client asked: I use the Filter function when I try to find an activity (e.g. email) in the Pending or History columns, but I still struggle to find specific people or activities. Is this improved in GMPE?
It sure is - Goldmine Premium has many new features, one of which allows you to show the contact name in the Pending and History tabs.
To do this, make the following change:
* Select Tools > Options > Record
* Check the "Show contact name on activity tab"
* Click OK
Now instead of struggling to find an email from a certain person at your largest client, it’s easy to sort or filter on the contact name and quickly find that email you're looking for.
It sure is - Goldmine Premium has many new features, one of which allows you to show the contact name in the Pending and History tabs.
To do this, make the following change:
* Select Tools > Options > Record
* Check the "Show contact name on activity tab"
* Click OK
Now instead of struggling to find an email from a certain person at your largest client, it’s easy to sort or filter on the contact name and quickly find that email you're looking for.
Goldmine: LinkedIn GM+View
A client wants to know: Is there a way to use the GM+View to integrate LinkedIn with the current contact?
Sure, this business networking site is rapidly growing in popularity and a GM+View based on the Goldmine contact name is a great way to utilize the power of LinkedIn and Goldmine.
* Select Web > Setup GM+View
* Click New > Enter LinkedIn as Template Name
* Click in the large text box > click
* Erase or overwrite the html in the box with the following:
Note: To receive a text file with the html below, send an email to dave@marksgroup.net with a subject line of "LinkedIn"
-------------------------------------------------------------------
Save the GM+View and when it opens, it will place the contact name in the LinkedIn search and return the LinkedIn results. This is usually close enough, but if not, you can also type in the LinkedIn contact you are looking for in the Keyword Search.
Sure, this business networking site is rapidly growing in popularity and a GM+View based on the Goldmine contact name is a great way to utilize the power of LinkedIn and Goldmine.
* Select Web > Setup GM+View
* Click New > Enter LinkedIn as Template Name
* Click in the large text box > click
* Erase or overwrite the html in the box with the following:
Note: To receive a text file with the html below, send an email to dave@marksgroup.net with a subject line of "LinkedIn"
-------------------------------------------------------------------
Save the GM+View and when it opens, it will place the contact name in the LinkedIn search and return the LinkedIn results. This is usually close enough, but if not, you can also type in the LinkedIn contact you are looking for in the Keyword Search.
Goldmine: Slow Searches?
A client recently asked: I am using Goldmine Premium and the searches seem slow. Is there a way to correct this?
When you double-click a field to lookup by contact name, Goldmine does a search where the contact name begins with nothing which returns the entire database! Unless you can start typing before Goldmine starts searching, you will find your search to be slow.
You can modify this behavior by using a function called the "Lookup Alignment Delay". By modifying this, you give yourself more time to start typing in the search box before Goldmine starts searching the entire database.
To adjust, do the following:
* Select Tools > Options > Lookup tab
* Modify the Lookup Alignment Delay to 7 (7/10th of second)
This should provide you ample time to start typing your search criteria. If not, move it up to 9 or 10. If you are really quick, move it down to 4 or 5.
This setting can be different for everyone, so test different delays until you get the right one for you.
When you double-click a field to lookup by contact name, Goldmine does a search where the contact name begins with nothing which returns the entire database! Unless you can start typing before Goldmine starts searching, you will find your search to be slow.
You can modify this behavior by using a function called the "Lookup Alignment Delay". By modifying this, you give yourself more time to start typing in the search box before Goldmine starts searching the entire database.
To adjust, do the following:
* Select Tools > Options > Lookup tab
* Modify the Lookup Alignment Delay to 7 (7/10th of second)
This should provide you ample time to start typing your search criteria. If not, move it up to 9 or 10. If you are really quick, move it down to 4 or 5.
This setting can be different for everyone, so test different delays until you get the right one for you.
Goldmine: Got Notes?
A client asked: We use the Notes tab for preliminary information for suspects and prospects. Is there a way to put a check mark on the Notes tab so we know that it contains information?
Using the Notes tab for generic or preliminary information such as directions or comments about the contact is useful. However, unless you go into the Notes tab there is no way to know there is something there. To indicate you have Notes in a contact, use the following to put "Have Notes" on the upper half of the contact window.
* First, while logged in with master rights, Right-Click > New Field.
* Select dBASE Expression and Click OK.
* Move the field to an area of the screen where you have open space
* Double-click and add "Has Notes" (with quotes) in Field Data area
* Select the Color tab and add the following to Data Color
* Select Expression and type in iif(trim(notes) > ' ', 255, 16777215)
* On the Layout tab, set Field Label to 0 and Data Size to 10.
* Click OK.
Now when a contact has data in the Notes field, Has Notes will appear under the Address fields in the upper half of the Contact.
Using the Notes tab for generic or preliminary information such as directions or comments about the contact is useful. However, unless you go into the Notes tab there is no way to know there is something there. To indicate you have Notes in a contact, use the following to put "Have Notes" on the upper half of the contact window.
* First, while logged in with master rights, Right-Click > New Field.
* Select dBASE Expression and Click OK.
* Move the field to an area of the screen where you have open space
* Double-click and add "Has Notes" (with quotes) in Field Data area
* Select the Color tab and add the following to Data Color
* Select Expression and type in iif(trim(notes) > ' ', 255, 16777215)
* On the Layout tab, set Field Label to 0 and Data Size to 10.
* Click OK.
Now when a contact has data in the Notes field, Has Notes will appear under the Address fields in the upper half of the Contact.
Goldmine: Finding Contacts
A client wants to know: We communicate with prospects and customers primarily by email. Is there a way to find contacts without an email address?
Yes, you can use a SQL query to show you all your contacts without an email address.
To do this, select GoldMine’s main menu, choose Lookup > SQL Queries then copy and paste the code below into the top section then click Query. If you are using GoldMine Premium Edition then use Tools > Filters & Groups and the SQL Query tab.
-------------------------------------------------------------------
select company, contact, city, state, zip, key1, key2, key3, key4, key5 from contact1 where accountno not in (select accountno from contsupp where contact = 'E-mail Address' and rectype = 'P') order by company, contact
-------------------------------------------------------------------
You will see your results and if they appear useful use the 'Save' button to reuse this later on. Within the results, you can Right-Click > Output To > Excel to save the results, print them, and further analyze.
Yes, you can use a SQL query to show you all your contacts without an email address.
To do this, select GoldMine’s main menu, choose Lookup > SQL Queries then copy and paste the code below into the top section then click Query. If you are using GoldMine Premium Edition then use Tools > Filters & Groups and the SQL Query tab.
-------------------------------------------------------------------
select company, contact, city, state, zip, key1, key2, key3, key4, key5 from contact1 where accountno not in (select accountno from contsupp where contact = 'E-mail Address' and rectype = 'P') order by company, contact
-------------------------------------------------------------------
You will see your results and if they appear useful use the 'Save' button to reuse this later on. Within the results, you can Right-Click > Output To > Excel to save the results, print them, and further analyze.
Goldmine: Finding Unlinked Emails
A client asked: We have a number of emails which were filed without being linked to a contact. Is it possible to find these emails?
Yes, you could use a SQL query. Add the following to the SQL Query textbox in Goldmine:
select * from MailBox where AccountNo is Null
This will return unlinked emails and the results can be used to build a group. From this, the emails can be linked to the correct contact.
Yes, you could use a SQL query. Add the following to the SQL Query textbox in Goldmine:
select * from MailBox where AccountNo is Null
This will return unlinked emails and the results can be used to build a group. From this, the emails can be linked to the correct contact.
Goldmine: More Contact Information
A client asked: We use the GM+View for driving directions to clients. Are there other views that are available?
Sure, to search on a client email, you would do the following. This places the email address in Google and returns information about the current contact.
To implement this GM+View:
* Open the GM+View > Click New
* Type Contact Google Search as the name
* Click in the large text box > click
* Highlight the html in the box > Delete it
Enter the hmtl below:
-------------------------------------------------------------------
Note: to receive a text file with the html below, send an email to dave@marksgroup.net with a subject line of "Google Search"
Save the view and make sure there is a valid email address in the contact. This should be automatically placed in Google and searched. You may be surprised with the information you find!
Note: For a large collection of GM+Views and lots of other Goldmine explanations, tips and enhanced functionality, check out the "Goldmine Hackers Guide" by DJ Hunt
Sure, to search on a client email, you would do the following. This places the email address in Google and returns information about the current contact.
To implement this GM+View:
* Open the GM+View > Click New
* Type Contact Google Search as the name
* Click in the large text box > click
* Highlight the html in the box > Delete it
Enter the hmtl below:
-------------------------------------------------------------------
Note: to receive a text file with the html below, send an email to dave@marksgroup.net with a subject line of "Google Search"
Save the view and make sure there is a valid email address in the contact. This should be automatically placed in Google and searched. You may be surprised with the information you find!
Note: For a large collection of GM+Views and lots of other Goldmine explanations, tips and enhanced functionality, check out the "Goldmine Hackers Guide" by DJ Hunt
Goldmine: Pipeline Funnel
A client asked: How are the rungs in the Pipeline Funnel determined?
GoldMine’s Opportunity Manager gives companies and individuals an easy way to manage and track sales throughout the sales cycle with the Pipeline Funnel.
For salespeople and managers the Pipeline Funnel gives you an overview of the number of opportunities and total dollar value of the opportunities at each stage of the sales process.
To use the Pipeline Funnel:
Determine whether you want to see all opportunities, the opportunities of a user group, or a single users’ opportunities. This is done by changing the Manager drop down in the Opportunity Manager.
Then click the funnel icon, above and to the left of the Manager.
In the center of the rungs of the funnel will be the stage, “10 – Initial Contact, etc.” as well as the number of opportunities in that stage. Then to the right of each rung will be the total dollar value of those opportunities, and their percentage of the total sales. In the very top rung, the gray area, will be the total forecast and total # of sales.
The rungs are determined by the F2 lookup for Stage field within the Opportunity Manager.
GoldMine’s Opportunity Manager gives companies and individuals an easy way to manage and track sales throughout the sales cycle with the Pipeline Funnel.
For salespeople and managers the Pipeline Funnel gives you an overview of the number of opportunities and total dollar value of the opportunities at each stage of the sales process.
To use the Pipeline Funnel:
Determine whether you want to see all opportunities, the opportunities of a user group, or a single users’ opportunities. This is done by changing the Manager drop down in the Opportunity Manager.
Then click the funnel icon, above and to the left of the Manager.
In the center of the rungs of the funnel will be the stage, “10 – Initial Contact, etc.” as well as the number of opportunities in that stage. Then to the right of each rung will be the total dollar value of those opportunities, and their percentage of the total sales. In the very top rung, the gray area, will be the total forecast and total # of sales.
The rungs are determined by the F2 lookup for Stage field within the Opportunity Manager.
Goldmine: Tracking Your Sales Pipeline
A client wants to know: We want to start tracking our Sales Pipeline using Goldmine. Where do we start?
To maintain an accurate and up to date pipeline, you need to make sure you update an Opportunity after each visit or call to a prospect. Forgetting to create that opportunity and a good prospect can end up "falling through the cracks". Below is a simple trick to make the process of creating a new opportunity easier and part your daily workflow.
* When completing an activity, e.g. a call or appointment, if it’s a good prospect, click the “New” button in the Complete window to create a new opportunity for the prospect.
* After clicking New, you’ll be asked to confirm who the opportunity is for. You can accept the default record or select another. Select Opportunity and any template that might be appropriate.
* Then complete the basic information and click OK. The opportunity will show up in the Opportunity Manager
This information can be used in the Pipeline Funnel, Goldmine and Crystal reports to better track your sales pipeline.
To maintain an accurate and up to date pipeline, you need to make sure you update an Opportunity after each visit or call to a prospect. Forgetting to create that opportunity and a good prospect can end up "falling through the cracks". Below is a simple trick to make the process of creating a new opportunity easier and part your daily workflow.
* When completing an activity, e.g. a call or appointment, if it’s a good prospect, click the “New” button in the Complete window to create a new opportunity for the prospect.
* After clicking New, you’ll be asked to confirm who the opportunity is for. You can accept the default record or select another. Select Opportunity and any template that might be appropriate.
* Then complete the basic information and click OK. The opportunity will show up in the Opportunity Manager
This information can be used in the Pipeline Funnel, Goldmine and Crystal reports to better track your sales pipeline.
Goldmine: Auto Fill Text Boxes
A client asked: Do we have to use the pop-up selection method for required fields? Is there a way to fill in the closest match on some fields and use the pop-up selection on others.
Yes, whether to use a pop-up or auto-fill is controlled on a field by field basis. To use the auto-fill method, do the following:
* Click on the F2 lookup icon > click on Setup
* Check the auto-fill option
* When the user starts typing, the closest match will appear
* If Force Valid Input is unchecked, the user entry will be accepted
* If Force Valid Input is checked, the user must select from the list
For many users, the auto-fill is preferred since it functions like other software packages. Also, auto-fill is especially useful if you have exceptionally large F2 Lookup lists. Auto-fill is unchecked by default.
Yes, whether to use a pop-up or auto-fill is controlled on a field by field basis. To use the auto-fill method, do the following:
* Click on the F2 lookup icon > click on Setup
* Check the auto-fill option
* When the user starts typing, the closest match will appear
* If Force Valid Input is unchecked, the user entry will be accepted
* If Force Valid Input is checked, the user must select from the list
For many users, the auto-fill is preferred since it functions like other software packages. Also, auto-fill is especially useful if you have exceptionally large F2 Lookup lists. Auto-fill is unchecked by default.
Sunday, June 28, 2009
HEAT: Passing Crystal Parameters
A client asked: Is there a way to add information to a Crystal report without filling in the pop-up parameters?
Yes, there is a trick using HEAT Call Logging that uses data inside a Call record to populate the parameters. Below is how you configure the built-in utility:
* Select Report > Manage Reports
* Add a report with a parameter
* Select "All Assignments by Assignee."
* Go to the Report Parameters tab
* Click on the Assignee parameter
* Click the first checkbox: Preset parameter values
* Click Add, then hit insert Assignment.Assignee
When using this function, Crystal Reports will not prompt for the parameter. This will run the report and take the Assignee from the Call record.
Yes, there is a trick using HEAT Call Logging that uses data inside a Call record to populate the parameters. Below is how you configure the built-in utility:
* Select Report > Manage Reports
* Add a report with a parameter
* Select "All Assignments by Assignee."
* Go to the Report Parameters tab
* Click on the Assignee parameter
* Click the first checkbox: Preset parameter values
* Click Add, then hit insert Assignment.Assignee
When using this function, Crystal Reports will not prompt for the parameter. This will run the report and take the Assignee from the Call record.
HEAT: Call Log Shortcuts
A client recently asked: Is there a way to avoid using the tabs? Our users do not complete functions that require opening a tab.
Yes, it is actually quite easy to place a number of functions on the Call Log form to avoid the need to open a tab. Some of the functions called from the front form are: Save Call, Create Assignment, Create Journal, and Go To Ticket
To develop this functionality, you need to use the Administrator module and create an edit set. This will allow you to add buttons to the front screen to run many functions without ever going to another tab.
Below are the instructions to Save a Ticket:
* Select Administrator > Select New Edit Set
* Select Call Log > Open Form
* Select Command Button > Place of the form
* Select Control > Connect Autotask
* Select the "Save a Call" autotask > Click OK
Commit the edit and open Call Logging. A new catalog should be created and then you will see the new Save a Call button. Clicking on this will save the call. Use this process to create other buttons as outlined above.
Note: In order for these buttons to be set up, separate AutoTasks need to be created that perform each of these functions. Once these AutoTasks are created, they can be linked to the buttons via an Edit Set or Quick Customize using the HEAT Administrator module.
Yes, it is actually quite easy to place a number of functions on the Call Log form to avoid the need to open a tab. Some of the functions called from the front form are: Save Call, Create Assignment, Create Journal, and Go To Ticket
To develop this functionality, you need to use the Administrator module and create an edit set. This will allow you to add buttons to the front screen to run many functions without ever going to another tab.
Below are the instructions to Save a Ticket:
* Select Administrator > Select New Edit Set
* Select Call Log > Open Form
* Select Command Button > Place of the form
* Select Control > Connect Autotask
* Select the "Save a Call" autotask > Click OK
Commit the edit and open Call Logging. A new catalog should be created and then you will see the new Save a Call button. Clicking on this will save the call. Use this process to create other buttons as outlined above.
Note: In order for these buttons to be set up, separate AutoTasks need to be created that perform each of these functions. Once these AutoTasks are created, they can be linked to the buttons via an Edit Set or Quick Customize using the HEAT Administrator module.
HEAT: Effective Calendar
A client recently asked: Is it possible to use a specific Calendar based on the Call Log or Assignment data?
Yes, you can pass a calendar from a ticket through BPAM. Passing a calendar constraint on a call record or assignment is often overlooked. Depending on the specific data of a call record or assignment, you can assign a calendar instead of forcing all records to use a single calendar in a BPAM rule.
To do this, you need:
* A field in the Call Log table for Calendar name
* A BPAM rule (If ticket stays unchanged for X amount of time)
* A Calendar to be used
To create this, do the following:
* Use Administrator to add the "Effective Calendar" field to the Call Log table.
* Commit the edit set.
* Create a BPAM rule "If a ticket stays unchanged for x amount of time"
* Add the new calendar field to the "Field" drop-down box in BPAM*
* Add the calendar definition in Hours of Operation
Now, depending the data in the Call Ticket, the user can assign a calendar instead of all users being forced to use the same calendar.
Yes, you can pass a calendar from a ticket through BPAM. Passing a calendar constraint on a call record or assignment is often overlooked. Depending on the specific data of a call record or assignment, you can assign a calendar instead of forcing all records to use a single calendar in a BPAM rule.
To do this, you need:
* A field in the Call Log table for Calendar name
* A BPAM rule (If ticket stays unchanged for X amount of time)
* A Calendar to be used
To create this, do the following:
* Use Administrator to add the "Effective Calendar" field to the Call Log table.
* Commit the edit set.
* Create a BPAM rule "If a ticket stays unchanged for x amount of time"
* Add the new calendar field to the "Field" drop-down box in BPAM*
* Add the calendar definition in Hours of Operation
Now, depending the data in the Call Ticket, the user can assign a calendar instead of all users being forced to use the same calendar.
HEAT: Separate Tickets by Team
A client asked: We want to restrict tickets within the HEAT database so that only a member of a department can see their tickets. Is this possible?
Yes, you can separate tickets within HEAT based on security rights so other groups within your organization can use the same HEAT database. This requires well thought out Roles and Teams and adjusting the Rights to Role Settings.
The security right, "Go to Only in Open Call Groups", controls whether or not the Role has access to tickets outside of the Call Group given and Teams can determine which Call Groups are available to a HEAT user.
For example, if you have two groups in your organization, Network Services and Human Resources and they both would like to use the HEAT database, two Roles can be created, one for each group.
Then you can breakdown each group into Teams. Network Services can have Teams called NS Hardware, NS Software, NS Change, etc. Human Resources can have Teams called HR Benefits, HR Payroll, HR Complaints, etc.
So, if a someone belongs to the Team HR Benefits, then that person would belong to the Role HR which would then inherit the right to only see specific HR tickets, and not IT.
Yes, you can separate tickets within HEAT based on security rights so other groups within your organization can use the same HEAT database. This requires well thought out Roles and Teams and adjusting the Rights to Role Settings.
The security right, "Go to Only in Open Call Groups", controls whether or not the Role has access to tickets outside of the Call Group given and Teams can determine which Call Groups are available to a HEAT user.
For example, if you have two groups in your organization, Network Services and Human Resources and they both would like to use the HEAT database, two Roles can be created, one for each group.
Then you can breakdown each group into Teams. Network Services can have Teams called NS Hardware, NS Software, NS Change, etc. Human Resources can have Teams called HR Benefits, HR Payroll, HR Complaints, etc.
So, if a someone belongs to the Team HR Benefits, then that person would belong to the Role HR which would then inherit the right to only see specific HR tickets, and not IT.
HEAT: Creating Easy Alarms
A client recently asked: We would like to use the Alarms in HEAT, but it requires too many steps. Is there a faster way to setup an Alarm?
Yes, you can simplify this process by creating a series of pre-defined Alarms that can easily be set from the toolbar. These Alarms can be used to set reminders to follow-up on open issues and manage tasks. Alarms can also be linked to Call Records.
For example, you can create Alarm AutoTasks that will set reminders for various intervals (e.g 1 Hour, 1 Day, and 7 Days). You can also include ticket information in the alarm.
Here's how to build a 1 Hour AutoTask Alarm:
* Select Manage Autotask > click Add
* Name the Autotask > check Display on menu
* Click Add > select the Create an Alarm action
* Select 1 Hour > add "Followup" to the Subject
* Insert the CallLog.CallID field after "Followup"
* Insert Call Log fields in the Notes field
* Use the format: Customer:{|Subset.CustID}
* Add other Call Log fields (e.g. CallDesc, Status)
Repeat this for other intervals and save them to an AutoTask folder. You can then place a shortcut to this folder onto the toolbar.
Now, when working on a ticket, if you would like to schedule a follow-up call or action, click the toolbar button and select the follow-up time. The alarm will be set automatically.
Yes, you can simplify this process by creating a series of pre-defined Alarms that can easily be set from the toolbar. These Alarms can be used to set reminders to follow-up on open issues and manage tasks. Alarms can also be linked to Call Records.
For example, you can create Alarm AutoTasks that will set reminders for various intervals (e.g 1 Hour, 1 Day, and 7 Days). You can also include ticket information in the alarm.
Here's how to build a 1 Hour AutoTask Alarm:
* Select Manage Autotask > click Add
* Name the Autotask > check Display on menu
* Click Add > select the Create an Alarm action
* Select 1 Hour > add "Followup" to the Subject
* Insert the CallLog.CallID field after "Followup"
* Insert Call Log fields in the Notes field
* Use the format: Customer:{|Subset.CustID}
* Add other Call Log fields (e.g. CallDesc, Status)
Repeat this for other intervals and save them to an AutoTask folder. You can then place a shortcut to this folder onto the toolbar.
Now, when working on a ticket, if you would like to schedule a follow-up call or action, click the toolbar button and select the follow-up time. The alarm will be set automatically.
Saturday, June 27, 2009
Quickbooks: Account Numbers on Reports
A client wants to know: I want to display Account numbers on certain reports or at certain times. How can I do this?
The Account numbers display not on the Chart of Accounts list and the Account field drop down menus and also on financial reports like the Balance Sheet and Profit & Loss.
You can hide the Account numbers in Financial Reports by turning off Account Numbers in the accounting Company preferences. However, you have to do so each and every time you create a financial report. Since you can only turn off Account numbers as the file Administrator and only in single user mode,turning off the Account numbers each time you want to print financial reports is not practical.
Instead, you need to use a setting that will hide the Account numbers by default and then you can edit the Company Preferences only when you want the Account numbers to show (e.g. when printing the General Ledger and/or Trial Balance reports).
Do the following to hide the Account numbers on financial reports:
* Edit the description for each Account on the Chart of Accounts, including any that
you have made inactive but that might still show on financial reports. In most cases the description should be the same as the Account name.
* Edit the Reporting Company Preferences to display Accounts by Description rather than by “Name only.” The “Name only” setting doesn’t describe fully what the Name setting does. When you select “Name only” on the window shown below, QuickBooks reports include both the Name and Number.
* QuickBooks will now show the description in financial reports, not the name and number. If any Accounts do not have a description, QuickBooks will revert to displaying the Account by name and number, but just for the selected Accounts with no description. Once you add a description and refresh the report, the Account number will go away.
Note: You can enter a slightly different wording for the Account in the description field. For example, the Account name can read Accounts Receivable and the Description could read Receivables – Trade. The latter is a better wording for financial reports and the QuickBooks users will still see the wording “Accounts Receivable” on the Chart of Accounts window and on QuickBooks forms
The Account numbers display not on the Chart of Accounts list and the Account field drop down menus and also on financial reports like the Balance Sheet and Profit & Loss.
You can hide the Account numbers in Financial Reports by turning off Account Numbers in the accounting Company preferences. However, you have to do so each and every time you create a financial report. Since you can only turn off Account numbers as the file Administrator and only in single user mode,turning off the Account numbers each time you want to print financial reports is not practical.
Instead, you need to use a setting that will hide the Account numbers by default and then you can edit the Company Preferences only when you want the Account numbers to show (e.g. when printing the General Ledger and/or Trial Balance reports).
Do the following to hide the Account numbers on financial reports:
* Edit the description for each Account on the Chart of Accounts, including any that
you have made inactive but that might still show on financial reports. In most cases the description should be the same as the Account name.
* Edit the Reporting Company Preferences to display Accounts by Description rather than by “Name only.” The “Name only” setting doesn’t describe fully what the Name setting does. When you select “Name only” on the window shown below, QuickBooks reports include both the Name and Number.
* QuickBooks will now show the description in financial reports, not the name and number. If any Accounts do not have a description, QuickBooks will revert to displaying the Account by name and number, but just for the selected Accounts with no description. Once you add a description and refresh the report, the Account number will go away.
Note: You can enter a slightly different wording for the Account in the description field. For example, the Account name can read Accounts Receivable and the Description could read Receivables – Trade. The latter is a better wording for financial reports and the QuickBooks users will still see the wording “Accounts Receivable” on the Chart of Accounts window and on QuickBooks forms
Quickbooks: Missing Invoice Report
A client asked: I use the Missing Check Report to find checks that are unaccounted for. Is there a similar report for missing invoices?
No, however, you can create one using Quickbooks. Since QuickBooks has a report programmed to show missing Check numbers, finding a way to modify the existing report to show missing invoice numbers is the best way to get the information you need.
Do the following to create a Missing Invoice report.
* Create a Missing Checks report by selecting the Reports menu, selecting Banking and then selecting Missing Checks. QuickBooks displays the window shown below.
* Do not change the default bank account that appears in the Specify Account field and press OK to display the report.
* QuickBooks creates a report of Checks in numerical order and the report notes any missing and/or duplicate check numbers.
* Click the Modify Report button and then click the Filters tab to display the report filters.
* Remove the Account filter shown below.
* Filter by Transaction Type for “Invoices” and by Detail Level for “Summary Only.”
Edit the title of the report and memorize the report for future use.
Note: You can use the steps above to filter by other transactions types as well. Doing so will allow you to create a Missing Journal Entries report, Missing Estimates report, etc.
No, however, you can create one using Quickbooks. Since QuickBooks has a report programmed to show missing Check numbers, finding a way to modify the existing report to show missing invoice numbers is the best way to get the information you need.
Do the following to create a Missing Invoice report.
* Create a Missing Checks report by selecting the Reports menu, selecting Banking and then selecting Missing Checks. QuickBooks displays the window shown below.
* Do not change the default bank account that appears in the Specify Account field and press OK to display the report.
* QuickBooks creates a report of Checks in numerical order and the report notes any missing and/or duplicate check numbers.
* Click the Modify Report button and then click the Filters tab to display the report filters.
* Remove the Account filter shown below.
* Filter by Transaction Type for “Invoices” and by Detail Level for “Summary Only.”
Edit the title of the report and memorize the report for future use.
Note: You can use the steps above to filter by other transactions types as well. Doing so will allow you to create a Missing Journal Entries report, Missing Estimates report, etc.
Friday, June 26, 2009
Crystal Reports : Formatting Percentages
When creating a formula that should return a percentage value, you will need to format the field correctly to get the "%" sign to properly show.
Right-click on the field in question, select Format Field.
Click the "Display Currency Symbol" checkbox.
Click the "Customize" button.
Click the "Currency Symbol" tab.
Change your currency symbol from "$" to "%".
Then change the "Position" to "-123%".
This forces the percentage symbol to appear at the end of the numerical value.
This will give you percentages thus: "87.22%".
Right-click on the field in question, select Format Field.
Click the "Display Currency Symbol" checkbox.
Click the "Customize" button.
Click the "Currency Symbol" tab.
Change your currency symbol from "$" to "%".
Then change the "Position" to "-123%".
This forces the percentage symbol to appear at the end of the numerical value.
This will give you percentages thus: "87.22%".
Crystal Reports : Viewing the Report Selection Criteria on the Report
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.
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 (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.
Select Insert | Summary.
Select the fieldname you wish to summarize.
Make sure to select Distinct Count as the Summary Operation.
Crystal Reports : Changing an Existing Group
You may need to change the existing report "Groups" after defining them.
To do this, select from the top-level menu, Report | Change Group Expert.
Here, you will see all your defined Groups. You may select any of them and use the "up" and "down" arrows to change the Group order.
This is a nice way to regroup the report without having to do a lot of work.
To do this, select from the top-level menu, Report | Change Group Expert.
Here, you will see all your defined Groups. You may select any of them and use the "up" and "down" arrows to change the Group order.
This is a nice way to regroup the report without having to do a lot of work.
Quickbooks: Using Customers and Jobs
A client asked: We would like to account for every entry with either a customer or job entry. Is this possible?
You can use customer or job names in QuickBooks to segment your financial statements and to filter many of your QuickBooks summary reports and detail reports for specific customers or jobs. However, certain entries may not apply to specific customers/jobs or you may need to allocate the entry across multiple customers/jobs.
For example, you may need to allocate expenses for tools and equipment that you use over all of the jobs for any given year to multiple jobs. You may also incur expenses like utilities and other administrative expenses that you will never apply to a job.
So, depending on the transaction you can:
* Create a customer called “Overhead” and post the entry to that customer. If you prefer you can use a journal entry to allocate the overhead to the specific customers/jobs.
* Split the detail of the transaction so that the single transaction (e.g. check, bill or invoice) applies to multiple customers/jobs.
Whatever method you use, this willalways use a customer or job on each and every transaction. If you do not include a customer or job, you will:
* Create a discrepancy between the Profit & Loss by Job and the Profit & Loss. Transactions that do not include a Customer/Job name simply do not show on the Profit & Loss by Job report. If you use a Customer called “Overhead,” you will show the same totals on both the Profit & Loss and the Profit & Loss by Job.
* Receive a warning message that the transaction does not include a Customer/Job name. (This warning is available in the Contractor Edition only.) If you exclude the customer/job name on selected posts only (e.g. posts that are overhead or posts to Balance Sheet accounts), the benefit of this warning is diluted. Over time, users will become desensitized to the message.
For this reason it is best to use the Overhead Customer name on posts to Balance Sheet accounts even though QuickBooks does not allow you to filter the Balance Sheet by Customer/Job.
You can use customer or job names in QuickBooks to segment your financial statements and to filter many of your QuickBooks summary reports and detail reports for specific customers or jobs. However, certain entries may not apply to specific customers/jobs or you may need to allocate the entry across multiple customers/jobs.
For example, you may need to allocate expenses for tools and equipment that you use over all of the jobs for any given year to multiple jobs. You may also incur expenses like utilities and other administrative expenses that you will never apply to a job.
So, depending on the transaction you can:
* Create a customer called “Overhead” and post the entry to that customer. If you prefer you can use a journal entry to allocate the overhead to the specific customers/jobs.
* Split the detail of the transaction so that the single transaction (e.g. check, bill or invoice) applies to multiple customers/jobs.
Whatever method you use, this willalways use a customer or job on each and every transaction. If you do not include a customer or job, you will:
* Create a discrepancy between the Profit & Loss by Job and the Profit & Loss. Transactions that do not include a Customer/Job name simply do not show on the Profit & Loss by Job report. If you use a Customer called “Overhead,” you will show the same totals on both the Profit & Loss and the Profit & Loss by Job.
* Receive a warning message that the transaction does not include a Customer/Job name. (This warning is available in the Contractor Edition only.) If you exclude the customer/job name on selected posts only (e.g. posts that are overhead or posts to Balance Sheet accounts), the benefit of this warning is diluted. Over time, users will become desensitized to the message.
For this reason it is best to use the Overhead Customer name on posts to Balance Sheet accounts even though QuickBooks does not allow you to filter the Balance Sheet by Customer/Job.
Quickbooks: Auto-Apply Payments
A client asked: We would like to turn off the auto-apply for payments. Is this a mistake and, if not, how do we turn it off
It is almost always correct to turn off auto-apply for payments. There is a preference in the Sales & Customers preferences to automatically apply payments on the Receive Payments window. Uncheck this to turn off the feature.
With auto-application turned on, QuickBooks first looks for an invoice that is the same amount as the payment from the customer. If there is no invoice for the same amount as the payment (e.g. partial payments or checks that pay more than once invoice), QuickBooks applies the check to the oldest open invoice. If any amount is unapplied after paying the oldest open invoice, QuickBooks applies the remainder to the next oldest open Invoice and so on.
Although this feature is designed to increase data entry efficiency, it is almost always best to turn the feature off because the auto-payment process only works well when the check is the exact amount of the invoice. The application of non-exact amounts to the oldest invoice is rarely the best approach.
There is one exception - if you almost never have more than one open invoice for a customer at any given time, you can use the auto-application of payments to invoices with little risk of misapplication.
It is almost always correct to turn off auto-apply for payments. There is a preference in the Sales & Customers preferences to automatically apply payments on the Receive Payments window. Uncheck this to turn off the feature.
With auto-application turned on, QuickBooks first looks for an invoice that is the same amount as the payment from the customer. If there is no invoice for the same amount as the payment (e.g. partial payments or checks that pay more than once invoice), QuickBooks applies the check to the oldest open invoice. If any amount is unapplied after paying the oldest open invoice, QuickBooks applies the remainder to the next oldest open Invoice and so on.
Although this feature is designed to increase data entry efficiency, it is almost always best to turn the feature off because the auto-payment process only works well when the check is the exact amount of the invoice. The application of non-exact amounts to the oldest invoice is rarely the best approach.
There is one exception - if you almost never have more than one open invoice for a customer at any given time, you can use the auto-application of payments to invoices with little risk of misapplication.