GP Batch Attach Code Walkthrough

A question came up on the GPUG forums (Bringing Attachments from Legacy System into GP) asking about attaching files programmatically in GP. My GP Batch Attach works solely with the PM module as my previous employer’s needs really only surrounding around attaching vendor invoices as that was the bulk of documents they had.

In this post I will walk through my code and show how to easily expand it to any window in GP.

Walkthrough

The document attach process in GP is fairly simple and is as follows:

  1. Create a note (paperclip icon) on the document window you wish to attach a file
  2. Attach the document in GP referencing that note’s index

When utilizing the attach function in GP, you need only 3 things:

  1. Note number/index (Including path. See below)
  2. File path for upload
  3. File name

Once you have that, all you have to call is the following function:

Microsoft.Dexterity.Applications.Dynamics.Forms.CoAttachManagement.Functions.CreateAttachment.Invoke(noteNumber, filePath, fileName, false, 2)

I’ll be honest, I do not know what the last two parameters are for. I ran the debug logger while performing the attach feature in GP and noticed the parameters provided were always false and 2 so I have always left it as this. I have not been able to find any documentation as to what these two parameters do.

You can tell from this that is it extremely easy to call the attach function from c#. The most “complicated” part of automating this is navigating to the correct window that you wish to attach the file.

Code to Attach Document to Payables Invoice/Document

The following works whether a note has already been created for the document or not.
Link to code snippet: GP Batch Attach Code Snippet

1. Navigate to the PM Transaction Window for an existing document

Microsoft.Dexterity.Applications.Dynamics.Procedures.PmZoomToEntryForm.Invoke(docType, voucherNum, trxSource, tableFlag, “”, 2, 7814);

Variables:
docType <short> = Payables Document Type (i.e. 1 for invoice, 5 for credit memo, 6 for payment)
voucherNum<string> = Voucher Number of the document
trxSource<string> = Transaction Source for the document
tableFlag<short> = 3 for history table, 2 for open table, 1 for temp/work table where the document exists
Similar to the CreateAttachment procedure, I could not find sufficient documentation on the last 3 parameters. Again, I ran GP with the debug logger on and made note of the parameters provided and they were always blank, 2 and 7814.

2. Open the note window (same as clicking the paperclip icon on the window)

Microsoft.Dexterity.Applications.Dynamics.Forms.PmTransactionEntryZoom.PmTransactionEntryZoom.NoteAbsentButtonWindowArea.RunValidate();

3. Open the document attach window (same as clicking the Attach button)

Microsoft.Dexterity.Applications.Dynamics.Forms.FormNote1.FormNote1.OleNoteAbsentButton.RunValidate();

4.Once the document attach window has been opened, we can retrieve the note number and note origin/location within the GP file structure:

string localNumber = Microsoft.Dexterity.Applications.Dynamics.Forms.CoAttachManagement.CoAttachManagement.LocalNumber.Value;

string localOrigin = Microsoft.Dexterity.Applications.Dynamics.Forms.CoAttachManagement.CoAttachManagement.LocalOrigin.Value;

string noteNumber = @”0\System\” + localOrigin + @”\” + localNumber;

5. Finally, issue the procedure to attach the file (from above):

Microsoft.Dexterity.Applications.Dynamics.Forms.CoAttachManagement.Functions.CreateAttachment.Invoke(noteNumber, filePath, fileName, false, 2);

6. Lastly close any tables or forms you opened:

Microsoft.Dexterity.Applications.Dynamics.Forms.CoAttachManagement.Close();
Microsoft.Dexterity.Applications.Dynamics.Forms.FormNote1.Close();       Microsoft.Dexterity.Applications.Dynamics.Forms.PmTransactionEntryZoom.Close();

Comments

People may notice, my GPBatchAttach only requires a vendor ID and document number to identify the unique document however the PM Transaction Zoom procedure requires the transaction source number and document type as well. I built in additional queries to fetch this information using the vendor ID and document number within my code. This in itself is also tricky and I will keep it out of this post as it is kind of an aside as you can technically fetch this information numerous other ways.

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

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.

Creating Custom Excel GP Functions Using VBA

We do a lot of monthly account reconciliations at work in Excel. This is a very repetitive process every month so any bit of effort I can spend now to reduce time spent in the future is a win in my book. To this end, I created a couple functions accessible through Excel’s function bar which pull information such as account balances for a period, account descriptions and multi-currency balances. I saved all of the VBA code driving these functions in a .xlam and “installed” this Add-in on all users’ computers in a uniform directory (C:\GPFunctions). Alternatively you can just save it in your Personal workbook that launches on Startup. The prerequisite for this to work is that all users’ Windows login must be enabled to have read access to the GP SQL Server (Trusted Connection).

So what? Well this effectively allows us to pull live account balances into a single cell in any Excel worksheet without being logged into GP. I would not recommend doing this for more than 100 accounts per worksheet as it effectively performs one SQL query per function (100 queries) and be slow to refresh. But for multi-company dashboards (such as inter-company balance checks) or simple balance sheets, pulling account balances is quick and painless. While this may not apply to all companies, we are license starved at my company so everyone has to fight to log into GP. This effectively bypasses the need to log into GP to pull a simple balance as this queries the SQL Server directly.

For now I simply pasted the VBA code for GPAccBal below. I plan to release my full .xlam, but there are other features in the .xlam I wish to write about before releasing it so sit tight.

Screenshots

GPFunctions

GPAccBal Example

GPAccBal Sample

GPAccBal Result

GPAccBal VBA Code:

Public Const gpServer As String = “” ‘Put your SQL Server name here

Function GPAccBal(company, account, period, fiscal_year, Optional server)
‘Do not forget to import Active X library

‘References     : Microsoft ActiveX Data Objects 6.1 Library (or newest version)
‘Written By     : James Lyn
‘Date Written   : December 2014
‘Purpose        : To return period balance for one account from the GP database

‘Inputs
‘   company     : designates the database to query against
‘   account     : designates the account number to query
‘   period      : 1-12                  – designates the period for which to return the balance as per the fiscal calander
‘   year        : i.e. 2014             – deisgnates the fiscal year to query
‘   server      : optional, default gpServer. If the server name changes, input changed name into parameters

‘Output         : Period balance for a single account for the selected company and period in the functioning currency

‘Store current application settings
screenUpdateState = Application.ScreenUpdating
statusBarState = Application.DisplayStatusBar
calcState = Application.Calculation
eventsState = Application.EnableEvents

‘Disable unnecessary functionality
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

Dim query As String
Dim serverVar As String

If IsMissing(server) Then
serverVar = gpServer
Else
serverVar = server
End If

query = “select  sum(PERDBLNC) ” & _
” from   (select PERDBLNC, YEAR1, PERIODID, ACTINDX from GL10110″ & _
” Union all” & _
” select PERDBLNC, YEAR1, PERIODID, ACTINDX from GL10111 ) GL” & _
” where” & _
” actindx in (select distinct actindx from GL00105 where actnumst = ‘” & account & “‘)” & _
” and year1 = ” & fiscal_year & _
” and periodid <= ” & period & _
” group by actindx”

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

On Error GoTo ConnectionError:

‘Open trusted connection with server
cn.Open “DRIVER={SQL Server Native Client 10.0};SERVER=” & serverVar & “;trusted_connection=yes;Database=” & company

‘Query server
rs.Open query, cn

‘Copy query results
If (rs.EOF = False) Or (rs.BOF = False) Then
GPAccBal = rs(0)
Else
GPAccBal = “”
End If

‘Close connections
rs.Close
cn.Close

‘Restore application settings
Application.ScreenUpdating = screenUpdateState
Application.DisplayStatusBar = statusBarState
Application.Calculation = calcState
Application.EnableEvents = eventsState
Exit Function

ConnectionError:
‘MsgBox “Error: (” & Err.Number & “) ” & Err.Description, vbCritical
‘MsgBox “Please check your parameters.”
If rs.State = 1 Then
rs.Close
Set rs = Nothing
End If

Set cn = Nothing

‘Restore application settings
Application.ScreenUpdating = screenUpdateState
Application.DisplayStatusBar = statusBarState
Application.Calculation = calcState
Application.EnableEvents = eventsState
Exit Function

End Function

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.

“Copy” any field from any Dynamics GP window using Dexterity SDK

I usually spend maybe 30-60 minutes Google-ing something before I just give up and write code to do it. For all I know there is a better way to go about this but this is my approach. In the famous words of James May, I would classify this as “an ingenious solution to a problem that should never have existed in the first place” (RIP Top Gear).

I have always wondered why you couldn’t select data in windows in Dynamics GP. Theft? Because that would make life too easy and we have to make sure there is still enough work for accounting technicians to do at the end of the day before robots take over? Regardless I wrote a short C# procedure that grabs the current window and dumps all non-empty fields into a nice data grid view that IS copyable. Needless to say this does not output data in scrolling windows as that is a separate window from the main window (although just as easy to copy from). As this is written as an add-in, you have to add the menu handler to each window you would like this on, but because I love coding in a very universal matter, no extra parameters or changes are required when calling the procedure from a new window.

Now for the code walkthrough (complete code provided below):

Grab the current window

static void displayDetails(object sender, EventArgs e){
Microsoft.Dexterity.Bridge.DictionaryForm currForm = sender as Microsoft.Dexterity.Bridge.DictionaryForm;

When you call a procedure from any window in GP, the SDK sends the reference to that form through the sender object

Create our data table

DataTable dt = new DataTable();
dt.Columns.Add(“Name”, typeof(String));
dt.Columns.Add(“Value”, typeof(String));

Loop through each field in the window

foreach (var field in currForm.Windows[0].Fields)

I left the index at 0 because for the most part I am just interested in the main window which is *usually* 0. You could add a form name check here to see if it matches the sender’s name to check for the main window to make this more robust

Loop through each property in the field hunting for the Name and Value

foreach (var prop in field.GetType().GetProperties()){
switch (prop.Name){
case “Name”:
name = prop.GetValue(field, null).ToString();
break;
case “Value”:
value = prop.GetValue(field, null).ToString();
break; } }

Save the results to the data table

if ((name != “”) && !(String.IsNullOrEmpty(value))) {
DataRow row = dt.NewRow();
row[“Name”] = name;
row[“Value”] = value;
dt.Rows.Add(row); }

The only “hard-coded” aspects of this code are the property values “Name” and “Value” which I think is fairly safe to say are not going to change anytime soon. I created a form that stores my datagridview and bind the data table to that view. I also set the shortcut to close the form to CTRL-Q (same shortcut used to execute the code). This allows the user to quickly open and close it using the same shortcut. The other benefit of being a separate form is that you can navigate away from the original form but leave the Details_Form open for reference. I did not provide a .dll as it is up to you what windows you want to add this to but if there is anyone who wants me to build it based on a list of windows, comment below.

Code: Display Details.txt

Screenshot (blanked out sensitive information):

displayDetails

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.

GP Batch Attach Payables Documents

Last up for now is my Batch Attach add-in. We tend to use Integration Manager at work to upload lots of invoices. In this day and age, there has been a push to move everything electronic and stop wasting so much paper. The new document attach feature was great addition over the old OLE notes which made attaching documents much easier and more intuitive. Unfortunately manually attaching 100+ PDF invoices every week is cumbersome.

There is where I stepped in. Using a vendor ID and document number, I navigate to the payables transaction zoom window and essentially use my Quick Attach code to attach the file that you designate. Since this is all done via Sanscript/Continuum this all happens in milliseconds so looping this through 100+ invoices takes seconds.

I read information from the Clipboard so the information that needs to be copied from Excel or any Tab-delimited file is Vendor ID, Document Number, and the file path of the document.

GPBatchAttach(Template)

GPBatchAttach(PM)

Before the attach process starts, I verify that the document number provided exists for that vendor and that the file path provided is accessible.

GPBatchAttach(Form)

A confirmation window will appear displaying the number of successfully attached documents.

GPBatchAttach(Confirmation)

Note: There is a function I used in Continuum that I was not 100% of the last parameter. I have verified that it works on our installation of GP but as I said before, I have no ability to test on a different configuration.

Prerequisite: At least 2013 R2 must be installed as this uses the new Document Attach module not the old OLE notes version.

Installation: Copy the dll into the Add-ins folder in your Microsoft Dynamics GP installation directory

Use: Batch Attach currently only works with the Payables module. It can be accessed from the following windows from the Additional menu on the toolbar if installed properly:

  • Payables Transaction Inquiry – Vendor
  • Payables Transaction Inquiry – Document

Download: GPBatchAttach.dll (Version 1.0.5578.19243)

Sample template: Batch Attach Template.xlsx

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.

GP Quick Attach

Next up for release is my “Quick Attach” feature. This was simply driven by laziness as people disliked having to open up so many windows just to attach one document. This adds the attach feature to the window you are currently working in.

Prerequisites: This assumes you are on at least 2013 R2 and are using the new Document Attach feature rather than the old OLE notes version. The new version stores the actual files in the SQL database rather than references a shared folder.

Installation: Copy the dll into your Microsoft Dynamics Installation folder’s Add-in folder. Restart GP

Use: Quick attach can be accessed from the following windows using the “Additional” menu that should appear on the toolbar if properly installed.

  • GL Transaction Entry
  • GL Journal Entry Inquiry
  • Payables Transaction Entry
  • Payables Transaction Entry Zoom
  • Sales Entry
  • Sales Inquiry

QuickAttach

Download: GPQuickAttach.dll (Version 1.0.5578.18959)

The way I coded the add-in makes it extremely easy to add to a new window. For me, it just takes one line of code to add quick attach to a new window so if others want it on other windows, comment below and I’ll try to update it.

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.

GP Paste From Excel With Intercompany

As mentioned before, here is my add-in for Microsoft Dynamics GP. The version I use it with is 2013 R2 but I have no reason to believe that it would not work for older or newer versions of GP. While developing it, I made sure to make it conform to the same format as GP’s built-in Paste feature so the order of the columns is typically Description, Account, Debit and Credit. For Intercompany, the company ID is required and for Payables or Sales, the document type is needed. The dll reads the data stored in your Clipboard in the order shown in the examples so you just need to copy data from Excel or any Tab-delimited file.

Installation: Drop the dll into your Microsoft Dynamics installation folder’s Add-In folder and restart GP.

“Paste Entry” is accessible from the “Additional” toolbar at the top of the following windows:
– GL Transaction Entry
– Payables Transaction Entry (Distribution Window)
– Sales Transaction Entry (Distribution Window)

GPPaste(PM) GPPaste(RM) GPPaste(GL)

Features Supported:
– Intercompany
– Multicurrency (values are entered as Originating amounts)

Download: GPPaste.dll (Version 1.0.5578.19478)

Copy template: Paste GL Transaction Example.xlsx

GPPaste(Template)

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.