With ERP Link, SalesLogix has the capability to integrate with MAS 500 in a more robust fashion than is possible with DynaLink. Lets take a look at the components of ERP Link.
Overview of the Components
First of all the actual ERP Link product is mainly contained in a couple of .NET extension in the SalesLogix database. These .NET extensions contain both the integration engine, with the associated rules and template logic, as well as actually containing the screens that a user of SalesLogix sees that displays the back office information.
These .NET plugins are embedded into several standard “shell” SalesLogix form plugins. These shell plugins are empty and simply provide a way for the ERP Link .NET extension screens to be embedded into. This shell approach allows for these custom tabs to be controlled via the normal SalesLogix release mechanism, while at the same time allows a more rich GUI feature set for presenting the ERP Link Data.
The ERP Link module also consists of some traditional SalesLogix form plugins, used to display or configure attributes for the integration.
These components, along with the ERP Link table structure are what is installed with the ERP Link bundle and consist of the entire solution.
As mentioned, the following shell plugins exists. These are simply containers into which the .NET forms embed themselves.:
- ERP Link Query Data
- ERP Link Order Details (The opportunity sales order grid form)
- ERP Link Contract Reference
- ERP Link Contact Reference
- ERP Link Account Reference
The following are standard SalesLogix plugins used to display or configure data related to the back office integration:
- ERP Link Account Financial Data
- ERP Link Site Reference
- ERP Link Product Status
- ERP Link Manage Sites
- ERP Link Address Reference
- ERP Link Add Edit Site
- ERP Link Account Item Sales History
- ERP Link Account Sales History
- ERP Link Account Financial Details
The following database objects are added for ERP Link:
- Table ERPXREFTABLES
- Table ACCOUNTREFERENCE
- Table ADDRESSREFERENCE
- Table CONTACTREFERENCE
- Table PRODUCREFERENCE
- Table SALESORDERREFERENCE
- Table CONTRACTREFERENCE
- Table SITEREFERENCE
- Table SYSCOLLABORATION
- Table SYSAPPLICATION
- Table SYSIDENTITY
- Table SYSOPTIONS
- Table ERPLOGIN
- Table ERPPROFILE
- Table ERPUSERPROFILE
- Table ERPUSERPROFILEMAP
- Table ERPCONTEXTCALLS
- Table ERPDATARECORD
- Table ERPDATAQUERY
- Table ERPPRODUCTMAP
- Table PRODUCTSTATUS
- Table CONTRACTINVOICE
- Table CONTRACTPOSTING
- Field TICKETACTIVITY.BILLEDAMOUNT
- Table ERPTERMS
- Table SALESHISTORY
- Table ITEMSALESHISTORY
Once installed, to configure ERP Link you use a series of wizards that are also contained in the SalesLogix ERP Link .NET extensions. All of these wizards are accessed through a new custom SalesLogix client menu, found under Tools…Manage, called “ERP Link”.
In order to configure these wizards the computer you run them on must have the MAS 500 client installed. This is because it attempts to locate a configuration file located in the MAS client install folder that specifies the location of the MAS 500 server.
Set Login Parameters
This screen defines the MAS user account used to connect while doing the integration steps and running the various queries for returning things like inventory levels.
An important note about this screen
The login defined here is per user not system wide, so each user would need to set this up if they will be doing any of the following configurations/loading.
Set Up Wizard
Used to configure the connection to the MAS server and also to define the templates available.
Once you connect through the first screens you arrive at the template configurations.
Templates are a core concept to ERP Link. They allow an administrator to define the attributes that are assigned to an entity an MAS 500 when that entity is created from SalesLogix. Multiple templates can be created for each type of entity insertion. You can then assign a specific template to a user so that they use that template’s attributes whenever they create the given entity in MAS 500. While this sounds good there are some issues with this. I will touch on that in a future post.
Each entity template comes with a default template. This is the template all users will use unless they are assigned a different template.
The first entity template in the wizard is the Customer Entity template. This template is used to define the attributes that are populated when a SalesLogix Account is moved into MAS 500 as a Customer.
When you edit the template, you can see what attributes can be defined. These attributes are intended to fill in fields that are necessary to describe a Customer in MAS that do not have a corresponding SalesLogix Account equivalent.
Each of the attribute fields here are directly linked to the MAS 500 system (based on the defined company code). When you click an ellipse on any of the fields you will be presented with the valid entries from the MAS 500 system.
The template which is used when inserting a new Contact into MAS 500 from a SalesLogix Contact.
An address in MAS 500 can be associated to a Customer, Contact, and Sales Order or Sales Order Line item. This template is used when creating these entities from a SalesLogix Address record.
Invoices are created from Support Contracts in SalesLogix. This template defines those attributes used to create a MAS Invoice from a SalesLogix Support Contract.
On any of the template screens you can select the “users” button to assign users to the currently highlighted template. A user can only be assigned to one Template per entity.
Entity Mapping Wizard
The entity mapping wizard allows you to define specific field data that should be moved between SalesLogix and MAS 500. You may also specify which direction these mappings occur (From SalesLogix to MAS, from MAS to SalesLogix, or bi-directional).
Each of the possible entity creation areas can have the field mappings modified.
Clicking the ellipse next to one of the entities will open the field mappings for that entity creation.
In the mapping’s add/edit view you can identify the SalesLogix field or literal value, The MAS field or literal value, the direction which the data flows, data translation functions and conditions to apply to the data.
Also on the Template Select screen, is a button called “Transformation Tables”. A transformation table is a named list of mapped values. Transformation tables are valuable to ensure data integrity when mapping values between Sage SalesLogix and Sage MAS.
Transformation tables are also necessary for data in Sage SalesLogix fields that is larger than what is accepted by Sage MAS. For example, the country field in Sage MAS only accepts three characters. The same field in Sage SalesLogix accepts free text. A transformation table is necessary to map the Sage SalesLogix country to a two character code accepted by Sage MAS.
When the Transformation Tables button is clicked, the manage dialog opens. From here you can add, edit, or delete transformations:
Data Query Wizard
Data queries are queries built against the MAS 500 system used to load the results of the query into tables within SalesLogix. These queries are what is used to map the financial history details from MAS to SalesLogix, such as YTD Sales, Open Payments, Open Orders, etc.
There are two types of queries:
- Mapped Queries – Data is mapped to specific tables and fields in Sage SalesLogix. The imported data can be used to create groups, mail merges, and perform other data related functions.
- Ad Hoc Queries – Data is synchronized to Sage SalesLogix as XML data and mapped to a single database field as an XML blob. Data is only displayed on the Account’s ERP Data tab.
You can right click and choose add, edit or delete to manipulate the queries used to populate SalesLogix.
- In the Query Name box, type the name that identifies the query and displays on the ERP Data tab in the Sage SalesLogix Client.
- In the Query Type box, select they data query type.
- In the Data Table box, select the table in the Sage SalesLogix database that the data will be mapped to. This option is only available for mapped queries.
- Click Map to open the Data Query Field Mapping dialog box to view or create the field map. This option is only available for mapped queries.
- Select the Enable Record Parsing check box to remove records created in the mapped table when they are removed from Sage MAS. For example, if a sales order is invoiced as complete in Sage MAS and is removed, then the order is also removed from Sage SalesLogix. This option is only available for mapped queries.
- In the Enter Query Text section, define the data query to be used to read data from the Sage MAS system. This must be a valid query for the selected Sage MAS system.
- In the Parent Table box, select the parent table for the selected data query. The parent table is used to define the base relationship of the selected data.
- A mapped query does not require a parent table, but if one is not selected, the imported data is not related to a specific account. A mapped query that has a parent table must use the AccountReference table as the parent. This allows the data to be associated with the account defined in the AccountReference link record.
- In the Relationships grid, define the parent and child fields that create the relationship between the Parent table and the current query. Mapped queries must have a relationship defined. The relationship is automatically maintained in the Relationships table.
- The query must be a child query of another query to define a relationship. Queries with no parent table or with the AccountReference parent table cannot have user defined relationships.
- Click Add to add a new row to the grid.
- Click Delete to delete the selected row from the grid.
- Click Test to test the query.
- Click Views to add Sage MAS system views to the selected query data using the Add/Edit User Views dialog box.
- The views are not automatically added to the Visual Basic Forms created to display data. However, information is stored which allows the views to be opened if the required code is added to the Visual Basic Form.
Load Refresh Products
This utility is used to configure which products from MAS are mapped into SalesLogix products. This screen allows you to specify which MAS Company you wish to pull products from. It then allows you to see which MAS items are linked (highlighted in green with a Linked checkbox) and allows you to Select new MAS items to link to SalesLogix products (by clicking the Selected checkbox).
Select the Refresh Stock Status check box to reload the stock counts based on the configured sites (warehouses). When this option is cleared and you load products from Sage MAS none of the Warehouse stock numbers will be associated to the product. To associate stock numbers, you must select this option and refresh the product again.
This dialog also allows you to define the field mappings used when a MAS item is created as a SalesLogix product. This is done by clicking the Mapping button.
Clicking the Proceed button will link any newly selected MAS items. This creates a record for them in the SalesLogix product table.
Load Refresh Accounts
This utility is used to bring across customers in MAS as Accounts in SalesLogix. It can also be used to refresh the financial data of these customers.
Functionality on this screen is similar to the product screen, allowing you to see which MAS customers are already linked and allowing you to select new ones to bring across.
When the Sync All Linked Entities check box is cleared, data synchronization is disabled and only the link is created for the account . When the option is selected, data synchronization is performed. If the Sage MAS customer is added to Sage SalesLogix as a new account, this option is ignored and the full data synchronization will be performed.
Select the Refresh Accounting Data check box if you want to populate all the ERP Accounting tabs in the Account detail view.
Use the Auto Link dialog box to build a query by which Sage SalesLogix attempts to find existing records in the Sage MAS system. Auto Link finds duplicates when creating associations between Sage MAS and Sage SalesLogix. You can set selection criteria using the following fields. Use the drop-down boxes to set the query parameters.
An important notes on the Account Loading
According to the Sage ERP Link online help: “If more than one user is loading or refreshing accounts at the same time, duplicate accounts may be created in Sage SalesLogix. To ensure duplicates are not created, do not allow multiple users access to the load/refresh account function.”
So that is ERP Link in a nutshell. The installation and configuration of ERP Link is pretty straight forward, provided you want it to do what it was designed to do. What exactly do I mean by that?
The downside (My Opinion)
Sage missed the mark in a couple of areas around ERP Link. Lets take a look at the two biggest problems
While I understand what the objectives were with the template concept (Allow an administrator to define what accounting level attributes a SalesLogix record should pass across as defaults in MAS when a new MAS record is created), the way in which it was implemented is critically flawed. Lets take a look at the Customer Template:
When defining an Account template, some of the options you can set for the account are:
- Customer Class
- Freight Method and Ship Via
- Sales Person
- Tax Schedule
If you remember, you can create multiple templates and then assign one template to each SalesLogix user. When creating an account, some of these options seem to be unrealistic to be stored in an account template. While I understand that these are required attributes MAS 500 will require during the customer creation in MAS, having all of these in a template will be a problem.
Consider just one of these options, Tax Schedule. It is completely unrealistic that a given SalesLogix user will create accounts that fall within only one Tax Schedule. As to Sales Person, that would mean you would need at least one template per sale person, in order for commissioning to properly be set up when a
customer is created in MAS, would it not?
Since the account attributes directly impact the pricing that a given account will receive, all of these settings will also determine what a given user will be able to price products at in SalesLogix. Again, it is unlikely that every new account a user creates in SalesLogix should have the same pricing attributes, yet since all accounts created are derived based on their Account Template this would be the case.
Also, these attributes would seem to require setting up a multitude of templates (One template per tax schedule, one template per sales person, etc.). When you then consider all of the needed combinations.
For instance: Our sales guy Jimmy sells to 15 different customer classes. Of those 15 customer classes, which actually comprise of hundreds of customers, there are 12 different sales terms involved. Jimmy also sells in 594 tax schedules. That means it would require at least 15x12x594 = 106,920 profiles just for jimmy to have a customer have a chance of getting correctly set up. Now imagine a sales force of 200 users, each with at least as many attribute combinations. The number of templates required would be daunting.
This would be assuming that one user could even use multiple templates. The rub there is that they can’t. They are assigned only one template. Not good.
Another issue with the template usage is some attributes from the customer are used when posting a sales order. Specifically Terms, Freight Method, and Ship Via are all defined at the Customer Template level. This means that every Sales order posted from SalesLogix uses these same customer template attributes assigned to the SalesLogix user. Have an order that needs to ship over night? Too bad your template has UPS Ground defined. Selling a big order with special terms? Not according to your template. Along those same lines, whichever user actually pushes the order across is the user whose template is used in creating the customer/ order. What if you don’t have the right sales person defined in your template?
As you can see the template implementation is seriously flawed. So much so that it renders the standard product virtually unusable.
OK, so we see the problem with templates. At least for existing customers in MAS the Sales Orders that post from SalesLogix will be correct, right? Nope. The other main issue with ERP Link is that the template attributes continue to be used even for existing customers. For instance, “ACME Corporation” is your largest customer and is set up in MAS with some very sweet terms, and special shipping rates. Well with ERP Link, an order processed through SalesLogix is subject to the terms and shipping details defined in whatever user’s template that happens to process the order. Chance of that being correct?
So is all lost? Is ERP Link not a viable product? I wouldn’t go that far. There are some ways of fixing these inherent flaws. They don’t exist out of the box but they are possible.
Unfortunately, this post has gone on too long. You will have to wait for the next post when I discuss how to work around these limitations.