The Etelligent blog is a leading source for ERP content in Western Canada. With nearly 1000 readers per month (and growing), Etelligent offers free tips and tricks, how to’s, and video updates that speak to users, and potential users, of Microsoft Dynamics ERP. More importantly, all contributors are existing consultants who offer valuable insight into usability, customization, and problem fixes. The Etelligent blog is a strong resource if you are considering purchasing Microsoft Dynamics products, or looking to expand your knowledge as a current user.
Recently I was reviewing some vendor address data within a spreadsheet that a client was preparing for import. One of the columns contained telephone number s but they were not formatted consistently. The client took on the job of editing the phone numbers.
When the updated file was ready for import I asked the client how they had fixed the phone numbers. Did they use spreadsheet formulae to manipulate the data, did they re-key the phone numbers, or did they do something else? They shared that they re-keyed the phone numbers, but that it didn’t take long because one of their strengths was data entry. I responded that data entry is not one of my strengths so I would have used spreadsheet formulae to reformat or update the numbers.
You may be wondering by now, “What does this have to do with importing account segment descriptions?” The point of the anecdote is that where possible I want to use tools to reduce data entry, and entering segment descriptions is no exception. However with account segment descriptions the integration tools that I usually use, Integration Manager or SmartConnect, do not handle this task. Therefore, I have had to come up with an alternate method to avoid keying in account segment descriptions. My method is to use a Word Mail Merge to populate a SQL update statement.
The three key components for a mail merge are a spreadsheet data source, a Word merge template, and the resulting SQL Update statement. The spreadsheet must have at least three columns. I like to use: segment_number; segment_value; and segment_description. This data is often available or mostly available from the general ledger account import list and should not have to be created from scratch.
The Word mail merge template would use the spreadsheet as the data source for the mail merge. In my scenario there would be a single statement:
update gl40200 set dscriptn = '«segment_description»' where sgmtnumb = '«segment_number»' and sgmntid = '«segment_value»'
The resulting merged data could then be saved as a SQL query and run against the appropriate company database. Note that it is best practice to validate database updates in a test environment and to have a current database backup in place before running update statements. Given those warnings, here are the first four lines from a set of sample data:
update gl40200 set dscriptn = 'BANK – GENERAL' where sgmtnumb = '1' and sgmntid = '10000'
update gl40200 set dscriptn = 'BANK – USD' where sgmtnumb = '1' and sgmntid = '10300'
update gl40200 set dscriptn = 'PETTY CASH' where sgmtnumb = '1' and sgmntid = '10900'
update gl40200 set dscriptn = 'CASH SUSPENSE' where sgmtnumb = '1' and sgmntid = '10999'
I have also used the mail merge functionality to create macros for data entry but they take longer to set up and execute, so I prefer a SQL statement in this case. If you are not familiar with the Mail Merge functionality in Microsoft Word then look into it. You could also utilize it in printing addresses on envelopes for your Christmas cards or other mass mail outs.
Sheldon, ERP Consultant for Etelligent Solutions
Contact Sheldon Here
This information is provided “as is”. The author accepts no liability for the content of this article, or for the consequences of any actions taken on the basis of the information provided.
Etelligent Solutions - Edmonton, Calgary ERP and CRM
Other Microsoft Sites
I'm a Customer
I'm a Partner
Follow Microsoft Dynamics