Data

InRule provides a number of ways for making internal and external data structures available to rule conditions. All data store mappings exist under the Data folder for the rule application.

The following sections describe the various methods for defining these data structures so they may used as input to an expression:

• Data Folders
• Database Tables
• Inline Tables
• SQL Queries
• XPath Queries
• Linked XML Documents
• Inline XML Documents
• Inline Value Lists
• Query Value Lists
• Examples

Creating a data item

Any of the data items listed above can be added to a Data Folder using the following procedure:

1. Click on Data in the Navigation Pane
2. Click on the Add button and select the type.

The navigation pane and edit pane will update to reflect the added data item.

Data Folders

The "Data" folder provides a way for you to organize your tables, lists, queries, and document data stores.

Subfolders may be created under the "Data" folder to help organize these data items.

Tables

A table is a set of data elements (values) that is organized using a model of vertical columns (which are identified by their name) and horizontal rows. A table has a specified number of columns, but can have any number of rows. InRule provides two types of tables: Inline, where the table is defined within a rule application, and Database, where the table exists in an external database that you have referenced in End Points.

• Database Table
• Inline Table

Database Table

A Database Table is a link to a table in an external database such as SQL Server, Oracle, DB2, etc. Rules can use data functions in conjunction with Database Tables in their associated expressions for data lookups.

For example, as part of a line item total calculation (e.g. price * quantity), you may want to lookup the value of the price of an item based on its product key. Your database connection could link to an Item table and a Price calculation rule could perform the lookup to return the price. The Price calculation may look like this:

TableLookup("Item","Price","ItemID", ProductKey)

Here, Item is the Database Table, Price is the column in Item to return to the calculation, ItemID is the column to filter on, and ProductKey is the rule application element to filter with.

The following is a description of the Database Table form:

Name

Specifies the logical name to use when referring to the table within the rule application.

Table Name

Specifies the physical table name within the database into which you are linking the table.

Database

Specifies the Database Connection End Point to use for the database where the table is located. See Setting up End Point, Database Connection for information on how to setup a Database Connection.

Cache(sec)

Specifies the time in secs for which the rule engine will store( or cache) the look up data before it does the next lookup. This setting is used only by functions like TableLookup and Lookup(). It is not used by SqlQuery against this table.

Inline Table

An Inline Table is a table structure with data that is stored in the rule application. Once defined, rules can use data functions in conjunction with InLine Tables in their associated expressions for data lookups.

InRule provides a schema editor for defining the columns that describe the table. After your table schema is completed, you can enter or modify data directly in the table edit grid.

Alternatively, you can define your Inline Table by importing the structure and data from an external data source.

The following is a description of the Inline Table Edit form:

Name

Specifies the logical name to use when referring to this table within the rule application

Edit Schema

Specifies the structure of the table. Clicking on this button will display a dialog box for defining the structure. In the screen shot below, you add columns by clicking on the button. Once you enter the name and data type, click on the Update button get the column information in the list. Use the up and down arrow to move columns. Finally, use the button to delete a column.

Import from Database

Specifies the external data source information from which to import the structure and data of the Inline Table. Clicking on the Import button will display a dialog box for importing the structure and data. In the screen shot below, you specify a standard OLEDB connection string and the SQL SELECT statement from which to define the table.

Import from Clipboard

Often data may exist in a tabular structure somewhere else in your environment. InRule allows the importing of data from applications such as Microsoft Excel, Access, Word, etc. through the Import from Clipboard mechanism.

Example:

1. Highlight the rows/cells in Excel that you want to import:
   
2. Create a new Inline Table, click the Import button, select the appropriate options:
   
3. Click the OK button and the data will be imported into the Inline Table.

Notes

You should not attempt to create/import tables with more than 1000 rows when working with Inline Tables. Loading very large tables can lead to significant memory usage and cause sluggish performance within the Rule Application.

Value Lists

A value list is a lookup list that is often used to populate drop down lists. Any field may have a Value List associated with it, which link a list of possible values for the field. The Value List can be used to create “LimitToValueList” Constraints, provide additional "has a value of" Authoring Templates, drive Decision Table Conditions, or populate drop-downs for dynamic UIs.

The Value List is essentially a two column table that contains a Key Value column and optional Display Value column. The Display Value is used for representing the Value List item in authoring, while the Key Value is used for the underlying comparison. For example, a State Value List could have "TX" as the Key and "Texas" as the Display Value.

• Value Lists are stored in the Data folder. "Go" navigates to the value list in the data folder.
• An existing Value List may be selected, or a new one created here.
• Any Value List (except for a Query Value List having one or more "Passthru" parameters) may be used when authoring a Business Language rule to pick a value.
• Any Value List may be directly queried from a syntax or Business language expression for value existence or display name lookup from value.

Notes:

• When one or more XSD Schemas are in use, any Enumeration facets are automatically imported as Field Value Lists with the LimitToValueList constraint set.
• When one or more .NET Assembly Schemas are in use, the Enums associated with any properties or fields are imported as Field Value Lists with the LimitToValueList field constraint set.

InRule provides two types of value lists:

• Inline Value List, where the list is defined within a rule application
• Query Value List, where you provide a source as to where the data resides and criteria as to how to select the data

Inline Value List

An Inline Value List is a lookup list (value, with optional display text) with data that is stored in the rule application. The Value List may be associated with any Field.

Once associated with a Field, a "Limit to Value List" constraint is typically added to enforce legitimate runtime values.

Inline value lists are automatically imported from external XSD schemas having "Enumeration Facets", and from external .NET Assembly schemas having enum types. The "Limit to Value List" constraint is automatically added on import.

A value from the value list may be selected while using Business Language Authoring.

Lookups may be performed against a value list by querying the value list directly to find a display name corresponding to a value.

The following is a description of the Inline Value List Edit form: 

Name

Specifies the logical name to use when referring to this Value List within the rule application

Value

Value - must be unique.

Display Name

Optional Display Name - must be unique when specified.

Import from Database

Specifies the external data source information from which to import the structure and data of the Inline Value List. Clicking on the Import button will display a dialog box for importing the structure and data. In the screen shot below, you specify a standard OLEDB connection string and the SQL SELECT statement from which to define the table.

NOTE: If more than two columns are selected, only the first two will be imported as the Value and Display Name.

Import from Clipboard

Often data may exist in a tabular structure somewhere else in your environment. InRule allows the importing of data from applications such as Microsoft Excel, Access, Word, etc. through the Import from Clipboard mechanism.

NOTE: If more than two columns are selected, only the first two will be imported as the Value and Display Name.

Example:

1. Highlight the rows/cells in Excel that you want to import and copy to your clipboard:
   
2. Create a new Inline Value List, click the Import button, and select "Tab-delimited from Clipboard" option.
   
3. Click the OK button and the data will be imported into the Inline Value.
   

Query Value List

A Query Value List is a lookup list (value, with optional display text) derived by querying an Inline Table or external database table. The Value List may be associated with any Field.

Once associated with a Field, a "Limit to Value List" constraint is typically added to enforce legitimate runtime values.

A value from the value list may be selected while using Business Language Authoring, unless "Passthru" is selected for one or more parameters, in which case the value list is available only at runtime.

Lookups may be performed against a value list by querying the value list directly to find a display name corresponding to a value.

The following is a description of the Query Value List Edit form:

Name

Specifies the logical name to use when referring to this Value List within the rule application

List Source

Query source - may be any Inline Table or SQL Query.

Value

Specifies column returning value.

Display Text

Optionally specifies a column returning display text.

Parameters

For SQL Queries having one or more parameters, the value for each parameter is specified here.

Either a constant expression may be entered for each value, or "Passthru" may be checked.

When "Passthru" is checked, the value is specified by each referencing field's value list specifier - this value may be any valid expression in the referencing field's context.

If parameter direction is Output or ReturnValue, parameter value is not relevant.

Test Query Result

By clicking on the "Refresh Query" button, irAuthor will attempt to execute the SQL Query that this value list is based on and return the results.

Queries

A query is scripted logic that is used to get specific information back from a database or XML document. InRule provides two types of queries:

• SQL Query
• XPath Query

A SQL Query

A SQL Query provides the capability to execute a database command that returns a single value to the rule engine. You can use this feature to access stored procedures, views, and tables, from either an external database or from an Inline Table stored in the Rule App.

To execute a SQL Query as an action, define an Execute SQL Query action, where you specify an expression for each SQL Query parameter, and an optional field or variable to assign the result to. The SQL Query output may be mapped to a value-type Field or Variable, Complex / Entity Field or Variable, or Collection.

To get a value-type result from an SQL Query in an expression, use the SQL Query name as a function and provide the parameters defined in the SQL Query as parameters to the function:

GetPartDesc(PartID)

The expression above will access the SQL Query called GetPartDesc and pass the value of the PartID from the rule application. The rule engine will format the appropriate SQL command against the database and return the result to the rule condition.

The following is a description of the SQL Query Edit form:

Name

Specifies the logical name to use when referring to this query from within the rule application

Source

Specifies whether to query against an external database or inline table.

Command Type

Specifies the type of SQL Command to use: Select Rows, Select Value, or Execute.

Return Type

Specifies the type of data to return:

• If the Command Type is Execute: Modified Row Count.
• If the Command Type is Select: Rows.

Cache

Specifies the length of time the rule engine will store (or cache) and use the queried data sets before it executes the same query against the database. (Applicable only for Select queries). A query result will be cached for each unique combination of parameter values (see below). A value of 0 disables caching.

Parameters

Specifies the values to use in the parameterized query or stored procedure execution. In the grid, enter the name and data type of each parameter. Note that each parameter name must start with "@".

The Data type must be one of the following: Boolean, Integer, Number, Text, Date or DateTime. NOTE: Not all endpoint types support the listed data types. There may be situations where a query may produce an error when the data types don’t line up. For example, using a Boolean parameter on an Oracle database endpoint will result in a ‘Value does not fall within the expected range.’ error because Oracle does not use Boolean values. Additionally, when comparing character data from database endpoints, rule authors should be aware that the database may have different case sensitivity settings. For example, default MS SQL Server installations have case insensitive collations (ñ = Ñ, ss= ß), while default Oracle installations have case sensitive collations(ñ != Ñ, ss != ß).

ParameterDirection must be one of the following: Input, InputOutput, Output, ReturnValue

Input -- The parameter is an input parameter.

InputOutput -- The parameter is capable of both input and output

Output -- The parameter is an output parameter.

ReturnValue -- The parameter represents a return value from an operation such as a stored procedure, built-in function, or user-defined function.

While using functions in sql query, parameter type must always be input and command type must be SELECT and while using stored procedures the command type must be EXECUTE.

Query

Specifies the SQL to use when accessing the database.

In the example below, the rule engine will execute the query using the parameter values provided by the calling rule condition.

SELECT title FROM titles WHERE title_id = @PartID

In this case, the parameter value supplied by an Execute SQL Query or calling rule condition's function call for @PartID will mapped into the statement.

Note that string parameter values are automatically quoted in the mapping processes.

An inline table essentially a ADO.Net DataTable. They utilize the SELECT method for SQL operations and use the following reserved words, operators, and comparison expressions: 

Modulus may be the only unfamiliar term listed here. Modulus performs division, dividing the first digit by the second digit, but instead of returning a quotient, a "remainder" value is returned instead.

Below are few more examples of SQL queries using stored procedures for different parameter directions

XPath Query

An XPath Query provides the capability to execute an XPath query against XML data. You can use this feature to query either an xml file from the file system or from an Inline XmlDocument stored in the Rule App.

To execute an XPath Query as an action, define an Execute XPath Query action, where you specify an expression for each XPath Query parameter (if any parameters), and an optional field or variable to assign the result. The XPath Query output may be mapped to a value-type Field or Variable, Complex / Entity Field or Variable, or Collection.

The XMLLookup() function may be used within expressions to return a single value, as an alternative to the Execute XPath Query action.

The following is a description of the XPath Query Edit form:

Name

Specifies the logical name to use when referring to this query from within the rule application

XML Document

Specifies whether to query against a linked (external) XML file or an Inline Xml Document.

Parameters

Specifies the values to use in the parameterized query. In the grid, enter the name of each parameter. Note that each parameter name must start with "$".

Query

Specifies the XPath query to use.

In the example below, the rule engine will execute the query using the parameter values provided by the calling rule condition.

//Benefit/[@PlanType=$TypeofPlan]

In this case, the parameter value supplied by an Execute XPath Query action will be mapped into the statement.

XML Documents

InRule provides two types of XML Documents:

• Linked XML Document
• Inline XML Document

Linked XML Document

A Linked XML Document is a link to an external XML Document on your file system. An XML Document Path end point is required to define the location of the document on your file system. Rules can use functions in conjunction with XML Documents in their associated expressions to access data in XML Documents.

The primary function that accesses an XML document for values is XMLLookup. To use this in a rule expression, you supply the name of the XML Document, the XPATH, and a default value in the case that your XPATH query does not return a value.

For example, let's say you had an XML file that contained a list of territory descriptions by territory number. To get the description out of the file using the XMLLookup function, you might write an expression like the following:

XMLLookup(TerritoryXML, "//Definition/[Number=10]/Text1", "No Desc Found")

The following is a description of the XML Document Edit form:

Name

Specifies the logical name to use when referring to this document within a rule expression.

XML Document Path EndPoint

Specifies the XML Document Path endpoint where the default location of the XML document is stored.

Inline XML Document

An Inline XML Document is a XML Document with data stored in the rule application. Rules can use functions in conjunction with XML Documents in their associated expressions to access data in XML Documents.

The primary function that accesses an XML document for values is XMLLookup. To use this in a rule expression, you supply the name of the XML Document, the XPATH, and a default value in the case that your XPATH query does not return a value.

For example, let's say you had an XML file that contained a list of territory descriptions by territory number. To get the description out of the file using the XMLLookup function, you might write an expression like the following:

XMLLookup(TerritoryXML, "//Definition/[Number=10]/Text1", "No Desc Found")

The following is a description of the Inline XML Document Edit form:

Name

Specifies the logical name to use when referring to this document within a rule expression.

Inline XML

Displays the inline XML in the text box for editing or viewing.

REST Operation

A REST Operation sits in between a REST Service Endpoint and an Execute REST Service Action to define the key elements of a REST Service call. It is similar in many respects to a parameterized database query.

The following is a description of the REST Resource form:

Name

Specifies the logical name of the REST Resource to which Execute REST Service Actions will refer to within the rule application.

REST Service

Specifies the REST Service which contains the Root URL and Authentication Type information.

Operation Inputs

Input parameters to be specified by the calling action. These can be tokenized for use in the URI template, Header values and Body.

Verb

Available actions to perform:

• GET
• POST
• PUT
• DELETE

URI template

The portion of the URI containing the required resources and parameters. This will be appended to the REST Service Root URL at runtime. The values can be tokenized so that runtime values can be passed from the action. See the screenshot for an example where the $ token surrounds the Rating input parameter. If the URI template requires a dollar sign you can use a double dollar sign ($$) to represent that.

Body Format

Applicable only when using the Post or Put verbs. Current available formats are:

• XML
• JSON
• Forms Encoded

Body

Used only when using the Post or Put verbs. Additional information needed by the resource to complete the operation. You can also take advantage of tokenized parameters that have been added to Request Inputs. See the screenshot for an example where the $ token surrounds the Rating input parameter. If the URI template requires a dollar sign you can use a double dollar sign ($$) to represent that.

Wait for response

Specify whether the engine should wait for a server response or continue execution without waiting. TRUE by default. Set to FALSE when executing a "fire and forget" request.

Validate status codes

Specify whether or not the engine should check the HTTP response code against the Valid Status Code range.

Valid status codes

Specify a range of values that you want to trap with an exception To halt execution after the first exception, please use the "Halt" setting located under Settings.

Number of retires

The number of retries to attempt before responding back that the resource is unavailable. Current available choices are:

• 0
• 1

Timeout (in seconds)

This is the amount of time for which the rule engine will retain data. If the resource is used more than once during execution with the same input parameters, then subsequent calls to the REST Service will use the cached results. This avoids unnecessary calls to the the REST Service and can improve performance.

Headers

Any additional information requested or needed by the resource such as custom Authentication method data. You can also take advantage of tokenized parameters that have been added to Request Inputs.

Examples

Rule authors often have a need to work with data that is not available in a rule applications entity structure. For example:

• An airline might want to run a promotion that the first 500 frequent flyers that buy tickets for travel in June will be entered in a drawing for a free trip to Hawaii.
• When a frequent flyer purchases a ticket the rule engine will need to evaluate if the person is a frequent flyer and if the flight is in June before going to a database to see if any of the 500 spots are still available.
• If the flyer does meet the criteria and the lookup reveals that there are still drawing spots available, then the database should be updated so that the customer is added to the drawing list.

This is a fairly simple example but it becomes clear there is often a need to combine frequently changing business logic with database access. The purpose of this article is to expose you to some of the various ways that can be accomplished in irAuthor with actual examples and references to the relevant help-file articles.

About Accessing Data

InRule allows you to access data in a variety of ways over-and-above what’s available simply by passing data into the rule engine from a calling application. This functionality was specifically designed to let you:

• Access database data conditionally based on rules for use in calculations or other rules
• Load data into entities and collections
• Control data in the same place you author rules within a spreadsheet-like grid
• Add, delete or update data directly from rules

Like much of the functionality in irAuthor, the data access functionality is intended to be setup by technical users and integrated into rules by business analysts.

It should be noted that InRule does an excellent job of storing table information for codes, IDs, or other information commonly tied to business logic. Because inline tables and value lists are stored, managed, and version enabled in InRule, users have less of a need to interact with database administrators to create tables, change columns, or version data against effective dates. This is especially apparent in larger organizations.

The following sections cover in detail some of the scenarios in which users would access data.

• Getting Data Using a Table Lookup
• Getting Data Using a SQL Query
• Using A List of Valid Values in Rules and Field Validations
• Working With Data Through a Web Service
• Working With Data Through a .NET Method

Using a Value List in Rules and Field Validations

irAuthor provides various features around a concept called a value list. Values lists are intended to be linked to fields that should have a distinct a set of values. These fields might show up in your application as drop down or list boxes. Values lists are intended to allow rule authors to do the following:

• Evaluate field(s) in a rule without having to worry about spelling and capitalization
• Set the value of a field by selecting from a set of valid values
• Easily validate fields by verifying that a value entered by a user is in the value list

Value lists are simply a list of values that are stored in the ruleapp or come from a database. They can optionally have descriptions associated with the values which is useful if the values are codes, abbreviations etc.

There are a variety of ways value lists can be used within irAuthor. The following examples use a value list of United States state codes that has been associated with the State/Province field:

Using a value list to evaluate a field:

Using a value list to set the value of a field:

Setting up a Constraint (field-level validation) using a value list:

Getting Data Using a Table Lookup

A table lookup is an excellent way to pull a value from a data table into a rule or calculation. They can be used with data stored in your rule application (Inline Table) or in a database table. Inline Tables work particularly well for relatively small tables (e.g. under 500 rows). If the table is large (e.g. a customer table) it would be better to use a SQL query.

As an example maybe a rule author is writing some rules to calculate shipping for an order and wants to see if a customer’s loyalty status qualifies the order for free shipping. The author might have a table that looks like this:

Based on a customer’s status they want to get the FreeShipping value on the right. An easy way to do that is to return that value to a TRUE/FALSE (Boolean) calculation called Free Shipping.

Example of a table lookup in Business Language:

Example of a table lookup in Syntax:

Even though this example uses a calculation you can evaluate the result of a table lookup in a rule expression or action.

Note: Table lookups require a database table (along with a connection to a database) or inline table to be setup in the rule application.

Getting Data Using a SQL Query

Using a SQL query within rules and calculations is a powerful way to combine database functionality with rules. This functionality was designed for you to:

• Lookup and evaluate individual values, records or sets of records from database tables
• Load database data into entities and collections
• Modify database data from rules via stored procedures or transactional queries

Like other types of functionality in InRule once the SQL query and database connection is setup by a technical individual, it can be relatively easy for a business analyst to use the query within rules and calculations.

In this example you’ll see a different way to perform the lookup that was performed in the previous TableLookup example. There are two reasons to use a SQL query over a Table Lookup for this scenario:

• The rule authors have little or no understanding of database concepts and/or the underlying database table(s)
• The underlying table is relatively large so using a SQL query may yield better performance

To recap the scenario from the previous example, you want to see if an order qualifies for free shipping based on the customer’s loyalty status by looking up the FreeShipping flag in this table:

If a SQL query called GetFreeShippingStatus has been created in the rule application, the rule author could use it in a rule or calculation. SQL queries are often setup to take parameters to narrow the results. Following is the query for this example that takes one parameter:

Example of SQL query in business language

Example of SQL query in syntax

This example uses this SQL query:

Click on these links for more information on setting up SQL Queries and Database Connections

Getting Data Using a Web Service

Many times the data that you want to use is not available directly from the database. Instead it is made available to applications through a web service. InRule provides robust functionality for interacting with web services right from the rule authoring environment. In irAuthor a technical user generally sets up the connection to the web service. Then less technical users can map data into and back out of the web service.

Following is an example showing how a web service is used to set the value of the sales tax amount on an order:

Input mappings that show the subtotal of the order and the State/Province code are used to calculate the sales tax amount:

Output mappings that show the sales tax amount on the order will be set with the return value from the web service:

Getting Data Using a REST Operation

Sometimes a rule author will want to access data from external web sites that have exposed data through REST based services. Typically web sites will use REST when they want to have as broad a reach as possible, allowing people to use simple HTTP verbs to manage data.

InRule supports being able to retrieve data through the use of a REST Operation.

This example will use the freely available test OData endpoint at http://services.odata.org/V4/OData/ OData.svc. The test OData endpoint exposes the following EntitySets:

For this scenario, we will walk through setting up a connection to this OData endpoint and consume it via business rules.

Adding a REST Service

Adding a REST Operation that will query products by rating

Calling a REST Service using the Execute REST Action function

1. Select "Execute REST Service" from the Right Click "Add" -> "Actions" menu on a Ruleset
2. Provide an Expression for each input to the REST Operation and assign the result of the call to either a text variable or a  text field

Looking up a specific value from the Execute REST Action response

Getting Data Using a .NET Method

Many organizations use a “.NET layer” to handle the communication between applications and their databases. For those situations, rule authors can use functionality in irAuthor that allows them to execute a .NET method. irAuthor provides the ability to setup user-friendly business language that can be used to execute the .NET method. In that way technical users are able to provide business- user-friendly access to traditionally technical functionality.

InRule supports being able to setup references to static and instance methods.

This example will use the previous example of determining if a customer gets free shipping based on their loyalty status by performing a search of this table via a .NET method:

For this scenario, once a technical user has setup the reference to the method, they can change the template text for the method to make the method reference more useable. Following that, a business user can use the method reference in the following ways:

.NET Method call in Business Language

.NET Method Call in Syntax

.NET Method Call using the Execute Method Action