Tuesday, October 28, 2014

Webcast Presentation Slides: Dynamics GP Integrations: Options and Strategies

By Steve Endow

This morning I had a great webcast presentation session with MS Dynamics World, discussing Dynamics GP Integrations: Options and Strategies.


For those who are interested in getting a copy of the presentation slides, a PDF of the presentation can be downloaded from this OneDrive link:

http://1drv.ms/1FT1Ck1


I will be giving two presentations at the reIMAGINE 2014 conference in Fargo in November (one on SQL and one on .NET development), so if you are attending, please say hi!


Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter



Tuesday, October 14, 2014

Webcast: Microsoft Dynamics GP Integrations: Options and Strategies

By Steve Endow

On Tuesday, October 28, 2014 I will be giving a 1 hour presentation on Microsoft Dynamics GP Integrations: Options and Strategies, hosted by MS Dynamics World.

I'll present several options for importing data into Dynamics GP, and I will share some techniques for exporting Dynamics GP data for use by external systems. 

I will also discuss several strategies and techniques that I've learned over the last decade to help design better, more reliable integrations with Dynamics GP.

If you are planning a Dynamics GP integration, support Dynamics GP integrations, or just want to learn more about how to import and export data from Dynamics GP, please register for the webinar.

Hope to see you there!


Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter


Monday, September 29, 2014

The Triad of Fun- Forecaster, ExpressLink, and FRx

ExpressLink is the piece of Forecaster that allows you to import the chart of accounts from GP to Forecaster, and also transfer balances (e.g., actuals or existing budgets) from GP to Forecaster.  This is not the same as the Forecaster Data Transfer Tool, which moves data from Forecaster back to GP.

It has become a challenge to use ExpressLink because it relies on FRx to connect to the GP databases (yes, you read that correctly).  But the tool is so handy, because users can use to to automatically add new accounts from GP to their segment list in Forecaster (rather than having to keep track and handle it manually). 

Although not supported, we have found that we can install FRx on a 64 bit box (there are plenty of blog posts on this subject), and then you can set up the companies in FRX (which is required to use ExpressLink).  Once this is done, the ExpressLink option is available in the Forecaster installation wizard (if FRx is not installed and configured, the option to install ExpressLink will not be available).  But recently, I ran in to an issue where I installed ExpressLink but two funny things happened...

1.  Although I had marked the option to install ExpressLink, and it appeared to have completed successfully, if I went back in to the install wizard to change the installation it did not appear as marked/installed
2.  Within Forecaster, Tools-Import-ExpressLink was grayed out

Now, in this case, FRx was not installed initially.  So we had installed Forecaster, then FRx, and then gone back to install ExpressLink.

After checking everything, making sure FRx was functional, etc, I found that the issue was that ExpressLink wasn't actually installing- I did not see either ExpressLink DLL in the installation directory.  If I went in to Add/Remove Programs and tried to change or repair the install, I got the same results.  I believe this was because I had added Service Pack 5 to Forecaster.  So that was the only option showing in Control Panel, but because ExpressLink was not part of the original RTM install, it was not updating properly.

To resolve the issue, I uninstalled Forecaster.  Then I reinstalled the RTM with ExpressLink marked, and then reapplied the service pack.  Then Tools-Import-ExpressLink was available!

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a senior managing consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.

Thursday, September 25, 2014

Getting GP data for use in .NET integrations: C# technique to populate object properties from SQL query results

By Steve Endow

When I develop integrations, I sometimes have to retrieve a handful of data from a record in the Dynamics GP company database so that I can perform some other process.

Today, I had to create a routine that would automatically process credit card transactions.  Well, the API for the credit card software requires that I send in a bunch of customer and account info in order to process a transaction.  So I have to query a dozen or so fields from GP and then use those field values for the CC transaction.

To keep my code clean, I like to have one method to submit the transaction, and separate methods to retrieve any other data that is required.  So I might have a SubmitCCTransaction method that uses a GetGPCustomerInfo method to get data about the customer.

But if I am querying a dozen or more fields for the GP customer, I don't want to pass a dozen or more by ref parameters.  So I instead create a GPCustomer object that has a bunch of properties.  This is pretty standard.

Here is a simplified GPCustomer object with a dozen properties.


So I call GetGPCustomerInfo and send a GPCustomer object by reference.  GetGPCustomerInfo then retrieves data from GP, populates the GPCustomer properties, and sends the data back to my SubmitCCTransaction routine.

But I didn't want to write code to retrieve each field in the data table and assign it to each property of my GPCustomer object.  I wanted to essentially convert the row in my data table into a GPCustomer object.


Here's an example of how to do that.


using System.Data;
using System.Data.SqlClient;
using System.Reflection;

public bool GetGPCustomerInfo(string gpDatabase, string customerID, ref Models.GPCustomer gpCustomer, ref string message)
{
    try
    {
        //SQL query to get GP customer info
        //Field list and field names must match object properties
        string commandText = "SELECT  CUSTNMBR, CUSTNAME, CNTCPRSN, ADRSCODE, ADDRESS1, ";
        commandText += "ADDRESS2, ADDRESS3, CITY, STATE, ZIP, PHONE1, PHONE2 FROM RM00101 ";
        commandText += "WHERE CUSTNMBR = @CUSTNMBR";

        //Parameter for customer ID
        SqlParameter[] sqlParameters = new SqlParameter[1];
        sqlParameters[0] = new SqlParameter("@CUSTNMBR", System.Data.SqlDbType.VarChar, 15);
        sqlParameters[0].Value = customerID;
               
        DataTable dataTable = new DataTable();

        //Method to retrieve query results into data table
        int recordCount = DataAccess.ExecuteDataSet(ref dataTable, gpDatabase, CommandType.Text, commandText, sqlParameters);

        //We should only retrieve one row for the customer
        if (recordCount != 1)
        {
            message = recordCount + " records were retrieved for customer " + customerID + " in database " + gpDatabase;
            return false;
        }

        //Get the one row
        DataRow row = dataTable.Rows[0];
        PropertyInfo prop;

        //Iterate through the columns in the data row
        foreach (DataColumn column in row.Table.Columns)
        {
            //Assign the row field values to the appropriate customer object properties
            prop = gpCustomer.GetType().GetProperty(column.ColumnName);
            prop = typeof(Models.GPCustomer).GetProperty(column.ColumnName);
            prop.SetValue(gpCustomer, row[column.ColumnName]);
        }
               
        message = string.Empty;
        return true;
    }
    catch (Exception ex)
    {
        message = "An unexpected error occurred in GetGPCustomerInfo: " + ex.Message;
        return false;
    }

}


The key part is the foreach loop that iterates through the fields (data columns) and assigns the value to the matching object property.


        PropertyInfo prop;

        //Iterate through the columns in the data row
        foreach (DataColumn column in row.Table.Columns)
        {
            //Assign the row field values to the appropriate customer object properties
            prop = gpCustomer.GetType().GetProperty(column.ColumnName);
            prop = typeof(Models.GPCustomer).GetProperty(column.ColumnName);
            prop.SetValue(gpCustomer, row[column.ColumnName]);
        }
             

The only requirement is that your object properties must match the field list in your SQL query.  Your SQL query can contain a fewer number of fields than the number of object properties, but it cannot contain more.  And the object property names must match the field names that are queried.

Anyway, it took me a little bit of digging to find the specific syntax to do this, so I wanted to post it here for future reference.


UPDATE:  After some testing, I found that the code may require further refinement, depending on your SQL data source.  In my case, I was querying a table for a third party CC product.  That table has a field that is a GUID data type, and it also has fields that allow NULL.  Both of those conditions will cause errors with the above code.  Here is modified code to demonstrate one way to deal with those situations.

Before calling SetValue, I check the DataType of the column to see if it is a SQL GUID data type.  Then, I also have to check if the field value is NULL, in which case I assign am empty string.


if (column.DataType.ToString() == "System.Guid")
{
    prop.SetValue(account, row[column.ColumnName].ToString());
}
else
{
    if (row[column.ColumnName].GetType().ToString() == "System.DBNull")
    {
        prop.SetValue(account, string.Empty);
    }
    else
    {
        prop.SetValue(account, row[column.ColumnName]);
    }
           
}


Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter


Yikes! Missing Transaction Numbers!

Once every few months, I will get an email from a client or coworker concerned because they have gaps in their transaction numbers in GP.  When I say "transaction numbers" I mean the automatically generated unique numbers GP assigned to transactions- like bank transfer numbers, bank transaction numbers, journal entry numbers, payables voucher numbers, receivables invoice numbers, etc.

So, first things first, DO NOT PANIC.  I repeat, DO NOT PANIC.  As a side note, my daughter loves the children's book "Dragons Love Tacos".  If you have not checked it out (even if you don't have kids), you should.  But back to the subject at hand, gaps in transaction numbers assigned by GP are to be expected because they are assigned when transactions are entered.  Like in this example...

  • Sally starts a payables invoice, is assigned voucher #1
  • Mark starts a payables invoice, is assigned voucher #2
  • Tammy starts a payables invoice, is assigned voucher #3
Now, at this point, Mark deletes his invoice instead of posting it.  The numbering will still continue forward with #4 being the next number assigned.  So there will be a gap for missing #2.

So what to do about it?  To be able to judge if something has been removed from the system at the database level?  Well, don't use transaction numbers to do that.  Use the Audit Trail Code (ATC).  The ATC is assigned when a transaction post (transactions posted in batches are assigned the same audit trail code) and follows the transaction to the GL as the originating source.  Because the ATC is assigned at posting, the only way one would be missing is if it was deleted from the database itself.  And if a subledger ATC does not exist in the GL, you know that the batch was deleted from the GL and not posted (if you are posting to, not through).

So where do you find the ATC?  It appears on the posting journals for the subledger posting, as CMTRX000002 or PMTRX000056.  For a full list of Audit Trail Codes and their meaning, refer to Tools-Setup-Posting-Audit Trail Codes in Dynamics GP.


Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a senior managing consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.

Wednesday, September 24, 2014

Suppress Some Zero Lines But Not All in Management Reporter

Most folks are familiar with how to suppress all zero lines on a report in Management Reporter.


The setting is located on the Settings tab of the Report Definition.  If "Display Rows with No Amounts" is marked, zero rows will show on the report.  But if the option is unmarked, then all zero lines will be suppressed on the report.  This is useful to avoid extraneous lines appearing on reports (for example, if you use a generic row format with a department tree, some accounts may apply to certain departments only- so this option will hide the irrelevant lines on the other departments).

But, sometimes, you want to suppress some zero lines but have other lines appear even if they are zero (for example, accounts that generally only have activity in the later part of the year, and you want to communicate that the balance is zero at this time).  To select this setting by line, you would first want to make sure that "Display Rows with No Amounts" is MARKED.  Then you will specify a print control in the row format.

 
 
In the row format, click to open the Print Control window.  On the lines that you WANT to suppress if zero, you would select the "Suppress Row if All Zeroes" option.  On the lines that you want to appear, even if they are zero, you would simply leave the Print Control blank.
 

Although this may be more work up front, it allows you to tailor the suppression of zeroes to your specific needs.


Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a senior managing consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.
 


Oddity in GP 2013 Home Page

I thought I would post this out in to the blogosphere to see if anyone has been experiencing this issue, and possibly crowdsource the cause.  As I understand from Microsoft, the issue has been reported but they have been unable to reproduce the issue.  So here it is...

1.  Log in to GP
2.  Click Customize this Page on the Home Page
3.  Make changes, for example unmark or mark additional sections
4.  Save changes
5.  Changes appear

Then, at some point in the future, the user logs back in (it may be 3 times from now, or 20) and the Home Page modifications they have made are gone.  And if they try to make the changes again, the system does not save or apply them (despite the lack of an error message).  The only option at this point is to reset the user's home page role (thereby clearing the customizations and resetting the home page to the defaults for their role).  And then it functions correctly for a while once again.

There is a suspicion that it is related to a third party product, but since it doesn't recur predictably and it is not practical to disable all third party products for an indefinite period of time, there is no easy way to confirm this.  In this client's case, they have the following third parties/other products installed...
  • Binary Stream- Property Management
  • Mekorma- MICR
  • Kwiktag- GP Add In
  • Field Service
  • Support Debugger Tool
Anyone else out there have this issue? If so, what third parties/other products do you have installed?

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a senior managing consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.