Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Attaching Documents us...
Microsoft Dynamics GP (Archived)

Attaching Documents using SQL

(0) ShareShare
ReportReport
Posted on by Matthew Falle 90

Hello,

I am trying to write some SQL to attach documents so that I don't have to do in manually all the time. So far I have managed to convert my PDF into a BLOB file and have inserted data into the tables that get updated when you attach a document. However the it doesn't show as "Attached" in GP. Does anyone have any experience in trying to do this or know of another method I may use? 

The tables I have added data to are 

  • CO00101: Document Attach Master
  • CO00102: Document Attach Reference
  • CO00103: Document Attach Properties
  • CO00104: Document Attach Status
  • CO00105: Document Attach E-Mail
  • CoAttachmentItems: Contains BLOB 

Kind Regards, 

Matthew Falle 

*This post is locked for comments

  • sandipdjadhav Profile Picture
    sandipdjadhav 18,265 on at
    RE: Attaching Documents using SQL

    Hello Ven, 

    I am looking for the same to import the image using sql script.  Do you like to share the script? Feel free to email me at : sandipdjadhav@hotmail.com

    Thanks much
    Sandip

  • limeboy19 Profile Picture
    limeboy19 5 on at
    RE: Attaching Documents using SQL

    Hi Ven -

    I am attempting to create a scheduled job to insert files as blobs into the GP tables with the intention of them showing up on the front end of GP. We have a business use case needed the files to be uploaded externally, so I am researching the best way to do this. Do you mind sharing or pointing me in the direction of how you are uploading the blobs into SQL? Any pointers would be appreciated.

    Cheers -

    Emil

  • Suggested answer
    26P2ER Profile Picture
    26P2ER 1,773 on at
    RE: Attaching Documents using SQL

    Hello 

    I have used straight SQL to insert data including files as blobs in the referenced tables. This was a requirement in our workplace. Our procurement system (Paramount) wouldn't do it so we built a custom integration to send the document attachments in Paramount over to GP.


    Have you tried using a uniqueidentifier data type variable in your code? I have. I have also made use of the newid() function to populate the same.

    Good luck

    Ven

  • renzweiler Profile Picture
    renzweiler 50 on at
    RE: Attaching Documents using SQL

    Is the Attachment ID created through SQL or Dex ? Example 7b0c1be7-f63f-4121-8f37-50332641a445. I can do the BusObjKey through SQL but not seeing the Attachment ID creation in the stored procs. I am running this through a Smartconnect job and creating the attachments if the document succeeds. I am either going to call the stored procs or do the inserts into the CO tables through the Smartconnect Tasks.

  • Tim Wappat Profile Picture
    Tim Wappat 5,701 on at
    RE: Attaching Documents using SQL

    If SOP then yes the first number is soptype.

    At the end of the day it is trying to identify the unique record, hence the name of biz key, so they are trying to combine the

    • DEX product module (0)
    • "path" for the module table \Sales\Sales Order\
    • The key or composite key for the record in that table, in SOP that is a composite key  (SOPTYPE + SOPNUMBE).

    Tim.

  • MattPaulen Profile Picture
    MattPaulen 6,910 on at
    RE: Attaching Documents using SQL

    I just looked up some of our records in SQL and the number before the tilde for Sales Order looks like it corresponds to the SOPTYPE so you are right in thinking that an invoice will be a 3.  Here's a couple of examples we have for an order and a return:

    0\Sales\Sales Order\2~ORD01300782

    0\Sales\Sales Order\4~R0096627

  • Matthew Falle Profile Picture
    Matthew Falle 90 on at
    RE: Attaching Documents using SQL

    Thanks Tim, this and your blog is really informative. I was wondering if you would happen to know how to get the one for Sales Transactions on invoices.

    It looks something like 0\Sales\Sales Order\3~INVS303

    I think the 3~ is that it is an invoice and the INVS303 is the Document No.

    I am trying to find a way to automatically attach some PDF's to their correct invoices.

  • Tim Wappat Profile Picture
    Tim Wappat 5,701 on at
    RE: Attaching Documents using SQL

    The different objects in GP have their own formats for the bus key, some examples follow. The notes attachments use the funky hex versions of the integer record reference, others use the pain record keys such as the one you are talking about. 

    In Dex there is a method on the window to call to do the conversion, in SQL we don't have that, although I'm getting tempted to write one! Work flow has a procedure (that I used to get list below), but it only covers objects that work flow interfaces with, so falls short as a comprehensive list.

    So basically define your own function or procedure to do the conversion, based on the object type it is for.

    ---------------------------------------------------------------------------------------------------------------------

    '0\PM\Purchase Order\' + ltrim(rtrim(@Key1))

    '0\PM\Requisition\' + ltrim(rtrim(@Key1))

    '0\UPR\Timecard\' + ltrim(rtrim(@Key1)) + '~' + ltrim(rtrim(@Key2)) + '\' + ltrim(rtrim(@Key3)) + '\' + ltrim(rtrim(@Key4))

     '0\PM\Vendor Maintenance\' + ltrim(rtrim(@Key1))

    '258\PA\PTE Timesheet\' + ltrim(rtrim(@Key1))

    '258\PA\PTE Expense Report\' + ltrim(rtrim(@Key1))

    '0\UPR\ESS Employee Profile\' + ltrim(rtrim(@Key1)) + '~' + ltrim(rtrim(@Key2)) + '\' + ltrim(rtrim(@Key3))

    '0\UPR\ESS W4\' + ltrim(rtrim(@Key1)) + '~' + ltrim(rtrim(@Key2))

    '0\UPR\ESS Direct Deposit\' + ltrim(rtrim(@Key1))

    '0\UPR\ESS Employee Skills\' + ltrim(rtrim(@Key1)) + '~' + ltrim(rtrim(@Key2)) + '\' + ltrim(rtrim(@Key3))

    '0\PM\Payables Transaction\' + (LTRIM(RTRIM(str(DOCTYPE))) + '~' + VCHNUMWK)

    there are many more and 3rd party products too!

  • Matthew Falle Profile Picture
    Matthew Falle 90 on at
    RE: Attaching Documents using SQL

    I manged to get the document attached in the end, there was a problem with using the wrong ~ (tilde). My only issue is that I had to specify the BusObjKey as I am not 100% sure how it is generated.

    Do you know a way to generate the correct BusObjKey? I have tried to use SELECT CONVERT(VARCHAR(MAX), CONVERT(binary(4),cast(NOTEINDX as integer)), 2) (source: https://community.dynamics.com/gp/b/dynamicsgp/archive/2016/05/02/document-attach-vs-notes-and-how-to-locate-whether-a-master-record-has-a-document-attached) How ever I am not sure how they went from the code  '0000003B0' then to the BusObjKEy of '0\PM\Vendor Maintenance\ACETRAVE001

    Do you have any advice?

  • Tim Wappat Profile Picture
    Tim Wappat 5,701 on at
    RE: Attaching Documents using SQL

    Can you show us the rows from the tables you have created and what documents you expect them to show up in. (you can attach screenshots to posts by using the rich formatting option and the media button.)

    I can only anticipate that the Bus Obj Key is wrong format or number for the object you are attaching to?

    Tim.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,269 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,198 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans