Tool to Browse Attached Files Stored in GP Server Via Document Attach

It has been a while since my last post. I have been busy writing my professional exams and starting with a new employer. While that means I no longer develop GP tools, I still have a couple projects I have not previously posted due to complexity but I feel some may be able to put it to use.

The tool is continuation from my automated batch attach addin. The previous addin allows users to attach files in bulk. This tool allows the user to browse the GP server for all attachments in the most used tables and download them (in bulk).

Some background first. The old OLD notes attachment feature was essentially a link to a network accessible file. The new document attach feature stores the file within the GP server itself. This means with simple SQL and c# code, you can perform a binary save to “download” or retrieve the file out of the server outside of the GP client. Since this can be done all within SQL, the user can filter based on any field within the database to locate a file.

ToolPreview
Figure 1: Screenshot of Tool

The tool presumes you have read access to the GP server via your Windows authentication.
Quick Start:
1. Type the name of the GP server in the “GP Server” box
2. Type the name of the GP database you wish to query in the “DB Name” box
3. (optional) Check off any filter boxes and input filter criteria
4. Hit “Refresh”

Assuming proper authentication, the tool will populate the datagrid with all of the attachments found (filtered down by any filters selected).

NOTE: This tool has minimal error trapping. Incorrect GP server or GP database names or other variables will more than likely crash the tool.

Link to tool: GPAttachments.zip

This was written in c# using SQL to query the database. If users wish, I will be willing to provide the SQL or c# sourcecode.

Enjoy,

~James

Disclaimer: I am very much an amateur programmer. I have never taken an official Microsoft course. I cannot guarantee any code/program that I provide as I only have the ability to test my work on a small sample set. Users accept all the risk of using anything I provide and are encouraged to thoroughly test in a dedicated test environment before moving to production. Files I provide are free for use and are not to be resold. By using my files you agree to not hold me liable for any damages caused by said files.

Advertisements

7 thoughts on “Tool to Browse Attached Files Stored in GP Server Via Document Attach

  1. HI James, I am looking at writing something in GP to assist with AP payment approvals and the client would like to be able to view the attachments (source documents) made by the users prior to the batch approval. I would appreciate it if you could share the c# & SQL code you have to allow the retrieval of the document? Many thanks. Robert

    Like

  2. Hi Robert,

    Sample SQL Query: https://drive.google.com/open?id=0B_u3lBw1w4P_WWVOcEhLRHVVaFU

    I have included many of the most commonly used tables so you can pick and choose and filter down to whatever suits you for your program.

    All attachments have a unique attachmentID in the database. Once you know that and the file name+extension, you can perform a download in c#:

    string connectionString = “Server=GPServerName;Database=DatabaseName;Trusted_Connection=True”;
    SqlConnection conn = new SqlConnection(connectionString);
    SqlDataAdapter da = new SqlDataAdapter();
    SqlCommand command = conn.CreateCommand();
    DataSet ds = new DataSet();
    DataTable result = new DataTable();
    string query = “select binaryblob from coAttachmentItems where attachment_id = ‘12345’”;
    command.CommandText = query;
    da.SelectCommand = command;

    conn.Open();
    da.Fill(result);
    conn.Close();

    //Output directory
    string dir = @”C:\”;

    //Filename of the file in the DB with extension
    string fileName =”filename.txt”;
    byte[] data = (byte[])result.Rows[0][“binaryblob”];

    FileStream fs = File.Create(dir + fileName, 2048, FileOptions.None);
    BinaryWriter bw = new BinaryWriter(fs);
    bw.Write(data);
    bw.Close();
    fs.Close();

    Source code of my original tool: https://drive.google.com/open?id=0B_u3lBw1w4P_eE9MQmwyTEJhYkU

    Let me know if you have any questions or if a link is broken!

    ~James

    Like

  3. Hello James,

    I’m trying to import invoice attachments from another application into GP. The source application stores attachments as blobs in SQL. I see that you have experience with loading attachments into GP using GP Batch Attach add-in. Would you share a sample code on how to create an attachment in GP using SQL?

    Thank you,
    Lev.

    Like

    • Hi Lev,
      Unfortunately the GP Attach tools I have made do not use SQL to attach (only view and download). Instead they use the GP Dexterity C# SDK + Sanscript to execute commands in GP. It is not that simple to bypass GP and attach a file directly into the SQL database. I’ll try to walk through the attachment process below (I am no expert so there may be other tables missing):
      1. User hits the paper clip/note icon. This creates a “note” entry in SY03900 and is assigned a unique noteindx. This noteindx is stored in the relevant entry in the table where the document is. For example, if you are attaching an item to a payables invoice, it will be stored in the payable tables (one of the PM series). If it’s being attached to a journal entry, then the GL series etc.
      2. User uploads an attachment. This stores the attachment as a binary blob in coAttachmentItems and assigns it a unique attachment_id. Table CO00105 then sores the mapping between the noteindx and attachment_id.

      There are many other tables that get effected which is why it makes manually attaching files outside of GP not ideal due to other SQL triggers that might be effected.

      If possible, I would recommend using c# and the SDK to execute the upload. The code is fairly simple but essentially you have to navigate to the item in GP you want to attach a file to, initiate the note creation, and execute the upload script. I can provide the code for this if that is the route you wish to attempt.

      James

      Like

      • Hello James,

        I just returned from GPUG Amplify where I asked the same question Microsoft and other ISVs. I was told that there is no API to attach documents and therefore it would not be possible to do it from the backend. However, I was given a SQL code that performs attachment to a note. I will try to modify it so it will attach to transaction instead of a note. I will keep you posted.

        Again thank you for your time.

        Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s