Difference between revisions of "Creating a Databox"

From Keyfax Wiki
Jump to: navigation, search
(Created page with "Creating a Databox Select Databoxes from the Entities menu to open a new tab. The Navigation Pane - shows the Databox Selection, containing existing Databoxes organised i...")
 
(→‎SQL Query)
 
(17 intermediate revisions by the same user not shown)
Line 1: Line 1:
−
Creating a Databox
+
==Creating Databoxes==
  
−
Select Databoxes from the Entities menu to open a new tab.
+
Select '''Databoxes''' from the entities menu to open a tab to manage your databoxes. The screen is divided into three sections. The navigation pane, menu bar and the editing pane. You can create a new databox by selecting a type from the list in the editing pane.
−
 
−
 
 
−
The Navigation Pane - shows the Databox Selection, containing existing Databoxes organised into groups and then listed alphabetically.
 
−
 
 
−
Selection:
 
−
 
 
−
Filter - type in characters to search for Databox names with the specific text. As you type into the Filter the list is updated with Databoxes matching your Filter criteria selected.
 
−
 
 
−
To open a category to view the Databoxes, click on  to the left of the category title.
 
−
 
 
−
The Editing Pane – displays the different Databox types:
 
−
 
 
−
Databox Type
 
−
 
 
−
When the Databox page first appears, you can create a new Databox by selecting the type. You can choose to to ignore this and select an existing Databox from the navigation pane.
 
−
You can create a new Databox at any time by clicking on the  icon, where you will be presented with a list of Databox types types in the Editing Pane (see Previous Sections for an explanation of each Databox type). The options are:
 
−
 
 
−
 
 
−
 
 
−
The Editing Pane  contains the Databox Properties, where you can define specific aspects of the Databox.
 
−
 
 
−
Databox Properties
 
−
 
 
−
 
 
−
 
 
−
--------------------------------------------------------------------------------
 
−
 
 
−
Script Data
 
−
 
 
−
 
 
−
 
 
−
 
 
−
 
 
−
Group - when displayed in the Selection List in the Navigation Pane Databoxes are arranged into groups. Select a group from the drop down list to add the Databox to an existing group or type in a new group name to create a new group.
 
  
−
Name – a descriptive name for the Databox. Use a meaningful description to identify the Databox in relation to its purpose for ease of recognition.
+
[[File:databoxes41.png]]
  
−
Empty bookmarks allowed – check this box if the Databox can be used in a task or message without being required to hold a value.
+
==Databox Types==
  
−
Description - a short description of the Databox. This is displayed in the Databox Selection list in the Navigation Pane and is searchable using the filter.  
+
When the Databox page first appears, you can create a new Databox by selecting the type. You can choose to to ignore this and select an existing databox from the navigation pane.
 +
You can create a new Databox at any time by clicking on '''Add''' on the menu bar, where you will be presented with a list of databox types types in the editing pane The options are covered below.
  
−
Expressions – these are covered in-depth in the Expressions section.
+
===Script Data===
−
Test – test the expressions setup against the databox.
 
−
 
−
Help with Expressions – Help on constructing expressions to manipulate the data, which also includes examples of specific expressions and guidance on where to find extra help.
 
−
 
  
−
+
[[File:scriptdatabox41.png]]
  
−
--------------------------------------------------------------------------------
+
*'''Group''' - when displayed in the Selection List in the Navigation Pane Databoxes are arranged into groups. Select a group from the drop down list to add the Databox to an existing group or type in a new group name to create a new group.
  
−
SQL Query
+
*'''Name''' – a descriptive name for the Databox. Use a meaningful description to identify the Databox in relation to its purpose for ease of recognition.
  
 +
*'''Empty bookmarks allowed''' – check this box if the Databox can be used in a task or message without being required to hold a value.
  
 +
*'''Description''' - a short description of the Databox. This is displayed in the Databox Selection list in the Navigation Pane and is searchable using the filter.
  
 +
*'''Expressions''' – these are covered in-depth in the [[Admin_Tools_Manual#Databox_Expressions|Expressions]] section.
 +
*'''Test''' – test the expressions setup against the databox.
 
   
 
   
 +
*'''Help with Expressions''' – Help on constructing expressions to manipulate the data, which also includes examples of specific expressions and guidance on where to find extra help.
  
 +
===SQL Query===
  
−
General: SQL Queries can be issued against any configured/compliant database in order to return a single row of data which internally is held as an XML packet and can be referenced by Expressions, e.g. Item. If an SQL Query returns more than one row, this is considered an error and the SQL query should be amended to ensure this cannot happen. It is good practice to always test for this eventuality in which case you will see this message:
+
[[File:SQLdatabox.png]]
−
 
 
−
 
−
 
 
−
  Depending on the context, there may be various techniques to restrict results to a single row; typically this may use the 'TOP n'  clause. For example, to return a single row containing the current balance for the most recent tenancy record:
 
−
 
 
−
SELECT TOP 1 CurrentBalance FROM Tenants WHERE TenantID = {Import.CallerId} ORDER BY TenancyStartDate DESC 
 
−
 
 
−
Having said that, there are exceptions where multiple rows can be returned but the results must be coerced into an XML packet; this will require more specialist knowledge - click  here for more details.
 
−
 
 
−
Database - the database drop-down list specifies which database the SQL Query Databox will read from. By default the Keyfax Database and Keyfax Data•View Database are available. If you are wishing to query SQL databases these will need to be added to Keyfax Support, to do this read-only login credentials will be needed, along with database name and location.
 
−
 
 
−
SQL Query – This is where the SQL query is added. The Query normally includes a reference to another Databox, for example, a Databox obtaining the AssetID of a property. The maximum length of the query is around 1900 characters; this varies depending on the selected database name and the possible references to other Databoxes. Longer SQL queries should be set up as Stored Procedures in the database with EXECUTE permissions granted to the role: OMFAXROLE. The Databox SQL can then simply execute the stored procedure with the appropriate parameters.
 
  
 +
SQL Queries can be issued against any configured/compliant database in order to return a single row of data which internally is held as an XML packet and can be referenced by Expressions, e.g. Item.
  
−
Tip
+
If an SQL query returns less than two columns, this will not result in an XML packet and the value of the databox will be the value returned and no expression is required.
  
−
Adopt the following naming convention for your stored procedures to ensure they will never conflict with existing or future items of the same name and hence ensure they are never overwritten/deleted by database upgrades:
+
If an SQL Query returns more than one row, this is considered an error and the SQL query should be amended to ensure this cannot happen. It is good practice to always test for this eventuality in which case you will see this
 +
message:
  
−
CUSTOM_storedprocedurename
+
[[File:mr2.png]]
  
−
  e.g.
+
  It should also be taken into consideration that if an SQL Query does not return nothing then any expressions against it will not run.
  
−
CUSTOM_GetRentBalance
+
Depending on the context, there may be various techniques to restrict results to a single row; typically this may use the '''TOP n'''  clause. For example, to return a single row containing the current balance for the most recent tenancy record:
  
−
Test - used to test the SQL Query against the Database that the Databox is connected to. Click on the TEST button, to open the SQL Query Test in a separate window, to allow the SQL statement to be tested.
+
'''SELECT TOP 1 CurrentBalance FROM Tenants WHERE TenantID = {Import.CallerId} ORDER BY TenancyStartDate DESC'''
  
 +
*'''Database''' - the database drop-down list specifies which database the SQL Query Databox will read from. By default the Keyfax Database and Keyfax Data•View Database are available. If you are wishing to query SQL databases these will need to be added to Keyfax Support, to do this read-only login credentials will be needed, along with database name and location.
  
−
Bookmarks - those used in the SQL statement are listed in the Bookmark column. Enter a Test Value from the database to test the statement and click the Evaluate button. The result of the statement is displayed in the Results area.
+
*'''SQL Query''' – This is where the SQL query is added. The Query normally includes a reference to another Databox, for example, a Databox obtaining the AssetID of a property. The maximum length of the query is around 1900 characters; this varies depending on the selected database name and the possible references to other Databoxes. Longer SQL queries should be set up as Stored Procedures in the database with EXECUTE permissions granted to the role: OMFAXROLE. The Databox SQL can then simply execute the stored procedure with the appropriate parameters.
  
−
Expressions – these are covered in-depth in the Expressions section.
 
  
−
Help with Expressions – Help on constructing expressions to manipulate the data, which also includes examples of specific expressions and guidance on where to find extra help.
+
Adopt the following naming convention for your stored procedures to ensure they will never conflict with existing or future items of the same name and hence ensure they are never overwritten/deleted by database upgrades: CUSTOM_storedprocedurename e.g. '''CUSTOM_GetRentBalance'''
  
 +
*'''Test''' - used to test the SQL Query against the Database that the Databox is connected to. Click on the TEST button, to open the SQL Query Test in a separate window, to allow the SQL statement to be tested.
  
−
Tip
+
[[File:SQLquerytest41.png]]
  
−
You can use the SQL Query Test facility to check or test the behaviour of an Expression that you may want to use (for an SQL Query or any other Databox type that supports expressions). You can easily generate databox values, for example:
+
*'''Bookmarks''' - those used in the SQL statement are listed in the Bookmark column. Enter a Test Value from the database to test the statement and click the Evaluate button. The result of the statement is displayed in the Results area.
  
−
SELECT 833728
+
*'''Expressions''' – these are covered in-depth in the [[Admin_Tools_Manual#Databox_Expressions|Expressions section]].
  
−
or
+
*'''Help with Expressions''' – Help on constructing expressions to manipulate the data, which also includes examples of specific expressions and guidance on where to find extra help.
  
−
SELECT 'Mr F. Smith, 21 The Causeway, Bicester, Oxon, OX26 6AN'
 
  
 +
You can use the SQL Query Test facility to check or test the behaviour of an Expression that you may want to use (for an SQL Query or any other Databox type that supports expressions). You can easily generate databox values, for example: '''SELECT 833728''' or SELECT '''Mr F. Smith, 21 The Causeway, Bicester, Oxon, OX26 6AN'''
 
  etc...
 
  etc...
  
−
  It is wise to include comments preceding the SQL Query itself. In this way, original author and details of any amendments can be recorded to aid support should problems arise:
+
It is wise to include comments preceding the SQL Query itself. In this way, original author and details of any amendments can be recorded to aid support should problems arise:
−
 
 
−
 
 
−
 
 
−
 
 
−
 
 
−
Tip
 
−
 
 
−
When your script  runs, every reference that made to a Databox will cause it to be re-evaluated, i.e. the SQL Query will be executed again. To ensure that this doesn't happen, you can 'shelve' results from a SQL Query by performing a Databox Read which is combined with a Databox Write to store results on the said 'shelf'. Any expressions against the 'shelved' databox contents will not cause the SQL to be rerun.
 
−
 
 
−
 
 
−
Stored Procedures
 
−
 
 
−
It is possible to create and run custom Stored Procedures to return data for use in your scripts. Care must be taken because EXECUTE permissions must be granted appropriately. For example, running SQL Tests in Admin Tools runs under a different user than the Script Engine at run time. PLEASE CONTACT SUPPORT IF THIS IS A REQUIREMENT.
 
−
 
 
−
 
 
−
 
 
−
 
 
−
--------------------------------------------------------------------------------
 
−
 
 
−
Import XML
 
−
 
 
−
 
 
−
 
 
−
 
 
−
Group - when displayed in the Selection List in the Navigation Pane Databoxes are arranged into groups. Select a group from the drop down list to add the Databox to an existing group or type in a new group name to create a new group.
 
−
 
 
−
Name – a descriptive name for the Databox. Use a meaningful description to identify the Databox in relation to its purpose for ease of recognition.
 
−
 
 
−
Empty bookmarks allowed – check this box if the Databox can be used in a task or message without being required to hold a value.
 
−
 
 
−
Description - a short description of the Databox. This is displayed in the Databox Selection list in the Navigation Pane and is searchable using the filter.
 
  
−
Read Only – check this box if the Databox will only be available to be read from when used within the Scripts.
+
[[File:SQLcomment41.png]]
  
−
XPath - the XPath details the node/element of the Import XML that is to be read from by the Databox. This has to be detailed in standard XPath format.
+
When your script  runs, every reference that made to a Databox will cause it to be re-evaluated, i.e. the SQL Query will be executed again. To ensure that this doesn't happen, you can 'shelve' results from a SQL Query by performing a Databox Read which is combined with a Databox Write to store results on the said 'shelf'. Any expressions against the 'shelved' databox contents will not cause the SQL to be rerun.
  
  
−
Tip
+
====Stored Procedures====
  
−
XPath is a language that describes how to locate specific elements, attributes or processing instructions in an XML document. It allows you to locate particular content within a document. XPATH treats an XML document as a logical ordered tree.
+
It is possible to create and run custom Stored Procedures to return data for use in your scripts. Care must be taken because EXECUTE permissions must be granted appropriately. For example, running SQL Tests in Admin Tools runs under a different user than the Script Engine at run time. Please contact support if this is a requirement.
  
−
Test – test the expressions setup against the databox.
+
===Import XML===
  
−
Help with Expressions – Help on constructing expressions to manipulate the data, which also includes examples of specific expressions and guidance on where to find extra help.
+
[[File:importdatabox41.png]]
  
 +
*'''Group''' - when displayed in the Selection List in the Navigation Pane Databoxes are arranged into groups. Select a group from the drop down list to add the Databox to an existing group or type in a new group name to create a new group.
  
 +
*'''Name''' – a descriptive name for the Databox. Use a meaningful description to identify the Databox in relation to its purpose for ease of recognition.
  
 +
*'''Empty bookmarks allowed''' – check this box if the Databox can be used in a task or message without being required to hold a value.
  
−
--------------------------------------------------------------------------------
+
*'''Description''' - a short description of the Databox. This is displayed in the Databox Selection list in the Navigation Pane and is searchable using the filter.
  
−
Export XML
+
*'''Read Only''' – check this box if the Databox will only be available to be read from when used within the Scripts.
  
 +
*'''XPath''' - the XPath details the node/element of the Import XML that is to be read from by the Databox. This has to be detailed in standard XPath format.
  
−
  Type – select from the drop down list to specify how data is to be exported into the XML. The default options are:
+
  XPath is a language that describes how to locate specific elements, attributes or processing instructions in an XML document. It allows you to locate particular content within a document. XPATH treats an XML document as a logical ordered tree.
−
•General: Standard exporting of data into a node of the export XML.
 
−
•Service: Exporting into a Service Code node in the export XML.
 
−
•Custom: Creates a ‘Custom Key’ node in the Export XML. This is primarily used for backwards compatibility and for specific host systems that require data exported in a ‘Custom Key’ format.
 
−
•Audit: Used for exporting information that requires being audited or logged by Keyfax.
 
  
−
Name – a descriptive name for the Databox. Use a meaningful description to identify the Databox in relation to its purpose for ease of recognition.
+
*'''Test''' – test the expressions setup against the databox.
  
−
Append ‘recorded’ data - checking this option ensures that any data exported by the Databox is appended to any existing data. The data exported will be delimited by a semi-colon.
+
*'''Help with Expressions''' – Help on constructing expressions to manipulate the data, which also includes examples of specific expressions and guidance on where to find extra help.
  
−
Empty bookmarks allowed – check this box if the Databox can be used in a task or message without being required to hold a value.
+
===Export XML===
  
−
Read Only – check this box if the Databox will only be available to be read from when used within the Scripts.
+
[[File:exportxml.png]]
  
−
XPath - the XPath details the node/element of the Export XML that is to be written to by the Databox. This has to be detailed in standard XPath format.
+
*'''Type''' – select from the drop down list to specify how data is to be exported into the XML. The default options are:
 +
**'''General:''' Standard exporting of data into a node of the export XML.
 +
**'''Service:''' Exporting into a Service Code node in the export XML.
 +
**'''Custom:''' Creates a ‘Custom Key’ node in the Export XML. This is primarily used for backwards compatibility and for specific host systems that require data exported in a ‘Custom Key’ format.
 +
**'''Audit:''' Used for exporting information that requires being audited or logged by Keyfax.
  
−
Test – test the expressions setup against the databox.
+
*'''Name''' – a descriptive name for the Databox. Use a meaningful description to identify the Databox in relation to its purpose for ease of recognition.
  
−
Help with Expressions – Help on constructing expressions to manipulate the data, which also includes examples of specific expressions and guidance on where to find extra help.
+
*'''Append ‘recorded’ data''' - checking this option ensures that any data exported by the Databox is appended to any existing data. The data exported will be delimited by a semi-colon.
  
 +
*'''Empty bookmarks allowed''' – check this box if the Databox can be used in a task or message without being required to hold a value.
  
 +
*'''Read Only''' – check this box if the Databox will only be available to be read from when used within the Scripts.
  
 +
*'''XPath''' - the XPath details the node/element of the Export XML that is to be written to by the Databox. This has to be detailed in standard XPath format.
  
−
--------------------------------------------------------------------------------
+
*'''Test''' – test the expressions setup against the databox.
  
−
System Values
+
*'''Help with Expressions''' – Help on constructing expressions to manipulate the data, which also includes examples of specific expressions and guidance on where to find extra help.
  
 +
===System Values===
  
 +
[[File:systemdatabox41.png]]
 
   
 
   
  
−
System Value – these are internal to the system and provides access to a number of predefined values which are being added to all the time. You must select a value from the drop down list the type of value to be used:
+
*'''System Value''' – these are internal to the system and provides access to a number of predefined values which are being added to all the time. You must select a value from the drop down list the type of value to be used:
−
•DataViewURL: This returns the DataView URL for the current configuration and should only be used when DataView is installed (requires a separate licence). The url returned will be formatted with the current users credentials each time the script is executed and the Databox value is referenced
+
**'''DataViewURL''': This returns the DataView URL for the current configuration and should only be used when DataView is installed (requires a separate licence). The url returned will be formatted with the current users credentials each time the script is executed and the Databox value is referenced
−
•Date: This returns the current date and time.
+
**'''Date: This returns the current date and time.
−
•CallRef: This returns the Order Id reference where a Service (SOR code) has been generated by the script. It can only be used as a Task bookmark as the Order Id is not generated until after the system results script and only if any Service has been generated by the script.
+
**'''CallRef''': This returns the Order Id reference where a Service (SOR code) has been generated by the script. It can only be used as a Task bookmark as the Order Id is not generated until after the system results script and only if any Service has been generated by the script.
−
•Services: This returns details of all Services recoded in a script in an XML format
+
**'''Services''': This returns details of all Services recoded in a script in an XML format
−
•SriptPath: This returns an HTML formatted list of questions and responses.
+
**'''SriptPath''': This returns an HTML formatted list of questions and responses.
−
•TaskCode: This should only be used in a task template and returns the task action code for the 'current' task being processed. This is typically used to place the task code on the template as a visible 'stamp' to identify the source of a letter or email after it has been sent.
+
**'''TaskCode''': This should only be used in a task template and returns the task action code for the 'current' task being processed. This is typically used to place the task code on the template as a visible 'stamp' to identify the source of a letter or email after it has been sent.
−
•TaskID: This returns the ContactView Task Id reference and is only relevant for installation with ContactView configured (requires a separate license). It can only be used as a template bookmark as the ContactView Task Id is not generated until after the system results script.
+
**'''TaskID''': This returns the ContactView Task Id reference and is only relevant for installation with ContactView configured (requires a separate license). It can only be used as a template bookmark as the ContactView Task Id is not generated until after the system results script.
−
•TotalCost: This returns the total cost based on the Services generated by a script. The total cost is based on the recorded services and quantities with a configurable fee plus VAT. This is formatted to 2 decimal places.
+
**'''TotalCost''': This returns the total cost based on the Services generated by a script. The total cost is based on the recorded services and quantities with a configurable fee plus VAT. This is formatted to 2 decimal places.
−
 
 
−
 
 
−
Tip
 
  
−
A System Values Databox is Read Only.
+
A System Values Databox is Read Only.
  
 +
===Company Data===
  
−
--------------------------------------------------------------------------------
+
[[File:companydatabox41.png]]
  
−
Company Data
+
*'''Group''' - when displayed in the Selection List in the Navigation Pane Databoxes are arranged into groups. Select a group from the drop down list to add the Databox to an existing group or type in a new group name to create a new group.
  
 +
*'''Name''' – a descriptive name for the Databox. Use a meaningful description to identify the Databox in relation to its purpose for ease of recognition.
  
 +
*'''Values'''
 +
**'''Name''' - This column details the description of the data
 +
**'''Value''' - This column contains the actual value of the data to be used. This value may be used throughout the scripts and in databoxes, tasks and messages as bookmark values
  
 +
[[File:companydataexample41.png]]
  
 +
Company databoxes can contain multiple values. These can be added using the plus sign to the right of the values panel.
  
 +
You can only remove rows of fixed data from a Company Data Databox if you are in Exclusive mode.
  
−
Values - each column details a separate piece of fixed-value information. The Name column details the description of the data. The Value column describes the actual value of the data to be used. This is for a variety of uses but will typically contain details about your Company that can be used throughout your scripts. You can also create databoxes that more logically describe the fixed value, rather than simply using the value itself in a script.
+
==Special HTML characters &, <, >==
−
+
The system expects these values to be displayed within an HTML document and encodes certain reserved characters such that they appear as text and are not part of the underlying HTML. E.g. behind the scenes, & is encoded as &amp;.
−
Click the  button to add additional rows of fixed data to the Databox. Click the  button to delete rows of fixed data from the Databox.  Any number of rows (values) can be added to a Company Databox however at least 1 value is required (the databox itself is simply a container and only the individual values can be used for scripting).
 
−
 
 
−
 
 
−
TIP
 
−
 
 
−
You can only remove rows of fixed data from a Company Data Databox if you are in Exclusive mode.
 
−
Special HTML characters &, <, >
 
−
The system expects these values to be displayed within an HTML document and encodes certain reserved characters such that they appear as text and are not part of the underlying HTML. E.g. behind the scenes, & is encoded as &amp;.
 
  
−
If you are using these values in non-html fields e.g. email task address fields or explicitly want to generate these characters un-encoded then the encoding can be switched off by wrapping the value between 'html|' and '|'. e.g. "html|A&E@poole.nhs.uk|".
+
If you are using these values in non-html fields e.g. email task address fields or explicitly want to generate these characters un-encoded then the encoding can be switched off by wrapping the value between 'html|' and '|'. e.g. "html|A&E@poole.nhs.uk|".

Latest revision as of 09:56, 19 March 2020

Creating Databoxes

Select Databoxes from the entities menu to open a tab to manage your databoxes. The screen is divided into three sections. The navigation pane, menu bar and the editing pane. You can create a new databox by selecting a type from the list in the editing pane.

Databoxes41.png

Databox Types

When the Databox page first appears, you can create a new Databox by selecting the type. You can choose to to ignore this and select an existing databox from the navigation pane. You can create a new Databox at any time by clicking on Add on the menu bar, where you will be presented with a list of databox types types in the editing pane The options are covered below.

Script Data

Scriptdatabox41.png

  • Group - when displayed in the Selection List in the Navigation Pane Databoxes are arranged into groups. Select a group from the drop down list to add the Databox to an existing group or type in a new group name to create a new group.
  • Name – a descriptive name for the Databox. Use a meaningful description to identify the Databox in relation to its purpose for ease of recognition.
  • Empty bookmarks allowed – check this box if the Databox can be used in a task or message without being required to hold a value.
  • Description - a short description of the Databox. This is displayed in the Databox Selection list in the Navigation Pane and is searchable using the filter.
  • Expressions – these are covered in-depth in the Expressions section.
  • Test – test the expressions setup against the databox.
  • Help with Expressions – Help on constructing expressions to manipulate the data, which also includes examples of specific expressions and guidance on where to find extra help.

SQL Query

SQLdatabox.png

SQL Queries can be issued against any configured/compliant database in order to return a single row of data which internally is held as an XML packet and can be referenced by Expressions, e.g. Item. 

If an SQL query returns less than two columns, this will not result in an XML packet and the value of the databox will be the value returned and no expression is required.

If an SQL Query returns more than one row, this is considered an error and the SQL query should be amended to ensure this cannot happen. It is good practice to always test for this eventuality in which case you will see this 
message:

Mr2.png 

It should also be taken into consideration that if an SQL Query does not return nothing then any expressions against it will not run.

Depending on the context, there may be various techniques to restrict results to a single row; typically this may use the TOP n clause. For example, to return a single row containing the current balance for the most recent tenancy record:

SELECT TOP 1 CurrentBalance FROM Tenants WHERE TenantID = {Import.CallerId} ORDER BY TenancyStartDate DESC

  • Database - the database drop-down list specifies which database the SQL Query Databox will read from. By default the Keyfax Database and Keyfax Data•View Database are available. If you are wishing to query SQL databases these will need to be added to Keyfax Support, to do this read-only login credentials will be needed, along with database name and location.
  • SQL Query – This is where the SQL query is added. The Query normally includes a reference to another Databox, for example, a Databox obtaining the AssetID of a property. The maximum length of the query is around 1900 characters; this varies depending on the selected database name and the possible references to other Databoxes. Longer SQL queries should be set up as Stored Procedures in the database with EXECUTE permissions granted to the role: OMFAXROLE. The Databox SQL can then simply execute the stored procedure with the appropriate parameters.


Adopt the following naming convention for your stored procedures to ensure they will never conflict with existing or future items of the same name and hence ensure they are never overwritten/deleted by database upgrades: CUSTOM_storedprocedurename e.g. CUSTOM_GetRentBalance
  • Test - used to test the SQL Query against the Database that the Databox is connected to. Click on the TEST button, to open the SQL Query Test in a separate window, to allow the SQL statement to be tested.

SQLquerytest41.png

  • Bookmarks - those used in the SQL statement are listed in the Bookmark column. Enter a Test Value from the database to test the statement and click the Evaluate button. The result of the statement is displayed in the Results area.
  • Help with Expressions – Help on constructing expressions to manipulate the data, which also includes examples of specific expressions and guidance on where to find extra help.


You can use the SQL Query Test facility to check or test the behaviour of an Expression that you may want to use (for an SQL Query or any other Databox type that supports expressions). You can easily generate databox values, for example: SELECT 833728 or SELECT Mr F. Smith, 21 The Causeway, Bicester, Oxon, OX26 6AN
etc...

It is wise to include comments preceding the SQL Query itself. In this way, original author and details of any amendments can be recorded to aid support should problems arise:

SQLcomment41.png 

When your script  runs, every reference that made to a Databox will cause it to be re-evaluated, i.e. the SQL Query will be executed again. To ensure that this doesn't happen, you can 'shelve' results from a SQL Query by performing a Databox Read which is combined with a Databox Write to store results on the said 'shelf'. Any expressions against the 'shelved' databox contents will not cause the SQL to be rerun.


Stored Procedures

It is possible to create and run custom Stored Procedures to return data for use in your scripts. Care must be taken because EXECUTE permissions must be granted appropriately. For example, running SQL Tests in Admin Tools runs under a different user than the Script Engine at run time. Please contact support if this is a requirement.

Import XML

Importdatabox41.png

  • Group - when displayed in the Selection List in the Navigation Pane Databoxes are arranged into groups. Select a group from the drop down list to add the Databox to an existing group or type in a new group name to create a new group.
  • Name – a descriptive name for the Databox. Use a meaningful description to identify the Databox in relation to its purpose for ease of recognition.
  • Empty bookmarks allowed – check this box if the Databox can be used in a task or message without being required to hold a value.
  • Description - a short description of the Databox. This is displayed in the Databox Selection list in the Navigation Pane and is searchable using the filter.
  • Read Only – check this box if the Databox will only be available to be read from when used within the Scripts.
  • XPath - the XPath details the node/element of the Import XML that is to be read from by the Databox. This has to be detailed in standard XPath format.
XPath is a language that describes how to locate specific elements, attributes or processing instructions in an XML document. It allows you to locate particular content within a document. XPATH treats an XML document as a logical ordered tree.
  • Test – test the expressions setup against the databox.
  • Help with Expressions – Help on constructing expressions to manipulate the data, which also includes examples of specific expressions and guidance on where to find extra help.

Export XML

Exportxml.png

  • Type – select from the drop down list to specify how data is to be exported into the XML. The default options are:
    • General: Standard exporting of data into a node of the export XML.
    • Service: Exporting into a Service Code node in the export XML.
    • Custom: Creates a ‘Custom Key’ node in the Export XML. This is primarily used for backwards compatibility and for specific host systems that require data exported in a ‘Custom Key’ format.
    • Audit: Used for exporting information that requires being audited or logged by Keyfax.
  • Name – a descriptive name for the Databox. Use a meaningful description to identify the Databox in relation to its purpose for ease of recognition.
  • Append ‘recorded’ data - checking this option ensures that any data exported by the Databox is appended to any existing data. The data exported will be delimited by a semi-colon.
  • Empty bookmarks allowed – check this box if the Databox can be used in a task or message without being required to hold a value.
  • Read Only – check this box if the Databox will only be available to be read from when used within the Scripts.
  • XPath - the XPath details the node/element of the Export XML that is to be written to by the Databox. This has to be detailed in standard XPath format.
  • Test – test the expressions setup against the databox.
  • Help with Expressions – Help on constructing expressions to manipulate the data, which also includes examples of specific expressions and guidance on where to find extra help.

System Values

Systemdatabox41.png


  • System Value – these are internal to the system and provides access to a number of predefined values which are being added to all the time. You must select a value from the drop down list the type of value to be used:
    • DataViewURL: This returns the DataView URL for the current configuration and should only be used when DataView is installed (requires a separate licence). The url returned will be formatted with the current users credentials each time the script is executed and the Databox value is referenced
    • Date: This returns the current date and time.
    • CallRef: This returns the Order Id reference where a Service (SOR code) has been generated by the script. It can only be used as a Task bookmark as the Order Id is not generated until after the system results script and only if any Service has been generated by the script.
    • Services: This returns details of all Services recoded in a script in an XML format
    • SriptPath: This returns an HTML formatted list of questions and responses.
    • TaskCode: This should only be used in a task template and returns the task action code for the 'current' task being processed. This is typically used to place the task code on the template as a visible 'stamp' to identify the source of a letter or email after it has been sent.
    • TaskID: This returns the ContactView Task Id reference and is only relevant for installation with ContactView configured (requires a separate license). It can only be used as a template bookmark as the ContactView Task Id is not generated until after the system results script.
    • TotalCost: This returns the total cost based on the Services generated by a script. The total cost is based on the recorded services and quantities with a configurable fee plus VAT. This is formatted to 2 decimal places.
A System Values Databox is Read Only.

Company Data

Companydatabox41.png

  • Group - when displayed in the Selection List in the Navigation Pane Databoxes are arranged into groups. Select a group from the drop down list to add the Databox to an existing group or type in a new group name to create a new group.
  • Name – a descriptive name for the Databox. Use a meaningful description to identify the Databox in relation to its purpose for ease of recognition.
  • Values
    • Name - This column details the description of the data
    • Value - This column contains the actual value of the data to be used. This value may be used throughout the scripts and in databoxes, tasks and messages as bookmark values

Companydataexample41.png

Company databoxes can contain multiple values. These can be added using the plus sign to the right of the values panel. 

You can only remove rows of fixed data from a Company Data Databox if you are in Exclusive mode.

Special HTML characters &, <, >

The system expects these values to be displayed within an HTML document and encodes certain reserved characters such that they appear as text and are not part of the underlying HTML. E.g. behind the scenes, & is encoded as &.

If you are using these values in non-html fields e.g. email task address fields or explicitly want to generate these characters un-encoded then the encoding can be switched off by wrapping the value between 'html|' and '|'. e.g. "html|A&E@poole.nhs.uk|".

Retrieved from ‘http://wiki.keyfax.biz/index.php?title=Creating_a_Databox&oldid=1286’