Lawson Add-ins for Microsoft

1
Lawson Add-ins for Microsoft Office

• Tips Tricks

Don Peterson Solutions Group don.peterson_at_us.lawso
n.com
2
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

3
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

4
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

5
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

6
Call String

• Displays the URL the Add-in is sending
• Accessible from a button on the Wizard
• To view the full URL, you must click Finish or
  Upload first
• Uses
• Helps to determine the source of an error
• The URL can be used outside of the Add-ins

7
Call String

• Query Wizard String
• http//localhost/cgi-lawson/dme.exe?PRODLIVEFILE
  EMPLOYEEINDEXEMPSET1KEY1FIELDCOMPANYEMPLOY
  EEFULL2DNAMEADDR1CITYSTATEZIPPAEMPLOYEE2EB
  IRTHDATESELECTSTATE3DIL7CSTATE3DMNMAX600OU
  TCSVDELIM09
• Upload Wizard String
• http//localhost/servlet/ags?_PDLLIVE_TKNSL00.1
  _EVTCHG_RTNDATA_TDSIGNOREFCChange_ADDINS
  TRUE_f1C_f3BRC_f140A_f150006_f160Huma
  n20Resources_f180A_DELIM09_OUTTEXT_EOTT
  RUE

8
User Preferences

• Sets retrieval time-out on queries and uploads.
• Accessible from a button on the wizard
• Determines how long the Add-in will wait for a
  response from the server.
• Disconnects the Add-in from the server after the
  time-out is reached
• DOES NOT stop the query from running on the
  server
• Be careful not to set this too low

9
Retrieval Time-out
10
Finding Table and Field Names

• Lawson Portal
• Bring up a form in the Portal
• Position your cursor in the field
• Press Ctrl-Shift-O on your keyboard
• You see the information in the lower left corner
  of your screen
• Example Company on Invoice screen AP20.1

11
(No Transcript)
12
Field Information

• Field Name
• Table prefix API
• Field in table COMPANY
• Field Number
• Id _f6
• Field Size
• Size 4
• Field Key Identifier
• KNb 01
• Field Type
• Type text
• Field Data Edit
• Edit numeric

13
Table Prefix Translation

• Enter the table prefix, API, into the Table
  Prefix Translation field on the Query Wizard
• API APInvoice which is found in the AP,
  Accounts Payable system code

14
Finding Table and Field Names

• LID
• Display field numbers by selecting from the form
  menu
• Options Show Field Numbers
• Then point at the field
• To find the Table and Field Names
• First Choice
• If you have access to FormDef, get that for the
  form
• Second Choice
• Get a copy of the .scr file for the form
• Last Resort
• dburf and dbdoc

15
LID AP20.1
16
Form Definition (FormDef)

• Web accessible definition of the form layout
• http//SERVER/cgi-lawson/formdef.exe?_PDLPRODUCT
  LINE_TKNFORM_OUTTEXT_DELIM

17
(No Transcript)
18
(No Transcript)
19
(No Transcript)
20
(No Transcript)
21
(No Transcript)
22
(No Transcript)
23
(No Transcript)
24
(No Transcript)
25
Technical Text and Data File Text

• Start with the program to find the tables
• Use the command line program dburf
• Take the table name to find fields
• Use the command line program dbdoc
• Knowledge Base
• Select Product, Release and enter keywords for
  the search
• Select File Layouts

26
Query Wizard Criteria Tab

• Indexes
• Conditions
• Selection Criteria
• Sample Data
• Parentheses
• Edit Existing Criteria

27
Criteria Tab
28
Criteria Parentheses
29
Query Wizard Drill Around

• Find a Lawson form which has the Drill Around
  that you want to do
• Include the required key fields from that form,
  along with the field on which you want to drill,
  in your query
• Required key fields are usually the fields at the
  top of the form
• From the Portal, you can do Ctrl-Alt-A to get to
  the List Collection which is a good guess as to
  which the required key fields are.

30
(No Transcript)
31
(No Transcript)
32
(No Transcript)
33
(No Transcript)
34
(No Transcript)
35
(No Transcript)
36
Upload Wizard Fields

• Required Fields
• Open the blank form and click Add
• You will be positioned on the first required
  field, note the field
• Enter the field value and click Add
• You will be positioned on the next required
  field, note the field
• Repeat until the Add goes through
• Optional Fields
• Add other fields you want to upload to your
  worksheet
• Defaulted Fields
• Be sure to include any defaulted fields in your
  worksheet if you do not want the default value.
• ALWAYS try your uploads on test data with just a
  couple of rows first
• If the upload fails and you do not know why,
  enter the same data from the worksheet row into
  the form itself and see what happens

37
(No Transcript)
38
Upload Wizard

• Most forms may be uploaded to directly
• Some require a two-step upload, header form
  first, then detail
• Define Journal (GL40.2) - Header
• Journal Entry (GL40.1) Detail
• Some require detective work, educated guesses,
  dumb luck to figure out
• Forms that require an inquire first Individual
  Action (PA52.1)
• HK required form key values
• Sub forms which only have an OK and Cancel
  button Asset Management (AP22.2) accessed from
  a button on Basic Invoice (AP20.1)
• Fields from sub form hidden on main form
• Key Values passed from main form Options Amount
  (BN18.1) to Option Rates (BN19.1)
• Fields from main form hidden on sub form
• Some require a custom programmed worksheet and
  not the Upload Wizard
• One-time Vendor with Basic Invoice (AP20.1)

39
(No Transcript)
40
(No Transcript)
41
(No Transcript)
42
(No Transcript)
43
(No Transcript)
44
(No Transcript)
45
(No Transcript)
46
(No Transcript)
47
(No Transcript)
48
(No Transcript)
49
Login Changes with Add-ins 2.0.x

• 2.0.x connects via winhttp.dll on the PC
• XP SP2 and Windows 2003 Server SP1 have tightened
  security with winhttp.dll
• Firewall automatically enabled on each PC
• Login errors 602 and 12029 can be caused by the
  tightened security
• See Lawson Knowledge Base article 555327 to
  troubleshoot for these errors
• The Add-ins work with either pre or post Lawson
  9.
• The login determines the Lawson version and
  adjusts the Add-ins

50
Additional Resources

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

51
Special Thanks to

• Niki Denker
• Senior Technology Support Engineer
• Lawson Global Support Center
• For contributions to this presentation

52
Questions?
53
Thank You!