GSL Blog

Back to Blog Homepage »

Posts Tagged ‘Technology DynamicsGP’

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

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

Dynamics GP 2010 Seminar

Thursday, September 15th, 2011

Looking for people and process improvements? Learn how to maximize your Dynamics GP investment: FREE Dynamics GP 2010 Seminar

dynamics-gp-2010-logo3

Dynamics GP 2010 Seminar

With the release of Microsoft Dynamics GP 2010 comes even more seamless integration to Office, reporting options, key performance indicators, shortcuts and reminders, and a new Management Reporting tool.

Gilchrist Scott is hosting a FREE seminar to offer insight to these productivity enhancements. Don’t miss out - seating is limited!

What: Microsoft Dynamics GP 2010 Seminar
When: Tuesday, October 18th, 9-11 a.m.
Where: Gilchrist Scott, 30 Milk Street, Portland, ME (Map)
Cost: FREE
RSVP:
aberglund@gilchristscott.com or 207.761.2133 ext. 203

Gilchrist Scott is committed to helping you maximize your investment in GP. Please let us know if you have any questions in regard to the seminar. We look forward to hearing from you!

 

 

Share/Save/Bookmark

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

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

Dynamics GP Events you do NOT want to miss – Get them into the Budget Now!

Friday, July 15th, 2011

Convergence 2012 – March 18-22, Houston, TX

GP Partner Connections 2011 – November 6-8, Caesars Palace, Las Vegas

 

DYNAMICS CUSTOMERS:

Convergence 2012:

The Customer event that cannot be matched…..  Convergence is the place to be if you’re a Dynamics Customer, Partner, ISV or Consultant.  With more learning opportunities than you can possibly jam in, you cannot afford to miss this conference.  Get this in the budget now and save your spot at this event where thousands of Dynamics users share tips and tricks and best practices for using Dynamics to gain the intelligence you need to meet your business goals. 

Sign up now to get email notifications on conference agenda, travel, and registration information. 

http://www.microsoft.com/dynamics/convergence/houston12/

Need more convincing?  Check out our March 2011 blog:  Convergence 2011 - Premier Microsoft Dynamics event

 

DYNAMICS GP PARTNERS

Partner Connections 2011:

If you’re a Dynamics GP Partner, ISV, Consultant, or Technical Sales Engineer and pride yourself on being the best in this changing field of applying business requirements to software and technology, you do not want to miss this event!

The agenda is a mix of workshops, roundtable discussions, general sessions, classroom training, and of course… fun in Vegas.

Register by October 1, 2011 and it’s only $499

http://dynamiccommunities.com/GPPartnerConnections/conference.html

 

Share/Save/Bookmark

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

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