Category Archives: Microsoft Dynamics

Dynamics GP 2015/2016/2018 and FRx

Today’s little project was upgrading my internal system to GP 2015, which all went quite smoothly. Unfortunately, I don’t have a domain, so I am not able to install and configure Management Reporter for my financial reporting. I still use FRx. But after upgrading to GP 2015, FRx would no longer connect to my company database. Instead, I received a lovely error referencing a utility dll that I was not able to locate.

After uninstalling FRx, removing all vestiges of it from my workstation, and rebooting, I did a fresh install and encountered the same problem, which — after some searching online for related issues– I traced to the common files folder at this path: C:\Program Files (x86)\Common Files\Microsoft Shared\Dexterity.  I renamed all the dlls in this path, did a fresh installation of FRx and discovered that one dll was created afresh: GPConn.dll.  The FRx one is dated 4/6/06. This is from the FRx674 build for GP10, which was the last full release of FRx. The new GPConn.dll created by the GP2015 installer has a date of 11/14/14.

FRx ran just fine and connected to my company database after recreating the original dll. So far I’ve had no problems running GP 2015 either, so I thought I would share this in case anyone else out there is still using FRx and wants to upgrade to GP 2015.

Hi, all. Just updating this to let you know the same steps work for GP 2016 and GP 2018. FRx is still viable with the latest release of Dynamics GP.

28 Comments

Filed under Microsoft Dynamics

Fun with SSRS

It seems an increasing amount of my time is spent building reports in Smartlist Builder and SQL Reporting Services. I thought it might be fun to share a couple of SSRS reports I’ve been working on lately.

The first is a Customer Statement. If you use GP customer statement forms, you are probably aware of the limitations in modifying the standard report format. Even the Word template format is cumbersome to modify, so using SSRS to print your statements is a great alternative. You can make an SSRS format as simple or complicated as you like.

I usually begin the process of any SSRS report by building a SQL view containing all the necessary information. In the case of customer statements, there is an additional level of complexity if you want to add apply detail, and most of us do! In my case, we needed a report showing all the open invoices along with all applied open payments and any payments applied during the selected date range.  My customer wanted to be able to enter a date range and select which of their customers would receive statements.  The final product is shown below.

This is a statement from my own GP environment. I’ve removed customer details to protect the innocent, but when you open the report in designer, you will see the Customer ID field and the address block. 2014-07-06_1300

As you can see, it’s a pretty simple format. Once you open the RDL in SQL report builder, you can change the logo and make any formatting modifications you like.  Just note at the invoice detail is in the main report and the APPLY detail is contained in the sub report. If you add columns in either place, you will need to adjust the formatting so that everything lines up.

The SQL view I used can be downloaded here.  (Remember to right-click and save as!) In order to use the RDL I  have provided here, you will need to deploy this SQL view to your environment.  You will also need the SQL view behind the apply detail sub report here (thank you, Victoria Yudin!) and the associated RDL for the sub report here.

If you are familiar with deploying SSRS reports and SQL views, you shouldn’t have any trouble with these.  You will need to point to your GP datasource and ensure that the appropriate security is set to the SQL views and the newly deployed reports. TEST, TEST, TEST to make sure the report shows accurate data for you!

The real power of this particular statement format is that you can print them for any date range. You can print a customer statement from the beginning of time until the last month’s cutoff, or you can print a partial month. You can print statements for all customers or for a select few. I’ve added a comment field as a parameter to accommodate one of my customers who needed to be able to enter a dynamic comment to print on all statements in a print run.

One challenge I have found in SSRS reports in general is how multiple pages are rendered, so if you have long statements, make sure you test with several different scenarios, as you may need to adjust the format to get it perfect.

 

 


 

The second SSRS report I want to share is a simple sales document.  This one uses the actual GP tables SOP10100, SOP10106 (header comments) SOP10200, and SOP10202 (line item comments). If you require more customer billing details, you could easily link the RM00101 (customer master) or RM00102 (customer address master) as well. 2014-07-06_1428

Once again, I’ve made this pretty simple, but additional fields can be added easily. For end user ease of use, you could also write the necessary code to render SOPTYPE as a text field. For simplicity, I have left it as a number from 1-5 to represent QUOTE, ORDER, INVOICE, RETURN, and BACKORDER.

Also, additional parameters would make this more usable depending on a company’s business processes for printing or generating invoices. For example, you could add parameters for customer class, customer, city, state, priority code, etc.

Finally, for return document amounts and for fields such as markdown and trade discount, you may want to modify the expression to render these as negative numbers. This is very simple to do using the SSRS expression builder.

The RDL files for this basic SOP document are here (main doc) and here (line detail). Again – right-click and save as to download the RDLs. Otherwise, they will open as XML.

Again, deploying these is a matter of uploading them to your SQL Reporting Services environment and making the necessary changes to the datasources.

Once you’ve begun using SSRS for documents such as these, you begin to see the power of using the same format for historical documents. By simply making a change to the dataset, you could pull all historical invoices and define parameters to print by a date range, batch ID, etc. Posting a batch of invoices prior to printing is no longer a problem – simply use SSRS to print any and all based on your customer parameters.

Well, that’s all I have for now.  I hope you will have fun using these templates and share your own developments!

 

 

 

5 Comments

Filed under Microsoft Dynamics

September was a whirlwind month for me, and it was also a steep learning curve month. I love learning new things, but I also hope to spare some of you the frustrations I encountered while delivering these solutions.

First, let me state emphatically that the internet is a wonderful resource. Let me temper that by stating that it can also be very confusing when you are looking for specific information and only find snippets of code and overviews of how “it should work”.  I have a fondness for examples and complete end-to-end documentation, and because I’m not a programmer I often find it difficult to make the leap to a complete solution by piecing together bits of script from various sources.

AA and Integration Manager

One of my customers has begun to use Analytical Accounting for simple project tracking. Of course, there is plenty of documentation on how to set up AA and distribute transaction amounts, etc. The challenges begin when you need to import AA data and prepare reports containing the AA detail information.

So in the interest of time, I am going to share the KB articles that explain in depth how to set up AA integrations.  These were provided to me by Microsoft, and they worked like a charm. Just right-click and choose “save link as” to download the PDFs.

AA GL Transaction

AA Sales Transaction

After reviewing the AA GL Transaction document, I identified the area where I had gone wrong in building my integration originally, and it’s now working beautifully.  If you are having trouble building an AA integration, I highly recommend that you follow this document step by step to understand how it works.  I will also clarify that my customer distributes to multiple dimensions and codes, so I had to tweak my source file to accommodate that requirement.  The AssignID on each AA code distribution has to be unique for the amount being distributed, not for the dimension or the code. Below are the actual examples from my test transaction:

Header

***Now if I can just figure out how to dynamically assign the next journal entry number, this little project will be completed.  I hope one of you will provide me with a working example of the necessary scripts!***

AA and Management Reporter

One of the most critical components of any system is the output.  In fact, a lot of times when I’m discussing a customer’s needs, I will begin with “what do you want to accomplish by doing this?”  Normally in an accounting system, the ultimate goal of any data input is to view the information on a financial report.  Analytical Accounting information  is no different.  So it was with dismay that I realized the obstacles faced when trying to present these simple codes on an existing financial report.

First, my customers was using Dynamics GP10 and Frx.  When attempting to install the AA add-on for Frx, we discovered that it won’t run on a 64-bit server.  So we attempted to run it from a 32-bit workstation, but no luck was had there either.  I finally convinced the customer to move forward with their upgrade to GP2010 and Management Reporter, so we upgraded their databases and migrated their Frx reports to MR 20102.

I’m relatively new to Management Reporter, but I had some experience with the installation, configuration, and FRx migration process.  I now feel that I have mastered these steps,  thanks to some very helpful sessions at the  sold-out GP Technical Airlift in Fargo, which took place early in September.  By the way, if you are a partner or work for a partner, you should attend this event:  three days of technical information provided by the “in-the-know” folks at Microsoft.

With regard to the install and configuration, here is the link to the Microsoft download site.

Once I had Management Reporter configured and tested, it was a simple process to add a report to display the AA information.

Of course, much like the AA functionality in FRx, you have to be connected to the AA company in Management Reporter.  Beyond that, it’s a simple matter of adding the dimension codes in the row format like this:

When you double-click on the Link to Financial Dimensions, you will see the AA Dimensions as columns. Double-click on each one to define filters as needed.  In my example, I combined all expense types for each event on a distinct row, but you can use the dimension window to break out or combine codes as needed.

I’m sure I will be faced with combining the AA information with an existing financial report in the near future, so stay tuned for new adventures!

Leave a comment

September 29, 2012 · 8:19 am

WennSoft Troubleshooting for the Not So Faint of Heart

I have a couple of WennSoft customers in my fold, and they often pose some of the most challenging issues to resolve.  For those of you who are not familiar with WennSoft, you can find out more about their products here.  For those of you who are already – and reluctantly – aware of these add-ons, this post is for you.

GP service packs used to make me nervous, but they are almost effortless when compared to the issues I have encountered in applying WennSoft service packs.  Most recently, I took a customer from WennSoft Classic 2010 SP1 to WennSoft Signature 2010 R2 SP1.  This service pack contained 193 fixes to prior releases of the product and was compatible with GP 2010 build 1914.

I tried to anticipate (based on prior experience, of course) any obstacles that might come my way on a Saturday, when WennSoft support would not be available.  I requested and safely tucked away the new registration keys that I knew would be required; I requested additional information from WennSoft regarding any “gotchas” that might come my way and any additional downloads that might be needed;  I had the customer’s IT department download the appropriate files and place them on their server, and I double-checked the versions; I exported all modified reports for GP and WennSoft to package files; I backed up the GP code folder, etc. We planned a back-up schedule after close of business on Friday and I coordinated with IT to ensure that everything was ready.

After running the GP update Friday evening and verifying that it was error-free, I ran the WennSoft update utility.  As it was already late, I left that process running and went to bed.  Of course, in the morning I was greeted by a not-so-clear error message referencing a table that had updated sucessfully.  So after some research and pondering, I decided to give the update another shot.  It completed without error.  Yippee!

Unfortunately, my joy was short-lived.  I completed the update steps, took backups of the databases, verified that reports still worked, etc.  Then I notified the customer contact that the update was complete, so that she could do some testing before we updated clients.  This is all standard procedure, of course.  I had updated a single client and documented the steps so that the customer could complete the remaining workstations after testing.  I then moved on to deploying refreshable Excel reports, GP SRS reports and WennSoft SRS reports, and setting the necessary security for users to access them.

Monday morning we found out that the testing was not thorough enough. Users encountered errors when opening some windows, errors that indicated that tables had not been updated.  I’m sure you’ve seen this dreaded error message before: “Number of results columns doesnt match table definition.”  The customer contacted WennSoft directly for additional assistance and users continued to do their work in the system. We knew at this point that a rollback of 12 workstations and server would be quite time-consuming and that most of the system was operational, so my customer decided to trudge on.  A few hours later she received some guidance from WennSoft regarding a maintenance utility that would show us which tables did not update.  My response, of course, was to immediately run the maintenance utility, all the while wondering a) why does this not automatically run as a final step of the update process and b) why does it require a password?  Putting my frustration aside, I proceeded with the troubleshooting. There were 14 tables that were not updated. After another few hours of back-and-forth between my customer and WennSoft support, they offered to update the tables manually for a consulting fee. My customer asked if this was my recommended course of action, and I replied that I would do it for them, as I knew exactly what needed to be done to correct this error.

The answer was obviously to copy out the contents of these tables, rebuild the tables and associated stored procedures, then import the data back into the newly updated tables.  Fortunately, only six of the tables contained any records. The other ones were empty and could simply be dropped and recreated. I used the handy SQL maintenance window in GP to take care of this, beginning with the empty tables, then used the WennSoft utility to verify that the tables were at the correct version.  Three hours later, all the tables were rebuilt and the data back in place, and no further errors have been encountered in the system.

Harrowing experience? Yes! Rewarding? Absolutely!

1 Comment

Filed under Microsoft Dynamics

Dynamics GP Add-Ons Your Company Should Consider

Third party add-ons for Dynamics GP can be used to increase the efficacy of the program. There are a variety of add-ons most companies should consider that will improve the efficiency of searching, analysis, report making, and accessing SQL. I typically recommend the following add-ons: SmartList Builder, SmartFill, and SQL Reporting. Over the next few months, I’ll talk about these add-ons in more detail. Today, I want to focus on SmartListBuilder.

SmartList Builder is an add-on that makes the standard SmartList in GP work better for many companies. With SmartList Builder you are able to custom-build a SmartList with external or GP data. If you have a special report you need to create, such as sales history, or sales transactions, or purchasing, that is not a standard GP SmartList, then SmartList Builder will let you create it and then dump data out to an Excel spreadsheet where you can manipulate it, analyze it, or create additional reports.

The advantage, of course, of being able to dump data into Excel is the ability to use Excel to run hypothetical scenarios, create graphs that can be imported to PowerPoint for presentations, and otherwise use the data from Dynamics GP for decision making and planning. SmartList Builder also allows you to export data to Word or print a report directly from the add-on.

The biggest advantage of SmartList Builder is that it saves time and it makes repetitive tasks easy to do. The report writer that comes with GP is time-consuming and not at all intuitive. For example, let’s say you need to generate a list in Excel every month of all the items you sold for a certain period of time. The basic list could be generated by Dynamics GP, but if you also need to see any special fields, like line item comments for each item sold, then SmartList Builder lets you build a more robust report, and do it very quickly. SmartList Builder also allows users to generate reports using a combination of Dynamics data and other SQL data.

The biggest drawback of SmartList Builder is that you cannot dump out an AP Aging or an AR Aging report from SmartLists without a lot of extra manipulation to get it into Excel. There is an AP/AR Aging report in GP that dumps to a GP Report file, but it’s not compatible with Excel. However, SmartList Builder will let you build a SQL view in the database with all information calculated for you and then, using SmartList Builder, you can pull SQL view data.

SmartList Builder makes monthly reporting faster, better, and easier, and it makes Dynamics GP more versatile by allowing users to have access to information they may not otherwise have access to. It provides a user-friendly interface for making that data usable by people outside of IT and accounting.

1 Comment

Filed under Smartlist Builder

Getting the Most Out of Dynamics GP

Murphy’s Law states that if something can go wrong, it will. Perhaps the greatest antidote to stave off Murphy (or to lessen the consequences of a problem) is preparation. Q Factor strongly encourages all of its clients to perform regular practices to get the most out of Microsoft Dynamics GP. Dynamics GP takes well to customization, whether in creating accounting reports or allowing changes to the system, and we recommend two habits to help save a lot of headache for every company: making server notes whenever changes are made, and performing regular backups.

Whether your in-house accounting team is one or twenty, it is important that everyone who touches Dynamics GP make note on the server what changes and improvements they have made. The reason to make notations about any system modifications is so that, in case the “improvement” turns out to have unanticipated, or even has unwanted results, you are able to revert to a previous state. Often, you will not realize you made a mistake three steps ago until you’ve moved forward far enough to realize its impact. Without notes to understand what has been tweaked, it becomes a much bigger problem than it has to.

Additionally, if you have a team member leave the company, these notes allow for continuity. Many who are taking over the use of GP may not know the foundation of what comes with the software compared to the changes a team member introduced to accommodate your company’s business processes. Making notes can help your current and new team members understand a colleague’s stopping point. This also makes it far easier to have more members of your accounting team working with Dynamics GP, because they can follow one another’s improvements.

Regularly backing up GP-specific files, your team can be confident about protecting the efforts being made. Backups can also allow you to roll back to the prior state if a recent change interferes with the effectiveness of GP. Typically, the accounting team and the IT team are not working or communicating with each other effectively. If there is no one in the accounting department setting up backups of data, and no one has communicated the importance of such backups to IT, you can assume you’re not getting the backup you need.

Daily backups are critical to protecting your data. As your accounting team works to improve notation making habits, you should also be onboarding IT members to regularly back up the data that sits on the server.

Leave a comment

Filed under Microsoft Dynamics

Microsoft Dynamics GP 2010 Reporting

I was recently given the opportunity to write a book review on a new publication about one of my favorite topics: Dynamics GP reporting tools.  Read on and you may find yourself in possession of a free copy of this book!

The book is entitled Microsoft Dynamics GP 2010 Reporting and was written by David Duncan and Christopher Liley of I.B.I.S.

Microsoft Dynamics GP 2010 Reporting

Microsoft Dynamics GP 2010 Reporting

First, I must express my utmost respect for anyone who undertakes a task this daunting. The subject matter is dense, and the tools are ever-changing.  Kudos to these consultants for taking the time to create a handy guide like this!

To many new users of Dynamics GP, the variety of tools that accompanies the ERP system is overwhelming.  There are built-in or “canned” reports that can be modified, smartlists which can export data to Excel, and FRx or Management Reporter for creating standard financial reports.  The latest versions of Dynamics also offer collections of Excel Reports and SQL Reporting Services (SRS) reports which can be deployed and used right away. In addition to all of this fare, a customer may purchase Smartlist Builder and Excel Report Builder, both of which further extend the capability of smartlists and Excel reporting to all areas of the system as well as external data sources.  And we can’t forget Analysis Cubes for Excel, which is the height of business intelligence for the Dynamics end user. Essentially, data analysis and reporting tools for Dynamics GP have flourished over the past few years, and this book offers an excellent synopsis and technical comparison of the various tools available.

While it is geared toward power users and data analysts, the book does offer some valuable information to anyone who wishes to learn more about the multitude of reporting options out there.  The authors start out with a helpful discussion about the challenges of developing reports: defining the target audience, locating the appropriate data, selecting a form of presentation,  and applying permissions to the final product.  They then dedicate a full chapter (sometimes two) to each tool and provide some guidance on installation, deployment, security, and usage.  The final chapter Bringing It All Together offers an in-depth comparison of the various tools and discusses the trade-offs among them in terms of data sources, latency, ease of formatting, and required development and IT resources.

As a software consultant, I found this book very useful.  It is very well-organized and the examples provided are a great way to introduce a power user to the strengths and weaknesses of each tool.  Most importantly, the authors are enthusiastic about the wealth of reporting resources available and how each one can be used to improve visibility into an organization’s financial and operational health, which is really the fundamental point of an ERP system.  The book imparts enough information to inspire you to explore each tool by actually using it, which is the best method of learning!

So here’s the scoop on how to get your copy.  Send me a message about your own reporting challenges in the comments below this blog.  I just want a brief summary of what tools you are currently using and how you think this book will help you face those challenges.  I’ll select three lucky winners from the respondents during the month of July, and I’ll contact you for your address so no need to include that information at this time.

6 Comments

Filed under Microsoft Dynamics

Integration Manager: The Basics

I’m sure there have been numerous articles written about Integration Manager for Dynamics GP. Most of them are written for developers and contain advanced features, tips, and tricks for making it work in unique circumstances or using complicated data sources.

I find that most end users have a difficult time understanding the fundamentals of IM, so I want to outline a very simple integration design for those of you who are new to building integrations and feel a bit overwhelmed by the the tool.

The simplest way to approach a data import using IM is to first manually key in a transaction from the recordset you wish to import, whether it be a journal entry, a payables invoice, or a bank transaction.  This is best done in a test environment. Once you have the transaction entered, you can then open integration manager and better understand how it works because IM is like having another data entry person in your system. That’s right, folks: integration manager enters the data just like you did when you hand-keyed it.

Let’s have a look at a standard journal entry:

There are not a lot of fields on this window, but it is a good example of a transaction entry window in Dynamics GP:

  • It has a header section and a detail (line) section or grid
  • It has required fields
  • It has various options based on user input

Every transaction entry window has a header and a detail. In most cases, the header contains the information outside of the grid, or lines.

In this case, the header fields are:

  • Journal Entry number
  • Note
  • Intercompany checkbox
  • Batch ID
  • Transaction Type (standard or reversing)
  • Transaction Date
  • Reversing Date (only if you select the reversing transaction type)
  • Source Document
  • Reference
  • Currency ID

The detail/line/grid fields are:

  • Co. ID
  • Account
  • Debit
  • Credit
  • Description
  • Exchange Rate
  • Distribution Reference
  • Corresp Co. ID

Required fields are displayed in bold and red. This feature can be activated under user preferences and is very useful for identifying missing data values when importing records.

In terms of user-input options, it is actually surprising how many different parameters there on on this simple window.

  • If you mark the intercompany checkbox, for example, the Co. ID and Corresp Co. ID in the grid will be activated
  • If you select the Reversing type transaction, the Reversing date field will be activated
  • If you select an account in the grid that is linked to an analytical analysis group, the A next to the Account field will be activated, allowing entry of analysis information for this account

All of these options are also operational in Integration Manager and can be activated if needed.  Let’s compare the data entry window to the integration setup:

They certainly don’t look at all alike, do they?  But let’s take a closer look at IM and its components.

Under Sources, we have a header and a detail object, which correlate to the header and detail windows in the transaction entry window.

Query relationships allow us to link the header information to the detail information. More about that in a moment.

The Destination is the transaction entry window in Dynamics GP where the information will appear.

And Destination Mapping is where we will go to map each field from our source file(s) to the destination window.

Sources

Each source in an integration is linked to a data set that the user wishes to import into Dynamics GP.  The data can be in virtually any location and format, from a comma-delimited flat file to an Oracle database.  If using multiple sources (header and detail) there must be a column/field that establishes the link between the two files.  In our example, this is the DocNum field.  Alternatively, IM has the flexibility to allow use of a single datasource for both header and line information.  More about that below.

For our example, we’re going to use a sample integration that comes with Integration Manager.  It consists of two tab-delimited text files: one for the header and one for the detail. You will find these sample files by default in this location: C:\Program Files\Microsoft Dynamics\Integration Manager 10\Samples.

As you can see, the required information for a journal entry is all available in these two files.  The header information contains the Date and Reference values.  The line information contains the distribution accounts and amounts.  Both files contain the DocNum, which is a column used to join the files together and match the appropriate journal entry detail to each header.  We don’t need to provide a journal entry number, as we are going to allow Dynamics GP to assign this at the time of import.

Double-click on the Source object GL Trans Header to open the properties for this source:

You will notice the path to the source file on this window.  Use the elipse (3 dots) to navigate to your desired source file.  The remaining options are self-explanatory.  If you file is comma-delimited, choose that option, if tab-delimited, choose that option, etc.  If the first row of your source file contains field names, mark the checkbox to indicate that as well.

Click the Columns tab to continue and click the refresh button to display the columns from your source file.

Each time you change the source file, you MUST refresh the columns by clicking this button.  This forces IM to “read” the new source file and detect any additional columns or changes you have made to existing columns.

The additional tabs: Filter, Sorting, Scripts contain additional features that permit you to restrict the data to be imported, sort it prior to importing, and add logic to validate or manipulate the data prior to importing.  With scripts you can also enhance an integration to write back to the source and confirm importing was successful.  This is an advanced topic that I won’t address here, but examples are available if you need this functionality.

Repeat this process for each Source in your integration.  In this case, we would go through the same steps for the GL Line Detail source.

After you have clicked Apply and OK to close this window, you should right-click on each Source and choose to Preview.   This should show you the source data that will be imported and allow you to verify that the source files are pointed to the right place.  If the Preview does not look right or you receive an error message, you will need to correct the source data before proceeding.

Query Relationships

Once each source has been pointed to the appropriate source file, columns refreshed, and any additional options selected, we need to define the relationships between the various sources.  Double-click the Query Relationships object to display the sources.

To link the sources, simply click on the field name/column on the header source and drag your cursor to the same field name/column on the detail source.  This will create the arrow displayed.  If it is not set properly, right-click on the arrow and choose Remove.  For more advanced links, right-click on the arrow and choose Properties to open the Select Relationship Type window.  Remember that the Master file is the origin of the arrow.  The child file is where the head of the arrow is pointing.

Once you’ve completed the query relationships, close that window.

Destination

For pre-defined integrations, such as our sample one, the destination is already defined.  If you were building a new integration, you would right-click on the Destination folder object and choose Add a Destination, then select from a list of objects.  In this case, our destination is already set to General Journal.

Destination Mapping

The mapping of your source file to the Dynamics GP destination is the heart of integration manager.  You will notice when you double-click on Destination Mapping that all of the folders displayed pertain to a component of the destination window in Dynamics GP.

In this General Journal example, the top level is our header information.  Click on General Journal to view all of the fields available on the journal entry window in Dynamics GP: Journal Entry, Intercompany, Batch ID, etc.  They are all present and can be mapped here.  The real power of IM comes into play by using the Rules, however.

Notice that the Rule column is set to Default for almost every field.  This tells IM to allow Dynamics GP to automatically take care of the information in that field according to how it would occur if you were manually entering it into the transaction window in the system.  If you were keying a journal entry, Dynamics would provide you with the next journal entry number, the intercompany checkbox would be unmarked, you would need to enter a batch ID, etc.

In our example, we have made the Batch ID a constant value.  Other options are to Use a Source Field, Use Input, or Use Script.  Use a Source Field will allow you to map the field from your source file.  You would need to have a field/column in the header file that contains the Batch ID.  Use Input will prompt you at run time to enter a batch id.  Use Script will allow you to program how you want the batch id to be defined based on additional information you provide in a script.

For each field on the transaction entry window, you need to decide how you want the information populated.  The Rule dropdown may contain different options depending on which field you select.  In addition, if you select Use Constant, the Source column will be populated with the specific options pertaining to that field in Dynamics GP.  For example, if you select Use Constant for the Transaction Type field, you will be presented with the two options available in Dynamics: Standard or Reversing.  By selecting Reversing, you are ensuring that every record imported will be created as a Reversing Transaction, but you must also ensure that your source file header contains a reversing date field/column in order to map that information as well.

The Reference field is a required field, and I will use it  to demonstrate various options that are available in IM at the field/column level.  When you click on the Reference field and choose Use Source, you will notice the ellipsis (3 dots) is activated and you can use that to select the appropriate field to map from your source file.  The Source Object window will open, and the dropdown will contain all of the sources available for this integration. In our case, it will show the header and detail options.  Because we are mapping a field on the header, we want to select from the header source.  We would highlight the Reference field and click Select.

With your cursor still on the Reference field in the mapping window, you will notice there are additional options available in the lower left-hand area of the screen.  These features allow IM to handle various conditions in your source data in order for IM to import it properly into Dynamics GP.  For example, if your source file contains both Standard and Reversing transactions and you have a field in the header that indicates this using S or R, you would want to use the Translation option, which will allow you to tell Dynamics if this column contains an “S” that means Standard, if it contains an “R” that means Reversing, etc.

If you were mapping the batch ID from your source file, but it is in upper and lowercase format, Dynamics GP won’t import it.  The integration will fail because the Batch ID field must be in uppercase characters.  In the integration, you can specify that you want to convert the case at runtime.  The other options are for leading spaces, trailing spaces, how to handle a data string that is too long, and what to do if your source field is null/empty.  By default Dynamics will cancel a document if a required field is null, but for optional fields, you can choose to make it a blank value or use a default value from the Dynamics setup.

Once you have set the options for each field on this window, click the Options tab to select how you want to handle additional elements of the integration.  For example, if the Batch ID you have mapped or entered as a constant value does not exist in Dynamics GP, you can opt to have it created at runtime as shown below:

Once we’ve mapped the header (General Journal object), we need to proceed with the Entries object.  In addition, if we wanted to import Analytical Accounting distributions or Tax details, we would need to map each of those objects as well.  In our case, we will continue with Entries.

As you can see, we are using the source file for the account, debit, and credit amounts and allowing Dynamics GP to handle the rest of the distribution grid in the transaction entry window.  One very useful feature of IM that is available for distribution fields is the Rule to Use Positive Source Field or Negative Source Field.  This allows you to import data from a single field in the source file, as in our sample file where Amount is negative for credits and positive for debits.  IM allows us to use this same field and will designate the debit or credit status of the distribution based on this logic.

The options tab for Entries lets us specify the source object for this particular object: Line Detail.  The Rule column here also contains options such as Default (if you have default distribution accounts set up for a vendor for example) or Empty (to bring in zero amounts) or Default Non-Imported (which is helpful if you only want to map the debit side of a payable and allow Dynamics to assign the A/P account from the Vendor or Posting Setup window.  The Rules will vary depending on the Dynamics GP window destination and the type of transaction you are importing.

___________________________________________________________

After you have mapped all sources to their appropriate destination object, you are ready to run the integration.  The progress window will advise you of any issues with your import, and you can also view the log from that window after the integration has run.  To access a log after you have exited the progress window, simply double-click the top level of the integration (GL Transaction) and click on the Logs tab:

I hope this helps as an introduction to using this very powerful and flexible tool.  We have barely scratched the surface here, but I hope I have answered some fundamental questions about Integration Manager.

30 Comments

Filed under Microsoft Dynamics

Dynamics GP as a Single Source

More and more companies are integrating their various business information systems in an attempt to streamline IT and accounting processes.  One of my customers recently decided to use Dynamics GP as their primary database for storing Login information for all their disparate systems.

Before I outline our approach to meeting their requirements, let me explain how they were doing this.  Their recruiting and hiring process is fast-paced, to say the least. This company is opening new retail stores in new markets all the time, so their recruiters are on the road collecting applications daily and setting up new hire meetings every couple of weeks.  In order to set up a new employee, the recruiter would fill out all the basic information in a spreadsheet, which was then passed on to the payroll department to create an employee record in Dynamics GP.  The payroll department would then send a spreadsheet to the IT department with the new hires’ information.  IT would set up the necessary logins and passwords in each system based on another spreadsheet summarizing the different systems required by each job title, then communicate these back (again via a spreadsheet) to the recruiting department.  The final step for recruiting was to prepare (manually, using Word) a login sheet for each new hire to be distributed at their new hire orientation.  In addition to this “workflow”, there were additional data elements that were often not captured or only partially captured, resulting in inaccurate information or worse: erroneous payroll transactions.

Overall, a cumbersome process relying on several people to remember to fill out all the necessary information and send a notification by e-mail that the shared spreadsheet was ready or attach and e-mail a new spreadsheet to someone in another department. Additionally there was frequently a need for cross-communication regarding missing pieces of information for some new hires.  The primary pain point to be addressed was setting the appropriate commission flag in their point of sale system to ensure that employees received commissions when applicable.

The first step to address in developing their new process was removing the back-and-forth nature of creating employee information in their various systems. In order to handle the initial setup, we utilized Extender to create two new windows in Dynamics GP.  The first window is attached to the Payroll Position Setup window and lists all of the user logins required by position code along with a commissionable flag.  The second window is attached to the Employee Maintenance window and captures each employee’s logins for the various systems.  Once this was complete, we published each of these windows as SQL Views using Extender views, then used Smartlist Builder to attach the two views and create a smartlist that IT could use to generate a spreadsheet of new hires along with their required logins defined by position code. Finally, we created a second smartlist that recruiting can use to create a mail merge in Microsoft Word for their new hire login sheets.

We changed the business process to remove the payroll department from the initial setup of new hires.  The recruiting department now enters the employee ID and SSN in the employee maintenance window, along with the department and position code.  The IT department merely checks the smartlist on a daily basis to determine if new hires have been entered by the recruiting department (if the logins fields are blank, this indicates a new employee record). If records are found, they export the list to Excel, and as the various logins are defined IT completes this spreadsheet and uses an Extender import to populate the logins window in Dynamics GP.  Recruiting uses their login sheet mail merge smartlist to quickly generate the forms they need for orientations. The Payroll department completes an employee record after recruiting confirms the employee has attended an orientation and filled out all the necessary paperwork (this is also tracked using  Extender windows on the employee maintenance window).

Now that I’ve written all of that, it looks very complicated, but it really simplifies and streamlines the process of adding new hires and their various logins into the numerous systems. In addition, the commissionable flag is now determined at the position code level instead of on an employee-by-employee basis, so there is much less room for error in setting up the point of sale system. In addition, because all of the logins are now stored in a single location, the IT department is able to use the SQL views to push login information into various systems automatically instead of having to set up each one individually. A similar process (utilizing the same Extender windows and SQL views) is used by the IT and Payroll departments to communicate changes in status (which often result in a change to the commissionable flag and login requirements) and terminations.

2 Comments

Filed under Extender, Microsoft Dynamics, Microsoft SQL Server, Smartlist Builder