SmartList Builder Article Compliation

Ever since we've transitioned SmartList Builder back from our friends at Microsoft we've been utilizing our new found freedom and have been working hard on making it better than it's ever been before! Throughout the year we've had plenty of articles on the new SmartList Builder features. In case you've missed them I've compiled them below for you:

SmartList Builder 2013 New Feature of the Day:

Table Finder

New Icons

Preview Data

SQL Scripting

Built in GoTo's  

Create New SLB Setups from SmartList

Modifying Existing SmartLists

Auto Updating SmartList

Tech Tuesday: SmartList Builder

SQL Views vs SQL Script in SmartList Builder

SmartList Builder 2013 no longer requires Service Pack 2

Including Extender Fields in SmartLists using a Calculated Field in SLB

Table Joins in SmartList Builder

Using Drill Down Builder outside of Excel Report Builder

General SmartList Builder News

SmartList Builder to Come Home to eOne 

What is SmartList Builder? 

Where to Send Your Questions on SmartList Builder

SmartList Design Feature from Microsoft vs. SmartList Builder 

Send Your SmartList Builder Feature Requests our Way! 

Updated: SmartList Design Feature from Microsoft vs. SmartList Builder

New SLB 2013 Features Released

Ode to SmartList Builder

Look what's coming to SmartList Builder 

SmartList Builder, a Partner Perspective 

New Feature for SmartList Builder

If you have any questions regarding SmartList Builder or about an article please feel free to email us at sales@eonesolutions.com and we would be happy to assist you!

Microsoft releases R2 for Dynamics GP Today

It is great to see Microsoft keeps enhancing Dynamics GP and the release today has some nice features added. At eOne we have been busy testing SmartList Builder, Extender, Flexicoder, SmartConnect and SmartPost on the pre-release builds from Microsoft. Everything is looking good and as we do our final Quality Assurance regressions on the released build we will be releasing new builds next week.

We will have additional blogs on the new features we are adding in this release.

Thanks,
Chris Dew

Tech Tuesday: How to Determine if eConnect is Installed or Running on a Machine.

We try to cover questions and issues in our Tech Tuesday articles that we see frequently to equip you before you even think of the question, allowing you to use our products more efficiently and seamlessly. This week our Support Guru, Dave Youngquist, will walk you through determining if eConnect is installed or running on a machine for SmartConnect.

1. Press the Windows + R keys on your keyboard (will open the Run command)

2. Type in services.msc and click the OK button

3. The Services window will open. Scroll down until you see an entry that says “eConnect for Microsoft Dynamics GP 20XX Integration Service”. It may say 2010 or 2013 depending on the version of Microsoft Dynamics GP you are on.  

4. SmartConnect requires that the eConnect runtime be installed on every machine that has SmartConnect installed on it. If you do not see it listed in Services, it is not installed. Rerun the SmartConnect installer, and pick a version of eConnect to install on the machine.  eConnect 10 is for GP 10, eConnect 2010 is for GP 2010, and eConnect 2013 is for GP 2013. 

5. If eConnect appears in this window, then look in the column that says “Status” make sure that the status is set to “Started”. If the status is not set to started, then right click on the eConnect for Microsoft Dynamics GP 20XX Integration Service and select “Start” from the drop down menu. 

6. Watch the service for about 15 seconds. It should start and stay started. If it starts, and then stops within 15 seconds it means either the password is wrong, or the user is not a local machine administrator. 

7. To retype the password, right click on the eConnect service, and select Properties from the drop down menu. In the properties window, go to the “Log On” tab.  

 

8. Retype the password there and click OK. After clicking OK, right click on the service again and tell it to start again. 

9. If the service still stop, you need to make sure the service user is in the local machine administrators group. In the screenshot above, the user is “eonedemo\Administrator”

10. Click on the Windows + R keys again and in the Run command type in lusrmgr.msc

11. In the Local Users and Groups window, double click on Groups, then double click Administrators. Make sure the service account you found in step 7 is in the Administrators group. If they are not, they need to be added.   

12. After adding them to the Administrators group, right click on the service and select Start.  The service should start and stay running this time. 

Do you have any product features you'd like us to feature on our next Tech Tuesday article? If so please email me at danielle.erickson@eonesolutions.com.

Tech Tuesday: Using Drill Down Builder outside of Excel Report Builder

Drill Down Builder has been a part of SmartList Builder for a few releases now. It can be used to give you the ability to drill back from an outside application to the GP windows. Of course, it does require that you have security to the window that you are trying to drill back to and that you are logged into GP and into the company that the data is for.

To get started, you must setup the Drill Down in Drill Down Builder, defining the parameters and window you want to drill back to. Once you have that defined, the setup from Drill Down Builder can be added in Excel Report Builder and SmartList Builder. It can also be added to other locations such as Web Sites, e-mails, and SQL Reporting Services reports.

When you add that drill down to the external application, it is going to need some information from you so that it knows how to drill back and with what data. Below is the basic syntax for the drill down.

dgpp://DGPB/?Db=SQL_Instance&Srv=Server_ name
&Cmp=Database_Name&Prod=3830&Act=OPEN&Func=Drill_Down_ID&Param_Name=Value

The variables in the drill down break down as so:
Db: Equal to the Instance name of your SQL Server. If you do not have a SQL instance name, leave it equal to nothing.
Srv: Equal to the Server name of the SQL Server.
Cmp: Equal to the database ID of the company database to drill back to.
Prod: Equal to the product the drill down is from. If you are using Drill Down Builder, this would be 3830
Act: Equal to the Action Type which is generally going to be OPEN.
Func: Equal to the Drill Down Function you are calling. If you are using Drill Down Builder, it would be equal to the Drill Down ID.
Param_Name: Param_Name is to be replaced by the Parameter you are calling and set equal to the value to pass to that parameter.

Here are a couple of examples:

1. In this example, we are using the default Microsoft Dynamics GP drill down functionality.

dgpp://DGPB/?Db=SQL2012&Srv=EONE&Cmp=TWO&Prod=0&Act=OPEN&Func=OpenAcctIndx&ACTINDX=1

The SQL Server is an instance called EONE\SQL2012, the Company is TWO, it is using one of the built in drill downs in GP so the Prod is 0, the function being called is OpenAcctIndx, and the Parameter being passed is the Account Index (ACTINDX) with the value of 1.


2. In this second example, we are using a Drill Down Builder setup.

dgpp://DGPB/?Db=&Srv=EONE&Cmp=TWODM&Prod=3830&Act=OPEN&Func=RMDOC&FromRMNUMBE=
STDINV2060&ToRMNUMBE=STDINV2060

The SQL Server is the default instance on the EONE server, the Company is TWODM, it is using a Drill Down Builder built setup so the Prod is 3830, the function being called is RM, and the Parameters being passed are the FromRMNUMBE and ToRMNUMBE, both with the value of STDINV2060.

To help with this example, here is the setup in Drill Down Builder.

One easy way to find the syntax of a drill down that you have built with Drill Down Builder is to add it to an Excel Report Builder report. After publishing, open the file and unhide the hidden columns. You will see a column with the drill down for each record where you can find the syntax that is being used.

Interested in SmartList Builder? Sign up for an 11am Wednesday public demo here or email sales@eonesolutions.com for more information!

New SmartView Feature Coming!

Look what I have in my Beta SmartView! 
This is awesome. 
A Microsoft Dynamics GP Receivables Aged Trial Balance.  

There are a number of things that make this super cool for us back office accounting types.

1. It is really fast.

2. You can re-age right on the screen any time you feel like it

3. On screen sorting by any column

4. Excel like drop down filters

5. Find box

6. Onscreen grouping by salesperson, territory and more with subtotals for groups

7. Drill Down to all key GP screens

8. Ability to enter notes to a cell on the list for collections tracking

 

NO, it is not available yet, but will be soon. I believe this is really awesome, don't you agree?

Curious to learn more about SmartView? Feel free to email us at sales@eonesolutions.com for more information! 

Tech Tuesday: Ask User for Input

Have you ever wanted to ask a user to enter values when they integrate data in SmartConnect? This week's Tech Tuesday is from our Director of Professional Services, Lorren Zemke, who will lead you through an example of doing just that!

 

Overview

While most data integrations are executed on a scheduled basis or the data needed can be calculated or pulled directly from some source, there are times when you may want to ask a user for some values when they execute the process. This can be done in SmartConnect with a simple task and some global variables.

There are two requirements needed to be able to use this method. The main requirement is that the map will be executed manually by a user inside of the SmartConnect User Interface. The second requirement is that you must be using an ODBC Datasource so we can include the global variable as part of the query. The last requirement is that you are using VB.NET for your scripting language as C# does not have the input box method available.

For my example we will be asking the user to provide a document date and a batch number when executing our map to create Accounts Payable transactions.

Global Variables

To create the global variables, we will go to the SmartConnect Maintenance menu and click Global Variables. Enter the Global Variable name and click Add. After you have added your global variables be sure to click the Save button.

We will add GBL_BATCH and GBL_DOCUMENTDATE variables.

Create the Accounts Payable map

We will use an Excel workbook as our data source to create Accounts Payable transactions but we will use the ODBC Connection Data Source type so we can create a SQL statement for our data. Later we will modify the query to select specific data rows based on user input.

Create Script Task to Ask For User Input

Create a pre-map script task to ask the user for the values and store them into the global variables. Right-click on the Tasks that run before the map and choose New Task->Run Script.

We will utilize VB.NET code for this purpose by using the Microsoft.VisualBasic.Interaction.InputBox method to pop a dialog box where the user can enter the value requested.

The script will ask the user for the Document Date that we use in our source query. First, we loop until the user has entered a valid date in the format required. A RegEx Expression is used to validate the user entered the date in the necessary format. Second, we ask the user for a batch number and loop until one is entered. Finally we store the input into global variables.

Remember all task scripts in SmartConnect must return true to be successful otherwise return false to stop processing.

VB.NET Code

'---------------------------------------------------------------------------------------
' Requirements: This map should be located in the Tasks -> Map -> Before location so
'               that the dates entered can be used later on. This isn't required
'               however, and this script can be run anywhere it is needed.
'---------------------------------------------------------------------------------------
'---------------------------------------------------------------------------------------
' Setup:        The only variable that may need to be set is the regular expression
'               string. By default, it accepts a fairly standard input date, but it
'               can be edited if a change is needed.
'
' Update: If the map that the script is attached to is going to be
' scheduled or run real-time then the mapInterface variable should
' be set to 0.
' 0 = scheduled map / real-time map
' 1 = manually run map
'---------------------------------------------------------------------------------------
'----------------------------------- CONFIGURATION -------------------------------------
Dim regExpression As String = "^(0[1-9]|[1-9]|1[012])[ /](0[1-9]|[1-9]|[12][0-9]|3[01])[ /]((19|20)\d\d)"
'First we allow 01,02, etc. OR 1,2,etc. OR 10,11,12 as month entries
'The separators we use can be / to separate the month, day and year
'Next we allow 01,02, etc. OR 1,2, etc. OR 10,11,...,22,23 etc. OR 30,31 as day entries
'Another separator
'Finally we allow 19** OR 20** where the * represents a digit

Dim mapInterface As Integer = 1
'---------------------------------------------------------------------------------------

Try

Dim docDate As String
Dim batchNumber as String
Dim dDate As Date
Dim match As System.Text.RegularExpressions.Match

'Define the regular expression to check dates
Dim dateRegex As New System.Text.RegularExpressions.Regex(regExpression)

    'Prompt for the document date
    docDate = Microsoft.VisualBasic.Interaction.InputBox("Enter the document date (mm/dd/yyyy):", "Document Date")

    'Match the input against the regex
    match = dateRegex.Match(docDate)

    'While it doesn't match the regex or a valid date continue to prompt for it
    Do While (Not match.Success Or Not Microsoft.VisualBasic.IsDate(docDate))
       
'Alert the user of the problem
        Microsoft.VisualBasic.MsgBox("Please enter a valid date in the format of mm/dd/yyyy.")
       
' Prompt for the document Date
        docDate = Microsoft.VisualBasic.Interaction.InputBox("Enter the document date (mm/dd/yyyy):", "Document Date")

        'Match the input against the regex
        match = dateRegex.Match(docDate)
    Loop

    'Prompt for the Batch Number
    batchNumber = Microsoft.VisualBasic.Interaction.InputBox("Enter the Batch Number:", "Batch Number")

    'While it doesn't match the regex or a valid date continue to prompt for it
    Do While (string.IsNullOrEmpty(batchNumber))
       
'Alert the user of the problem
        Microsoft.VisualBasic.MsgBox("Please enter a valid Batch Number")
       
'Prompt for the ending date 
        batchNumber = Microsoft.VisualBasic.Interaction.InputBox("Enter the Batch Number:", "Batch Number")
       

    Loop

    'Store the input dates in Date datatypes
    dDate = CDate(docDate)
   

' Set the value into the global variables
GBL_DOCUMENTDATE = dDate
GBL_BATCH = batchNumber

Catch ex As Exception
If (mapInterface = 1) Then
Messagebox.Show(ex.Message, "DateInputTemplate Script Error")
End If
Return False
End Try

Return True

Create a Calculated Field to Use the Global Variables

To map the Batch Number global variable, we set up a calculation column and map that new column to the destination columns in our map.

We create the calculation column called BATCHNUMBER_CALC.

 Map the new calculation column to the Batch number.

Modify Your Query

Add the global variable to your ODBC Query to restrict what data is actually sent to the destination. When the map executes the user will be prompted to enter the Batch ID and then the Document Date. The Document Date entry will require the user to enter the date in a valid format of Month/Day/Year before continuing.

We will modify our query to select only those transactions in our source file with the date we just entered. We place single quotes around the global variable since SmartConnect replaces that global variable with the value it contains and SQL statements require the single quotes around a date value.

As a warning, with this setting you will receive an error when opening the map about an invalid data source. This is normal and will be changed in our 2014 SmartConnect release.

Execute

Execute the map and you will get the two dialog boxes asking for user input.

Once the user has entered valid data for the input boxes the map execution will proceed and create or update the batch entered for the documents matching the document date entered.

Happy Integrating!!

 If you have any questions regarding this Tech Tuesday article or SmartConnect in general please email sales@eonesolutions.com!