Management Reporter Migration and Row Linking

It has been very well publicized that MR does not yet have row linking capability.  In FRx, you could design a report tree and have each branch of the tree use a different row format.  In MR, you can link to different GL links within a row format but not to different row formats.  Recently, though, I learned of a quirk with the migration related to row linking that I did not expect.

But first, a couple of good blog posts from the Dynamics Corporate Performance Management Team regarding FRx migration to Management Reporter and row linking.

http://blogs.msdn.com/b/dynamicscpm/archive/2012/05/23/migrating-to-management-reporter-2012.aspx

http://blogs.msdn.com/b/dynamicscpm/archive/2011/08/09/management-reporter-advanced-reporting-scenarios-and-features.aspx

What I was surprised to learn is that when you have row linking on a tree in FRx, it impacts the migration in more ways than one.  Not only does it remove the row linking from the tree (which would be expected), it also does not migrate the GL linking in all of the row formats there were linked in the tree.  For example...

The tree has a summary and three branches:

• Summary
• Dept 1
• Dept 2
• Dept 3

In FRx, the three departments are linked to three different row formats as follows:

• Summary: Row format A
• Dept 1: Row format A
• Dept 2: Row format B
• Dept 3: Row format C

When the row formats are migrated, only Row format A would be migrated intact.  Row formats B and C would drop their GL links (when you open the row, there would only be descriptions) so that the row formats would be have to be rebuilt with the correct GL links.

And it is not enough to simply remove the row linking in the tree in FRx prior to migration, as unassociated rows will not migrate.  So you would have to associate the rows with another catalog for them to migrate properly.  A little prep work to consider if you use row linking in FRx.  Then you can address the report design (the blog post above on advanced reporting has some ideas) to achieve similar results to row linking.

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising 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.

Save Operation on UPR_WORK_State_Tax

When building a payroll (Transactions-Payroll-Build Checks) on GP2010, a client encountered the error "A save operation on table 'UPR_WORK_State_Tax' has created a duplicate key".  We isolated the error to benefit transactions for three specific employees and then determined what they had in common-- they were all being paid wages that were being taxed in a state other than their default state.  So, here is the scenario...

1. Employee has default state tax code defined, Cards-Payroll-Tax Information (in this example, MO)
2. Employee has pay code transactions entered (Transactions-Payroll-Transaction Entry) with state tax codes for another state only (in this example, IL)
3. Employee has benefit transactions entered (Transactions-Payroll-Transaction Entry) that are state taxable (Cards-Payroll-Benefits)

Now, in the example above, if the employee also had pay code transactions with the state tax code of MO then there would be no error.

Also, if you change the employee's default state tax code (Cards-Payroll-Tax Information) to IL, then there would be no error either.

I know this is not the most common scenario since most employees have wages that are only taxed in a single state. But I work with several organizations who need to tax specific wages in specific states for employees. I will update you all when I get more information from Microsoft on additional workarounds and/or a fix.

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising 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.

The Power of the Microsoft Dynamics Ecosystem

The following question was posted to Experts Exchange recently:

We currently use Solidworks for CAD modelling.  Initial BOMS are created in Solidworks, and we would like to be able to import a BOM from Solidworks into Dynamics GP.

Hmmm.  That's a good one.  I checked the eConnect documentation, but BOMs are not a supported import.  You could reverse engineer the tables and try and import the data, but that isn't the most desirable solution--and that's just the GP side of things.  Since I'm not familiar at all with CAD software or Solidworks, it didn't occur to me to look for a third party add-on that might import such data into GP.

Well, the author of the question did some research, and believe it or not, there is a solution called Agni Link designed to provide a real-time, bidirectional integration between three major CAD software solutions and all four of the Microsoft Dynamics ERP solutions.

This is an example of the power of the Microsoft Dynamics "ecosystem".

First, products like Dynamics GP have been around for a long, long time, starting with its many years as Great Plains.  Second, the Dynamics ERP products are used by tens of thousands of customers, providing a large installed based that makes it economically viable to develop relatively niche products like a CAD to Dynamics integration.  Third, Dynamics ERP products have development tools and APIs that facilitate the development of third party solutions.

And last, because they all run on common Microsoft technologies like Windows and SQL Server, both of which are ubiquitous in the business world, third party solutions have the option of integrating with multiple Dynamics ERP products, providing a larger customer base for those ISV solutions.

I thought this was a great example of how all of those things come together to help customers use Dynamics GP to integrate their business systems.

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

http://www.precipioservices.com

Calculating Number of Days Past Due in SmartList Builder

Do you want to be reminded when a payable becomes past due by a certain number of days?  This can be difficult because it needs to be based on a calculation using the system date and due date of the invoice.  Here is a way to create a calculated field using SmartList Builder to do it.

First, I created a basic SmartList in SmartList Builder using the PM Transaction Open table.

Then I clicked on Calculations, and created a new calculation.

This calculation uses two functions (if you do not see the Functions, click on the white triangle next to the Fields header on the right hand side of the window and change the view to Functions): DATEDIFF to calculate the difference between two dates in days, and GETDATE to pull the system date in to the calculation.  So, in this example, it will calculate the difference (in days) between the due date and the system date.

After you add a calculate field to a SmartList, make sure you mark the Default checkbox for the field if you want it to display by default in the SmartList.

The resulting SmartList has a field called Days Past Due that calculates exactly that!  Then, if desired, you could set search criteria for the SmartList to display only those records where Days Past Due > 90.  Then when you save the SmartList favorite, you can choose to Add a Reminder based on the SmartList as well.  More information on using SmartLists as the basis for custom reminders in GP in a future blog post :)

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising 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.

Management Reporter Drilldown Anomaly

Sometimes issues make sense even when you wish something worked differently. I came across a case like this in Management Reporter last week.  A client has a variety of reports for specific users, that contain the main accounts that each particular user is interested in. 

For example, for the user Bob Smith, the row format includes the range of  accounts from 4100 to 4800 as the following screenshot shows.

When the report is generated, and you drilldown on the row, you see all of the accounts in the range as expected.

But, let's say that you want to exclude the account 000-4510-01 because the user should not be able to see that account, Cost of Goods Sold.  So, you return to the row format, and update it accordingly:

What is key in this example is that we subtracted out the account we wanted to exclude, rather than modify the range.  When you subtract in this manner, the result is not what you might expect when you generate the report:



The account still appears on the drilldown report, although no amounts are displayed.  I understand. logically, why it is working this way-- as the account is included in the range and then subtracted. In the client's case, however, they would prefer that the account not appear at all.  And you will want to keep this in mind when designing reports and subtracting multiple dimensions.  As a workaround, you can avoid using the subtraction functionality and using smaller ranges instead in your row format.

On a side note, there was an earlier known issue where in this case the totals on the drilldown report would be incorrect as well.  But this has been resolved in the latest version of MR 2012.

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising 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.

Sending LinkedIn invites to new connections

Every once in a while, I receive a LinkedIn invitation from someone I don't know, have never met, and have never e-mailed or spoken with. 

The LinkedIn invitation email message simply says:

"Matt Groening wants to connect with you on LinkedIn"

"Aziz Ansari wants to connect with you on LinkedIn"

There is no note, no introduction, no explanation as to why they have sent me the invite.  Who is this person that I've never met and why would I want to accept his invitation?

Don't get me wrong--I don't mind receiving invites from people I don't know and have never met--in fact I appreciate them and think they are part of the value of LinkedIn and online networking.

But what I don't like is the complete lack of introduction by the person sending the invite to me.  It would be like attending a business networking "mixer" event, walking up to someone, handing them your business card, and then walking away, all without even saying "Hi".  You would never do that in person, so why do that online?

Sometimes I take the time to click on the LinkedIn profile and look up the sender.  Most of the time it appears that they work with Dynamics GP in some fashion.  But without any explanation, for all I know, they may have just sent random invites out to random people recommended to them by LinkedIn.

I am not a heavy LinkedIn user, but when I do send LinkedIn invitations, I always delete the default message of "I'd like to add you to my network" and then write a brief note introducing myself or mentioning why I would like to connect with the person.

Maybe I want to connect with someone after reading their blog.  "Hi Ted, I just read your blog post about how Dynamics GP is going to conquer the world and really enjoyed it.  I look forward to more articles!"

More often, I send an invite to people after I've spoken with them on the phone for the first time.  "Hi Sally, it was great to speak with you this morning about your client that is looking for assistance with their Dynamics GP integrations.  I look forward to speaking with you when they are ready to move forward."

Or even simpler.  "Hi, I see that you are a member of the Dynamics GP group.  I'm connecting with other GP users and consultants." 

Any reason is fine with me, as long as there is some reason and you are willing to take 30 seconds to type something. 

LinkedIn can be a valuable resource and the connections you can make can be valuable as well.  So invest a few seconds to at least write a brief note if you are going to send someone an invitation to connect.

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

http://www.precipioservices.com

Calculating the number of fiscal periods between two dates

I developed a Purchasing Invoice integration for a client that has been running well for over a year.  Recently, the client requested some help automatically handling invoice posting dates during month end close.

The client typically kept their period open for 3 extra days while they gathered invoices related the prior period, but any invoices that arrived after those 3 days should go into the current period.  So if a 9/28 invoice arrived on 10/2, it would post to 9/28.  But if a 9/25 invoice arrived on 10/4, it would post to 10/1.

This was a challenge for them with the Purchasing Invoice integration because the vendor invoice data files would often contain invoices with a mix of dates.  A file received on 10/8 could have invoice dates from 9/15 to 10/7.  Sometimes a data file would contain an invoice dated over one month in the past.

And this client uses 4-4-5 fiscal periods, so I couldn't just use calendar months for the periods--I would need to query the GP fiscal period tables to determine the start and end dates for every period.

Once I knew the fiscal period dates, I had to create logic to check the invoice date, compare it to the current period dates, and determine whether the invoice should post to the current period or the prior period.

The first step was to query the GP fiscal period tables.  Whenever I think of "GP" and "query", I think of Victoria Yudin, the Dynamics GP MVP and reporting guru, who has a few billion queries posted on her blog.  Sure enough, a quick Google search of "dynamics gp fiscal period query" sent me right to her blog. (Thank you Victoria!)

http://victoriayudin.com/2010/11/24/sql-queries-for-fiscal-years-and-periods-in-dynamics-gp/

I borrowed Victoria's second query and adjusted it to only return info about the current fiscal period.

SELECT D.PERIODID PeriodNumber, D.PERNAME PeriodName, D.PERIODDT StartingDate, D.PERDENDT EndingDate, D.YEAR1 FiscalYear
FROM SY40100 D INNER JOIN SY40101 H ON H.YEAR1 = D.YEAR1
WHERE D.FORIGIN = 1 AND D.PERIODID <> 0
   and GETDATE() between H.FSTFSCDY and H.LSTFSCDY
   and GETDATE() between D.PERIODDT and D.PERDENDT
ORDER BY D.PERIODID

Now that I had the current fiscal period start and end dates, I could calculate if the period "cutoff window" (3 days) was still open for the prior period, and whether an invoice should post in the current or prior period.  That worked out well for invoices dated in the current period or the immediate prior period.

But what about an invoice dated 8/1 that arrives on 10/10?  I needed a way to determine how many periods "old" the invoice date is for these older invoices.  Since the client uses fiscal periods and not calendar periods, I can't count the number of months difference between the current date and the invoice date.

I created another version of Victoria's fiscal period query to get the period info for the invoice date.  My .NET code substitutes in the invoice date in the WHERE clause.

SELECT D.PERIODID PeriodNumber, D.PERNAME PeriodName, D.PERIODDT StartingDate, D.PERDENDT EndingDate, D.YEAR1 FiscalYear
FROM SY40100 D INNER JOIN SY40101 H ON H.YEAR1 = D.YEAR1
WHERE D.FORIGIN = 1 AND D.PERIODID <> 0
   and '07/15/2012' between H.FSTFSCDY and H.LSTFSCDY
   and '07/15/2012' between D.PERIODDT and D.PERDENDT
ORDER BY D.PERIODID

In this example, the 7/15/2012 date would return fiscal period 7.  If the current date is 10/10/2012, then I know that 10 - 7 = 3, so this particular invoice cannot post to the immediate prior period, and must post to the current period (October).

But that 10 - 7 calculation only works for periods in the same year.  What if it is January 2013 and the invoice is dated December 2012?  I can't use 12 - 1 = 11, as that is incorrect.

The trick here is to multiply the fiscal years by 12, then add the period numbers, then find the difference between those two values.  So using the example above:

(Current Year * 12 + Current Period) - (Invoice Year * 12 + Invoice Period)

(2013 * 12 + 1) - (2012 * 12 + 12) = 1

24157 - 24156 = 1

And if an old invoice dated May 2012 arrives in January 2013:

(2013 * 12 + 1) - (2012 * 12 + 5) = 8

24157 - 24149 = 8

And that is how you calculate the number of fiscal periods between two dates.

The last minor challenge I faced was that the eConnect taPopEnterMatchInvHdr object does not have a property for GL posting date--it only allows you to import the invoice date.  This is not uncommon for eConnect transaction headers, but it is annoying.  So, after you import each document, you have to directly update the transaction GL posting date yourself.

In my case, I updated the POP10300 table.

UPDATE POP10300 SET GLPOSTDT = @GLPOSTDT WHERE POPTYPE = 2 AND POPRCTNM = @POPRCTNM AND VENDORID = @VENDORID AND VNDDOCNM = @VNDDOCNM AND receiptdate = @INVOICEDATE


And then I was finally done.


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

http://www.precipioservices.com

The most baffling Dynamics GP issue I've ever seen

I spoke with a customer today who discovered an extremely unusual problem with one of their Dynamics GP batches.  I think it rises to the top of my list to be the most baffling problem I've ever seen.

The client has two company databases--one is for a US company, and the other is for a Canadian company.  While reviewing some of their posted transactions, they found that a batch from the Canadian company had "posted" to their US company.

You read that right.  A batch from Company A ended up posting to Company B.  And this isn't an intercompany transaction.

What does that even look like?

This is a screen shot from the US Company.  Notice that the customer Name field is blank and the Currency ID is CAD.

The customer number 131654 doesn't even exist in the US company, and the US company doesn't process Canadian Dollar transactions.

In the Distribution window, there are no distributions.

And like the transaction window, the customer Name is blank.

Making it more fun is that the distributions posted to the GL in the Canadian company!

So the transaction posted to the subledger in one company, and the GL was affected in another company.

This is the case for all transactions (dozens) in a single batch.

Anyone who understands how GP is designed, how it works, how it posts transactions, and how the underlying company databases are setup would, I believe, say that such a situation is impossible.

I would have previously agreed, but now that I've seen the screen shots and stopped stuttering in disbelief, I can't dispute that it happened; however, I also can't begin to rationally explain how it could have possibly happened.

Like many puzzling situations, there are a few clues, although none directly explains what happened.

First, this batch was imported into GP--it was not manually entered.  So perhaps, maybe, somehow, something in the data that was imported could have resulted in some highly unlikely posting fluke.  But, I'm told the batch was imported using eConnect.  eConnect is pretty rigorous with its validation, so it's unlikely that any transaction data elements could have possibly caused this.  And even if we say that something was odd about this particular imported batch, I can't imagine any scenario that could possibly cause a batch to be posted to two different companies simultaneously.  Imported into the wrong company, maybe, but not two different companies.

Second, the batch was posted automatically using an ISV solution.  The logs from that batch posting solution show that the batch was detected and posted in the Canadian company.  So that is presumably where the posting occurred.  But even if something went wrong or there was a posting error or problem, how could that possibly explain what happened?  The batch posting solution simply logs into the GP company, selects the batch, and then posts it, all within the GP client application.  It isn't doing anything behind the scenes, calling stored procedures, or otherwise attempting to simulate the posting process outside of GP--it's all native GP functionality.

The client has been importing these batches for several months, and automatically posting them with the ISV batch posting solution for months.  So this batch was, in theory, no different than any other batch.  But obviously something went wrong.

I'm only left with one tenuous, and perhaps unprovable theory.  My wild guess is that when the automatic batch posting solution switched GP companies and logged into the Canadian company, something in GP stayed "connect to" or "logged into" the US company.  When the post command was issued for the batch in the Canadian company, somehow GP posted the subledger transactions in the US company, but posted the distributions in the Canadian company.

Or perhaps it's simpler than that.  Perhaps the transactions all "posted" in the Canadian company, but when GP moved the transactions from the Work to Open tables, the subledger transactions were moved from the Canadian company to the US company.  That's probably the best guess I can come up with, simply because it is the simplest technical explanation I can imagine--although even saying something like about Dynamics GP that sounds like complete crazy talk.  Unless it can be reproduced, which I'm guessing it can't, nobody is going to believe it.

This was only recently discovered, so the research is still ongoing and it will likely require a support case to resolve.

But I am pretty confident that it is a contender for one of the most unusual GP support issues.

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

http://www.precipioservices.com

NIST SHA-3 Cryptographic Hash Selected

Today, NIST announced that they have selected the "Keccak" algorithm to be the new SHA-3 cryptographic hash algorithm.

Interestingly, Bruce Schneier, one of the SHA-3 contest finalists, recently wrote that he didn't think NIST should select any of the candidates.

For anyone that doesn't know what a cryptographic hash algorithm is, here is the Wikipedia page that hopefully offers an understandable explanation.

In short, they're pretty important to computer security.


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

http://www.precipioservices.com

C# errors updating Excel file: "External table is not in the expected format", and "Operation must use an updateable query"

A client has been using a Dynamics GP Purchasing Receipt Import application that I developed over a year ago.  The application reads receipt data from an Excel file, imports the receipts and matches them against Purchase Orders, and then updates each row in the Excel file with a status, the receipt number assigned by GP, and any notes regarding the transaction.

The client recently setup some new GP Terminal Servers running Server 2008 R2 and Office 2010, so I transitioned the Receipt Import to the new servers.

After the server move, when they tried to run the import, it was not importing all of the receipt lines, and we saw that some of the rows in the Excel file were not updated with a status or receipt number.  In the log files, we found two different error messages:

• External table is not in the expected format
• Operation must use an updateable query

Both of these are relatively common JET or ACE OLEDB driver errors when working with Excel files.  At first I thought that the updates to the Excel file were consistently failing, but some rows were updated successfully.

I then thought that maybe there was a data type issue or formatting issue in some Excel rows that was preventing them from being updated--something I've seen regularly, and is one of the reasons why I no longer recommend using Excel as a data source.  But after reviewing the data for the rows that had update errors, I didn't see anything wrong with the data.

I researched both errors, but the common solutions for each were not applicable.  I've done plenty of integrations using Excel files, so I knew that my connection string was fine, my Excel provider was correct, and that the Excel file permissions were not the issue.  After all, most rows were updating properly--but every once in a while, an update would fail.

After a dozen rounds of debugging and stepping through the code, I couldn't figure out the exact cause, but I accidentally found a workaround to avoid the errors.

It seems that there might be an issue with the Microsoft ACE driver when it attempts to connect to an Excel 2007 or 2010 file.  In some cases, the Connection Open command or an Execute command will simply fail, returning one of the two errors listed above.

It seems neither of the errors is accurate, but rather the connection to the Excel file is somehow bad or in an invalid state.  My guess is that this occurs when there are frequent connections and updates, which is what my import is doing--updating hundreds of rows in the Excel file, one at a time, after each receipt line is imported into GP.

While stepping through the code, after the exception was caught, I jumped back to the point where my connection string was built and the connection was opened--just to try the same update query again and confirm where the exception occurred.  But the second time the code ran, the connection opened fine, and the query executed properly.

One moment, the query fails, the next moment it works fine, as long as the connection was re-opened.

So, I added an additional try / catch block around my connection open and execute commands, and in case of an exception, the code simply tries to reconnect to the file a second time.


try
{
    //Do not use IMEX=1 on updates to Excel, as the IMEX parameter prevents updates
    connString = "provider=" + Properties.Settings.Default.excelProvider + ";Data Source='" + fileName + "';Extended Properties=\"" + Properties.Settings.Default.excelWriteSettings + "\";";
    xlConn = new OleDbConnection(connString);
    xlCmd = new OleDbCommand(xlQuery, xlConn);

    xlConn.Open();
                   
    rowsAffected = xlCmd.ExecuteNonQuery();
}
catch (Exception ex)
{
    log.Write("Re-trying UpdateExcelRow for query (" + ex.Message + "): " + xlQuery, false, true);
    //Try and close and then re-open the connection
    xlConn.Close();
    connString = "provider=" + Properties.Settings.Default.excelProvider + ";Data Source='" + fileName + "';Extended Properties=\"" + Properties.Settings.Default.excelWriteSettings + "\";";
    xlConn = new OleDbConnection(connString);
    xlCmd = new OleDbCommand(xlQuery, xlConn);

    xlConn.Open();

    rowsAffected = xlCmd.ExecuteNonQuery();
}


It's a bit like using duct tape and twine, but this approach appears to have resolved the issue, and was much quicker than diagnosing the underlying cause, which may be an ACE driver issue.  I was able to watch the exceptions occur occasionally, but the second connection Open and Execute commands always seemed to work.

The new code is now in production, so I'll see if that fully resolves the issue, or if there is some other gremlin waiting to cause new problems.

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

http://www.precipioservices.com