Wednesday, April 24, 2013

SData with Sage 100 ERP 2013

Sage 100 ERP version 2013 now supports SData. 

SData is a Sage standard for exchanging information with Sage applications.  Typical uses of SData include displaying data from a Sage application in another application, web page/portal, etc. or editing/updating existing data.

To date there has been very little information available about the Sage 100-specific implementation of SData.  In this brief post I will share the prerequisites for SData with Sage 100 ERP and explain how to configure an SData ‘widget’ in SageCRM that displays information from Sage 100 ERP.

To begin, SData needs to be installed and configured per the Sage 100 Install Guide; it is a separate install listed as “Productivity Application” under the main Sage 100 ERP installer.  Once installed, within Sage 100 ERP, in System Configuration, check the “Enable Native SData Provider and Access within Role Maintenance” box. In Company Maintenance, check the “Allow External Access” box for companies you wish to access via SData.  Finally, be sure to set SData permissions within at least one Role and verify that your test user is a member of that Role.  (Note: as you move beyond ‘testing’, be careful about how you setup security.)

Test Sage 100 ERP 2013 SData URL
Once you have completed the setup steps, you should be able to verify that SData is working by opening the following URL in a browser, replacing ‘servername’ with the name of your server or IP address. 

You should be prompted for a name/password and then your browser should load a very long page listing the entire Sage 100 ERP schema.

A few notes about the URL:

·        If using https: (SSL) be sure that your certificate matches the server name you are using/testing.

·        The string “ABX” refers to the Sage 100 ERP company code that you previously configured to “Allow External Access”.

·        The string “$schema” will return all the tables from Sage 100 ERP. 

Here is an example of another URL and the resulting screenshot I receive:

In the above example, notice that the URL includes the additional string “AR_Customer” which is a primary customer table.

Technical note: If the Sage 100 ERP data schema gets altered, perhaps by adding a User Defined Field, the ‘Contract’ that SData uses to send/receive information is automatically regenerated, so an SData feed should always reflect the ‘live’ Sage 100 ERP schema.  Fundamentally, the list of tables/fields/etc., i.e. schema, available from Sage 100 via SData is the same schema that is available via ODBC, with a few exceptions (additions) to address parent/child tables.  (An explanation of how that works is outside the scope of this post.)

Simple SData widget within SageCRM
To create a widget on the SageCRM interactive dashboard using the above Sage 100 ERP 2013 SData feed, follow these steps:

1.      Within the interactive dashboard, select Template, SData Feed Template. 

2.      Click new.  On the Feed Properties tab, fill out the fields similar to the following screenshot (using the full URL from above, replacing with your server name).  Note: if you leave the user name and password fields blank, SageCRM users will be prompted for a name/password, or, they can save one when they add the widget to their dashboard.

3.      Click the Assigned Users tab to assign/make available this specific ‘SData feed’ to teams/users.  Don’t forget this step!!  Click Save.

4.      Within the interactive dashboard again, select New Gadget, Create Gadget.  Choose SData List.

5.      Choose ‘Use pre-configured SData feed’ and select the SData feed you saved above.  Click next.

6.      You should be able to select ‘AR_Customer’ as the entity.  Click next.

7.      Select all or certain fields using the checkboxes.  Click next.

8.      Optionally add a filter.  (In my example, I set ‘include data where PriceLevel is Equal to 1’ to only get a subset of customers.  Note that this filter is saved with the SageCRM user’s dashboard, so a different user may use the same SData feed but with different filters.

9.      Last step: give it a name and optionally a description.  I named mine “Customer – Price Level 1”.  See below for a screenshot of the resulting widget.

I hope this helps you begin experimenting and thinking about how you can utilize SData with Sage 100 ERP.  I’ll post more examples in the future.


No comments: