Finding A Field In The Lawson Database

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:

Finding A Field In The Lawson Database; Tips and Tricks for Database Navigation
Topic Started: Mar 16 2006, 09:22 AM (2,702 Views)
LawsonsNbr1Fan	Mar 16 2006, 09:22 AM Post #1
Super Member Posts: 103 Group: Members Member #118 Joined: January 12, 2006	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.



cjmart	Mar 16 2006, 10:24 AM Post #2
Chris Martin Posts: 205 Group: Members Member #332 Joined: January 18, 2006	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



xqqqme	May 11 2006, 08:21 AM Post #3
Advanced Member Posts: 31 Group: Members Member #376 Joined: January 24, 2006	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.



Milo	May 12 2009, 12:59 PM Post #4
Rebmem Member Posts: 361 Group: Members Member #424 Joined: February 9, 2006	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.


« Previous Topic · Member Articles · Next Topic »