GSL Blog

Back to Blog Homepage »

Archive for the ‘Technology’ Category

« Older Entries

Windows Service Application & Web Services for near-real-time Integration

Thursday, December 1st, 2011

A customer recently brought us a requirement to create a two-way integration between GP and a third party POS system. This integration needed to include Customer Master Records, Vendor Master Records, Address records, Items, Sales Invoices, Sales Payments, POP receipts and IV Adjustments. To make it more complex, the two systems needed to be in control of certain types of records. The POS system, for instance needed to be the “master” when it came to Customers, but GP needed to be the “master” for vendors. This integration also needed to be near real-time.

My first thought for this Integration was Dynamics GP Web Services and / or eConnect. These tools provide a tight integration with GP and satisfy the near real-time requirement. GP Web Services needs to be called using an ASP.Net application like a web site or Window’s form application and is event based, like any other ASP.Net app, so something needs to occur in the app before web services code can be called. The POS system was not written in .NET and the only way to get information in and out was through direct SQL statements or DTS package exports. The DTS package, while old school, caught my attention for a moment because this lead me towards using Integration manager. IM is a manual process that will integrate data from a text or Excel files into GP. This, however, did not satisfy the near real-time requirement so it was quickly scrapped.

I needed to come up with a .NET application that runs on some kind of a timer to call the GP Web Services. Sounds like a great scenario for a Windows Service Application. There is a template in Visual Studio that creates an installable Window’s Service based on .NET and this sounded perfect, so I decided to test it out. I created a holding table in SQL that is populated by SQL triggers when a document or master record is create or updated in GP. I then used a .NET timer control to trigger the GP Web Services. This will, in essence, act as a polling application. The code on the timer will check the holding table for any new records then query the tables for the type of record in the holding table and use GP Web Services to bring in or query GP documents to move them into the POS system.

An added bonus feature is that the application can write events to the Window’s event log. Successful and, more importantly, error messages are recorded and easily queried just like Window’s events. An e-mail feature was also added to warn certain users when certain types of records were integrated. Another great feature is that the Window’s Service can use an .xml config file to hold settings. This allows the ODBC connection settings to the third party app, amongst other types of settings, to be stored outside of the source code which makes this application easily modifiable for different client requirements. While this solution is not yet in production, initial testing looks terrific and seems to be a great fit for these requirements.

This is just another custom solution from Gilchrist Scott Development. If you are looking to integrate another application with GP and do not want to bother with manual processes such as Integration Manager, then a Windows Service Application from GSL may be for you. Please contact us at 207-761-2133 for more information.

Share/Save/Bookmark

Tags: , , , , ,
Posted in Business Process Knowledge, Dynamics GP, Technology |

US Payroll Year End Update

Monday, October 17th, 2011

Mark your calendar - the US Payroll Year-end Update for Dynamics GP is scheduled to release November 17th. This update will be required for all U.S. customers to install.

Changes include:

  • W2 and W3
  • EFW2  (magentic media)
  • 1099 R  for pension payroll
  • 1099 payables Dividend
  • Fixed assets luxury depreciation
  • FICA Social Security

There are also changes to the SQL tables included in this update, to add the new EmployerKind data in the Dynamics and Company databases, in the following tables:

  • W2000006
  • UPR40100
  • UPR10100

There will also be several Bug Fixes for other modules, including Inventory, Manufacturing, Project Accounting, Sales, Field Service, eConnect and Extender.

As a reminder, the release schedule is tentatively scheduled as follows:

  • US Payroll 2011 Year End update will release mid-November 2011.
  • Canadian Payroll Year End Update will release mid-December 2011.
  • US Payroll 2012 Tax Round 1 will release mid-December 2011.
  • US Payroll 2012 Tax Round 2 will release in January 2012.

These updates are only available for supportedversions: Microsoft Dynamics GP 10 and GP 2010.  Click HERE for information on our 4th Quarter Upgrade Special.

Contact us to schedule assistance with these payroll updates, or to discuss an upgrade.

Share/Save/Bookmark

Tags: , , , , , , ,
Posted in Dynamics GP, Support, Technology |

A First Look at GP 12

Tuesday, October 11th, 2011

The next release of GP has some very exciting new features.  This is the first release of GP that is geared towards cloud environment deployments.  The “Software as a Service” initiative Microsoft has been plugging for quite some time has come to Dynamics GP 12.

·         Web Based Architecture – Dynamics GP 12 will be deployed in 2 distinct architectures.  We can deploy using the standard 2-tier client/server architecture with a SQL server and either workstation installations of a terminal server/Citrix server.  GP 12 will also be able to be deployed using web client architecture.  The SQL server will remain the same, but now we can deploy a web server to serve up the client via Internet Explorer using ASP.Net and Silverlight.  No more client installations on workstations or Terminal Server/Citrix servers.

·         Named System Database – GP 12 will now allow you to rename the DYNAMICS database which will vastly simplify cloud hosting by allowing multiple customer organizations to be hosted in multi-tenant SQL environments on one instance

·         Multitenant Service Architecture – Multi-tenant services will create a tenant configuration service that can be used by GP Web, Web Services, eConnect, Integration Manager and other applications as needed in order to support multiple customer organizations on a single instance of the application.

·         Printing – GP 12 will now allow you to change the printer at the time of printing the documents (I can hear everyone clapping as I write this).

·         Bin Transfer History

·         Enhanced Document Attachments

·         Reason Codes for Stock Movements

GP 12 is due out towards the end of 2012.

Share/Save/Bookmark

Tags: , ,
Posted in Dynamics GP, Microsoft, Technology |

IT Projects: The Human Element

Monday, September 26th, 2011

Information technology projects are essentially business process re-engineering projects.  Too many decision-makers expect that:

1.       The business problem is an automation problem.

2.       The automation solution will completely replace the manual process

Usually, neither case is completely true.  Sometimes not even remotely true.

If you think of an IT system as another employee, one with very specific talents, limitations and costs, you might set better expectations for your IT projects and realize better return on the investments.  For example, certain people are not good with customers, so you wouldn’t put them in a difficult customer service position, because they would ruin your customer relations.  When you do find a person who is qualified, you still make adjustments to their role based on their strengths.  The same is true when automating.

For example, you want to automate the import of invoices.  Currently, field salespeople maintain Excel workbooks, which they email to your accounting department for manual data entry.  You are, in effect, replacing the person who does the manual entry.  Don’t make the mistake that that person is just transferring data from one place to another.  The REAL business process demands that the employee make many decisions during data entry. Over time, their position has become the vital – albeit informal - quality control point for order processing.  That can be the very hardest automation to accomplish well.  It will demand that either the import process can replicate the smart data entry person OR that the data coming into the process becomes more formalized with validation and error-checking done in the field.

The point is, if you try to take the human completely out of the process, it will most likely fail and negatively affect your whole company.  Instead, focus on using automation to increase the efficiency of the data entry person’s process.  Expect to import 60% or so of the Excel data and send the rest as exceptions to the human for real attention.

This approach helps in many ways:

1.       Staff and project expectations and goals are attainable

2.       The project can be implemented more quickly because the more relaxed requirements allow the automation to be added to, instead of replacing, the current process.

3.       The automation process can be improved and updated without disrupting the whole process.

4.       As new things come up, new products, new business lines, new salespeople, etc., the process will be resilient, allowing the human to catch the new issues without creating a log jam.

Share/Save/Bookmark

Tags: , , ,
Posted in Business Process Knowledge, Technology |

SQL Server Power Pivot for Excel

Tuesday, August 30th, 2011

Power Pivot is an add-in to Microsoft Excel 2010 and is intended as a self-service Business Intelligence tool that allows power users to create their own models from just about any data source including Microsoft Dynamics GP.  Best of all, this add-in is free of charge and can be downloaded here.  Once installed, you will have a ribbon tab in Excel called ‘Power Pivot’

PivotTable Ribbon

To add data, simply click on the ‘PowerPivot Window’ button.  You can then import data from many different sources including SQL Server, RSS and flat text file among others.  Once you have the data imported that you want to use, simply click on the ‘PivotTable’ button and choose how you would like to view the data

PivotTable

You can then slice and dice the data any way that you like.  Here we display 2 charts, 1 for sales by salesperson and the other for sales by customer from GP.  This literally took 3 minutes to create.

 

PivotTable Charts

A few of the amazing things that PowerPivot allows you to do are:

1.       PowerPivot is not limited to 1 million rows as Excel is.  There is actually no limit on the amount of rows.  PowerPivot is able to work with very large sets of data because it stores the data in memory

2.       PowerPivot allows you to define relationships.  In Excel, if you need to join data from two separate sheets it requires you to use the VLOOKUP function.  PowerPivot uses relationships on columns and most of the time automatically recognizes such relationships

3.       DAX Function language – Not only are the 80 Excel functions included in this language, but over 60 new functions are defined, including a function that accommodates fiscal years.

4.       Easily create Pivot Tables and Charts using the PowerPivot field list.

 

PowerPivot is a powerful tool for the Excel Data Analyst taking pressure off the IT department to create reports.

 

 

 

Share/Save/Bookmark

Tags: , , , , , ,
Posted in Dynamics GP, FRx / Reporting, Technology, Tips 'n Tricks |

What can a Dynamics GP Light User do?

Monday, August 1st, 2011

There’s been so much buzz about Dynamics GP Light Licenses lately, and why shouldn’t there be?  When you have the ability to access data, turn it into business intelligence and deliver insight to your team, we would be putting our head in the sand by not asking about it.

 

Understanding and complying with Microsoft’s licensing rules requires some education, but it’s not complicated when limited to Dynamics GP.   

 

What can a Dynamics GP Light User do?

·         Perform real-time analyses with refreshable Excel reports

·         Access the library of key metrics via SQL Server Reporting Services – Charts, Metrics, KPI’s

·         Gain insight using Analysis Cubes

·         View financial statements with Management Reporter

 

How does the Light User access this information?

·         Using a web browser, Light Users access information through SharePoint, including:

o   Role Center Dashboards

o   External Lists

o   Business Portal Applications such as Time & Expense and Requisition Management

o   Workflow Approvals

o   Enterprise Search

 

Why buy a full Microsoft Dynamics GP User License when you can get a Light License and real time data for under $200? (What CAN’T a GP Light User do?)

·         A GP Light User can NOT log into the GP Application or perform any tasks within the program.

 

How do you know when you need a Light License in order to be in compliance?

·         GP Light Users are perfect for a non-GP User who is accessing a report, query, or application that uses the Dynamics databases, for example:

o   Executive Centers and Dashboards

o   Excel Reports (Smartlist Builder and Microsoft Excel Reports)

o   SSRS, Crystal, and other reporting tools

o   Visual Studio, Access, or other development applications that produce or update GP data

o   Workflow in Dynamics GP

o   Business Portal’s Order Management and Requisition Management

o   Payroll and HR Self Service suite

 

Still not sure?  Contact us today to get the answers you need to take advantage of the price and opportunity to deliver intelligence rather than meaningless data.

 

Share/Save/Bookmark

Tags: , , , , , , , , ,
Posted in Best Practices, Dynamics GP, FRx / Reporting, Microsoft, Technology |

New Users and Dynamics GP

Friday, June 17th, 2011

While brainstorming about an interesting topic for my next Gilchrist Scott blog post, I got a call about setting up new users.  Seemed very straight- forward at the time. 

Step 1: Log into Dynamics GP, go to Tools ->Setup->System->User and enter an ID and Password.  You can enter a class if your company is using that.

Step 2: Once that part is done, you’d then go to Tools ->Setup->System->User Access and grant them access to each of the companies you want them to be able to get into. 

Step 3: Grant Security so that based on the work that they do, they will only be able to access those areas that have to do with the duties that they perform.

Well, we did this process together, and then tested the users.  Perfect!  We were able to log in as each user and all was working very well.

The part that was not so straight-forward? When they tried to log in as these new users on another machine.  They weren’t able to get these users logged in.  They kept getting the error “Your login failed.  Attempt to login again or contact your system administrator.”  Typically this means that you just entered the password incorrectly.  But while performing the definition of Insanity – “Performing the same steps over and over again, and expecting different results,” we found that we weren’t typing in the wrong password, because when we went back to the original machine that we setup the users on, the same password worked.

Now we needed to figure out what was different about these two machines that would play a role in these strange happenings.  There were several things that could have been contributing to the problem, so we decided to start with the most obvious first.  Any ideas?  Well, I took a look at the ODBC connection.  This is what is used to get Dynamics GP to be able to talk to the database where all your data is stored. 

When I looked at the connection, what I found was that the initial workstation was setup with an IP address (numbers that identify the server) and the other was setup using the server name as it appears on the network.  This was causing the issue for the Id’s and passwords.  The ODBC connections all need to be setup identically or there may be issues such as this.  It happens because the when the password is saved it gets encrypted based on the way the connection is setup, and if you try to access that new user on a workstation where the connection is not setup exactly the same as the workstation that created it, the decryption will not work to extract the password and allow the user in.

As usual with troubleshooting issues that come up, always be sure to look at the obvious first.  Just because something seems simple doesn’t always mean it is. 

Here’s to happy connections!

Share/Save/Bookmark

Tags: , , , , ,
Posted in Best Practices, Dynamics GP, Support, Technology |

SmartList Export Solutions for Dynamics GP

Tuesday, May 31st, 2011

If you use SmartLists in Dynamics GP, you have most likely used the Excel button to export a SmartList to Excel.  What you may not know is that there is another option called SmartList Export Solutions that is available for you to use.

Examples of things you can use Smartlist Export Solutions for are to automate totaling and formatting of SmartList data in an existing Excel Spreadsheet.   And you can export SmartList Data to the same spreadsheet over and over again.  You also gain additional functionality by being able to use macros that automate various tasks like clearing data from an existing spreadsheet, creating a new sheet within a workbook before adding data, adding up columns etc..

To Setup a Smartlist Export Solution

1)      You’ll want to save a SmartList with the columns you want, as a Favorite

2)      Export your Favorite SmartList to Excel

3)      Save the Excel Spreadsheet to the location of your choosing  and close out of Excel

4)      View your Favorite SmartList in Dynamics GP

5)      Select Smartlist>>Export Solutions from the Menu

a.       Name:          Enter a Name to identify the Spreadsheet that you will be exporting to

b.      Document:                 Enter the Path or browse to where you previously exported and saved the SmartList in Number 3

c.       Preparation and Completion Macro:  If you added a macro to the Excel File enter those names here

d.      Visible To: Enter who you would like to be able to use/see this solution

e.      Application:  Select Excel

f.        Works for Favorites:  Check the box next to the Favorite that this solution is associated to

g.       Save and Close the Export Solutions window

Back at the SmartList window open your Favorite SmartList

1)      Click the Excel Button

a.       You will now have an additional choice of Export Solution (Quick Export is the usual way of exporting a SmartList)

b.      Click Export Solution and your spreadsheet will open

                                                               i.      If you created a before and/or an after macro these will run ahead of and/or after your is exported

It can be that simple, or you can design macros for different tasks like creating pivot tables etc..  This gives you quite a few more options when exporting SmartLists to Excel, especially when you do the same type of export month after month, and this should save you lots of time once the solution is in place.  Happy Exporting!

Share/Save/Bookmark

Tags: , , , , , , , , ,
Posted in Best Practices, Business Process Knowledge, Dynamics GP, Specific GP Modules, Support, Technology, Tips 'n Tricks |

« Older Entries