Purchase Order Cost Review Message (MA66.3) comments

 8 Replies
 0 Subscribed to this topic
 1 Subscribed to this forum
Sort:
Author
Messages
pglibra
Veteran Member Send Private Message
Posts: 76
Veteran Member

Good afternoon,

I am trying to create a query in Access to look at closed cost messages to include the comments from "Has Comments" field.

What table can I find the comment info?

Thank you

Paul

Kat V
Veteran Member Send Private Message
Posts: 1020
Veteran Member
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.
brupp
Veteran Member Send Private Message
Posts: 165
Veteran Member
Our Crystal Report is pointed to L_HMAG.Object for the MA66.3 comments.
pglibra
Veteran Member Send Private Message
Posts: 76
Veteran Member

Thanks for your response.

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

Thank you

Paul

pglibra
Veteran Member Send Private Message
Posts: 76
Veteran Member
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.
Kat V
Veteran Member Send Private Message
Posts: 1020
Veteran Member
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?)
brupp
Veteran Member Send Private Message
Posts: 165
Veteran Member
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')
John Henley
Send Private Message
Posts: 3353
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
Thanks for using the LawsonGuru.com forums!
John
John Beard
New Member Send Private Message
Posts: 1
New Member
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 "