On HR11 'Ben Flags' there is a field called, 'Established Patient.' I need to find what database table this fields is stored in. I tried dbdoc and dburf with no luck. Am I missing something? Lawson's GSC told me this questions was a billable issue.

:lol: I understand your furstration.

The field you're looking for is actually called: ESTAB-PATIENT and its on the PAEMPLOYEE table.

If you want I can teach you a somewhat quick way to look this up next time.

:console:

If you use Portal, there is way you can find the field name by pressing Cntrl + Shift + O (the letter, not the number). At the bottom of Internet Explorer it will show you the field name, the field length, and field type. Though it doesn't show you table it is located in, it is another tool to narrow down the infinite search.

CTRL + SHIFT + O in Portal is a godsend if you are a report developer. Just to clarify though, it will help you identify the table name by providing the three letter acronym used by Lawson. You will then have to look up the Table Prefix/TLA to determine the full table name. For the example above, CTRL + SHIFT + O will display the following:

"PEM-ESTAB-PATIENT..."

As indicated previously, ESTAB-PATIENT is the field name (which will be named ESTAB_PATIENT if your RDBMS is Oracle). PEM is the Table Prefix/TLA for the table. How I would identify the corresponding table name is use the Data File Text file for the HR system code. Do a search on PEM, which will reveal PAEMPLOYEE as the file name for the PEM Prefix (in the File Listing By Name at the beginning of the document).

I would bet that there are many other ways identify the table name when you know the prefix, but this is the way I do it...

Chris Martin
cjmart@swbell.net

If you want to get a complete list of the Lawson tables and their 3 character prefixes:

rngdbdump -cn gen filedef -f prefix filename -vproductline=your productline name

Redirect the output to a file and you could get it into Excel and do some sorts, printing, etc. Use this and the ctrl-shift-o and you should be able to find your fields.

Is there a function similar to this for LID? I've not found anything...

jacflash:

To find out what field is being referenced in LID, you go to the screen listing. Command-line command is 'led YourProductLine ScreenID' then select 'C - Form'. What you are looking at is the actual 4GL description of the screen. Be careful, you are in edit mode when you do this!

On large programs, there are various sections for each screen. Each screen section is listed as the 4GL screen first, then the fields. The fields are listed in the order that they appear, from left to right as you go down from top to bottom.

This is not always as easy as it sounds. There are often display-only fields that you may not always see on the screen. Other fields that have an asterisk (*) directly in front of them are not used on the screen, they are being used to link to other screens. Also, when there are tabs on the screen, you will see one huge screen listing broken into separate tab screens, then the field listing is broken out into tab sections.

Just to make things even more confusing, occasionally fields are not actually used the way they look, but instead the COBOL code moves their data into other data fields. One quick way to check is to use a test product line, make an entry into the field in question, make sure that it saves, and then look at the data element in the record that you think that you changed.

BTW, on a report screen, the 4GL is different. For each report form, the report fields are laid out with letters under them, and then the fields are specifically listed with their letter. However, you have to go into the COBOL itself to see where the data is actually coming from.

Good luck!

How I find out what table the form uses is in lid open the form transer and under form ID type laenv which takes you to a menu of enviromental utilities and choose user desktop, then application text and if you choose technical text you can find out what tables a form uses. If you choose data text you can find out what fields a table holds.

I have been unsuccessful in locating the Dependent Date of Death field on HR13 in any Lawson table. I used the CTRL+SHIFT+O trick you mentioned here (and that is wonderful by the way) and I get the following in the status line. I can't deem the table from it?

DATE-OF-DEATH|Id= _f64|name=TF0-2|SIZE=10|Type=text|Edit=date|Parent=TFO-2

DATE-OF-DEATH is not easy to locate in a relational database world. Using COBOL procedural code, it is mapped to columns 2-9 of field DATA in file HRUTILITY. HRUTILITY holds data from a number of HR programs.

To locate the appropriate record, you have to specify a key of:
- SYSTEM = "BN"
- RELEASE = "7"
- LEVEL = "2"
- KEY1 = "HR13"
- KEY2 = Company #
- KEY3 = Employee #
- KEY4 = Sequence Number

This is based on our current Applications level of 8.0.3 MSP9.

Then look up the record. Get the field DATA, and extract the 8 characters starting at the 2nd characters. That gives you the Date of Death. Simple, right? :D

(My guess is that this code goes back 20 years to good old non-relational coding techniques. I rarely see stuff this old-fashioned in Lawson. Ah, for the good old days!)

Check DEATH_DATE (if Oracle) on EMPDEPEND.

Chris Martin
cjmart@swbell.net

Fyi - looks like dependent date of death is not available on EMDEPEND until 8.1

Okay, so EMPDEPEND shows up in Apps 8.1. That's why I didn't find it. Ooops. :blink:

I should have specified that Date of Death sitting in a portion of a poorly-mapped HRUTILITY field is in Apps 8.0.3.

Version 2.0.4 of MS addins has a neat feature to help find what table a field is in. Using the Query Wizard, go to the Fields tab and look in the lower left corner for the Table Prefix Translation. Enter the 3-digit prefix you find using Cntrl-Shift-O on the field in Portal and it will give you the table name.

This wasn't in the previous version of addins we had installed.

If you're using Oracle for your rdbms and have access to a sqlplus prompt you can find what table a column (field) appears in, or all tables a column with a similar name appear in.

If I want to know which tables any field with the name "BIRTH" in it is:

select table_name, column_name
from user_tab_columns
where column_name like '%BIRTH%';

I get this back on 8.0.3:
TABLE_NAME COLUMN_NAME
------------------------------ --------------
APPLICANT BIRTHDATE
EMDEPEND BIRTHDATE
HCPATIENT BIRTHDATE
PAEMPLOYEE BIRTHDATE
PAEMPLOYEE BIRTH_CITY
PAEMPLOYEE BIRTH_STATE
PAEMPLOYEE BIRTH_CNTRY_CD
PARTICIPNT BIRTHDATE


-GW