Ancillary Sources for DocClass and Batch Fields

An ancillary source in Transflo DMS is an alternative means of populating the value of a DocClass or batch field. An ancillary source replaces the current value in a field with a new one it generates. The type of value is determined by which type of ancillary source is selected and how it is configured.

Seven types of DMS Gateway ancillary sources are available:

  • Time Stamp

  • Static Pick List

  • SQL Pick List

  • String Generator

  • External Text File

  • Field Copier/Splitter

  • Bar Code Source

Because ancillary sources can be assigned to both DocClass fields and batch fields, it is possible to map a batch field with an ancillary source to a DocClass field with its own ancillary source. To prevent conflicts in this situation, ancillary sources are applied in the following hierarchical order:

  1. The batch field in the current queue.

  2. That same batch field in the next queue.

  3. The DocClass field to which the current queue batch field is mapped.

 

Multiple ancillary sources can be assigned to a single batch field or a single DocClass field. Each ancillary source has a priority property that is used to determine the order that each ancillary source is applied for a given batch field or DocClass field. By default the priority property starts at 0 for the first ancillary source that you create for a given field.

For each additional ancillary source you create for the field, the priority is incremented in the order that you add the ancillary sources. You can change this order by editing the priority property on the ancillary sources for the field.

The process by which ancillary sources are applied depends upon whether a field has an empty value and whether the ancillary source overwrite setting is enabled.

Tip: If an ancillary source is created for a batch field that also has a field mask constraint, the ancillary source value entered must match the defined mask.

 

Ancillary sources are applied according to one of two scenarios, depending upon whether the value of the batch field in the current queue is empty or non-empty.

Scenario 1: The batch field in the current queue has an empty value.

  • The value of the batch field ancillary source is applied. The ancillary source value might also be empty.

  • If the ancillary source of the batch field in next queue has an overwrite setting of true or the field value is still empty, the value of that batch field ancillary source is applied.

  • If the ancillary source of the DocClass field has an overwrite setting of true or the field value is still empty, the DocClass field ancillary source value is applied.

Scenario 2: The batch field in the current queue has a non-empty value.

  • If the batch field ancillary source has a non-empty value and an overwrite setting of true, its value is applied.

  • If the ancillary source of the batch field in next queue has a non-empty value and an overwrite setting of True the value of that batch field ancillary source is applied.

  • If the ancillary source of the DocClass field has a non-empty value and an overwrite setting of true the DocClass field ancillary source value is applied.

SQL Pick List Ancillary Source

The SQL Pick List ancillary source is used to execute a SQL statement and write the result as the Field’s value.

Creating a SQL Pick List ancillary source

To add a SQL Pick List ancillary source to a DocClass Field or a batch field, right-click the Ancillary Sources node under the Field’s name in the tree pane.

On the shortcut menu, point to Insert, and then click SQL Pick List.

Configuring a SQL Pick List ancillary source

Now that you have created the Static Pick List ancillary source, you can configure its settings via the text boxes on the right side of the window.

These settings are:

  • Overwrite

  • Trigger Mode
  • Automate

  • Separator
  • Connect String

  • DSN
  • User ID

  • Password
  • Connect Options

  • SQL
  • Param Delimiters

To configure the SQL Pick List ancillary source:

1. Click the Overwrite arrow, and then click either True or False in the Overwrite list

If the Overwrite setting is True, then the SQL Pick List ancillary source will overwrite any existing data in the Field; if the Overwrite setting is False, then the Field’s original value will remain untouched.

The default Overwrite setting is False.

2. To control when the SQL Pick List Ancillary source fires, select a Trigger Mode from the Trigger Mode list.

The available Trigger Mode settings are:

Setting Description
Auto

If the Ancillary Queue is the first Queue, the ancillary source rule only fires when the Documents exit the Queue.

Otherwise, the rule only fires when the Documents enter the Queue. The default Trigger mode is Auto.

On Enter

The ancillary source rule only fires when the Documents enter the Queue.

On Exit

The ancillary source rule only fires when the Documents exit the Queue.

Both

The ancillary source rule fires when the Documents enter the Queue

and fires again when the Documents exit the Queue.

3. The Automate function enables Gateway to decide how to process a list of choices that are returned by the SQL Pick List.

The available Automate settings are:

Setting Description
Never

No choice is selected (the ancillary source returns an empty string).

This is not a useful choice in Gateway at present, as it is designed to work with third-party applications.

Unique Value

If there is only one choice in the list, it is used.

If there is more than one choice, then no choice is selected (the ancillary source returns an empty string).

First Value

The first choice in the returned list is automatically used.

All Values

If there is more than one choice in the list, then all choices are

concatenated together using the Separator value specified for the ancillary source.

Click the Automate arrow to expand the Automate list, and then click one of the four available options.

4. In the Separator box, type the character that should be recognized as the separator in the Batch file.

The default separator is a semicolon (;).

5. (Optional) If you wish, instead of entering the DSN, User ID, and Password information for your SQL connection separately, you can type the connection string for your SQL Server instance in the Connect String box.

If you do not enter a connection string, then the DSN, User ID, and Password boxes are required.

6. In the DSN box, type the ODBC data service name for your SQL Server instance.

7. In the User ID box, type the login name used to connect to your SQL Server instance.

8. In the Password box, type the password that corresponds to the login name you entered.

9. (Unused Setting) The Connect Options setting is not currently used in Gateway.

In the Connect Options box, accept the default value of 0.

10. (Optional) The default parameter delimiters used by the SQL Pick List ancillary source are square brackets -- [].

To use some other character pair for parameter delimiters, it is best that you make the change now before continuing.

To do this, replace the square brackets in the Param Delimiter box with your preferred delimiter pair.

Only valid SQL delimiters can be used.

Now that you have finished entering your SQL Server connection information, you can test your connection and begin writing the SQL query to be executed by the SQL Pick List ancillary source.

Click the Browse button to the right of the SQL box to open the Edit Host Query dialog box.

The Connect string, DSN, User ID, and Password boxes already contain the settings you entered for the SQL Pick List.

These are the settings used to connect to your SQL Server instance.

Click in the SQL box and begin typing your SQL query.

If you need to include the name of a DocClass Field in your query, then rather than typing the Field’s name, you can insert it by selecting it from a list.

To do this, click the Insert Param button to open the Select Field dialog box, click the name of the desired DocClass Field, and then click OK.

The dialog box closes, and the DocClass Field name is added to the SQL box.

When you have finished writing your SQL query, click the Test button to execute the query.

If you included a DocClass Field as a query parameter, Gateway will prompt you for a string to use to test the query, since no data will be available from the DocClass Field until runtime.

Type this string in the Enter test value dialog box.

Your query results are displayed in the Results box.

You can modify and re-test your query as many times as necessary.

When you are satisfied with your query, click OK to close the Edit Host Query dialog box.

Your SQL query now appears in the SQL box.

The text is shaded, indicating that you cannot directly edit your query in the box.

To make changes to the SQL query, you must first re-open the Edit Host Query dialog box.

Note: You cannot use a Pick List Field in an insert statement for a Gateway Field SQL Pick List.

In order to extract DOCTYPE (or other pieces of information) from barcode values like these: 1234567BOL and 234567INV the following technique can be used.

Pegasus TransTech created the following SQL Server stored procedure that parses an expression and extracts a substring that matches a certain pattern.

This stored procedure can be invoked from Gateway by using a "SQLPick List" ancillary source.

The following are the steps to do this:

1. Add the following 2 stored procedures.

The second one is just a wrapper around the first one, to make it easy to use in Gateway.

The first stored procedure is generic. It receives as a parameter an expression (e.g. 1234567BOL) from which a piece of information needs to be extracted (e.g. BOL), the pattern to look for and the length of the substring to extract.

It returns null if no matching substring is found, or the matching substring.

The second stored procedure is just a wrapper around the first one to extract three consecutive uppercase letters:

The following will parse a barcode value and return the last 3 characters that match the pattern of uppercase values A through Z for each one of the 3 characters.

Both of the following will return BOL as a result:

exec usp_parse_regex '1234567BOL', '%[A-Z][A-Z][A-Z]', 3 exec usp_parse_regex '234567BOL', '%[A-Z][A-Z][A-Z]', 3

The following will parse a barcode value and return the first 6 characters that match the pattern of numbers only ranging from 0 to 9 for each one of the 6 characters.

This one will find a match of 123456:

exec usp_parse_regex '1234567BOL', '%[0-9][0-9][0-9][0-9][0-9][0-9]%',6

This one will not find a match of because we are asking for 6 characters and there are only 5 that match this pattern:

exec usp_parse_regex '34567BOL', '%[0-9][0-9][0-9][0-9][0-9][0-9]%', 6

To install the stored procedures, copy the following into an SQL Query window using SQL Server Management Studio:

USE TRANSFLO® GO

create procedure usp_parse_regex(

-- Add the parameters for the stored procedure here @Expression varchar(255),

@Pattern varchar(100), @Length int

) AS

BEGIN

declare @PatternPosition bigint

set @PatternPosition = PATINDEX(@pattern, @Expression) if @PatternPosition is null or @PatternPosition = 0

select '' else

select SUBSTRING(@Expression, @PatternPosition, @Length)

END GO

--usp_parse_doctype '1234567BOL'

create procedure usp_parse_doctype(

-- Add the parameters for the stored procedure here @Expression varchar(255)

) AS

BEGIN

exec usp_parse_regex @Expression, '%[A-Z][A-Z][A-Z]', 3

END GO

2. Add a SQL Pick List ancillary source to a Field (DOCTYPE in this case) in Gateway, as shown in the screen print below:

Please note the single quotes around the field [TEST] in the SQL query above.

It will parse the content of the field TEST, which could be the result of a barcode read on the previous queue and try to extract a substring that matches the pattern: '%[A-Z][A-Z][A-Z]'

For more on search pattern syntax see:

http://msdn.microsoft.com/en-us/library/ms187489(v=sql.105).aspx

String Generator Ancillary Source

The String Generator ancillary source is used to create a string value out of system information, DocClass Field values, and literal text.

Creating a String Generator ancillary source

To add a String Generator ancillary source to a DocClass Field or a batch field, right-click the Ancillary Sources node under the Field’s name in the tree pane.

On the shortcut menu, point to Insert, and then click String Generator.

Configuring a String Generator ancillary source

Now that you have created the String Generator ancillary source, you can configure its settings via the text boxes on the right side of the window.

These settings are Overwrite and Mask.

To configure the String Generator ancillary source:

1. Click the Overwrite arrow, and then click either True or False in the Overwrite list.

If the Overwrite setting is True, then the String Generator ancillary source will overwrite any existing data in the Field; if the Overwrite setting is False, then the Field’s original value will remain untouched.

The default Overwrite setting is False.

2. To control when the String Generator Ancillary source fires, select a Trigger Mode from the Trigger Mode list.

The available Trigger Mode settings are.

Setting Description
Auto

If the Ancillary Queue is the first Queue, the ancillary source rule only fires when the Documents exit the Queue.

Otherwise, the rule only fires when the Documents enter the Queue. The default Trigger mode is Auto.

On Enter

The ancillary source rule only fires when the Documents enter the Queue.

On Exit

The ancillary source rule only fires when the Documents exit the Queue.

Both

The ancillary source rule fires when the Documents enter the Queue

and fires again when the Documents exit the Queue.

3. The String Generator produces a value based on a string mask that you create.

To begin creating this mask, click the Browse button to the right of the Mask box to open the String Generator Mask dialog box.

There are ten types of data that the String Generator can mask:

  • Literal Text
  • Field Value
  • Year
  • Month
  • Day
  • Hour
  • Minute
  • Second
  • Millisecond
  • Serial Number

4. These masks are inserted by selecting them from a menu.

To open this menu, right-click the String Generator Mask dialog box anywhere but on its title bar.

On the menu, click the data type of the mask you wish to add.

5. The majority of the available masks represent system-generated data types.

These data types are Year (YYYY), Month (MM), Day (DD), Hour (HH), Minute (NN), Second (SS), Millisecond (ZZZ), and Serial Number (###).

To select any of these data types, click its name on the menu.

The mask is instantly added to the Mask box in the String Generator Mask dialog box, while an example of that mask is displayed in the Example box.

Note: To shorten the Year mask from four digits to two after adding it to the Mask box, delete the first two Y characters in the mask.

Note: The Serial Number mask is only three digits long, displayed as three number signs (###).

If you require more digits, add more Serial Number masks in succession, and then delete any extra number signs.

For example, to create a mask for a five-digit serial number, add two Serial Number masks, and then delete one of the number signs in the string.

6. To add a mask for a DocClass Field, click Field Value in the list to open the Select Field dialog box.

Click the name of the DocClass Field you wish to add, and then click OK.

If the needed Field is not in the list, then you must first create the DocClass Field.

See “Adding Custom DocClass Fields” on page 42

Note: If you are adding this ancillary source to a DocClass Field, the DocClass Field will not be listed in the Select Field dialog box.

Likewise, if you are adding this ancillary source to a batch field, the DocClass Field to which the batch field is mapped will not be listed.

7. To add literal text, click Literal Text in the list to open the Enter literal element dialog box.

Type the desired text in the Value box, and then click OK.

Alternately, you can type literal text directly into the Mask box in the String Generator Mask dialog box.

This is helpful if, for example, you wish to insert slash characters into a date mask, changing MMDDYYYY into MM/DD/YYYY.

When you enter literal text via either method, the letters D, H, M, N, S, Y, and Z will automatically be prepended with a backslash, e.g., \M.

This is to distinguish these characters as literal text and not masks.

Note: If you add and then delete literal text, take care that you do not leave behind two backslashes in a row (\\).

Doing this will cause a number to appear in the text when it is viewed outside of the String Generator Mask dialog box.

8. When you have finished creating your string mask, click OK to close the String Generator Mask dialog box.

Your string mask now appears in the Mask box.

The text is shaded, indicating that you cannot directly edit your mask in the box.

To make changes to the mask, you must first re-open the String Generator Mask dialog box.

External Text File Ancillary Source

The External Text File ancillary source is used to insert the contents of a plain text file as a Field value.

There is no limit imposed on the number of characters that can be in the file.

Creating an External Text File ancillary source

To add an External Text File ancillary source to a DocClass Field or a batch field, right-click the Ancillary Sources node under the Field’s name in the tree pane.

On the shortcut menu, point to Insert, and then click External Text File.

Configuring an External Text File ancillary source

Now that you have created the External Text File ancillary source, you can configure its settings via the text boxes on the right side of the window.

These settings are Overwrite and Path.

To configure the External Text File ancillary source:

1. Click the Overwrite arrow, and then click either True or False in the Overwrite list.

If the Overwrite setting is True, then the External Text File ancillary source will overwrite any existing data in the Field; if the Overwrite setting is False, then the Field’s original value will remain untouched.

The default Overwrite setting is False.

2. To control when the External Text File Ancillary source fires, select a Trigger Mode from the Trigger Mode list.

The available Trigger Mode settings are:

Setting Description
Auto

If the Ancillary Queue is the first Queue, the ancillary source rule only fires when the Documents exit the Queue.

Otherwise, the rule only fires when the Documents enter the Queue. The default Trigger mode is Auto.

On Enter

The ancillary source rule only fires when the Documents enter the Queue.

On Exit

The ancillary source rule only fires when the Documents exit the Queue.

Both

The ancillary source rule fires when the Documents enter the Queue

and fires again when the Documents exit the Queue.

3. The external text file that is the source for the Field’s value must be present either on the local computer or on your network.

To select this file, click the Browse button to the right of the Path box.

In the Open dialog box, browse to the text file’s location, click the file name to select it, and then click Open.

The file path now appears in the Path box, and the External Text File ancillary source is properly configured.