Open Forum

Like what you see? Discover the benefits of the GPUG Community. Learn More

Retrieving document attachments

  • 1.  Retrieving document attachments

    Posted 06-04-2018 01:06 PM
    Hello,

    We're looking to start using the document attachment feature for journal entries and invoices.
    We've also started doing a lot of reporting in Power BI.
    I was wondering if there's a way to create a link or something similar to open the attachments via an Excel or Power BI report instead of opening them through the regular user interface.
    This would be a great tool for period-end review and for department heads who don't use GP.

    Thanks,

    ------------------------------
    Miriam Retter
    Owings Mills MD
    ------------------------------


  • 2.  RE: Retrieving document attachments

    TOP CONTRIBUTOR
    Posted 06-05-2018 07:55 AM
    ​If you use the AccountTransactions SQL view, there are links in it for drill down. If you were writing an SSRS report, you would apply the link to the text box containing the object, like journal entry number. I haven't tried it in Power BI, though.

    ------------------------------
    Charles Allen
    Senior Managing Consultant
    BKD Technologies
    Houston, TX
    ------------------------------



  • 3.  RE: Retrieving document attachments

    Posted 06-05-2018 09:38 AM
    Thanks for your response.  It seems to me that the drillback feature opens the transaction within the GP interface.  It also requires the user to be logged in to the correct company, and our organization has many GP companies.  I was hoping there would be a way to open the attachment directly (ex. open a pdf in adobe).  Is that wishful thinking?

    ------------------------------
    Miriam Retter
    The Harry & Jeanette Weinberg Foundation
    Owings Mills MD
    ------------------------------



  • 4.  RE: Retrieving document attachments

    GPUG ALL STAR
    Posted 06-05-2018 10:11 AM
    Edited by Beat Bucher 06-05-2018 10:15 AM
    Hi Miriam,
    I fear that this will hardly be doable.. the document attached in GP are stored in 'blob' fields in SQL server tables.. not sure you can simply 'pull' them out with a magic query and be able to open the corresponding apps, since it could be any type of file (PDF, XLS, DOC, TXT, JPG, name it.. ).

    Without using some program codes (.NET or C#), this is not feasible.. Check out this blog about SQL Blob's:
    https://www.akadia.com/services/dotnet_load_blob.html

    and Microsoft's paper on Technet:
    https://technet.microsoft.com/en-us/library/aa496014(v=sql.80).aspx

    ------------------------------
    Beat Bucher
    Business Analyst, Dynamics GP SME
    Ultra-Electronics Forensic Technology Inc.
    Montreal QC/Canada
    @GP_Beat http://dyngpbeat.wordpress.com/
    Montreal QC GPUG Chapter Leader
    GP2013R2 / MR2012 CU14
    ------------------------------



  • 5.  RE: Retrieving document attachments

    SILVER CONTRIBUTOR
    Posted 06-06-2018 07:07 PM
    Hi,

    You could do this using URL links in your notes or a custom field but it would require that everybody drink the same Kool-Aid and agree to naming and filing conventions.
    This would then need to be maintained.
    I've done this with other applications using network shares and with SharePoint  or Office 365 SharePoint.
    Then it's just text in an SSRS report.
    Training users is the hardest part at first, then you realize your file structuring isn't any good, etc, etc, etc,.
    It's doable, and with good planning, it can be successful.



    ------------------------------
    Tim Andaya
    Strategic Systems Group
    "There are no stupid questions."
    ------------------------------



  • 6.  RE: Retrieving document attachments

    GPUG ALL STAR
    Posted 06-08-2018 08:11 AM
    Agree with Tim,
    We had done this in the past with Inventory Item's drawing document (PDF) that are stored on our SharePoint Intranet.. and we used the Internet Information field to store the URL..
    Then the Internet Information table was queried thru an Excel report and some VBA code would transform the URL into 'clickable' links (this doesn't happen automagically when retrieving URL's by SQL code)

    The biggest hurdle we had to face was to mass-update all the URLs in the table the day our SharePoint was updated from an older version to 2013.. and boy let me tell you that it was a painful exercise.. you better stick with network shared folders.. much easier.

    ------------------------------
    Beat Bucher
    Business Analyst, Dynamics GP SME
    Ultra-Electronics Forensic Technology Inc.
    Montreal QC/Canada
    @GP_Beat http://dyngpbeat.wordpress.com/
    Montreal QC GPUG Chapter Leader
    GP2013R2 / MR2012 CU14
    ------------------------------



  • 7.  RE: Retrieving document attachments

    GPUG ALL STAR
    Posted 06-11-2018 01:48 AM
    I haven't digested this fully, but here's an interesting approach to explore:

    https://www.jitendrazaa.com/blog/sql/sqlserver/export-documents-saved-as-blob-binary-from-sql-server/

    Zubin

    ------------------------------
    Zubin Gidwani
    Founder
    Dynamic Budgets

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



  • 8.  RE: Retrieving document attachments

    SILVER CONTRIBUTOR
    Posted 06-11-2018 08:26 AM
    I have custom .NET C# code used to load these attachments as PDF and also saves to file.  I use this to present invoices to check signers that don't have access to GP so that they can verify before signing.  This works across two company databases and allows for choice between ribbon attachment or notes attachment.  One uses the NOTEINDX to match th3e busobjkey, the other uses the AP voucher number to match the busobjkey.  This is for single lookup, but could be changed to load these images into file folders.

    Let me know if this would be helpful to you.

    ------------------------------
    Steven Mayhue
    Lead Developer/DBA
    My Florida Regional MLS
    Altamonte Springs FL
    ------------------------------



  • 9.  RE: Retrieving document attachments

    GPUG ALL STAR
    Posted 06-11-2018 08:32 AM
    Thanks Zubin for posting this link..
    I wasn't aware of such option and I'm going to keep the link.. could become handy one day :-)

    ------------------------------
    Beat Bucher
    Business Analyst, Dynamics GP SME
    Ultra-Electronics Forensic Technology Inc.
    Montreal QC/Canada
    @GP_Beat http://dyngpbeat.wordpress.com/
    Montreal QC GPUG Chapter Leader
    GP2013R2 / MR2012 CU14
    ------------------------------



  • 10.  RE: Retrieving document attachments

    GOLD CONTRIBUTOR
    Posted 06-11-2018 10:31 AM
    Works like a charm!  You have done your job!  You may go home now!

    ------------------------------
    Leah Nolan
    Great Plains Administrator
    XO Group, Inc
    Austin TX
    ------------------------------