Lawson Add-ins for Microsoft

1
Lawson Add-ins for Microsoft Office

• For Financials

Don Peterson Solutions Group don.peterson_at_us.lawso
n.com
2
First of All

• Special thanks to
• Ann Roth
• Formerly with Lawson Learning
• Who created the original presentation and passed
  it on to me
• to use as a basis for this presentation

3
Would You Like To

• Easily upload information into Lawson?
• Without using FTP or Lawson ENV utilities
• Quickly create a custom report or chart?
• Based on a Lawson database table, or form
• Simply create a Mail Merge document?
• Based on a Lawson database table, or form

4
The Lawson Add-ins Allow you to

• Perform all of these tasks with PC-based software
  your staff already uses
• Make use of Lawson Security to limit the data
  users may access

5
What are the Add-ins?

• Applications that integrate Lawson with the
  Microsoft Office Suite
• Lawson Query Wizard with Drill Around
• Lawson Upload Wizard
• Mail Merge using the Lawson Query Wizard

6
Lawson Query Wizard with Drill Around

• Extract data from Lawson into Microsoft Excel
• Format the data, set selection criteria, sort the
  results, perform calculations, and specify a
  worksheet for output
• Ad-hoc analysis
• Simple operational reports
• Query records for input into other systems
• For a Lawson product that enables you to create
  more complex reports, check out our Reporting
  Services, which is part of Lawson Business
  Intelligence

7
Lawson Upload Wizard

• Transfer data into Lawson without having to enter
  data manually or write a batch update program
• Upload transactions from non-Lawson systems
• Mass change records where programs dont exist
• Web User and Attribute maintenance
• Use together with the Lawson Query Wizard

8
Mail Merge using Lawson Query Wizard

• Extract data from Lawson into a standard
  Microsoft Word Mail Merge
• Create internal memos to employees
• Create external letters to customers and vendors
• Create address labels or printed envelopes for
  employees, customers and vendors
• For a Lawson product that sends out notifications
  automatically upon the existence of certain
  criteria, check out Smart Notification, which is
  part of Lawson Business Intelligence

9
Options for Uploading Data

• Manual entry on Lawson application form
• Not efficient
• Lawson conversions
• More complex process
• Need to use FTP or Lawson ENV utilities
• Standard conversions dont exist for all tables
• Custom Conversions
• Costly
• Technical knowledge required
• Importing with Lawson ENV utility
• Not as user-friendly
• No built-in field validation and messaging

Or
10
Lawson Add-ins Upload Wizard

• User friendly
• Batch driven, but less efficient than other
  options
• Does not require FTP or Lawson ENV utilities
• You can map to most Lawson forms easily
• Data validation
• Return message of the results of the uploading -
  line-by-line

11
Upload Wizard

• Layout the Microsoft Excel worksheet
• You MUST include all of the required fields
• Include any other fields you want to populate
• Be aware of any defaulted fields on the form and
  include those also if you want values other than
  the default values
• Upload data from Microsoft Excel into Lawson
• You may Add, Change or Delete records with the
  Upload Wizard
• You are able to upload selected rows and columns
  of data

12
Basic Steps for the Upload Wizard

• Determine the Lawson form you would use if you
  were to enter the data manually
• Determine the required fields on the form
• Plus any optional fields you want to fill
• Create the Microsoft Excel worksheet
• Map the worksheet columns to the Lawson form
• Upload the data
• Verify the data
• If you are uploading to a Lawson conversion file,
  then run the job to update the Lawson database
  tables

13
Determine the Lawson Form for the Upload

• Upload General Ledger Journal Entries into Lawson
• Transaction Interface Maintenance (GL65.1)
• Single form instead of two forms
• Define Journal (GL40.2)
• Journal Entry (GL40.1)
• Business rules for converting journal entries are
  applied
• Can review and edit journal entries before
  interfacing to permanent tables

14
Determine the Required Fields

• Click Add on the blank form
• You will be positioned to the first required
  field
• Press Ctrl-Shirt-O on the keyboard to see the
  field name and number
• Add that field to your worksheet
• Fill in that field on the form and click Add
• You will be positioned to the next required field
• Repeat these steps until the Add goes through
• Use Ctrl-Shift-O to determine the field names and
  numbers of any additional fields you want to fill.

15
(No Transcript)
16
Ctrl-Shift-O
17
Create the Worksheet

• Enter the field name from the form as column
  headings
• Include the field names
• Include the field numbers
• Enter the data

18
(No Transcript)
19
Map Worksheet to Lawson Form

• Open Upload Wizard from Microsoft Excel menu
• Login to Lawson
• Select the Product Line, System Code, Lawson Form
• Enter the rows which contain the data
• Select the fields to map to the worksheet columns

20
(No Transcript)
21
(No Transcript)
22
(No Transcript)
23
(No Transcript)
24
(No Transcript)
25
(No Transcript)
26
Additional Upload Wizard Tips

• Some forms may return the message Must inquire
  before using this function code.
• See Knowledge Base Article 126655 for additional
  information
• The form being used only displays one page of
  data at a time. How can I modify the additional
  records?
• See Knowledge Base Article 525187 for additional
  information

27
Query Wizard

• Quickly create a custom report or chart
• Base the report on a database table or Lawson
  form
• Query Wizard settings can do simple formatting
• Once the data is in Microsoft Excel, you have
  the full power of Excel
• Query Lawson data so it can be changed
• Use the Query Wizard to select the data
• Edit the data
• Upload the data back into Lawson using the Upload
  Wizard
• Simply create a Mail Merge document
• Base the query on a database table or Lawson form

28
Database Table vs. Application Query

• Database Table based on Lawson database tables
• Much faster than an application query
• One call to the server retrieves all the records
  you request
• More flexible
• Can include information from a table and related
  tables
• Appropriate for a larger record set
• Application based on a Lawson form
• Much slower than a database table query
• Each record returned requires a call to the
  server
• More intuitive
• You do not need to know the database tables
• Restricted to information displayed on the
  selected Lawson form
• Appropriate for a smaller record set

29
Basic Steps for the Query Wizard

• Choose a query source
• Database tables whenever possible
• Select the file or online form to query
• Select the fields to display in the report
• Select the Related Table and fields (if
  applicable)
• Select the criteria for the report
• Format the output
• Verify the results
• Use Excel to produce a chart (if needed)

30
Business Scenario

• Create a report of unreleased AP Invoices

31
(No Transcript)
32
(No Transcript)
33
(No Transcript)
34
(No Transcript)
35
(No Transcript)
36
(No Transcript)
37
(No Transcript)
38
(No Transcript)
39
(No Transcript)
40
(No Transcript)
41
(No Transcript)
42
Mail Merge using the Query Wizard

• Business Scenario
• Create merge letters to customers to notify them
  of your new website at which they may place
  orders for your products on-line

43
Steps for Creating your Mail Merge

• Choose the query source
• Select the table and fields for your data
• Be sure Include Column Headings is checked on the
  Column Headings tab
• Enter Criteria to narrow down the results
  returned to what you need
• Check the Save Output for Mail Merge checkbox on
  the Worksheet tab
• Click Finish to send the data to the worksheet
• Verify the data and save the Microsoft Excel
  worksheet

44
(No Transcript)
45
(No Transcript)
46
(No Transcript)
47
(No Transcript)
48
(No Transcript)
49
Create the Mail Merge Document

• Open Microsoft Word and select Open Data Source
  from the Mail Merge Toolbar
• Select the worksheet that you saved which
  contains the result data from the Query Wizard
• Select Sheet1 from the list
• Create the Mail Merge document as you would any
  Mail Merge document in Word
• Select Merge to New Document

50
(No Transcript)
51
(No Transcript)
52
(No Transcript)
53
(No Transcript)
54
(No Transcript)
55
(No Transcript)
56
(No Transcript)
57
Lawson Add-ins for Microsoft Office

• Easily upload information into Lawson
• Quickly create a custom report or chart
• Simply create a Mail Merge document
• Perform all of these tasks with PC-based software
  your staff already uses
• Makes use of Lawson security to limit the data
  that users may access

58
Additional Resources

• Lawson Add-ins for Microsoft Office User Guide
• Web-Based Training (WBTs) available on
  support.lawson.com
• Lawson Knowledge Base

59
Questions?
60
Thank You!