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.

Friday, September 12, 2014

Bug in GP 2013 VS Tools Window Positioning

By Steve Endow

This week I finished up a custom Dynamics GP 2013 eConnect integration that I developed as a Visual Studio Tools AddIn.  The customer wanted to have a GP menu item that would open the integration window, so I used the Menus for Visual Studio Tools library to add a custom menu item.

The integration worked great and I packaged it up for deployment at the client.

Except as I was doing some final testing and double checking, I noticed a strange behavior with the window.  Sometimes when I opened the custom VST window, it would open "CenterScreen", which is what I wanted.  But other times, it would open in the upper left corner of my screen.

I tested it a few more times, and it seemed like the first time it opened after launching GP, it would open centered, but then subsequently it would open in the upper left corner of my screen.  I tried various settings to try and position the window in the center of the screen, like manually setting the window Location property.  But after several more tests, it would ALWAYS open in the upper left corner.  It was ignoring my manual Location setting in the code.

It drives me nuts to have windows open in random positions, and I can't stand windows that open in the upper left corner for no reason.

Baffled, I created a brand new VS Tools project with a test window.  When I opened that window from GP, it worked fine.  It was always centered.  What was going on??

I then compared every single window property in my integration project against the test VST project.  All of the window properties were the same.  Yet they behaved differently.

And then I finally noticed the difference.  In my test project, where the window would dutifully open CenterScreen every time, I saw that the icon in the upper left corner of the window was different.  It didn't have the GP window icon.  This meant that it was a standard Windows Form and not a  Dynamics GP VST Form.  Hmmm.

I then added a second window to the test project--this time a Dynamics GP Form.  I set all of the window properties exactly the same as the Windows Form.  I then launched the windows.  Sure enough, the Dynamics GP Form opened in the upper left corner of my screen, while the Windows Form opened Center Screen.



This screen shot shows how the standard Windows Form opens CenterScreen, while the Dynamics GP Form opens in the upper left corner of the screen.

And if that isn't the cutest photo ever featured in a Dynamics GP blog post, I don't know what is...

It seems that once you modify the window Startup Position or Location settings on the Dynamics GP Form, it triggers some bug that prevents the window from positioning properly, ignoring the window properties.  I tried changing the settings to different values, but none would reliably reproduce the CenterScreen equivalent.

So after dozens of tests, here is what I have found.

If you have StartPosition = CenterScreen, and if the Location value is 0, 0, the VS Tools window will always open in the upper left corner.  It may position properly the first few times, but if you change the StartPosition setting or Location setting, it seems to consistently revert to the upper left.

If you add code to set the form Location property during the OnLoad event, the window will open center screen the first time, but then subsequent opens will reposition it to the upper left.

But, if you then change the Location to some value other than zero, such as 100, 100, then setting the Location property will work every time.

However, there appears to be one additional catch.  Based on dozens of more tests, setting the Location only works if it is the first line in your Form Load event.  If you have any other code that refreshes the form, the Location setting will be ignored the second and subsequent time the window is opened.

So to summarize:

1. Set the form Location property to a value other than 0, 0, such as 100, 100

2. Add this as the very first line in your Form Load event:

this.Location = new Point((Screen.PrimaryScreen.Bounds.Size.Width / 2) - (this.Size.Width / 2), (Screen.PrimaryScreen.Bounds.Size.Height / 2) - (this.Size.Height / 2));


After hours of testing, I believe that these two techniques will allow you to work around the VS Tools window positioning bug.  And after another test, it looks like changing the Location property may not be necessary if the Location setting is the first line in Form Load.

I don't recall ever having this issue before, so I'm guessing it was introduced in GP 2013 or GP 2013 R2.  Given how subtle it is, I don't have high hopes that it will be fixed any time soon.


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





Wednesday, September 10, 2014

Importing Dynamics GP Vendors with Temporary Vendor IDs

By Steve Endow

In the last 10 years, I've only had one situation where a client considered temporary vendor IDs.  I remember looking into them, but don't know that the client ever used them.

Last week I was asked to develop an eConnect integration that would import AP Invoices, and every invoice would be issued to a temporary vendor ID.  So before each invoice was imported, I had to create a new vendor record with a temporary ID.

Dynamics GP has the slightly odd, or perhaps anachronistic, feature that allows you to delete temporary vendor records while retaining any transactions related to that temporary vendor.

My initial guess is that the feature may have been motivated by database size concerns of ye olde days.  Today, vendor records are usually a trivial concern relative to database size and server storage capacity, so that isn't a terribly compelling reason to use temporary vendors.

I say the feature is odd primarily because there is no corresponding Temporary Customer.  Why not?  I know of quite a few businesses that have thousands and thousands of one time customers, but relatively few one time vendors.

But in this client's situation, each vendor will always be paid once, for a small amount, and the client has indicated that it is highly unlikely that any of these particular vendors will ever be paid again--if so, it would be very infrequent.  In this case, the vendors are "one time" vendors, so it makes sense to utilize the Temporary vendor feature.

So what makes importing temporary vendors different than normal vendors?  As the title of this post hints, it has to do with temporary vendor IDs.  In the Payables Setup Options window, there is a "Next Temp. Vendor ID" value, that allows you to specify a vendor ID series just for temp vendors.


 This number is utilized when you press CTRL + T on the vendor ID field of the Payables Transaction Entry window, and causes GP to automatically populate the temp vendor ID into the Transaction Entry window and the Vendor Maintenance window.


So that's neat and all, but how would you import temporary vendors?

Well, just like normal vendors, except that you have to grab the next temporary vendor ID.  Which, of course, is something eConnect does not support.  And as far as I can tell, Dynamics GP gets the next vendor ID using code, and not a stored procedure.

So, I had to roll my own.


Below are my C# routines to get the next temporary vendor ID.  A few small details make it tricky.  Once you retrieve the next temp ID, I check to make sure that the ID hasn't been used already.  There is no logic in GP that prevents a Temp ID from being manually entered, and it is possible for the next temp ID to be changed.

Next, you have to increment the ID and store the new "next" value back to the database.  Because the temp vendor ID is an alphanumeric string, you need to retain the prefix and increment the numeric suffix.  And I couldn't assume that the vendor's Temp ID sequence prefix would be the same as my test environment.  While not rocket science, it was a bit tricky to find an elegant way to split the string, increment the numeric suffix, and then re-join the string.  I eventually found a forum post with a nice Regex implementation which worked quite well.



internal string GetNextTempVendorID()
{
    try
    {
        string records = string.Empty;
        int tries = 0;
        bool success = false;

        int digitStartIndex = 0;
        string alpha = string.Empty;
        string digits = string.Empty;
        int digitLength = 0;
        int tempVendorNumber = 0;

        Match regexMatch;

        string sqlCommand = "SELECT RTRIM(NXTVNDID) AS NextVendorID FROM PM40100 WITH (TABLOCKX HOLDLOCK)";
        string tempVendorID = DataAccess.ExecuteScalar(CommandType.Text, sqlCommand, null);

        bool vendorExists = VendorIDExists(tempVendorID);

        while (vendorExists && tries < 50)
        {
            tries++;

            regexMatch = Regex.Match(tempVendorID, "[0-9]");
            if (regexMatch.Success)
            {
                digitStartIndex = regexMatch.Index;
                alpha = tempVendorID.Substring(0, digitStartIndex);
                digits = tempVendorID.Substring(digitStartIndex);
                digitLength = digits.Length;
                tempVendorNumber = Convert.ToInt32(digits);

                tempVendorID = alpha + (tempVendorNumber + 1).ToString().PadLeft(digitLength, '0');

                vendorExists = VendorIDExists(tempVendorID);
            }
        }

        vendorExists = VendorIDExists(tempVendorID);
        if (vendorExists)
        {
            Log.Write("Failed to get next temp vendor ID");
            return string.Empty;
        }
        else
        {
            success = UpdateNextTempVendorID(tempVendorID);
            if (success)
            {
                return tempVendorID.Trim();
            }
            else
            {
                Log.Write("Failed to UpdateNextTempVendorID");
                return string.Empty;
            }
        }

    }
    catch (Exception ex)
    {
        Log.Write("An unexpected error occurred in GetNextTempVendorID: " + ex.Message);
        return string.Empty;
    }

}





private bool VendorIDExists(string vendorID)
{
    try
    {
        string sqlCommand = "SELECT COUNT(*) AS Records FROM PM00200 WITH (NOLOCK) WHERE VENDORID = @VENDORID";
        SqlParameter[] sqlParameters = new SqlParameter[1];
        sqlParameters[0] = new SqlParameter("@VENDORID", System.Data.SqlDbType.VarChar, 15);
        sqlParameters[0].Value = vendorID.Trim();

        string records = DataAccess.ExecuteScalar(CommandType.Text, sqlCommand, sqlParameters);
        int vendorCount = Convert.ToInt32(records);

        if (vendorCount > 0)
        {
            return true;
        }
        else
        {
            return false;
        }
    }
    catch (Exception ex)
    {
        Log.Write("An unexpected error occurred in VendorIDExists: " + ex.Message);
        return false;
    }
}




private bool UpdateNextTempVendorID(string lastTempVendorID)
{
    try
    {
        int digitStartIndex = 0;
        string alpha = string.Empty;
        string digits = string.Empty;
        int digitLength = 0;
        int tempVendorNumber = 0;
        string nextTempVendorID = string.Empty;

        int recordCount = 0;

        Match regexMatch = Regex.Match(lastTempVendorID, "[0-9]");
        if (regexMatch.Success)
        {
            digitStartIndex = regexMatch.Index;
            alpha = lastTempVendorID.Substring(0, digitStartIndex);
            digits = lastTempVendorID.Substring(digitStartIndex);
            digitLength = digits.Length;
            tempVendorNumber = Convert.ToInt32(digits);

            nextTempVendorID = alpha + (tempVendorNumber + 1).ToString().PadLeft(digitLength, '0');

            string sqlCommand = "UPDATE PM40100 WITH (TABLOCKX HOLDLOCK) SET NXTVNDID = @NXTVNDID";
            SqlParameter[] sqlParameters = new SqlParameter[1];
            sqlParameters[0] = new SqlParameter("@NXTVNDID", System.Data.SqlDbType.VarChar, 15);
            sqlParameters[0].Value = nextTempVendorID.Trim();

            recordCount = DataAccess.ExecuteNonQuery(CommandType.Text, sqlCommand, sqlParameters);

        }

        if (recordCount == 1)
        {
            return true;
        }
        else
        {
            return false;
        }
    }
    catch (Exception ex)
    {
        Log.Write("An unexpected error occurred in UpdateNextTempVendorID: " + ex.Message);
        return false;
    }
}


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






Setting distributions for a Integration Manager single file SOP Invoice import using VB Script

By Steve Endow

Let's say you want to import some SOP Invoices into Dynamics GP using Integration Manager.  And let's say that you have a single data file that contains the invoice lines.

Setting up a standard integration with separate Header and Line data sources is fairly straightforward.

But then you realize that you need to set the distributions for each invoice.  You need to assign different AR accounts and different Sales accounts to the invoices.  The customer does not have any default accounts for their GP customers, and you need to setup 3 different integrations, each of which will post to a different AR and sales account.  Fortunately, all invoices for each integration will be assigned to the same AR and sales account--so that was some consolation.

I'm actually a little rusty with this type of IM challenge.  Maybe there is a better way to accomplish this, but the last time I had to do this, about 9 or 10  years ago, I wrote a Before Integration VB Script that read the invoice data file and generated a Distribution file.

Not wanting to dig through 10 year old client files to find my ancient script, and not really wanting to have to add a Distribution data source to this integration, I thought of a hack.

What if we were able to use the customer default accounts so that IM would use those accounts for the invoice distributions?  That would allow us to avoid touching the distributions in the IM integration.  How would we do that?

Well, what if we had a Before Document script that set the default AR and Sales distributions for the customer?  We know which AR and Sales account should be used for each integration, and all invoices in that integration will use the same accounts, so we should be able to set the default account for each customer right before each invoice is imported.

But after we set the default AR and Sales account for the customer, and after the invoice is imported, then that customer has default accounts that we actually don't want as the defaults.  So why not create a similar After Document script to clear the default accounts?  Since this customer does not have any default customer accounts, we just wipe out the AR and Sales accounts for the customer.


So here is the script that I came up with.  I pulled it together and handed it off to a partner to test, so there might be a few bugs or rough edges, but you should get the drift.

Here is the Before Document script, which sets the default account for the customer.  You would change the account numbers and the source field for the Customer ID.

If your customer records have default accounts that you want to preserve, you could query those, store them in integration variables, and then set them back in the After Document script rather than clearing them.


UPDATE & CORRECTION:  So after some testing, the partner found that the default Customer Sales Account is not used for SOP--only for RM.  This makes sense, but was annoying to discover.  So instead of setting the sales account at the customer level, it can be set at the system level.  I've modified the scripts to reflect the update to SY01100 for the sales account.


'9/10/2014
'IM Before Document Script to set AR and Sales accounts for customer prior to IM invoice import

strIntercoID = GPConnection.GPConnInterCompanyID

'Get a connection object.
Set objConn = CreateObject("ADODB.Connection")
objConn.ConnectionString = "database=" & strIntercoID
GPConnection.Open(objConn)

'Create an ADO command object.
Set objCmd= CreateObject("ADODB.Command")
objConn.CursorLocation = 3

'set the database to the currently logged in db.
objConn.DefaultDatabase = strIntercoID
objCmd.ActiveConnection = objConn

objCmd.CommandType = 1

strARAccount = "000-1200-00"
strSalesAccount = "000-4100-00"

'Read customer ID
strCustomerID = Trim(SourceFields("InvoiceData.Customer ID"))

strSQL1 = "UPDATE RM00101 SET RMARACC = (SELECT ACTINDX FROM GL00105 WHERE ACTNUMST = '" & strARAccount & "') WHERE CUSTNMBR = '" & strCustomerID & "'"

strSQL2 = "UPDATE SY01100 SET ACTINDX = (SELECT ACTINDX FROM GL00105 WHERE ACTNUMST = '" & strSalesAccount & "') WHERE SERIES = 5 AND SEQNUMBR = 400"

'Set and execute the queries
objCmd.CommandType = 1 '1 = Text

objCmd.CommandText = strSQL1
objCmd.Execute

objCmd.CommandText = strSQL2
objCmd.Execute



And here is the After Document script which clears the accounts for the customer.


'9/10/2014
'IM Aftwer Document Script to clear AR and Sales accounts for customer after IM invoice import

strIntercoID = GPConnection.GPConnInterCompanyID

'Get a connection object.
Set objConn = CreateObject("ADODB.Connection")
objConn.ConnectionString = "database=" & strIntercoID
GPConnection.Open(objConn)

'Create an ADO command object.
Set objCmd= CreateObject("ADODB.Command")
objConn.CursorLocation = 3

'set the database to the currently logged in db.
objConn.DefaultDatabase = strIntercoID
objCmd.ActiveConnection = objConn

objCmd.CommandType = 1

'Read customer ID
strCustomerID = Trim(SourceFields("InvoiceData.Customer ID"))

strSQL1 = "UPDATE RM00101 SET RMARACC = 0 WHERE CUSTNMBR = '" & strCustomerID & "'"

strSQL2 = "UPDATE SY01100 SET ACTINDX = 0 WHERE SERIES = 5 AND SEQNUMBR = 400"

'Set and execute the queries
objCmd.CommandType = 1 '1 = Text

objCmd.CommandText = strSQL1
objCmd.Execute

objCmd.CommandText = strSQL2
objCmd.Execute



The partner is going to test it to confirm that it works for the customer, but if it does the trick, I think it's a pretty clever workaround to bothering with distributions for an Integration Manager import.


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 8, 2014

Dynamics GP BCHSOURC values

By Steve Endow

I was recently asked if there was a list of values and descriptions for the BCHSOURC field in GP.  I didn't find one during a quick search, so here is a list from the core GP modules.


BCHSOURC SERIES Description
ASMENT 5 Assembly Entry
GL_Clearing 2 Clearing Entry
GL_Normal 2 General Entry
Invoice Entry 3 Invoice Entry
IV_Trans 5 Transfer Entry
IV_Trxent 5 Transaction Entry
PM_Payment 4 Payment Entry
PM_Trxent 4 Payables Trx Entry
Rcvg Trx Entry 4 Receivings Trx Entry
Rcvg Trx Ivc 4 Purchasing Invoice Entry
RM_Cash 3 Receivables Cash Receipts
RM_Sales 3 Receivables Sales Entry
Sales Entry 3 Sales Transaction Entry


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



Friday, September 5, 2014

The kinder, gentler BSOD

By Steve Endow

I just finished testing a new release of a Dynamics GP integration.  I was in the middle of updating my documentation, working in Word, something I've done for a few years on my desktop computer.

After typing a word, blam-o, both monitors go black and then one turns blue.


Lovely.

This is the first time I've seen a BSOD on Windows 8 / 8.1.  While less obnoxious than the old Windows NT-style blue screens, it is no more comforting.

My hardware hasn't changed, and I haven't done anything crazy with new software, so hopefully this isn't a sign of my desktop jumping off a cliff like my file server did a few weeks ago.

Thankfully Word recovered my document, so I didn't lose any work.  And Windows 8.1 with an SSD boots up lightning fast, so I was back up and working quickly, but a BSOD is not what I wanted to see today.

Is it possible to actually get work done without interruption?


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


Thursday, September 4, 2014

RUN FOR THE HILLS! Christina is trying to write code!

Well, not really.   But I did borrow the following post from the venerable David Musgrave, in order to set a trigger to default the project accounting Change Order Entry window to make changes to the Forecast budget (rather than the default of Baseline budget).

http://dynamicsgphelp.com/2011/05/setting-default-values-in-gp-windows/

So, if you are familiar with Project Accounting, when you access Cards-Project-Change Order Entry it defaults to Track Changes To: Baseline.  Well, this is a bummer since most folks want to track changes to Forecast and preserve the Baseline as the original budget.  So user error can cause the change orders to be applied to the wrong budget.

So to set the field to default to Forecast instead, I used the Support Debugger option in David's awesome post, and used the following script...

out boolean OUT_Condition;
OUT_Condition = false;
if isopen(form PA_Change_Order_Entry) then
 OUT_Condition = true;
'PA GB Track Changes' of window 'PA_Change_Order_Entry' of form
'PA_Change_Order_Entry' = 1;
end if;

Pretty cool if you ask me!

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.

Dealing with 1/1/1900 with GP and SQL Reports

If you have created reports with SQL Reporting Services for GP, you have inevitably encountered 1/1/1900 showing up on your reports.  Maybe as the inactive date for an employee?  Or the due date on a  payables transaction?

In some cases, you may not mind that the dates display that way.  However, eventually, a client will ask...why do those dates show that way?  Can you hide them if they are blank?  Well, sadly, they aren't actually blank so it's not that easy.  Right?
But is actually pretty easy using an expression for the field, like this...

=IIF(Fields!THISISYOURDATE.value="1/1/1900 12:00:00AM","",Fields!THISISYOURDATE.Value)

So, in this example when my field THISISYOURDATE is 1/1/1900 the field will display as blank, otherwise it will show the date.

Easy easy :)

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.