Welcome Guest [Log In] [Register]
Bookmark and Share
Welcome to LawsonTalk. We hope you enjoy your visit.


You're currently viewing our forum as a guest. This means you are limited to certain areas of the board and there are some features you can't use. If you join our community, you'll be able to access member-only sections, and use many member-only features such as customizing your profile, sending personal messages, and voting in polls. Registration is simple, fast, and completely free. After registration, please check your e-mail within 24 hours for an message from us, read it, and reply to it.


Join our community!


If you're already a member please log in to your account to access all of our features:

Username:   Password:
Add Reply
Finding A Field In The Lawson Database; Tips and Tricks for Database Navigation
Topic Started: Mar 16 2006, 09:22 AM (32,031 Views)
LawsonsNbr1Fan
Member Avatar
Super Member
[ *  *  *  *  * ]
I get this question more than most. How do I find a field in the database? It seems like quite a few of us are using Crystal Reports and Access to pull information out of the database for our own custom needs. Depending on the number of Lawson modules that you use, you are probably working with a couple thousands of tables inside the Lawson database. You may just want one field. Well, here are some tips and tricks to make that process a little easier for you.

1) LawsonGuru website

The Lawson Guru at LawsonGuru.com has been nice enough to post some of the more common tables used in Lawson with an ERD diagram detailing some of the tables, the relationships, and the fields located within them. This is a good place to start.

2) DBURF

If you're in Lawson and you do a form transfer (F8), type in 'dburf' and hit enter. You will be taken to the 'Print Technical Text' program. Enter the system code and the Program code (example 'HR' and 'HR11') and hit enter again. A screen will appear detailing the tables that the form updates and the tables that the form references. This is extremely helpful information in narrowing down where the field you are looking for is.

3) DBDOC

Do another F8 and try 'dbdoc'. Now enter the system code and the table name (example 'HR' and 'Employee') and hit enter. Now a data dictionary will display with all of the fields in that particular table of the database. There is also a short description and information about the type and length of the field. At the bottom of this report, you will also find information about relationships to other fields.

4) Portal Trick

This is a neat trick. If you pull up a form in Portal (say, HR11). Click on the field and then press the Cntrl + Shift + O (the letter, not the number) on your keyboard. At the bottom of the screen, it will tell you the field name.

So hopefully, with all of the tools at your disposal, it will help you narrow down trying to find a field for your report or finding out how to tie two tables together for your report.

Happy hunting.
Offline Profile Quote Post Goto Top
 
cjmart
Chris Martin
[ *  *  *  *  *  *  * ]
GREAT information for assisting newbies with report development against Lawson tables.

Just an added note on #4: CTRL+SHIFT+O will give you the table prefix (three letter acronym) of the field. This can be referenced in the Data File Text documents for the appropriate system code to find the related table name.

Chris Martin
cjmart@swbell.net
Offline Profile Quote Post Goto Top
 
xqqqme
Advanced Member
[ *  *  * ]
Another way to find the table name is to use the Excel query add-in. The latest version of the add-in has a field where you can enter the prefix and it will display the table name and the system code.
Offline Profile Quote Post Goto Top
 
Milo
Member Avatar
Grizzled Die-Hard Lawson Vet
[ *  *  *  *  *  *  * ]
There's one more utility that will really help when creating reports. A number of fields listed in DBDOC are "derived". When you look in SQL or Oracle or DB2 and they simply don't exist. So you need to reconstruct them for reports. Whether you put the derived formula into your report, or set up a database view, you need to be able to see what these fields are.

Use the command line utility "derfldrpt" - Derived Field Report. Very useful.
Offline Profile Quote Post Goto Top
 
Lawsuss
Member Avatar
Advanced Member
[ *  *  * ]
Quote:
 
4) Portal Trick

This is a neat trick. If you pull up a form in Portal (say, HR11). Click on the field and then press the Cntrl + Shift + O (the letter, not the number) on your keyboard. At the bottom of the screen, it will tell you the field name.


This Feature is now Automated at : Lawsuss.com

You can enter the three letter code from a CTL+SHFT+o in a field and get the table information.

We hope this helps all Lawson users.


--------------------------------------
Patch Testing Forms
Coming Soon:
Offline Profile Quote Post Goto Top
 
dwolbee
Newbie
[ * ]
I am trying to access the time records that display in the Lawson ESS Timesheet.

We use KRONOS for our clocks.

Are the actual clock times that display in ESS stored in Lawson or do they come from KRONOS?
Offline Profile Quote Post Goto Top
 
« Previous Topic · Member Articles · Next Topic »
Add Reply

Sponsored by Nogalis, Inc.