Decision Analytics > LawsonGuru > LawsonGuru Letter > 2003

December 2003

The LawsonGuru Letter is a free periodic newsletter providing provocative commentary on issues important to the Lawson Software community.

The LawsonGuru Letter is published by-and is solely the opinion of-John Henley of Decision Analytics. Visit Decision Analytics at https://www.danalytics.com. For subscription information, see the bottom of this message.

The LawsonGuru Letter is not affiliated with Lawson Software.

In this issue:
1. Lawson Excel Add-In Power Techniques
2. Reader Feedback
3. Worthwhile Reading
4. Survey: 8.0 or 8.1?
5. Lawson Tips & Tricks

1. Lawson Excel Add-In Power Techniques
Recently, I had the privilege to attend the North East Lawson User Group's annual Fall Conference in Cooperstown. In addition to having a wonderful time (including dinner at the Baseball Hall of Fame!), I presented two sessions at the conference; one was "An In-Depth Look at Lawson's Microsoft Add-Ins".
I want to share some of the material from that presentation, particularly some "power techniques" you can use with the Excel Add-In. To download a copy of the presentation, including screen shots and diagrams, please go to Lawson SWUG 2004-10 Excel Addins.pdf (web site registration required).

Lawson's Excel Add-Ins are wizards that integrate Lawson into the Excel environment. Not only do the Excel Add-Ins provide you with the ability to import live Lawson data into Excel worksheets, but they provides the functionality (which is fairly unique in the ERP software arena) to upload spreadsheet data back into Lawson. And, the Excel Add-Ins use Lawson's
application security.
The Excel Add-Ins include:
- Query Wizard
- Ad-hoc Analysis
- Simple Operational Reports
- Query records for import into other systems
- Upload Wizard
- Upload Transactions from spreadsheets and non-Lawson systems
- Mass change records where interface programs don't exist
- Examples:
- Setup Code Maintenance
- Employee / Web User synchronization
- One-Time Deductions
- Journal Entries
- Use together with Query Wizard for extra functionality
- Upload data without using FTP or Lawson import utilities
- Drill-Around Wizard
- Provides Lawson Drill-Around links from spreadsheet cells back into the appropriate Lawson business objects
By far, I see clients using the Excel Add-In to fill two voids:
- Quick "data dump" reports
- Transactional and Reference
- Data Conversion & Interfaces
- Replacing CSV & Flat File Uploads
- "Occasional" interfaces
Query Wizard - Database Table or Application Form
The Query Wizard provides two ways to access your Lawson data and import it quickly into an Excel worksheet. You can use the Database Table query, which is faster because it doesn't need to go through the Lawson application logic, or you can use an Application Form query. An Application Form query returns data by populating a Lawson form and applying the business logic of that form for each record you select. Think of it like this. You're using a Lawson Application Form, and keep pressing the next key. And the data on
the form appears as a new row in Excel.
When you create an Application Form query, you are limited to the fields that are available on that form. When you create a database table query, you can query all fields in that table, as well as related tables.
Using the Upload Wizard to Load Data into Lawson
When you want to load data into Lawson from an external system, you have the following options:
- Manual entry on application form
- Standard Lawson conversion/interface program (not available for all data)
- Development of a custom conversion program
- Importing records directly into the database with Lawson environment import utilities
- Excel Add-In Upload Wizard!
Benefits of Excel Add-In Upload Wizard
- Compared to manual entry:
- Efficiency
- Compared to Lawson conversions:
- Much simpler process
- Don't need to use FTP or Lawson import utilities
- Standard conversions don't exist for all database tables
- Compared to custom conversions:
- Cost effective
- No technical knowledge required
- Compared to importing:
- Much more user-friendly
- Field validation, messaging, and application security
Lawson Excel Add-In Upload Wizard Process
- Determine online form(s) to be used for the upload (e.g. AC10, GL65)
- Map worksheet to Lawson form
- Format Excel spreadsheet to match Lawson form
- Spreadsheet must contain all required fields
- Upload data from Excel into Lawson
- Use to add or change records
- Can upload data from selected rows
- Upload data
- Verify data
- Update Lawson applications (if uploading to a conversion form)
If you want to read more about using the Excel Add-In Query Wizard as a conversion tool, check out the "Guest Spot: Lawson's Excel Add-In, A Useful Data Conversion Tool?" article in the March 2003 LawsonGuru Letter (https://www.danalytics.com/guru/letter/archive/2003-03.htm).
Selection Criteria
You can enter criteria on fields from the base table, as well as related tables. Try to always use index-based, rather than filter-based, criteria.
When entering index criteria for a multi-field index, you must enter criteria from the "top down". In other words, selecting a particular process level requires that you also select on the higher-level company field.
However, when selecting based on filter criteria, you can select on lower-level fields, such as process level, without entering higher-level criteria. Since filters are applied after reading all records, performance may suffer.
To enter multiple key values, separate each value by a semi-colon. To enter a range, separate the values by ->.
Changing the Product Line on a Saved Upload
When you create an Upload Wizard file, you may want to change it to point to another product line. The Excel Add-In doesn't provide an easy way to do that, short of creating a brand-new upload. Here's how to do it:
- Open the .uwf file in Notepad
- Change the product line
- Save it and use it!
Similarly, you can also edit a Query Wizard (.dme) file to change the
source product line.
Increasing the Columns in a Saved Upload
When you create an Upload Wizard file, and want to increase the number of columns available for mapping to the Lawson form, the Excel Add-In doesn't provide a way to do that, short of creating a brand-new upload. Here's how to do it:
- Open the .uwf file in Notepad
- Increase the number of columns
- Add empty columns at the end
- Format is ~<Column>~~, e.g. ~~M~
Uploading to a complicated header/detail form
When working against a complicated form:
- "Flatten" the Excel records
- (Don't try to fill multiple details from one record!)
- Use "Add, then Change" option
- Use Line-FC = A for each record
Performance
- In Query Wizard, always try to use "Database Tables" rather than "Application Forms"
- When querying "Database Tables"
- Use indexes against heavily populated tables
- Understand Lawson's Data Dictionary and alternate indexes
- Criteria = Slow
- Indexes = Fast
- Use Excel, not the Query Wizard, for filtering

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
- QUOTE OF THE ISSUE -

"Success is the ability to go from one failure to another with no loss of enthusiasm."
- Winston Churchill
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

2. Reader Feedback
Send your comments to mailto:letter-comments@lawsonguru.com.

- "The articles making it into the news letters are getting better all the time, and I'm feeling more compelled to start responding to some of them (this being my first response). So can I recommend that you start making the articles actual discussion threads and give readers the opportunity to respond to the articles they want right on the web site, then make the discussion threads browsable. I realize that takes additional software and hardware resources, but it would vastly improve the usability of the site and the ROR (return on reading)."

- "With regards to the September 2003 article on 'Scripting to Save Time' (see
https://www.danalytics.com/guru/letter/archive/2003-09.htm), though I agree this approach can indeed save a lot of time, I'm surprised however that the vast majority
of Lawson clients don't consider using some sort of source control management tool. Especially the clients that are making customizations to the existing Lawson
code. One of our employees recently attended our local Lawson User Group meeting and raised this issue, as we are in the process of implementing such a tool to manage
Lawson as well as other software source code. Not a single attendant besides our company was pursuing this option. Lawson did provide one themselves for a while,
one that was based on a solution from Continuous Software that they called EZUpdate. We used it while it was supported, but apparently we were in a small minority of clients that used it. Lawson stopped support for it earlier this year. We became dependent on the functionality of such a tool, so we are now building one ourselves, using the open source tools CVS and the Ant build tool. It's a lot of up-front work, but once its in place, such a system is a remarkable time saver. We estimate that the amount of time it would save our sys admin guys is equivalent
to a full-time employee's work load.
This would have implications for the topic of the other article in the September issue, 'Managing Customizations'. The SCM tools pretty well all have the ability to do
comparisons between source files, and allow you to merge only the parts you want."

3. Worthwhile Reading
Sarbanes-Oxley Has Companies Scrambling
New systems and lots of testing are needed to meet
financial-reporting rules
Information Week, November 10, 2003
http://www.informationweek.com/story/showArticle.jhtml?articleID=16000653
Enterprise Systems Show Results
more than three-quarters of companies implementing enterprise systems
say they've achieved at least half of the value they initially expected
from the technology, according to a study by Accenture.
CIO Magazine, November 1, 2003
http://www.cio.com/archive/110103/tl_numbers.html
Embracing Excel
Microsoft Excel is a much-beloved desktop tool for business intelligence,
yet many people want to contain its use or replace it.
Intelligent Enterprise, November 18, 2003
http://www.intelligententerprise.com/031118/618decision1_1.shtml
Strategy in Action
Strategy is all about creating value for your shareholders.
A strategy map is your guide to getting there.
CIO Magazine, November 1, 2003
http://www.cio.com/archive/110103/strategy.html

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Change Is Hard
CIOs, asked to name their single biggest fear concerning Oracle's
proposed takeover of PeopleSoft, cited rising prices more than
switching costs or support services
46% Prices soaring due to lack of competition
25% Having to switch application systems
16% Not concerned about the proposed acquisition
9% Oracle not supporting current PeopleSoft service agreements
4% Other
Total number of responses: 181
Source: CIO Best Practice Exchange online survey of CIOs from
companies with more than $250 million in revenue, July 2003
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

4. Survey: 8.0 or 8.1?
With the upcoming release of Lawson 8.1, those of you who are currently implementing, or upgrading to, 8.0 are forced to make a decision. Should you skip 8.0, and go directly to 8.1? If you are a new client, I think the direct path to 8.1 makes sense. But, if you're upgrading, it's a tough call. On the one hand, it's a brand new release, and you'll have to live with that pain (although some would argue that you live with that pain anyway!). On the other hand, think about how painful the upgrade process has been for you. If you go direct to 8.1, you won't have to go through two upgrades, just one. So, what are your plans?
Send me your thoughts: mailto:letter-survey@lawsonguru.com.

5. Lawson Tips & Tricks
Share your tips. Send them to mailto:letter-tips@lawsonguru.com.
Need to find out which programs use a particular library (pdlib)?
Do rngdbdump on dictionary "GEN", filename "PGMLIB", option "-k" (along with the product line and library name, both in UPPERCASE), and the PLBSET2 index. Example (enter the command all on one line):
$ rngdbdump -nt -iPLBSET2 gen pgmlib -f programcode -k PROD PRCHKPD | sort | uniq | lashow
Again, make sure you enter the product line and library name in UPPERCASE.
To see the syntax for the rngdbdump command, type "rngdbdump" at a command prompt and hit enter. Note: If you're using the Lawson Environment 7.3.3 or higher, you'll may need to turn off Lawson security to use rngdbdump and importdb with GEN.

The LawsonGuru Letter is a free periodic newsletter providing provocative commentary on issues important to the Lawson Software community. The LawsonGuru Letter is published by--and is solely the opinion of--John Henley of Decision Analytics. Visit Decision Analytics at https://www.danalytics.com.
To subscribe, send an email to: mailto:letter-subscribe@lawsonguru.com

To be removed from the subscription list, send to: mailto:letter-unsubscribe@lawsonguru.com

Decision Analytics is an independent consultancy, focusing on Lawson technical projects, and specializing in customization/modification, data conversion, and integration/interfaces. Please visit https://www.danalytics.com for more information.

Decision Analytics. Integrating Lawson with the Real World.