Do you have an IT question or concern that you’ve been hung up on? Now’s your chance to “Ask Sparky”! Sparky is what we loving call our company logo. Sparky is our fearless leader who encompasses all that is IT. Applied Tech works hard at keeping IT as pain-free as possible and Sparky is with us every step of the way! Send your IT questions to Sparky at sparky@appliedtech.us.

Heidi asks: I’m working with Excel where the first and last names are in different columns but I want them merged into one column. How do combine them?

Heidi,

Excel has a nifty little trick to save you plenty of time in your task. Let’s say your current spreadsheet reads as:

And you want it to look like this:

Here’s the time-saving trick for you:

In cell C, enter the following formula*: A2 & ” ” & B2. Enter. This will give you “First Name {space} Last Name” in one cell. Ex: Bart Simpson

Alternatively, if you’d rather the names appear “Last Name {comma}, First Name”, simply change the formula to: =B2 & “, ” & A2. Ex: Simpson, Bart

Once you’ve added the equation for one cell and ensured that it is correct, simply copy and paste the formula in the remaining cells. Voilà!

*Be sure to add a space between the quotation marks to ensure there is a space between the first and last name.

Got a question for Sparky? Ask away at sparky@appliedtech.us.
Look for Q&A’s in next month’s BrainBank!

Get spell check functionality in Dynamics GP with WillowWare’s SpellCheck. SpellCheck for Dynamics GP provides spell-checking on any Note, Comment, Text or String field in GP and 3rd party products. SpellCheck can be attached to any text entry field in the application.

SpellCheck requires the following Microsoft Office Components:

• Microsoft Word (any version)
• Office Proofing Tools

 SpellCheck is FREE!  Just send an email to support@willoware.com with the following information:

• Company Name
• Microsoft Account Number

They will send you a link to download the software.

~Content contributed by Pamela Close

“Pam Knows” is our newest Q&A forum for Microsoft Dynamics-related questions.  All answers are provided by Pamela Close, Resident GP Expert.  Have a Dynamics question you’d like to ask?  Submit them to sparky(@)appliedtech(dot)us.

–

Q: Renee writes..”I love the new AutoComplete feature in GP that brings up a possible match lists from previous keyed entries when you are typing in a field. However, there are several entries in the list that continue to come up that I want removed because they were entered incorrectly or misspelled. I know I can clear out all of my entries and start over but I don’t want to do that – I just want to remove some incorrect entries. Is there a simple fix for this?”

A:Thank you for your submitting your question, Renee. The AutoComplete feature is a great time saver but can be annoying when entries stay in there that are incomplete or misspelled. The good news is that there is a very simple fix to this problem.

To remove an erroneous AutoComplete entry, start typing the problem word until it appears as a selection. Right Click and select Remove From List to eliminate the error.

–

If you have a question you would like to ask our resident expert, please submit them to sparky(@)appliedtech(dot)us.

During normal business operations, thousands of transactions can be processed. When working with this volume of transactions, standard processes might be inadvertently changed or unintended entries made and posted. As a result, the payables or receivables balances on a General Ledger trial balance report might not match the corresponding balance on trial balance reports printed in Payables Management or Receivables Management. Some specific examples might include the following.

• A transaction is posted to-but not through-General Ledger and is deleted before it’s posted in General Ledger.
• A transaction is posted to General Ledger, but the amounts are changed before it’s posted in General Ledger.

Use the Reconcile to GL window to generate a Microsoft Excel spreadsheet that can help you match transactions in General Ledger with the originating transactions in Payables Management and Receivables Management. This spreadsheet can provide information that helps you enter adjusting transactions that reconcile any discrepancies.

1. Open the window at Microsoft Dynamics GP>>Tools>>Routines>> Financial>>Reconcile to GL.

2. Select either Payables Management or Receivables Management as the module.

3. Enter a Date range to reconcile transactions for. Only transactions for which the General Ledger posting date is within the specified range will be included in the list that is generated.

4. Enter the Accounts to be included in the reconcile process. For example, if you’re reconciling Payables Management, enter all the accounts in your general ledger that represent your accounts payable balance in your sub ledger.

5. Click Process to begin generating the report.

Once the report is run, the data will be presented in one of 3 categories:

Matches – Transactions from Receivables Management or Payables Management are “matched” using the transaction source code, document number, transaction date, and transaction amount.

Potentially Matched – Transactions are identified as “potentially matching” if some, but not all the information matches. For example, if the source document code for the transaction in Receivables Management or Payables Management matches the code in General Ledger, but the transaction dates or amounts don’t match, the transactions are considered a potential match. Similarly, the transaction dates and amounts might match, but the source document codes can be different. In this case, the transactions are listed in the spreadsheet as potentially matched.

Unmatched – If a transaction exists in General Ledger, but not Receivables Management or Payables Management-or vice versa-the transaction is considered “unmatched.” For example, a transaction might have been posted in Receivables Management, but not have been posted in General Ledger. Transactions in Work tables aren’t included in the reconcile process.

Although the reconcile routine doesn’t change data automatically, even if unmatched transactions are identified, it will save you a lot of time in tracking down the source of the discrepancies.

Tip: Turning off Allow Account Entry for control accounts like cash, AR and AP will do more to simplify month end GL to sub ledger balancing than any other single thing. This setting in the chart of accounts prevents direct GL entry and requires transactions using these accounts to flow through a sub ledger, thereby preventing rogue GL entries.

~Content Contributed by Pamela Close

Microsoft Dynamics CustomerSource is an information-packed, password-protected site for customers who use Microsoft Dynamics products. Included as a benefit of your service plan, CustomerSource allows you to search a powerful Knowledge Base for Microsoft Dynamics, view unlimited online training, download updates, and find other timely information and resources virtually 24 hours a day. (Sign-in required.)

How to sign in to CustomerSource

If you already have access to your organization’s CustomerSource account, simply visit the CustomerSource sign-in page and enter your Windows Live ID username and password to access the site.

If you do not have access to your organization’s CustomerSource account, please click on the following for more information on how to set-up your access to this site and begin expanding your Microsoft Dynamics GP knowledge.

~Content Contributed by Pamela Close

This feature allows users to quickly and easily e-mail single documents, batches of documents, and/or multiple documents from a list within the system. The user can select which documents they would like to send and also select which customers/vendors should receive their documents in e-mail. This feature also integrates to the Microsoft Word forms feature shipping in Microsoft Dynamics GP 2010. Therefore, the user can send the customized Microsoft Word forms that they have created to their customers and vendors.

Documents supported include:  Sales Quote, Sales Order, Sales Fulfillment Order, Sales Invoice, Receivables Invoice, Receivables Return, Receivables Debit Memo, Receivables Credit Memo, Receivables Finance Charges, Receivables Warranty, Receivables Service/Repair, Standard Purchase Order, Blanket Purchase Order, Drop Ship Purchase Order, Drop Ship Blanket Purchase Order, Vendor Remittances.

• Documents can be sent as either an HTML, XPS, PDF, or DOCX attachment
• Documents can be embedded into the body of the e-mail.
• Customized messages can be created for each document and customer/vendor combination.
• Multiple documents can be combined and sent in a single e-mail.
• Documents can be sent to a To, CC or BCC address.
• Can direct replies to a specific individual or address.

First you need to set up e-mail options for the company

Microsoft Dynamics GP menu >> Tools >> Setup >> Company >> E-mail Settings

From here you can setup the email options for the Sales or Purchasing series

Microsoft Dynamics GP menu >> Tools >> Setup >> Sales >> E-mail Settings

After that if you want to setup messages for email documents you can do that from Microsoft Dynamics GP menu >> Tools >> Setup >> Company >> E-mail Message Setup

Messages can be predefined on a company-wide basis

After setting up the company email options, a two very useful Windows are -

• 1. Mass Vendor E-Mail Settings window : Choose the Purchasing button in the navigation pane >> Vendors list >> select vendors >> select E-mail Settings in the Modify group
• 2. Mass Customer E-Mail Settings window : Choose the Sales button in the navigation pane >> Customers list >> select customers >> select E-mail Settings in the Modify group

Using these windows you can assign e-mail settings to multiple customers/vendors. You can select which documents you want to send. You also can specify message IDs and the document format to use.

~Content contributed by Pamela Close

Now is the time to start planning your upgrade to GP 2010 – Microsoft released service pack 1 on 9/22/2010. Here is a quick list with the Top 10 Reasons to upgrade this year:

1. Get Better Insight Into Your Data Using Dashboards And KPI’s With Drill-Back.There are now customizable dashboards built from SQL Reporting Services (SRS) reports. Each user can decide which ones they want to display, create their own and drill-back to GP from these reports.

2. Get More Out Of Excel With Predictive Modeling And Analysis.Tools integrating to Excel include the Excel Reports, which are in GP 10,but now there are additional ways to use them including Power Pivot tables and drill-back from Excel Reports to GP.

3. Work With Over 90 Built-In SRS Reports.An additional 20 SRS reports have been added that can be used in SharePoint, Outlook or within the dashboards.

4. Create Professional External Documents Using Word Forms.Users will be very excited to be able to deliver Word Forms and customize external (customer and vendor facing) documents within Word.

5. Tailor Your List Pages With Navigation List Builder.Those list pages that were underutilized in GP10 will be customizable with the new SmartList Builder tool – Navigation List Builder.

6. Create Mini-Applications With Extender. Building on the Extender functionality added in GP 10 SP 4, users can create their own free standing Extender windows, report on them with SmartList, and integrate to them with SmartConnect.

7. Connect More Easily With People Using Communicator Integration.Detecting the presence of customer and vendor contacts will allow you to work faster and more directly.You can send instant messages and share your desktop and email documents right from your desktop using these tools integrated to GP.

8. Integrate Systems Better With Web Services.More web services are available in the Human Resources Self Services Suite in Business Portal as well as other tools on SharePoint.

9. Leverage Other Technologies Such As 64 Bit And Workflow On Windows SharePoint Foundation.Management Reporter (FRx replacement) will take advantage of 64 Bit technology and the workflows will be able to be run on SharePoint Foundation.

10. Upgrades Are Much Easier. Customers have experienced very few problems with the upgrade process.

With over 15 years experience working with GP, Applied Tech has completed numerous upgrades and would be glad to help get you up-to-speed with the latest GP releases.  Please call Pam at 608-257-6051 ext 26 or close@appliedtech.us to schedule your upgrade today.

~Content contributed by Pamela Close

If you are a Dynamics GP payroll user and have hired an employee that qualifies under the HIRE Act of 2010 then you need to install the Round 5 tax code update to properly report these wages. The tax update for Dynamics GP allows payroll users to take advantage of the Hiring Incentives to Restore Employment (HIRE) Act, which provides incentives for hiring previously unemployed workers. The update allows users to print Form 941 and other quarter-end reports with HIRE Act adjustments.

Under HIRE, employers may receive an exemption from their 6.2 percent share of the Social Security tax on wages paid to qualified employees. The tax “holiday” is effective for wages paid from March 19, 2010 to Dec. 31, 2010.

Learn more about determining which employees may qualify under the HIRE Act.

To take advantage of the new capability, users must first download and install the latest tax update from CustomerSource. Once the tax code update is installed, users can perform the following tasks before printing Form 941 to report adjustments to their Social Security tax liability for qualified employees.

1. Complete and post all current quarter pay runs.

2. Update the employee tax maintenance record for each qualified employee.

3. Print the Payroll Tax Adjustment (HIRE) report.

4. Enter a manual adjustment to reverse the employer’s Social Security liability that was calculated and posted for qualified employees during the quarter.

5. Process the first pay run after updating the employee tax maintenance reports for qualified employees.

The documentation for the update anticipates that you will have installed the tax code update prior to any payroll runs in July (July being the start of 3rd quarter reporting). The documentation instructs you to run the report for the 2nd quarter only. If you had payroll runs in the 3^rd quarter before you install the Round 5 Tax Code Update you have FICA amounts posted to the general ledger that should be adjusted.

If you did have payroll runs in the 3^rd quarter, it is important to run the Payroll Tax Adjustment Report (HIRE) for the third quarter to identify FICA amounts that you will have to “back out” of your general ledger. Be sure to run the Payroll Tax Report after installing the Round 5 Tax Code, but before you run any more payrolls. The report will list all FICA amounts for the ENTIRE quarter so it is important to understand under what conditions you are running the report.

The HIRE Act actually went into effect on March 19, 2010.  If you had HIRE Act of 2010 qualified employees for the period 3/19/2010 to 3/31/2010, then you should determine the general ledger adjustments for FICA wages by reviewing historical payroll posting journals, and again make the necessary adjustments to FICA expense and liability. Running the Payroll Tax Adjustment Report will list all FICA wages for HIRE Act qualified employees for the ENTIRE 1st quarter.

Under HIRE, employers may receive an exemption from their 6.2 percent share of the Social Security tax on wages paid to qualified employees. The tax “holiday” is effective for wages paid from March 19, 2010 to Dec. 31, 2010.

Learn more about determining which employees may qualify under the HIRE Act.

~Content contributed by Pamela Close

CRM services are now expanding their offerings to include social networking platforms - both internal and external - to help boost client relationships.

With the popularity of social networking mechanisms and websites continuing to grow, more and more businesses are adapting their service offerings to include features that are linked into common social networking websites / platforms such as LinkedIn, Facebook, Twitter, MySpace, and the like. Some are even developing their own features that mimic social networking functions applied specifically for their own customer base - and many are doing both.

CRM, or customer relationship management, services especially are seeing the vast potential of tapping into the social networking phenomenon. With the goal of CRMs being to establish and nurture a company’s relationship with both existing and potential clients, it is only logical for such service providers to use a platform that boasts billions of interactions daily.

Microsoft CRM, for example, has integrated third-party applications such as social media accelerators, social network developers, and the like that enable businesses to develop and use social media platforms, both internal and external, for their own purposes and strategies.

Using social networking - both through developing their own apps and / or integrating with existing ones - CRMs are able to get a better and bigger sense of current and possible client bases. Interaction goes much faster, is much more real-time and genuine, and feedback is easier to gather.

Small and medium-sized businesses are especially benefitting from these developments, since their client bases are much smaller and thus much easier to manage and keep track of through the social networking medium.