Project Accounting Overview Videos

I just recorded some overview videos for Project Accounting to provide an introduction to some of the scope and functionality of the system.  I covered these topics:

Project Accounting – T&M  -  This video shows how to use Project Accounting in Dynamics GP to set up a simple T&M project, enter timesheet information, and create invoices using a batch process.

Project Accounting – Fixed Fee  -  This video shows how to use Project Accounting in Dynamics GP to set up a simple Fixed Fee project, enter timesheet information, recognize revenue, and create invoices using a batch process.

Project Timesheet Entry in Business Portal  -  This video shows how to easily enter project timesheet information into Business Portal, use an approval process, and post directly to the Project Accounting module in Dynamics GP.

These are quick little demos that cover a lot of material.

Creating SmartLists using SmartList Builder and SQL Views

There are a number of GP bloggers that often include SQL Query Language as part of their posts.  This is a rich source of specific problem solvers.

SmartList Builder allows you to easily take advantage of these resources to provide data inquiry and reports.

These are the steps to use once you’ve found a SQL Query that would be useful:

• Create the new SQL View using the SQL Query
• Grant access to the new view
• Create the SmartList using the SQL View

I created this video to show you a simple example of how to do it.

Victoria Yudin is one GP Blogger that regularly includes SQL Queries in her posts.  Check it out.

This is the specific post I referenced in the video.

Microsoft Dynamics GP upgrade thoughts

I hear more and more buzz being thrown around about Dynamics GP  2010 all the time. I myself have installed, tested, created youtube video's, participated in webinars, etc. so I guess I've just added to the general hysteria.

I was just trying to think back over the past several releases of Great Plains and remember if there was similar hype. I remember going to convergence during one of the major release announcements (could have been 7 or 7.5) and hearing MBS announce a great new version that is now available. Excited and surprised a bit, having to spend the whole convergence trying to find out about all the cool new features etc. I'm sure there will be those type of breakout sessions this time around in Atlanta but if you keep up with any of the Dynamics blogs, communities, and resources there is far less need to wonder what is being release.

I'm sure some of this is because of the following:

• Downloading a GB or so of data is way more feasible now than in 2005
• MBS used to send out CD's when the software was released. That was often the first time partners and customers actually got to play with the new version
• Bloggers, MVP's, MBS employee's etc. are able to disseminate great information to the Dynamics community where in the past the monthly email newsletter from your partner was about all the resources available
• Youtube demo's galore on Dynamics GP
• Webinars from MBS and your local partners regarding what's coming out new
• Beta testers used to be the risky early adopters. Now they seem a dime a dozen. ha.

So does all of the above make upgrading less of an unknown? Sure.

Does that mean you should run out and upgrade as soon as the RTM release is finalized? NO WAY.

I recommend waiting at least one full service pack before even considering upgrading. I’m sure there are lots of differing opinions on this subject but to me it’s just not worth the pain and agony that occur with the first version no service pack release of any software.

Should 8.0 and before clients upgrade to 10.0 at this point?

So if you are on an older version of GP (8.0 and before) should you be waiting until Aug/Sept timeframe for the first SP of Dynamics 2010 to be released? (that’s 5 or 6 more months) I guess it depends if you are OK with the risk of running without MBS support for that long. (Obviously if you are still on 8.0 or before this isn’t that big of an issue for you.)

My general thoughts are at this point, wait for the first SP of Dynamics 2010. You can skip some further pain that’s caused by unexpected bugs in waiting for the first SP and you can still have a good 4 or 5 years of use from the system. This is assuming you will use 2010 until very last legs of the product mid way through 2015. But you really should be upgrading at some point as I suggest in my To upgrade or not to upgrade post.

Technorati Tags: Upgrade

Integration Manager and ODBC

Integration Manager (IM) is a common choice for integrating data into Microsoft Dynamics GP. IM has several hooks into the Great Plains modules include the Financial, Payables, Receivables, Sales Order Processing, Payoll, Inventory, Project Accounting (via eConnect), Fixed Assets (via eConnect), and can even be used to update exchange rates. The tool allows users to define named integrations, source locations, source relationships, and target field mappings.

Integration Manager's general functionality is to take mapped data and validate it using Great Plains business logic. If the validation succeeds, IM creates the Great Plains records and uses system automation logic to perform subsequent calculations (like the sales tax engine or auto creation of general ledger distributions).

Integration Manager expects that the user be involved, and actually requires the push of a "Run" button to initiate the integration. There are no scheduling or service components to Integration Manager. IM is a great tool for small business integration, and is perfect when user involvement and manual data manipulation is acceptable.

Since the "IM Process" is manually driven, how can we make it easier for the user? Well, I believe one place to look is in the management of source locations. Most IM integrations are set up using the standard delimited text source (CSV, Pipe Delimited, etc..). Setting up integrations like this can be inconvenient for multi-record transactions, like Sales Order Processing (SOP Header, SOP Line). Each record type would have to be defined as a separate file in the integration, and saved as text delimited. This process can become quite combersome, especially when dealing with Excel to text conversion rules (try saving an Excel field as CSV with leading zeros in the field data...). Add on top of that fact that these integrations are regularly re-occurring, and this process can quickly become stale.

To get around some of these problems, we can use ODBC. IM provides two types of ODBC connection; I prefer to use the Advanced OBDC version. Let's walk trough an excercise.

1. Create an Excel file with two tabs - one for SOP Header and one for SOP Line information. Make sure the lines can at least be related to the header via document number. Save your Excel file to a network location. Name the tabs "Header" and "Line".


2. Go to Control Panel->Administrative Tools->Data Sources (ODBC). Create an Excel ODBC data source (System DSN) that connects to the file location - name it "IM Test". You will need to click "Select Workbook" and then browse to the network location and point the Data Source to the Excel File saved in step 1.
   
   
   
3. Start integration manager. Create a new integration called "Single File Test".




4. Add a source to the integration. When prompted for the Source Type, select "Advanced ODBC". Click on the "Define New Advanced ODBC" node. This will open the source query dialog.
   
   
   
5. Name your source query "SOPHDR". In the data source drop down, select "IM Test". This is the ODBC connection we set up in step 2. Paste the following statement into the SQL Statement box: SELECT * FROM [Header$]




6. Click "Apply". Check out the columns tab - notice that IM was able to read the columns from the Header worksheet.


7. Repeats steps 4-6 for the Line tab in the Excel worksheet. Note that the SQL Statement will change to: SELECT * FROM [Line$]


8. Now, configure your integration as you normally would by adding an SOP destination, query relationships between SOPHDR and SOPLINE, and destination mapping.


9. Run the integration - we now are able to run an IM integration with multiple sources, all from the same Excel file.

Why is this useful? Well, a good Excel person could create a template or macro to manipulate the original source data into the correct integration format. They could save this template/macro somewhere to be re-used. Each time the integration needs to be run, the Excel template is opened, the source data is feed to it (copy and paste anyone?), and the manipulation is done via template/macro. We have not quite hit the value point: now just save the loaded template file to the ODBC network location...run the integration - all done! There is no more saving of multiple files to multiple locations with specific names. We have configured all that in our template (including tab names). Just one file, copied to one location.

I should not stop there; however, the text delimited IM approach we are used to using is actually just circumventing the Windows operating system ODBC setup. IM is actually using ODBC in all configurations. You could mimic the text delimited setup by using the operating system Data Sources/ODBC connection and IM Advanced ODBC setup.

We can make one more conclusion from this reasoning - you can use ANY ODBC connection for IM, including direct connections to SQL Server, Oracle, and Microsoft Access! Here is a sample of some of the ODBC connections provided by windows:



"All the world's a stage, and all the men and women merely players: They have their exits and their entrances" - As You Like It by William Shakespeare