Attachments Report – Item Card

With the new attachment functionality in Dynamics GP, attachments to primary GP cards are enabled. What if I want to see all my cards with their associated attachments in one report, rather than scrolling through all the cards. In this post, an SQL script to retrieve attachment report for “Item Cards”, the same logic can be applied to retrieve Attachment report for other GP cards.

Tables Included:

• IV00101 | Item Master
• CO00101 | Document Attachment Master
• CO00102 | Document Attachment Reference

 

SELECT  A.ITEMNMBR ,
        A.ITEMDESC ,
        CASE ISNULL(B.ItemNumber,'')
          WHEN '' THEN 'No'
          ELSE 'Yes'
        END Attachment,
        ISNULL(FILENAME, ' ') AS 'FILE NAME',
        ISNULL(B.CreatedDate, '') AS CreatedDate,
        ISNULL(B.CreatedUser, '') AS CreatedUser,
        CASE ISNULL(B.DELETE1, '')
          WHEN 1 THEN 'Yes'
          ELSE 'No'
        END AS Deleted
        FROM    dbo.IV00101 AS A
        LEFT OUTER JOIN
        (
             SELECT    X.ItemNumber,
                       Y.fileName,
                       X.AllowAttachmentFlow,
                       X.AllowAttachmentEmail,
                       X.AttachmentOrigin,
                       X.DELETE1,
                       X.CreatedDate,
                       X.CreatedUser
             FROM     
                       (
                               SELECT    RIGHT(RTRIM(BusObjKey),
                               LEN(BusObjKey)-
                                    ( CHARINDEX('Item Maintenance',
                               RTRIM(BusObjKey), 1) + 
                                  LEN('Item Maintenance') )) 
                                  AS ItemNumber,
                               Attachment_ID,
                               AllowAttachmentFlow,
                               AllowAttachmentEmail,
                               AttachmentOrigin,
                               DELETE1,
                               CREATDDT AS CreatedDate,
                               CRUSRID AS CreatedUser
                               FROM    CO00102
                               WHERE   BusObjKey 
                                       LIKE '%Item Maintenance%'
                       ) AS X
                         LEFT OUTER JOIN CO00101 AS Y 
                         ON X.Attachment_ID = Y.Attachment_ID
        ) AS B ON A.ITEMNMBR = B.ItemNumber
        ORDER BY Attachment DESC

Best Regards,
Mahmoud M. AlSaadi