Prev
Next
Forums User Experience Lawson Design Studio

Querying comments table for comments

 3 Replies
 0 Subscribed to this topic
 12 Subscribed to this forum
Sort:
Author
Messages
Ward Mitchell
Veteran Member
Posts: 81
Veteran Member
11/18/2010 9:13 PM
    We did some work some time ago via sql querying comments for an invoice using the following query:

    SELECT top 3 api.COMPANY

    ,ISNULL(apd.DIS_ACCOUNT,'')

    ,ISNULL(apd.DIS_ACCT_UNIT,'')

    ,ISNULL(apd.DIS_SUB_ACCT,'')

    ,ISNULL(apv.VENDOR_VNAME,'') -----AS VENDORNAME

    ,api.INVOICE_DTE -----AS INV_DATE

    ,api.INVOICE

    ,api.TRAN_INV_AMT ----AS AMOUNT

    ,api.DESCRIPTION

    ,api.BATCH_NUM ----AS Batch

    ,api.VENDOR

    ,api.L_INDEX

    ,ISNULL(lhap.NAME,'') ----AS FIRSTCOMMENT

    ,ISNULL(lhap.[OBJECT],'') ----AS SecondComment

    ,''

    FROM APINVOICE AS api

    INNER JOIN APVENMAST AS apv ON

    api.COMPANY = apv.VENDOR_GROUP AND api.VENDOR = apv.VENDOR

    LEFT OUTER JOIN APDISTRIB AS apd ON api.OBJ_ID = apd.API_OBJ_ID

    LEFT outer JOIN L_HAPI AS lhap ON api.L_INDEX = lhap.L_INDEX

    --WHERE api.INVOICE=''

    ORDER BY apd.DIS_ACCOUNT,api.VENDOR, api.INVOICE_DTE



    What I want to know though is, is there a DME query out there to do the same thing? Just curious
    John Henley
    Posts: 3353
    11/18/2010 10:01 PM
      This topic is definitely not for novices...you have to use getattachrec.exe; syntax looks something like this (in this case AR):
      https://server/cgi-lawson...410289&K3=1&_OUT=XML

      You would need to find out the values for the parameters specific to APINVOICE
      &_ON (object name) -- look in AP.or
      Thanks for using the LawsonGuru.com forums!
      John
      Ward Mitchell
      Veteran Member
      Posts: 81
      Veteran Member
      11/18/2010 10:10 PM
        Perfect John. Thanks so very very much. I can bastardize from here!
        Terry P
        Veteran Member
        Posts: 234
        Veteran Member
        11/18/2010 10:22 PM
          Another good way is to use Portal in Debug mode. Drill down to the attachments for comments, then look at the DME call in the debug window. That helps a lot when trying to figure out the parameters, etc.