What is the Infor CRM Connector Tool Pack?
The Infor CRM Connector tool pack gives a simple, straightforward way of providing connectivity between BPA Platform and Infor CRM.
Without this tool, it is still possible for BPA Platform to read data from an Infor CRM system via an Database Query (OLEDB) Tool step using the appropriate OLE DB driver; or to read from and write data to Infor CRM using the Web Service Connector Tool, however this requires knowledge of the underlying web service. The Infor CRM Connector tool pack provides an easy to use user interface which removes this complexity and provides a single tool to enable read and write access to and from Infor CRM installations.
The Infor CRM Connector uses XML as its input and output data format. Interoperation with standard BPA Platform tools is achieved by using those tools which accept or output XML as standard or which enable conversion of recordset data into XML. Handling of events within Infor CRM can be achieved by use of a standard BPA Platform trigger event on the Infor CRM database tables.
The Inform CRM Connector itself does not directly connect to Infor CRM. All data transfer to/from Infor CRM is done via the Infor CRM OLE DB Provider, and this takes place in a Web Service that is distributed with the tool pack. This allows distribution of the components so that the BPA Platform server does not have to be on the same machine as the Infor CRM OLE DB Provider.
Infor CRM Integration Features
- Infor CRM Connector
This tool is the main communication mechanism between BPA Platform and Infor CRM. It allows the user to build tasks that can read and send information from/to Infor CRM. Data is transferred to/from the tool via XML. - Infor CRM Connector Web Service
This is an XML Web Service that is used by the Connector tool to communicate data from the BPA Platform server to the Infor CRM database server, via the OLE DB provider.
White Paper - Infor CRM Tool Pack 1.3
The Infor CRM Tool Pack white paper describes how to use the tool.
System Requirements
The Infor CRM Connector must be installed on the BPA Platform Server and any BPA Platform client machines that need to interact with tasks using the tool.
Architecture
The diagram below provides a high-level system architecture diagram of the Infor CRM Connector within the context of BPA Platform and Infor CRM. Items in blue are part of the Infor CRM Tool Pack.
About the Infor CRM Connector
The Infor CRM Connector is used to operate with data from/to an Infor CRM database. The operations available are Get, Update, Add, and Delete.
The tool transforms incoming XML data into an XML format for the specific Infor CRM object and operation selected. This XML is then passed to the Connector’s Web Service which does the actual communication with Infor CRM via the dedicated OLE DB Provider. The response is exposed as XML from the tool, which could include details of the object requested, errors from the provider, or the values of any automatically generated ID fields.
The connector does not accept a BPA Platform recordset directly as input. A BPA Platform recordset can, however, be processed by the Convert Recordset to XML Tool first to transform it into XML.
Global Configuration
The global configuration dialogs for the Tool are where connections are defined for each Infor CRM database that is to be used by BPA Platform.
Click Add to create a new connection.
The information for each connection includes:
- Connection Name — Name for the connection.
- Use Default Web Service URL — By default, the Web Service URL as defined in the main global configuration dialog box. If a different web service URL is required for this connection, then it can be entered here. This enables possible configuration of multiple Infor CRM servers over multiple sites within one instance of BPA Platform.
- Web Service URL — The web service URL to be used by the connection.
- Timeout — In the event of a communication failure with the web service, this is the amount of time in seconds that is given to allow the connection to attempt a reconnect. If the time expires whilst still trying to reconnect, any task using the connection will fail and the error will be recorded in the BPA Platform event logs.
- Server — The name of the Infor CRM database server.
- Server Data Link Name — The name of the Infor CRM OLE DB provider data link to be used by the connection.
- User — The Infor CRM user to be used for the connection.
- Password — The password for the Infor CRM user to be used for the connection.
- RW/RO Password — The RW/RO password for the Infor CRM data link connection, if applicable.
- Default SECCODEID — The value to be used for SECCODEID fields when adding data objects to Infor CRM, if the SECCODEID fields are not specified in the step configuration. If this is blank, then the Infor CRM OLE DB provider default SECCODEID is used. This varies from version to version of Infor CRM.
When the connection is set up, the available objects and fields for the selected Infor CRM database are retrieved, and a schema for each object is created.
This information is used in the tool’s step configuration. If there is a change to the Infor CRM database (alteration to the user defined fields/tables, for instance) then the object schemas can be refreshed by editing the connection.
Step Configuration
About the General Tab
At configuration time, when the connector step is added to a task it will allow the user to select the data source to be passed at runtime through the General tab of the Infor CRM Connector dialog.
The Data source can be either a task step, or a custom data source.
If a Task step is used, then the data source can be set to an available BPA Platform XML data source. Only those steps that are capable of natively exposing an XML document at runtime are listed (this may be another Infor CRM Connector step, or tools such as Convert Recordset to XML, Import XML Document, or the Web Service Connector).
It is also possible to define a custom data source. This data source has two parts – a definition of the schema used by the incoming data, and the selection of a BPA Platform variable that will contain the XML data at runtime.
Custom Schema Configuration
The Custom Schema Configuration dialog is used to define the schema of the XML to be used as the input data source for the Infor CRM Connector step. An XML schema defines the structure of the XML, in terms of what tags are present, and the nesting of the tags. The format used by the Infor CRM Connector is the industry standard XSD format.
If an XSD schema file is available for the XML that is to be used as the input data, then this can be loaded in from a file on disk (via the Import XSD/XML File button, which displays a file selection dialog) or pasted directly into the text box shown. Any XML passed into the step at runtime must conform to this schema, otherwise an error will be reported.
Using an XSD schema file is the recommended way of defining the XML schema. However, if an XSD schema is not available, then an example of the XML that will be used at runtime can be loaded in or pasted into the text box. From this example XML, the tool will try and infer the schema that defines the XML that has been input.
When using XML and schema inference, there are some important caveats that apply:
- All possible elements should be included in the example XML used. If the example used omits some possible fields that may be present in the data at runtime, a validation error will occur at runtime as the inferred schema will not contain those fields.
- Care should be taken to ensure that at least two instances of the elements are present, if appropriate. For instance, if the example XML used looks like this:
<root>
<ActivityData>
<Field1 />
<Field2 />
</ActivityData>
</root>
A schema will be inferred from this, but it will specify that only one ActivityData element should be present.
If, in the XML at runtime two elements are present, like so:
<root>
<ActivityData>
<Field1>Act1</Field1>
<Field2>notes1</Field1>
</ActivityData>
<ActivityData>
<Field1>Ac21</Field1>
<Field2>notes2</Field1>
</ActivityData>
</root>
Then a validation error will be reported. In this case, the example XML used should include at least two ActivityData elements, as this will mark the inferred schema so that multiple ActivityData elements are allowed at runtime.
About the Connection Tab
Through the Connection tab of the Infor CRM Connector dialog box a connection is also required for the step. This is selected from the list of connections previously created and set up in the Global Configuration for the tool through the Connection Configuration dialog box.
As an alternative to selecting a connection that has been previously created, a BPA Platform variable may be utilised by dragging and dropping it from the Task Browser dialog box into the Connection field to create a dynamic connection. If the dynamic option is used, then at runtime the text in the variable should match the name of one of the connections set up in the Global Configuration and this connection is then used.
About the Mapping Tab
Once the input data and the Infor CRM connection have been defined, the mapping of data from the source to an Infor CRM object, along with the operation required, is performed through the Mapping tab of the Infor CRM Connector dialog box.
The aim of this tab is to define the links between the incoming data and the outgoing data that is to be sent to the Connector Web Service and then into Infor CRM via the Infor CRM OLE DB Provider. These links are created by dragging and dropping fields from the data source left hand box into the tool input right hand box to connect the fields on each tab.
The Object drop-down holds a list of objects available for the selected Infor CRM connection. These are automatically obtained from Infor CRM when the connection is created in the Global Configuration and can vary between Infor CRM versions. Selecting an object will change the tree display in the right hand pane to reflect the fields and child objects available for that selected object.
The Operation drop-down displays the operations that the selected object supports. These are Get, Update, Add, and Delete.
When the object and operation have been selected, the appropriate schema will be presented in the right-hand XML tree control, enabling mapping to take place.
BPA Platform variables and formulas can be included in the source data, by simply dragging them from the BPA Platform Browser dialog box into a branch node in the XML tree control. Once in place, these can then be linked to the output XML tree control in the same way as normal fields.
Creating the links in the user interface defines how, at runtime, the incoming XML is to be translated into the XML required for the given object and operation.
The method used to translate the XML is XSLT (eXtensible Stylesheet Language Transformations), which is the industry standard mechanism for performing XML translations. There is an XSLT tab on the control, which will show the XSLT currently being generated for the given set of links created. In the case of the user interface not being able to handle a particularly quirky requirement for transforming data, it is possible for the user to go into “Free Type Mode”, where XSLT can be input directly to achieve a transformation from the source XML format into the XML format required for the desired object and operation. XSLT knowledge will be needed to do this.
Use Cascade Settings
When creating a Infor CRM Connector step, if the Delete operation is chosen, the Use Cascade Settings option is enabled. This option allows the step, when deleting the data from Infor CRM, to obey the cascade settings for the Infor CRM joins defined as part of the Infor CRM database.
If the Use Cascade Settings option is left unchecked, then the Delete operation will be performed according to the behaviour of the OLE DB provider. This behaviour, at present, is to simply delete the record from the table and ignore any linked records in other tables.
If the Use Cascade Settings option is selected, then the Infor CRM Connector tool will, when the record is deleted from the table, consult the cascade options for the joins setup on that table within Infor CRM, and obey the setting specified for each joined table. The only exception to this is the Replace setting, which can’t be achieved – in this case the Delete operation will work as if Clear was specified.
Filter Links
For the Update, Delete, and Get operations, the user interface allows the user to designate fields within the destination object to use as the filter when performing the operation. Such fields are linked in the normal way from the source data, but are then marked as a Filter Link. This means that at runtime, the Tool will compare the value in the source data against the Infor CRM database (the condition for the comparison can be set in the UI, and can be “=”, “<”,“>” or “< >”) when performing the operation. The presence of multiple filter links implies that all conditions must be met on all comparisons.
Examples
On a Get operation for an Account, the source dataset definition contains an Account object, with a type of “Prospect” in one of the fields. This field is linked to the “Type” field in the destination Account object. The link is automatically created as a filter field link as for a Get operation, only filter links are valid – ordinary links which indicate sending of data to Infor CRM are not relevant. The filter condition is set to “=”. At runtime, all information for those Accounts that have a Type set to “Prospect” are returned from Infor CRM.
For an Add operation, filter links are not relevant. The UI prevents their creation as only data transfer links are relevant.
On an Update operation for an account, the source XML contains information to be updated for a specific address within a specific account. Data transfer links are created from fields in the source dataset – they are linked to fields in the Address child object in the destination XML. A Filter Link is created from the “AddressID” field in the source to the “AddressID” field in the destination with the condition set to “=”, and another similar Filter Link is created from the “AccountID” field in the source to the “AccountID” in the destination Account object. At runtime, for each account object in the XML, the address that matches the “AddressID” for the containing “AccountID” will have its information updated with the data from the source XML for those fields where a data transfer link was created.
On a Delete operation for an Account, the source XML contains an Account object, with an “AccountID” field. This is linked to the “AccountID” field in the destination Account object. The link is automatically created as a filter field link as for a Delete operation, only filter links are valid – ordinary links which indicate sending of data to Infor CRM are not relevant. The filter condition is set to “=”. At runtime, all Accounts are deleted from Infor CRM that have an “AccountID” field set to the same value as the “AccountID” field for each Account object in the source XML. If the “Use Cascade” setting is enabled, then the Infor CRM cascade configuration is checked for the join information on the Account table and the related data from the joined tables is also deleted if applicable.
Transaction Control
It is possible to set the transaction configuration for the step to be Single or Multiple. This controls how the data sent to Infor CRM by the step is processed.
For Single, all data is processed in a single transaction, so that any error or failure encountered when performing the operation through the Infor CRM OLE DB Driver for any individual record results in the entire operation being failed, so that none of the records are processed.
For Multiple, each record in the XML is processed individually, so that any error or failure for one of them does not affect the others.
Source Key Field
It is possible to mark one of the fields in the source XML as being the Source Key Field. This can be an independent, non-linked field, or can be a field that is being used for a Filter or Data link. When a field is marked as the Source Key Field, at runtime the contents of this field will be carried through into the Output as the element SourceKeyField within each top level object in the XML (or within the error object in the XML, see Errors and failures). This gives the ability to be able to, in the output XML set by the Infor CRM Connector, connect together the value from a field in the source with the returned values from the operation, and also to make error handling easier; you can see in the error XML block the value of the nominated source field from the input.
About the Options Tab
The Options tab for the Infor CRM Connector dialog box is used to configure the Step behaviour if an error occurs when the Step is being processed. The following options are provided for selection:
- If an error occurs — This area contains the following options:
- Abort Step — If there is an error mapping a row then the step will abort
- Continue — The Step will continue even if there is an error mapping an individual row from the input data source
- If step aborted — This area contains the following options:
- Abort Task — If the Step is aborted then the Task is aborted
- Continue — If the Step is aborted then the Task will continue onto the next Step in the process if one exists
Tool Output
The Infor CRM Connector outputs XML. This XML could contain data returned from Infor CRM (in the case of a Get operation) or it could contain the key fields for the object instances affected by the operation. For example, in the case of an update, the key fields for the top level object instances updated would be returned. Finally, if any errors are returned in the Infor CRM OLE DB Provider, these are also contained within the XML.
This XML can then be used within other BPA Platform tools, either as is for tools which natively consume XML as an input source or through conversion to a BPA Platform recordset using the Convert XML to Recordset tool. In addition, the task browser object OutputXMLString is available and can be used to save to file on disk for example.
Runtime
The incoming XML is translated into the XML format for the object and operation selected in the configuration. The data for the linked fields is brought across into the output XML – only those fields that were linked are brought across. This XML is passed to the web service which then processes the data, performs the operation requested, and sends back an XML document containing the response. The XML can then be used by other BPA Platform steps.
Errors and failures
It is of course possible that errors may happen when the tool is processing data. These errors could come from a variety of sources and for a variety of reasons. Generally there are two types of errors that can happen at runtime.
Errors that happen outside of the Infor CRM OLE DB Provider
These types of errors could include:
- XML transformation errors
- Web Service connection errors
- Login to Infor CRM (at initialisation of the OLE DB provider) errors
These types of errors may result in the step failing. Whether or not the task fails depends on the standard step error configuration options set. The error is shown in the BPA Platform log for the task and is reported in the message shown in the BPA Platform client.
Errors/warnings/messages that are returned from the Infor CRM OLE DB Provider
These types of errors could include:
- Invalid data (according to Infor CRM) passed into the tool for the given fields
- Infor CRM business logic errors
These types of errors will not result in the step failing. The message from the OLE DB provider is returned in the result XML. The message shown in the BPA Platform client will reflect that some of the records failed; the result XML will give more detail about the problem.
Errors returned by the tool within output XML
Any errors of the second type described above are returned in the output XML. The error XML structure looks like this:
<Error>
<SourceKeyField /> — If a source key field was nominated as part of the step configuration (see Source Key Field) then this will contain the value of that field for the object instance being manipulated
<Object /> — This contains the name of the object (table) that was being manipulated when the error occurred
<Code /> — This contains the Infor CRM OLE DB provider error code
<Message /> — This contains the Infor CRM OLE DB provider error message
<ExtendedInfo /> — This contains extra information about the error which may be useful when ascertaining the nature of the problem
</Error>
Dealing with errors
There are three properties set on the step after processing:
RunCountproperty — at runtime, this contains the total number of top level objects in the input XML that were processed by the toolFailCountproperty — at runtime, this contains the number of error objects returned in the output XMLSuccessCountproperty — at runtime, this contains the number of non-error objects returned in the output
XML
This means that it is possible to check the FailCount property to see if any <error> elements have been returned. By using a Decision step, it is therefore possible to branch the task depending on if an error happened and alert someone to the problem. Because the<error> element is present in the output XML schema, a Convert XML to Recordset step can be configured to just take the contents of any Error elements and put them in a BPA Platform recordset. This can then be used for reporting purposes.
A step has been configured to add five contacts to an account. Four of the contacts are added successfully, but one of them – the fourth in the input data – contains some data which results in an error from the OLE DB Provider. The output XML from the step is as follows:
<Response>
<Operations>
<Operation Name="Add">
<Object>
<CONTACT>
<SourceKeyField>1</SourceKeyField>
<CONTACTID>CDEMOA00006D</CONTACTID>
</CONTACT>
</Object>
<Object>
<CONTACT>
<SourceKeyField>2</SourceKeyField>
<CONTACTID>CDEMOA00006E</CONTACTID>
</CONTACT>
</Object>
<Object>
<CONTACT>
<SourceKeyField>3</SourceKeyField>
<CONTACTID>CDEMOA00006F</CONTACTID>
</CONTACT>
</Object>
<Object>
<Error>
<SourceKeyField>4</SourceKeyField>
<Object>CONTACT</Object>
<Code>-2147217833</Code>
<Message>The statement has been terminated. :
String or binary data would be
truncated.</Message>
<ExtendedInfo>System.Data.OleDb.OleDbException:
The statement has been terminated.
: String or binary data would be truncated.
at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult
(tagDBPARAMS dbParams, Object&
executeResult)
at
System.Data.OleDb.OleDbCommand.ExecuteCommandText
(Object& executeResult)
at
System.Data.OleDb.OleDbCommand.ExecuteCommand
18 of 218
(CommandBehavior behavior, Object&
executeResult)
at
System.Data.OleDb.OleDbCommand.ExecuteReaderInternal
(CommandBehavior behavior, String method)
at
System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
at
SLXConnectorWS.SLXService.ProcessRequest
(String ConnectionID, String XmlSchemaName,
String RequestXml, Boolean BatchTransactions,
Boolean UseCascadeSettings, String
encryptionID)
in
C:\Development\TCTools\SLX\SLXConnectorWS\SLXService.asmx.cs:line422
</ExtendedInfo>
</Error>
</Object>
<Object>
<CONTACT>
<SourceKeyField>5</SourceKeyField>
<CONTACTID>CDEMOA00006H</CONTACTID>
</CONTACT>
</Object>
</Operation>
</Operations>
</Response>
The SuccessCount property on the step will be set to 4, and the FailCount to 1. Two separate Convert XML to Recordset steps could be added to the task, one to deal with the successes and one to deal with the errors. In the success handling step, both fields from the CONTACT element could be mapped to a recordset.
This will result in a BPA Platform recordset containing 4 rows, with the source key field from the source data and the CONTACTID returned from Infor CRM. In the error handling step, all the fields from the Error element could be mapped to a recordset. This will result in a BPA Platform recordset containing 1 row, with columns corresponding to the elements within the Error element. Included is the source key field, which gives a way of tying up the original data with the OLE DB provider error.
About the Infor CRM Connector Web Service
Introduction
The Infor CRM Connector Web Service is the communication layer which enables the Infor CRM Connector to perform operations on data held in Infor CRM databases. It is the only component that directly connects to an Infor CRM database and always connects using the Infor CRM OLE DB driver, never by any other method. This ensures that Infor CRM business rules are adhered to.
Connection information (e.g. server name, database, login name/password) is passed to the web service from the Connector. For each step and task instance, the Connector is able to dynamically set the connection used by the Infor CRM OLE DB Driver.
The Web Service performs two vital roles:
- It is used by the Connector at step configuration time to create the Infor CRM object schemas
- It is also used by the Connector at task run time to pass data to and retrieve data from an Infor CRM database.
Schema Creation
The Connector works both at configuration and runtime by processing object schemas. These object schemas are obtained from the Web Service. When the Connector, as part of the Global Configuration, requests from the Web Service the available objects in Infor CRM, all the object schemas for the given Infor CRM database are created. For Infor CRM, the word “object” can be interchanged with “table” when referring to entities within the Infor CRM database.
The schemas are created automatically, one per Infor CRM object, by looking at the three tables in the Infor CRM database that store the information for all the available objects, fields and relationships (including extension tables and fields) – RESYNCTABLEDEFS, SECTABLEDEFS, and JOINDATA. Each record in RESYNCTABLEDEFS represents one Infor CRM object, whose fields are represented in SECTABLEDEFS and whose relationships to other objects are represented in JOINDATA. From this information, a detailed schema can be created for each object, which includes all fields for that object. Links to other objects are represented by creating a link to that object’s schema, where the link information in Infor CRM indicates that it’s a child object. The Connector is then able to “walk” the schema, and all the linked schemas, at configuration time in order to create its user interface.
Because all user defined fields and tables created through Infor CRM are stored in these three tables, it means that these are automatically picked up by the schema creation system. The extension tables and fields are created as any other Infor CRM object schema. The Infor CRM display names for objects and fields, which can be setup in Infor CRM to be whatever is required, are also brought into the schemas, so that the Connector can use these in the user interface.
The schemas also contain information about what operations are supported for the Infor CRM objects. All objects will support Get, Add, Update, and Delete, and so these operation types will be set in the schemas, meaning that the Connector will pick these up for display in the configuration user interface. Note, however, that some operations for some fields within an object will be restricted by security settings set for the Infor CRM user selected for the connection details. This is determined by the Infor CRM OLE DB driver at runtime.
All schemas are in industry standard XSD format.
Using SQL views through Infor CRM
In recent versions of the software, it is possible to create a view within the underlying RDBMS and expose that view in Infor CRM. This view will be brought into the Connector and therefore all operations in theory can be performed upon it. However, caution should be used when attempting anything other than a Get operation, as the exact behaviour that will happen when adding/updating/deleting through a view is dependent on how the view has been set up in the underlying RDBMS.
Data Processing
When a task containing an Infor CRM Connector step runs, the step itself performs all of the processing of data from other steps in the task and assembles an XML document containing all of the information that the Web Service needs to perform the operation on the object against which the step is configured. This information includes the data itself to be passed from other steps, connection details for the Infor CRM OLE DB provider, details of the database table and field names that are to be used (available from the schema information) and details of any filter links (with their data and condition values). The XML is then transmitted to the Web Service via an HTTP or HTTPS connection. The web service then parses the XML and performs the required operation by translating it into SQL statements which are then passed to the Infor CRM OLE DB provider. An XML document is then created which contains the response from the Infor CRM OLE DB Provider. This may be information about the requested object data (if the operation was a Get operation, for instance) or could be an error (if, for instance, a Delete operation was attempted on an object that could not be deleted). This XML is then returned to the Connector.
How Data is Processed for Each Operation
There are four operations possible when processing data from the Connector:
Add
When adding data to Infor CRM, we are able to take advantage of the system within the application to automatically create the new key field values for objects. The Infor CRM configuration tables tell us what the key field is for the object being added and also indicates whether or not this key field’s value will auto-increment to the next value when an object is added. If this is set to auto-increment we do not take any action as the OLE DB Provider itself will take care of automatically filling this value in when we insert the record.
If not, we call the Infor CRM OLE DB Provider function SLX_DBIDS which gives us the next key field value, which we then use when inserting the record.
When adding child object records (e.g. Address information for an account) we are able to use the new object key field value we obtained when inserting the parent object and can automatically use this as the value for the foreign key field when inserting the child object.
For each data object contained within the request XML, the Data Transfer Link information along with the key field information as outlined above is used to generate an SQL INSERT Statement.
Update
For each data object contained within the request XML, the Filter Link information is used to generate an SQL SELECT Statement. The recordset returned from this is used to power the update operation. SQL UPDATE statements are generated using the key field information in the recordset returned by the SELECT statement, along with the data transfer link values that have been set in that object within the request XML.
Delete
For each data object contained within the request XML, the Filter Link information is used to generate an SQL SELECT Statement. The recordset returned from this is used to power the delete operation. SQL DELETE statements are generated using the key field information in the recordset returned by the SELECT statement. We are have built in functionality to emulate the Cascade settings on the join information within Infor CRM, if this is selected – see Use Cascade Settings.
Get
For each data object contained within the request XML, the Filter Link information is used to generate an SQL SELECT Statement. The data returned is transformed into XML before being returned from the Web Service.
Transactions
The Infor CRM OLE DB provider supports transactions. This means that we are able, within the Web Service, to ensure consistency when processing data. For example, an update is required to fields in an object, and fields also within a child object – for example, a Ticket and a Ticket Solution for that ticket. There will be two SQL UPDATE statements generated, but these can all be performed within the context of a transaction, meaning that if the update to the Ticket Solution table fails for whatever reason, the whole transaction is rolled back including the update to the main Ticket table.
On top of this, the transaction setting from the step configuration is used to wrap either each individual top level record operation in its own transaction, or to wrap all top level record operations up within one transaction.
Data Security
All data passed from the BPA Platform server to Infor CRM is sent via our Web Service over HTTP, as XML data. This includes login details for Infor CRM. To secure this, the connection details are encrypted when they are transmitted from BPA Platform to our web service, then are decrypted within the Web Service before being passed to the Infor CRM OLE DB Provider. The encryption standard used is AES (Rijndael), using an asymmetric encryption algorithm. For customers who wish to encrypt all traffic going through the Web Service, HTTPS can be enabled for the Web Service. This will mean the customer having to have an SSL certificate installed on their IIS Server and having the Web Server configuration in IIS setup to use it. All data will then be encrypted.
All Infor CRM OLE DB connection details entered as part of the Connector configuration are stored encrypted in the BPA Platform store, using BPA Platform’s built in encryption.
Infor CRM Security
The Infor CRM OLE DB provider is the only method used to connect to an Infor CRM database and read/write data. This means that all security present in the OLE DB provider is used and also means that any improvements made by Infor to the OLE DB provider in future should be automatically used.
Currently, the security implementation in the OLE DB provider is NOT the same as the security in the Infor CRM client itself. It is important to understand the differences in the security mechanism available.
When the Global Configuration for the Connector is set, part of the information entered for each connection is the Infor CRM user and password to be used when connecting through the OLE DB provider.
The OLE DB provider will then take the security settings for this user into account when performing SELECT statements (analogous to performing a Get operation in a step). This means that, for instance, if an Infor CRM Connector step is configured to do a Get operation on an Account object, using a filter field that matches all Accounts in the database, only those accounts that the Infor CRM user defined for the connection used in the step has permission to see will be returned. Furthermore, if field level security is set on the Account table, any fields that the user does not have permission to view will be returned as NULL. This means that the Connector is able to take into account security set up in Infor CRM for the user configured for the connection when performing a Get operation.
The situation is very different for the remaining three operation types, however. When data is written to Infor CRM (Add, Update, or Delete operations), the OLE DB provider does not take into account any security restrictions set up for the Infor CRM user configured for the connection used in the Infor CRM Connector step. This means that even if the user has got the options to add or delete records set as denied in Infor CRM, through the Connector step these options are ignored as the OLE DB provider does not take them into account. Similarly, field level security is also ignored by the OLE DB provider.
An Infor CRM Connector step is therefore able to add, delete or update any object in the database, regardless of the Infor CRM user used in the connection configuration for the step. This is important to understand.
It is possible to create an Infor CRM custom security provider that is used to augment the security provided by the OLE DB Provider. The Connector’s use exclusively of the OLE DB provider means custom security provided by a plug in is taken into account when performing an operation.
The Infor CRM connection Read/Write password is supported by the tool. If a Read/Write password has been set on the connection from within the Infor CRM Connection Manager, then this can be used by the tool by setting it as an option in the Global Configuration for a connection.
SECCODEID
When adding objects through the Infor CRM Connector, the SECCODEID field has to be set for those objects that have this field. This would mean mapping a value to this field on every Step that adds an object. As a convenience, it is possible on the Global Configuration dialog box for a connection to set a default SECCODEID value that will be used wherever a SECCODEID field is present on an object that is to be added, if the step configuration itself has not defined that value through the use of a mapping from a data source. This is simply a mechanism to make adding objects a little easier for situations where the SECCODEID value will always be the same for every addition.
Cascading Deletes
When defining a table join in Infor CRM, the user sets up the cascade deletion rule for that join. This information is stored in the JOINDATA table and so can be picked up when the object schemas are created. This means that if a delete operation is performed on an object, the cascade setting can be taken into account when performing the delete operation; the related child objects can be processed according to the cascade setting set for the join in Infor CRM. However, the Replace option won’t be supported. A join with this option set will simply act as if it is set to the Clear option and the foreign key values will be set to NULL.
The OLE DB Provider itself does not offer this functionality, but the Infor CRM Connector can be configured to emulate this by turning on the option in the step configuration screen. If the cascade settings are not used, then any child records will simply be left alone (much like the Ignore option for a join in Infor CRM).
Synchronisation
The Infor CRM OLE DB provider implements synchronisation support. This means that add/update/delete operations performed through the Infor CRM Connector Tool will be automatically logged for synchronisation.
Attachments
Attachments in Infor CRM are represented in the Attachments table within the database. There is no Infor CRM
mechanism available for adding attachment files to the software from outside of the Infor CRM client; thus ensuring
that the attachments themselves are available for synchronisation. If it is necessary to add an attachment, the file will
have to be placed in the correct directory, and given the correct name, by other methods. The filename used can
then be mapped in the Infor CRM Connector through to a new Attachment record if necessary.
Supported Objects
The objects currently supported by the Infor CRM Connector Tool are provided within this section. Every object listed supports the following operations: CREATE, DELETE, GET, and UPDATE.
Where an item is defined as a ‘sub-object’, within the connector tool this item will appear at the bottom of the list within a selected object. All sub-objects are objects within their own right.
Account Object
This object has the following fields:
| Data Fields | Comments |
|---|---|
| ACCOUNT | |
| ACCOUNT_UC | |
| ACCOUNTID | |
| ACCOUNTMANAGERID | |
| ACCOUNTOPERATINGCOMP | Child object; see AccountOperatingCompany Object |
| ACCOUNTPRODUCT | Child object; see AccountProduct Object |
| ACCOUNTSUMMARY | Child object; see Accountsummary Object |
| ACTIVITY | Child object; see Activity Object |
| ADDRESS | Child object; see Address Object |
| ADDRESSID | |
| ADHOCGROUP | Child object; see Adhocgroup Object |
| Aka | |
| Alternatekeyprefix | |
| Alternatekeysuffix | |
| ALTERNATEPHONE | |
| AppID | |
| ASSOCIATION | Child object; see Association Object |
| ATTACHMENT | Child object; see Attachment Object |
| Businessdescription | |
| Campaignid | |
| COMPETITOR | Child object; see Competitor Object |
| CONTACT | Child object; see Contact Object |
| CONTRACT | Child object; see Contract Object |
| CREATEDATE | |
| CreateSource | |
| CREATEUSER | |
| CREDITRATING | |
| Currencycode | |
| Defaultticketseccodeid | |
| DESCRIPTION | |
| DIVISION | |
| DIVISIONALMANAGERID | |
| Donotsolicit | |
| EMAILTYPE | |
| EMPLOYEES | |
| Engineerid | |
| ERPTRADINGACCOUNT | Child object; see ERPTradingAccount Object |
| Externalaccountno | |
| FAX | |
| GlobalSyncID | |
| GM_PROFILE | Child object; see GM_Profile Object |
| HISTORY | Child object; see History Object |
| Importsource | |
| INDUSTRY | |
| Internalaccountno | |
| LastErpSyncUpdate | |
| Lasthistoryby | |
| Lasthistorydate | |
| Leadsourceid | |
| MAINPHONE | |
| MODIFYDATE | |
| MODIFYUSER | |
| NATIONALACCOUNT | |
| NOTES | |
| Notifydefects | |
| Notifyonclose | |
| Notifyonstatus | |
| OPPORTUNITY | Child object; see Opportunity Object |
| OTHERPHONE1 | |
| OTHERPHONE2 | |
| OTHERPHONE3 | |
| Parentaccountno | |
| PARENTID | |
| PrimaryOperatingCompID | |
| PromotedToAccounting | |
| REGION | |
| REGIONALMANAGERID | |
| Relationship | |
| REVENUE | |
| RMA | Child object; see RMA Object |
| Salesengineerid | |
| SALESORDER | Child object; see SalesOrder Object |
| Score | |
| SECCODEID | |
| SHIPPINGID | |
| Shortnotes | |
| SICCODE | |
| STATUS | |
| Subtype | |
| TARGETACCOUNT | |
| TERRITORY | |
| Tick | |
| Ticker | |
| TICKET | Child object; see Ticket Object |
| TOLLFREE | |
| TOLLFREE2 | |
| TYPE | |
| Userfield1 | |
| Userfield2 | |
| Userfield3 | |
| Userfield4 | |
| Userfield5 | |
| Userfield6 | |
| Userfield7 | |
| Userfield8 | |
| Userfield9 | |
| Userfield10 | |
| WEBADDRESS | |
| Webaddress2 | |
| Webaddress3 | |
| Webaddress4 |
AccountingSystemInfo Object
This object has the following fields:
| Data Fields | Comments |
|---|---|
| Accountcompany | |
| Accountingsysinfoid | |
| Create Date | |
| Create User | |
| Currentaccountingperiod | |
| Lastextractdate | |
| Modify Date | |
| Modify User | |
| Productdatafeed | |
| Transportcomponent | |
| Transportdatabase | |
| Transportname | |
| Transportpassword | |
| Transportshortname | |
| Transportusername |
AccountOperatingCompany Object
This object has the following fields:
| Data Fields | Comments |
|---|---|
| Accountid | |
| Accountoperatingcompid | |
| AppID | |
| Createdate | |
| Createuser | |
| LinkedDate | |
| Modifydate | |
| Modifyuser | |
| SharedId | |
| Status |
AccountProduct Object
This object has the following fields:
| Data Fields | Comments |
|---|---|
| ACCOUNTPRODUCT | |
| Accountid | |
| Accountproductid | |
| Accountproductparentid | |
| ActualID | |
| AssetCode | |
| AssetSystem | |
| AssetVersion | |
| ContactID | |
| CONTRACTITEM | Child object; see ContractItem Object |
| Createdate | |
| CreateSource | |
| Createuserid | |
| Environment | |
| Evaluation | |
| Location | |
| Modifydate | |
| Modifyuserid | |
| Notes | |
| Opportunityid | |
| OppProductID | |
| PrdDescription | |
| Productid | |
| ProductName | |
| Quantity | |
| Returned | |
| RMARECEIVEDPRODUCT | |
| RMASHIPPEDPRODUCT | |
| SalesOrderID | |
| Serialnumber | |
| TICKETACCOUNTPRODUCT | |
| TICKETACTIVITYITEM | |
| Userfield1 | |
| Userfield2 | |
| Userfield3 | |
| Userfield4 | |
| Userfield5 | |
| Userfield6 | |
| Userfield7 | |
| Userfield8 | |
| Userfield9 | |
| Userfield10 |
Accountsummary Object
This object has the following fields:
| Data Fields | Comments |
|---|---|
| ACCOUNT | |
| ACCOUNTID | |
| Create Date | |
| Create User | |
| Modify Date | |
| Modify User | |
| Parentid | |
| SECCODEID | |
| TYPE |
Actuserdef Object
This object has the following fields:
| Data Fields | Comments |
|---|---|
| ALT1_CONT | |
| ALT1_PHONE | |
| ALT1_TITL | |
| ALT2_CONT | |
| ALT2_PHONE | |
| ALT2_TITL | |
| CONTACTID | |
| CREATEDATE | |
| CREATEUSER | |
| Modify Date | |
| Modify User | |
| USER1 | |
| USER2 | |
| USER3 | |
| USER4 | |
| USER5 | |
| USER6 | |
| USER7 | |
| USER8 | |
| USER9 | |
| USER10 | |
| USER11 | |
| USER12 | |
| USER13 | |
| USER14 | |
| USER15 |
Activity Object
This object has the following fields:
| Data Fields | Comments |
|---|---|
| ACCOUNTID | |
| ACCOUNTNAME | |
| ACTIVITYATTENDEE | Child object; see ActivityAttendee Object |
| ACTIVITYBASEDON | |
| ACTIVITYID | |
| ALARM | |
| ALARMTIME | |
| App Id | |
| ATTACHMENT | |
| AttachmentCount | |
| AttendeeCount | |
| CATEGORY | |
| ChangeKey | |
| CONTACTID | |
| CONTACTNAME | |
| CREATEDATE | |
| CreateSource | |
| CREATEUSER | |
| DESCRIPTION | |
| DURATION | |
| Foreignid1 | |
| Foreignid2 | |
| Foreignid3 | |
| Foreignid4 | |
| Global Sync Id | |
| HISTORY | Child object; see History Object |
| LeadID | |
| LeadName | |
| Location | |
| LONGNOTES | |
| MODIFYDATE | |
| MODIFYUSER | |
| NOTES | |
| OPPORTUNITYID | |
| OPPORTUNITYNAME | |
| ORIGINALDATE | |
| PHONENUMBER | |
| PRIORITY | |
| PROCESS | Child object; see Process Object |
| PROCESSID | |
| PROCESSNODE | |
| PROJECTID | |
| RECURITERATIONS | |
| RECURPERIOD | |
| RECURPERIODSPEC | |
| RECURRING | |
| RECURSKIP | |
| RESOURCESCHEDULE | Child object; see ResourceSchedule Object |
| ROLLOVER | |
| STARTDATE | |
| Tick | |
| Ticketid | |
| TicketNumber | |
| TIMELESS | |
| TYPE | |
| USER_ACTIVITY | Child object; see UserActivity Object |
| Userdef1 | |
| Userdef2 | |
| Userdef3 | |
| USERID | |
| USERNOTIFICATION | Child object; see UserNotification Object |
ActivityAttendee Object
This object has the following fields:
| Data Fields | Comments |
|---|---|
| Account Id | |
| Account Name | |
| Activity Attendee Id | |
| Activity Id | |
| App Id | |
| Attendee Flag | |
| Create Date | |
| Create User | |
| Description | |
| EntitiyType | |
| Entity Id | |
| Entity Type | |
| Global Sync Id | |
| Is Attendee | |
| Is Primary | |
| Modify Date | |
| Modify User | |
| Name | |
| Note | |
| Phone Number | |
| Role Name | |
| SLX User Association Id | |
| Status | |
| Tick | |
| Time Zone | |
| Title |
Activityresourceview Object
This object has the following fields:
| Data Fields | Comments |
|---|---|
| Accessid | |
| Activityresourceviewid | |
| Allowadd | |
| Allowdelete | |
| Allowedit | |
| Allowsync | |
| Createdate | |
| Createuser | |
| Islocation | |
| Location | |
| Modifydate | |
| Modifyuser | |
| Name | |
| Resourceid | |
| Subtype | |
| Type |
ActivityTrackingEventHistory Object
This object has the following fields:
| Data Fields | Comments |
|---|---|
| ActivityName | |
| Activitytrackevthistid | |
| ActivityType | |
| Createdate | |
| Createuser | |
| Description | |
| EventDateTime | |
| EventOrder | |
| ExecutionStatus | |
| Modifydate | |
| Modifyuser | |
| Processinstancestatusid | |
| SecurityId |
