Purchase Order Cost Review Message (MA66.3) comments

The comments are in one of the L_DM tables but I'm at a loss which one. I thought it was L_DMIV (Message Invoice) but I can't find a table called that.

Our Crystal Report is pointed to L_HMAG.Object for the MA66.3 comments.

Thanks for your response.

What table can I find L_HMAG? I do not see it in MAINVMSG.

Thank you

Paul

Ok...I found the L_HMAG table but unable to locate field that contains the actual text for the comments.
R_NAME gets me the Title of the comment.

If HMAG is the header - DMAG should be the detail with the actual comment. (Welcome to Lawson Tables - why put it all in one spot when you can put it in three?)

Here's the SQL - maybe there's something here that would help? I don't know SQL & really don't understand how comments work to be honest Good Luck!

SELECT
APINVOICE."BASE_DISC_AMT", APINVOICE."DISC_DATE", APINVOICE."DUE_DATE",
MAINVDTL."ITEM", MAINVDTL."MATCH_UNIT_CST",
MAINVMSG."AUTH_CODE", MAINVMSG."VENDOR", MAINVMSG."INVOICE", MAINVMSG."PO_NUMBER", MAINVMSG."PO_LINE_NBR", MAINVMSG."STATUS", MAINVMSG."CREATE_DATE", MAINVMSG."BUYER_CODE",
POLINE."AGREEMENT_REF", POLINE."ENT_UNIT_CST", POLINE."MANUF_NBR",
L_HMAG."OBJECT",
POVAGRMTHD."PO_USER_FLD_3", POVAGRMTHD."PO_USER_FLD_5"
FROM
{ oj (((("LSLMDB"."ls_apps"."APINVOICE" APINVOICE INNER JOIN "LSLMDB"."ls_apps"."MAINVDTL" MAINVDTL ON
APINVOICE."COMPANY" = MAINVDTL."COMPANY" AND
APINVOICE."VENDOR" = MAINVDTL."VENDOR" AND
APINVOICE."INVOICE" = MAINVDTL."INVOICE" AND
APINVOICE."SUFFIX" = MAINVDTL."SUFFIX" AND
APINVOICE."PO_NUMBER" = MAINVDTL."PO_NUMBER" AND
APINVOICE."PO_RELEASE" = MAINVDTL."PO_RELEASE" AND
APINVOICE."PO_CODE" = MAINVDTL."PO_CODE")
INNER JOIN "LSLMDB"."ls_apps"."MAINVMSG" MAINVMSG ON
MAINVDTL."COMPANY" = MAINVMSG."COMPANY" AND
MAINVDTL."VENDOR" = MAINVMSG."VENDOR" AND
MAINVDTL."INVOICE" = MAINVMSG."INVOICE" AND
MAINVDTL."SUFFIX" = MAINVMSG."SUFFIX" AND
MAINVDTL."PO_NUMBER" = MAINVMSG."PO_NUMBER" AND
MAINVDTL."PO_RELEASE" = MAINVMSG."PO_RELEASE" AND
MAINVDTL."PO_CODE" = MAINVMSG."PO_CODE" AND
MAINVDTL."LINE_NBR" = MAINVMSG."PO_LINE_NBR")
INNER JOIN "LSLMDB"."ls_apps"."POLINE" POLINE ON
MAINVMSG."COMPANY" = POLINE."COMPANY" AND
MAINVMSG."PO_NUMBER" = POLINE."PO_NUMBER" AND
MAINVMSG."PO_RELEASE" = POLINE."PO_RELEASE" AND
MAINVMSG."PO_CODE" = POLINE."PO_CODE" AND
MAINVMSG."PO_LINE_NBR" = POLINE."LINE_NBR" AND
MAINVMSG."VENDOR" = POLINE."VENDOR")
LEFT OUTER JOIN "LSLMDB"."ls_apps"."L_HMAG" L_HMAG ON
MAINVMSG."L_INDEX" = L_HMAG."L_INDEX")
LEFT OUTER JOIN "LSLMDB"."ls_apps"."POVAGRMTHD" POVAGRMTHD ON
POLINE."AGREEMENT_REF" = POVAGRMTHD."VEN_AGRMT_REF"}
WHERE
MAINVMSG."STATUS" < 9 AND
(MAINVMSG."AUTH_CODE" <> 'SRB' AND
MAINVMSG."AUTH_CODE" <> 'EMA' AND
MAINVMSG."AUTH_CODE" <> 'DLH')

This has been a cause of consternation in the Lawson community forever. Here is a link to a paper I wrote way back on how comments are handled in Lawson:
https://www.lawsonguru.co...ts-in-Lawson-v8.aspx

I find that most other messages are stored in the L_Hxxx Table and sometimes in the L_Dxxx table also, but 90% or more are in the L_Hxxx tables. The field OBJECT has the actual message beginning in column 96 I believe. The first 95 characters are header info and can be dropped from your report. Good Luck. We also found out that MS-Access sometimes has trouble matching up the L-Index key fields because it doesn't appear to always be case sensitive when linking. SQL didn't have this problem.
MAINVMSG zzzo zz I 0 08-NOV-06 121334 08-NOV-06 121334 113 95 TYPE=C,CREATE USER=coleman ,MODIFIED USER=coleman ,PER QUOTE 20000979
MAINVMSG zzzn zz m 0 08-JAN-08 103654 08-JAN-08 103654 171 95 "TYPE=C,CREATE USER=coleman ,MODIFIED USER=coleman ,THIS INVOICE IS MATCHED TO WRONG PO , SHOULD BE 1072967
JAY C "