1 / 53

Lawson Add-ins for Microsoft ® Office

Lawson Add-ins for Microsoft ® Office. Tips & Tricks. Don Peterson Solutions Group don.peterson@us.lawson.com. What are the Add-ins?. Applications that integrate Lawson with the Microsoft ® Office Suite Lawson Query Wizard with Drill Around ® Lawson Upload Wizard

johnson
Télécharger la présentation

Lawson Add-ins for Microsoft ® Office

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Lawson Add-ins for Microsoft® Office Tips & Tricks Don Peterson Solutions Group don.peterson@us.lawson.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 don’t 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?PROD=LIVE&FILE=EMPLOYEE&INDEX=EMPSET1&KEY=1&FIELD=COMPANY;EMPLOYEE;FULL%2DNAME;ADDR1;CITY;STATE;ZIP;PAEMPLOYEE%2EBIRTHDATE&SELECT=STATE%3DIL%7CSTATE%3DMN&MAX=600&OUT=CSV&DELIM=%09 • Upload Wizard String • http://localhost/servlet/ags?_PDL=LIVE&_TKN=SL00.1&_EVT=CHG&_RTN=DATA&_TDS=IGNORE&FC=Change&_ADDINS=TRUE&_f1=C&_f3=BRC&_f14:0=A&_f15:0=006&_f16:0=Human%20Resources&_f18:0=A&_DELIM=%09&_OUT=TEXT&_EOT=TRUE

  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. 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

  12. 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

  13. 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

  14. LID AP20.1

  15. Form Definition (FormDef) • Web accessible definition of the form layout • http://SERVER/cgi-lawson/formdef.exe?&_PDL=PRODUCTLINE&_TKN=FORM&_OUT=TEXT&_DELIM=~

  16. 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

  17. Query Wizard Criteria Tab • Indexes • Conditions • Selection Criteria • Sample Data • Parentheses • Edit Existing Criteria

  18. Criteria Tab

  19. Criteria Parentheses

  20. 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.

  21. 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

  22. 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)

  23. 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

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

More Related