External Functions

External Functions are used for integrating with other systems, data sources and custom code. For example:

• Notify other systems of data changes in Digital-Clay. This includes calling external triggers, or sending e-mails and SMS messages via the web.
• Lookup and retrieve values from an external database or web site. This includes values inside XML and unstructured HTML pages.
• Calculate values using custom code. Program your own logic and algorithms, and return a value for storing in Digital-Clay.
• Export data in real-time into external data sources.

Note that for importing records into Digital-Clay, ClayIntegrator or Data Injection would be more suitable in most cases.

There are five types of External Functions:

• Database Function: This a custom coded function added to ClayCentral's database (e.g. a function created in SQL Server) that can be used in Digital-Clay fields, filters and queries, or simply called for notification purposes. Note that SQL Server database functions cannot be used to modify data or to call stored procedures.

• Database Stored Procedure: This a custom coded stored procedure added to ClayCentral's database that can be used only with automations for retrieving custom values, or for running database queries. They cannot be used inside Clay custom functions for queries or custom fields.

• Web: Call a URL with dynamic parameter values and optionally retrieve data from web sites containing HTML, XML or plain-text results. This is also used for REST API, and can be used for SOAP API as well by sending the raw SOAP data as a Document Template.

• Database: Retrieve values from an external database using an SQL query, or run an update/delete/insert SQL query with dynamic parameters.

• SOAP: Call web-services and external applications via a SOAP procedure call, and optionally retrieve values.

External Functions may be used in Digital-Clay in any of the following ways:

• Via a 'Set Field' Automation action for setting field values based on external sources and functions.

• Notifications/triggers can be configured using the 'Call External Function' Automation action.

• Multiple values may be retrieved from a single external call using the 'Set Fields' Automation action.

• (To be done) Automatically populate a drop-down based on function results inside User-Generated Automation events.

• Database Function only: Use the external function inside Digital-Clay custom functions for use as custom fields, filters, or columns in lists and analysis queries. Database Functions will appear in the Custom Function function drop-down only if they return a single value.

All External Functions may be configured to accept up to 20 parameters. Each of these parameters must then be mapped to values or fields when using them in any of the above scenarios.

Purpose of the Table setting:

• To be able to map Digital-Clay fields to static parameters (see below). Note that dynamic parameters may be mapped to fields when building a Custom Function.
• To restrict with which table this function can be used. For example, when adding a Set Field (External Function) automation action, only the External Functions that match the Automation base table will be listed.

Note that this setting is disabled for Database Functions for now, since static parameters are not allowed with this type of function.

There are two types of parameters:

These are defined in the External Function and then mapped to actual values, fields or functions when they are used in Automation actions etc.

This allows the same External Function to be used with multiple tables and dynamic values. For example, a generic ConvertCurrency function can be used in multiple tables wherever a currency needs to be converted if the Table is set to 'Any', and the relevant parameters are not static.

External Functions are currently limited to 20 dynamic parameters.

In Web Functions, it is also possible to send Document Templates as parameters. See below.

These are defined in the External Function along with a static value or mapped field. These parameters will not appear in Automation actions and the parameters will be sent behind the scenes along with their values.

• If a static value is set for the parameter, the value will always be the same. This is useful when additional information such as a userID or command option must be sent to the function, and the values are always the same for every function call.

• If a Digital-Clay field is mapped to the parameter, the value will always be retrieved from the same field but the value will change according to the field value. This is useful when the function is always used for the same table and fields. It is also necessary when tens or even hundreds of fields must be sent to the function (for example, to export a complete record).

Unlimited static parameters may be added to an External Function.

External Functions are further categorized according to the amount of parameters they return:

• None: These functions are used for notification purposes only and for triggering external systems.

• Single: These functions return a single value. Note that Database Functions will appear in a custom function drop-down only if they return a single value.

• Multiple: These return multiple parameters. I.e. a function may return a first name, last name and phone number as separate parameters, each of which may be stored in a separate field by mapping each parameter to its field.

If the function returns multiple values for each parameter, only the first value is retrieved, unless it is for populating a drop-down.

By default, External Functions may be run by anyone. To restrict the use of the function, remove some of the Roles in the External Function Roles listbox.

Note that when using External Functions in Automation actions, the function will only be called if the Automation Owner has the appropriate permissions.

If any errors or timeouts are encountered while attempting to call the External Function, they will be added to the Automation Log as usual. If the External Function is used in a custom function by a user, the user will receive the error in a message box.

To track errors and handle them automatically by Automation, you must use the Set Fields (External Function) action (not the Set Field action). This action allows you to set a field value when an error is thrown. Based on this 'error field' change event, you may configure Automation Event Handlers to send a message/email, or try to set the field values using alternative sources and functions.

A Database Function must be created in the ClayCentral database (e.g. an SQL Server T-SQL stored procedure). Note that MDB databases do not support stored procedures since VBA functions cannot be called from external SQL queries.

Since the Database Function exists in the ClayCentral database, it is the only External Function that can be used in Digital-Clay Custom Functions for custom fields, filters, indicators and query columns.

A Database Function may return either a single parameter for use in queries and fields, or no parameters for use as a trigger/notification.

Sample function for counting the amount of spaces in a text value:

CREATE FUNCTION countSpaces (@var nvarchar(4000)) RETURNS INT AS 
BEGIN
 DECLARE @pos int; DECLARE @num int;
 SET @pos=1; SET @num=0;
 SET @pos=CHARINDEX(' ',@var,@pos);
 WHILE @pos>0
  BEGIN
   SET @num=@num+1;
   SET @pos=@pos+1;
   SET @pos=CHARINDEX(' ',@var,@pos);
  END;
  RETURN @num;
END

Note that to call this function, its internal function name must typically include its owner, e.g. The Internal Name should be “dbo.countSpaces”.

Also note that SQL Server functions cannot modify data or call stored procedures.

Identical to 'Database Function' above with the following differences:

• It cannot be used in Digital-Clay custom functions for queries and custom fields, only within automations.
• It can be used to run SQL queries, including update queries. It can also call other stored procedures.
• Static parameters (values and fields) can be added to this external function. This is convenient for statically mapping multiple fields to variables.
• Although stored procedures can theoretically return more than one value, this is currently limited to a single return value.

The same sample function for counting the amount of spaces as above, only this time created as a stored procedure in SQL Server:

CREATE PROCEDURE countSpaces @var nvarchar(4000), @ret INT OUTPUT
AS
 DECLARE @pos int; DECLARE @num int;
 SET @pos=1; SET @num=0;
 SET @pos=CHARINDEX(' ',@var,@pos);
 WHILE @pos>0
  BEGIN
   SET @num=@num+1;
   SET @pos=@pos+1;
   SET @pos=CHARINDEX(' ',@var,@pos);
  END;
  SET  @ret=@num;

Web Functions consist of:

• A URL, including the cgi/php/asp page that will accept the request parameters. (e.g. http://www.google.com/search). Note that Digital-Clay also supports SSL connections - simply enter “https:” as the URL protocol.

• Whether to send the parameters as an HTTP GET, POST or 'Template' request. This depends on what the server supports, but keep in mind that GET requests may be limited in size.

• The 'Template' send format means that the results of Document Templates may be sent as the body of an HTTP request. For example, a Document Template may be created in XML format to send XML data with dynamic field values, XML tables, or a SOAP request. If 'Template' is selected, a Template drop-down appears for selecting the template.

• Note that templates may also be sent as parameters in a normal POST request. E.g. one of the parameters may be an XML structure with dynamic values. To do this, select POST, and add a parameter of data type 'Template'.

• Optionally, an extra HTTP header may be added to the HTTP request. For example, a 'SOAPAction' header.

• Parameters (dynamic and static): Add these to map Digital-Clay fields and values to web parameters. Parameters are URLEncoded before sending.

• Return Format & Parameters: See below.

With this setting, all of the results are returned as a single parameter only. If the page is an HTML page, the complete page will be returned including HTML source code. This is typically useful only when the web page is designed to return values for applications rather than web browsers.

If the results of a URL are in XML or strict HTML format (i.e. all tags are closed and strict syntax is maintained). Then this format is preferred over HTML because it allows the program to retrieve results much more precisely.

To extract values from an XML document, Digital-Clay asks for an XPath statement per Return Parameter. Using XPath, you can specify exactly which node to retrieve, including its location in the document, its exact path and parents, attribute filters, the amount of nodes to skip, etc. For a quick primer on XPath, see http://www.w3schools.com/XPath/xpath_syntax.asp.

Since HTML results are typically unstructured and may contain dynamic content and structure, extraction is done by finding tokens before and after the text. To fine-tune and get exact results, tokens can be located in stages. To demonstrate this, let's use an example:

In this example, the following HTML source code is contained somewhere inside a larger HTML page:

Results for your query:<BR>
<div class='colheaderleft'>Name</div><div class='colheaderright'>Phone</div>
<div class='colvalueleft'>John Adams</div><div class='colvalueright'>888-654-3210</div>

…and we want to extract only the phone number. Here are some ways to approach this:

• If we are sure that the text '<div class='colvalueright'>' will only appear once in the page, then we can search for that as the first token, and search for </div> as the ending token.

• If we suspect it may appear more than once, we can narrow it down by looking for 'Results for your query' as Stage 1 in order to make sure we are in the right vicinity, and then find the '<div>' tag in Stage 2.

• If the div tag may also contain dynamic attributes or the 'class' value keeps changing, Stage 2 can look for the fourth (skip 3) '<div' and '</div>' tokens, and Stage 3 can search for the '>' token which will find the end of the '<div … >' tag no matter what attributes it contains.

• Alternatively, you can search for the text 'colvalue' and skip 1, or use the text 'Phone' in Stage 1 and then find the right tags in subsequent stages, etc. etc.

If you have done your best to find specific tokens but the end result may still contain unwanted HTML code, leave the “Remove HTML code” checkbox checked.

To be notified of a failure to find the tokens you entered, check the 'Treat token not found as error' setting. Otherwise it will only skip the parameter and not set its values in Digital-Clay.

To be done…

To be done…