Get Database Relationships from GEN

The Lawson database relationships are stored in the GEN database.  Foreign key constraints are not actually added to the database tables.  So, to see what these relationships are, you can query the GEN data.  The primary relationship information is stored in FILEREL.  Then, to get the fields that are used in the relationship you’ll need to join FILRELFLD and FILEINDFLD.  FILERELFLD contains the primary field names, and FILEINDFLD contains the foreign field names.  The below query is a sample run against a SQL Server database for a data area called “TEST”.

 

SELECT RTRIM(FILEREL.FILENAME) PARENT_FILE,

RTRIM(FILEREL.RELFILE) CHILD_FILE,

REPLACE(RTRIM(FILERELFLD.FRFLDNAME), ‘-‘, ‘_’) PARENT_FIELD,

REPLACE(RTRIM(FILEINDFLD.FLDNAME), ‘-‘, ‘_’) CHILD_FIELD

FROM FILEREL INNER JOIN

FILERELFLD ON FILERELFLD.FILENAME = FILEREL.FILENAME AND

FILERELFLD.RELNAME = FILEREL.RELNAME INNER JOIN

FILEINDFLD ON FILEINDFLD.FILENAME = FILEREL.RELNAME AND

FILEINDFLD.INDEXNAME = FILEREL.INDEXNAME AND

FILEINDFLD.FLDNBR = FILERELFLD.FLDNBR INNER JOIN

FILEDEF ON FILEDEF.FILENAME = FILEREL.FILENAME

WHERE FILERELFLD.FRFLDNAME <> ‘              ‘ AND

FILEREL.PRODUCTLINE = ‘TEST’