Tuesday, September 30, 2014

Tech Tuesday: Getting Ready to Update Extender to 2013?

Are you getting ready to update to Extender 2013 and wondering what you might need to be aware of?

The update from Extender 10 or 2010 to Extender 2013 has a lot of table changes in it. You can download a document that outlines all of these changes on the eOne website on the Documentation tab or here.

As the tables are changing and the way the data is stored in them is changing as well, there is a lot of transferring of data from one table to another in the update.

With this movement, we have seen a lot of invalid data that has been in the Extender tables. We are finding that many have imported directly into the Extender tables, used other importing tools, etc. which if not done correctly causes invalid data.

Because of this we modified the Extender update in the Extender 2013 R2 build 12.00.0110 to stop if it is going to cause the update to have to skip valid data. 

This build will check for any data that is going to cause duplicates that SQL will not accept into the new table. For example, if you have a window with 2 keys. If you have the following two records as keys in your EXT00100, it would be a duplicate that would cause the update to fail on the EXT00100. The duplicate comes in that the Key_Strings_1 and Key_Strings_2 are the same. For one reason or another a space was inserted between the two keys in the PT_UD_Key. The second record is technically not a valid record, but does exist in the tables.

Beyond what the update will check for, we have created a script that is downloaded with the Extender 2013 manual for you to run prior to the update. It will check for other data records that might not cause the update to fail, but may cause that individual record to not be updated. It will check for things such as the following.
  1. Blank Key_Strings_1 fields in the keys tables.
  2. Missing keys in the Key tables that have data in the other data tables.
If the script finds this type of data, our support team can help you sort out what needs to be done to correct this data so that the update will be able to successfully bring all of this data across.

So, what is the key take away from all of this? 
  1. Make sure that you have run the script before you update to see if it finds any invalid data.  If it does, make sure that you take care of that data before the update as it won't be there to fix after the update. 
  2. Make sure that you are using build 110 to update to Extender 2013.  It will check for the duplicate key issues you could run into and make you fix them before the update can be run so that the update doesn't fail 

Are you interested in Extender or eXtender Enterprise? Email us at sales@eonesolutions.com for more information!

Thursday, September 25, 2014

SmartList Builder Demo & Testimony from Belinda Allen

As of late eOne Solutions has had the frustrating honor of assisting countless customers who have purchased GP with intentions of using only the SmartList Designer tool to generate a report. They then come to eOne once they have spent a lot of time and money on the issue. We then assist them with the help of SmartList Builder, leaving them with "Why didn't we purchase this straight off the bat?". So instead of hearing it from us (because we're biased), we've decided to reach out to our partners to get their take on SmartList Builder and why it should be included with every Microsoft Dynamics GP sale.

Meet Belinda Allen, Microsoft MVP, MCP of Smith & Allen Consulting, Inc. who loves SmartList Builder so much she put together a short video testimony stating how her company had a discussion on whether they should keep using the eOne Solutions SmartList Builder tool and why they all without a doubt decided to continue using and selling it. The video also demonstrates how Belinda and some of her customers use SmartList Builder for Purchase Order Processing.


You can download the xml files Belinda mentions in the video to copy it to your own system here and here.

Interested in purchasing SmartList Builder? Email us at sales@eonesolutions.com for more info! For the differences between SmartList Builder and SmartList Designer click here.

Tuesday, September 23, 2014

Tech Tuesday: Using Multiple Data Sources to bring in the correct 1099 Amount on Payables Transactions

The post today has been inspired by real world events. I was recently at a customer conference put on by one of our partners. The customer had issues with bringing in 1099 amounts on AP transactions. After integrating with SmartConnect she would only see $0 amounts for each transaction even though the Vendor in this case was a 1099 vendor. Upon further review, I saw that eConnect was defaulting the value to $0 so we needed to pass in the appropriate amount. What we needed was to find out whether the Vendor was setup as 1099 inside of Dynamics GP as she didn't have that information inside of her Excel sheet.

The easy solution to this lack of information was to use a multi-data source inside of SmartConnect to find out if the Vendor had been configured as 1099 inside of Dynamics GP. Using the Multi Data Source will allow us to setup one "virtual" table to the Excel file and another "virtual" table to the SQL Server table Vendor Master (PM00200) and finally join them through our simple wizard on the Vendor ID.

The image below shows the main Multi Datasource window where you can add as many different datasources as needed (SQL Server, Text, Excel, XML, Webservice, Etc)

After adding your datasources (you can see we have one to an Excel file and one using a ODBC connection to a SQL Server) click on the Modify button to join your tables together on the common fields like the image below:

You can see in this instance we have joined the Vendor in our Excel file to the VENDORID field of our SQL Table. Additionally, we only needed to know the 1099 Type from that table so we only selected that field from the SQL Table and then selected all fields from the Excel file.

After you have the datasource you can now move to the Mapping window for the header (Create Payables Transaction). In the image below you will see we have now added a Calculated field for the 1099 Amount.

If we look at the calculation for the 1099 Amount you can see we have based it on the 1099 Type field. If the value is 1 (Not a 1099 Vendor) then we set the amount to zero, otherwise we use the purchases amount from our Excel file. The image below shows the script used to make the simple calculation.

This was a simple way to get the 1099 Amount correct on each of the transactions but I think you can see there are many more ways a Multi-Data Source can be used to validate data or get missing data.


Chris Dew
Director of Product Management

Interested in SmartConnect? Email sales@eonesolutions.com with you questions - we'd be happy to assist you!

Tuesday, September 16, 2014

Tech Tuesday: How to Add an Extender View to a SmartList

This week's Tech Tuesday article is from our very own Support Guru, Dave Youngquist. He will walk you through how to add an Extender View to a SmartList.

Login to Great Plains as the sa user (only the sa user will have Views as an option)

1. Click on Views, then click New

 2. In the Extender View window, give the view a name, and then click the add button.  

3. Pick “Extender Window” as the type, and pick the eXtender window you need to add to the SmartList.

4. Check off the fields you want to appear in the view. I would recommend marking all. 

5. Click Save on the View.  
6. Now to go Tools – SmartList Builder – Security – SQL Table Security.  
7. Checkmark the database you created the view in, and change the type to “Views”.  Place a checkmark in the view that you just created.  

 8. Hit the OK button to save the security. 
 9. Open up SmartList Builder and open the SmartList you need to add the eXtender data to.
 10. Click on the + button to add a table. Pick SQL Server Table


11. Change the list to show “Views”, select your view, and then add it to the SmartList.  Make sure you use a left outer join if you have data in the main table, but there may not be data in the eXtender table.  Pick the appropriate key field from the list to link the eXtender view to the main table. 

12. You now will have a SmartList that will display eXtender data on it. 

If you have further questions or curious to learn more please email sales@eonesolutions.com!