Lookup

Lookup



Lookup

Check if a column exists

Description

Check if a column exists is a step in the Lookup Plugin for Process Studio Workflows. This step allows you to verify the existence of a specific column in a database table.

Configurations

No.

Field Name

Description

1

Step name

Specify the name of the step as it appears in the workflow workspace. This name has to be unique in a single workflow.

2

Connection

Specify the database connection to use.

3

Schema name (optional)

Specify the schema name of the table containing the column to check 

4

Table name 

Specify the name of the table containing the column to check 

5

Tablename in field?

Enable to read the name of the table in an input field 

6

Tablename field 

Specify the fields containing parameters and the parameter type

7

Columnname field

Specify the name of the column field in the input stream. 

8

Result fieldname 

Specify the name of the result boolean flag field.


Check if file is locked

Description

Check if file is locked is a step in the Lookup Plugin for Process Studio Workflows. This step tries to determine if a file is locked by any other process.  It does this by trying to move it to itself.  

Configurations

No.

Field Name

Description

1

Step name

Specify the name of the step as it appears in the workflow workspace. This name has to be unique in a single workflow.

2

Filename field

Specify the name of the input field that will contain the names of the files during execution.

3

Result fieldname

Specify the name of the Boolean output field that will contain true or false depending on whether or not the file is locked or not.

4

Add filename to result

Enable checkbox to add the checked file-names to the list of files in the workflows result.



Check if Webservice is available

Description

Check if Webservice is available is a step in the Lookup Plugin for Process Studio WorkflowsThis step checks if a given URL (e.g. a Webservice URL) in the input stream is valid, can be connected and if data can be read from it. If it connects within the given timeout and data can be read, it returns 'true', otherwise 'false'. 

Configurations

No.

Field Name

Description

1

Step name

Specify the name of the step as it appears in the workflow workspace. This name has to be unique in a single workflow.

2

URL field 

Specify the URL fieldname in the data stream. The URL is checked for every row that is coming into this step.

3

Connect timeout (ms)

Specify the connect timeout in ms. The value will depend on the quality of service of this URL and experiences.

4

Read timeout (ms)

Specify the read timeout in ms during which the step tries to read data. The value will depend on the quality of service of this URL and experiences.

5

Result fieldname

Specify the name of the result boolean flag field. If it connects within the given timeout and data can be read, it returns 'true' else 'false'.

Further information of the failing reason can be found in the log when debug logging is enabled.

Database join

Description

Database join is a step in the Lookup Plugin for Process Studio WorkflowsThe Database Join step runs a query against a database using data obtained from previous steps. The parameters for this query are specified as follows:

  1. The data grid in the step properties dialog.  This allows you to select the data coming in from the source hop.
  2. As question marks (?) in the SQL query. When the step runs, these will be replaced with data coming in from the fields defined from the data grid.  The question marks will be replaced in the same order as defined in the data grid. 

Configurations

No.

Field Name

Description

1

Step name

Specify the name of the step as it appears in the workflow workspace. This name has to be unique in a single workflow.

2

Connection

Specify the database connection to use for the query.

3

SQL

Specify the SQL query to form the join between source parameters and the database connection specified; use question marks as parameter placeholders

4

Number of rows to return

Specify the field name to store Number of rows to return. Zero (0) returns all rows; any other number limits the number of rows returned.

5

Outer join?

Enable to always return a result, even if the query did not return a result

6

Parameters table

Specify the fields containing parameters.  Also specify the data type is required.

Database lookup

Description

Database lookup is a step in the Lookup Plugin for Process Studio Workflows. The Database lookup step looks up values in a database table. Lookup values are added as new fields in the stream.

Configurations

No.

Field Name

Description

1

Step name

Specify the name of the step as it appears in the workflow workspace. This name has to be unique in a single workflow.

2

Connection

Specify the Database connection for the lookup table.

3

Lookup schema

Specify the Database schema containing the lookup table.

4

Lookup Table

Specify the name of the database table used for the lookup.

5

Enable cache?

Enables caching of database lookups. This means that once a key (or group of key) has been looked up, the looked up values are stored, and returned again the next time this key (or group of key) is being looked up (without incurring the cost of a database call). 

6

Cache size in rows

Specify the size of the cache (number of rows), 0 means cache everything. 

7

Load all data from table 

Pre-loads the cache with all the data present in the lookup table. This may improve performance by avoiding database calls. However, if you have a large table, you risk running out of memory. 

8

Keys to look up table

Specify the keys and conditions to perform the database lookup.

9

Values to return table

Specify the fields from the lookup table to add to the output stream. 

  1. "New name" allows you to use a different name if the database column name is inappropriate. 
  2. "Default" is the value returned (instead of null) if the lookup fails. 
  3. "Type" is the type of the output field

Note: Enabling error handling on the Database Lookup step will redirect all failed lookup rows to the error handling step, instead of adding them to the main output stream with null/default values. 

10

Do not pass the row if the lookup fails

Enable checkbox to avoid passing a row when lookup fails. Within the SQL syntax, enabling this would be an INNER JOIN, otherwise it would be an OUTER JOIN.

11

Fail on multiple results?

Enable checkbox to force the step to fail if the lookup returns multiple results.

12

Order by

If the lookup query returns multiple results, the ORDER BY clause helps you to select the record to take. For example, ORDER BY would allow you to pick the customer with the highest sales volume in a specified state.

13

Get Fields

Click to return a list of available fields from the input stream(s) of the step.

14

Get lookup fields

Click to return a list of available fields from the lookup table that can be added to the step's output stream.

Dynamic SQL row

Description

Dynamic SQL row is a step in the Lookup Plugin for Process Studio Workflows. The Dynamic SQL row step allows you to execute a SQL statement that is defined in a database field. The lookup values are added as new fields in the output stream

Configurations

No.

Field Name

Description

1

Step name

Specify the name of the step as it appears in the workflow workspace. This name has to be unique in a single workflow.

2

Connection

Specify the field that contains a database connection for the lookup.

3

SQL field name

Specify the field that contains the SQL to execute.

4

Number of rows to return

Specify the number of records to return. 0 means, return all rows.

5

Outer Join

- false: don't return rows where nothing is found  

- true: at least return one source row, the rest is NULL

6

Replace variables

In case you want to use variables in the SQL, e.g. ${table_name}, this option needs to be checked.

7

Query only on parameters change

If your SQL statements do not change a lot, check this option to reduce the number of physical database queries.

8

Template SQL

In PROCESS STUDIO meta and data are separate so you have to specify the meta part in template SQL (field name and type). 
I mean any statement that returns the same row structure.

File exists

Description

File exists is a step in the Lookup Plugin for Process Studio WorkflowsThis step verifies the existence of a file where the filename comes from previous steps. The result is a Boolean flag field that gets added to the input fields in the output. 

Configurations

No.

Field Name

Description

1

Step name

Specify the name of the step as it appears in the workflow workspace. This name has to be unique in a single workflow.

2

Filename field 

Specify the input field that will contain the filename at runtime 

3

Result fieldname 

Specify the name of the field that will contain the boolean flag. 

4

Add filename to result 

Enable checkbox if you want to add the filename to the list of filenames that can be used in the next process entry.

5

Include file type

Enable checkbox to specify the file type in a field. 

6

File type field

Specify the  name of the field that will contain the file type as a String: "file", "folder", "imaginary" 

Fuzzy match

Description

Fuzzy match is a step in the Lookup Plugin for Process Studio Workflows. The Fuzzy Match step finds strings that potentially match using duplicate-detecting algorithms that calculate the similarity of two streams of data. This step returns matching values as a separated list as specified by user-defined minimal or maximal values.

Configurations

General Tab: 

No.

Field Name

Description

1

Step name

Specify the name of the step as it appears in the workflow workspace. This name has to be unique in a single workflow.


Lookup stream(source):


2

Lookup step

Specify the step that contains the fields to match.

3

Lookup field

Specify the field in the Lookup step above to match.


Main Stream:


4

Main stream field

Identifies the primary stream to match with the Lookup field.


Settings:


5

Algorithm

Identifies which string-matching algorithm to use. Options include,

  1. Jaro 
  2. Jaro Winkler
  3. Pair letters similarity 
  4. Levenshtein
  5. Damerau-Levenshtein
  6. Needleman Wunsch
  7. Metaphone 
  8. Double Metaphone
  9. SoundEx
  10. Refined SoundEx

6

Case sensitive

Enable or disable checkbox to determine if streams can or cannot differ based on the use of uppercase and lowercase letters---only for use with the Levenshtein algorithms

7

Get closer value

When checked, returns a single result with the highest similarity score---when unchecked, returns all matches that satisfy the minimal and maximal value setting as a separated list, separated by the values separator

8

Minimum value

Specify the lowest possible similarity score

9

Maximal value

Specify the highest possible similarity score

10

Values separator

Specify the string that separates the matches. Only available for specific algorithms and when the Get closer value option is unchecked.


Fields Tab: 

No.

Field Name

Description


Output fields:


1

Match field

Defines the name of the column that contains the comparison value.

2

Value field

Defines the similarity score for which to return a value.

3

Additional fields:

Specify the list of additional fields to retrieve from the lookup stream.

HTTP Client

Description

HTTP Client is a step in the Lookup Plugin for Process Studio Workflows. The HTTP client step performs a simple call to a base URL with options configured. The result is stored in a String field with the specified name. 

Configurations

No.

Field Name

Description

1

Step name

Specify the name of the step as it appears in the workflow workspace. This name has to be unique in a single workflow.

2

URL

Specify the base URL string to call.

3

Accept URL from field?

Enable this option if you want to get the URL from a previous step. Enabling this will also allow you to specify the name of the input field. 

4

URL field name

Specify the name of the incoming field that contains the URL

5

Connection timeout

Specify the timeout until a connection is established (milliseconds)

6

Socket timeout

Specify the number of seconds to wait if no data is returned from the server.

7

Connection close wait time

Specify Connection close wait time to Close all connections older than the value specified here.

8

Result fieldname

Specify the name of the field to store results

9

HTTP status code field name

Specify the name of the field to store the HTTP response code (e.g. 200, 404)

10

Response time (milliseconds) field name

Specify the name of the field to store the response time

11

Http Login

Specify the username to be passed during HTTP (Basic) authentication

12

HTTP Password

Specify the password to be passed during HTTP (Basic) authentication

13

Proxy Host

Specify the hostname of the Proxy Server to be used

14

Proxy Port

Specify the port number of the Proxy Server to be used

15

Parameters

Provide parameter name-value pairs to pass on the URL

16

Custom HTTP Headers 

Provide optional HTTP headers 

HTTP Post

Description

HTTP Post is a step in the Lookup Plugin for Process Studio Workflows. This step uses an HTTP POST command to submit form data via a URL.

Configurations

General Tab: 

No.

Field Name

Description

1

Step name

Specify the name of the step as it appears in the workflow workspace. This name has to be unique in a single workflow.

2

URL

Specify the Web service URL to submit the HTTP Post.

3

Accept URL from field?

If checked, you must specify which field to retrieve the URL from.

4

URL field name

If the previous option is checked, this is where you specify the URL field.

5

Encoding

The encoding standard for the files being accessed.

6

Request entity field

Specify the name of the field that will contain the POST request. When enabled, the Post a file option below will retrieve the file named in this field, and post the contents of that file.

7

Post a file

If a file is defined in the Request entity field, its contents will be posted if this option is checked. Currently "Request entity field" must be filled in order for "Post a file" to work. Selecting "Post a file" and specifying a field under "Body parameters" without selecting a value for "Request entity field" (the place for the file name) will fail silently.

8

Connection timeout 

Defines the timeout (defaults to 10000) in Milliseconds when connection attempts error out. 

9

Socket timeout 

Defines the timeout (defaults to 10000) in Milliseconds when socket error out. 

10

Connection close wait time 

Define the wait time after which the connection is closed in Milliseconds, the default -1 means the default wait time from the operating system (often 2 minutes). 

11

Result fieldname

Specify the field that you want to post the result output to.

12

HTTP status code fieldname

Specify the field that you want to post the status code output to.

13

Response time (milliseconds) fieldname

Specify the field that you want to post the response time, in milliseconds, to.

14

HTTP login

If this form requires authentication, this field should contain the username.

15

HTTP password

If this form requires authentication, this field should contain the password that corresponds with the username.

16

Proxy host

Hostname or IP address of the proxy server, if you use one.

17

Proxy port

Port number of the proxy server, if you use one.


Fields Tab: 

No.

Field Name

Description


Body(Header) Parameters:


1

#

Specify the order in which parameter will be passed to the Web application.

2

Name

Specify the name of the field that contains the value to map to the parameter.

3

Parameter

Specify the parameter to map the value of Name to.

4

Put in Header?

If set to Y, the parameter will be put into the request header.


Query Parameters:


1

#

Specify the order in which parameters will be passed to the Web application.

2

Name

Specify the name of the field that contains the value to map to the parameter.

3

Value

Specify the value to map to the parameter.

REST Client

Description

REST Client is a step in the Lookup Plugin for Process Studio Workflows. The REST Client workflow step enables you to consume RESTful services. 

Configurations

General Tab: Enter basic connection information for accessing a resource.

No.

Field Name

Description

1

Step name

Specify the name of the step as it appears in the workflow workspace. This name has to be unique in a single workflow.

2

URL

Specify the path to a REST client resource. 

(Representational State Transfer (REST) is a key design idiom that embraces a stateless client-server architecture in which the web services are viewed as resources and can be identified by their URLs).

3

Accept URL from field

Enable checkbox to accept path to a resource from a field.

4

URL field name

Choose a field which defines the path to a resource 

5

HTTP method

Choose how the step interacts with a resource---options are either GET, PUT, DELETE, POST, HEAD, or OPTIONS

6

Get Method from field

Enable checkbox to GET method from a field.

7

Method fieldname

Specify the field from which the GET method is defined.

8

Body field

Choose a field from the dropdown list that contains the request body for POST, PUT, and DELETE methods

9

Application type

Choose what type of application a resource is---options are either TEXT PLAIN, XML, JSON, OCTET STREAM, XHTML, FORM URLENCODED, ATOM XML, SVG XML, or TEXT XML

10

Result fieldname

Specify the name of the result output field

11

HTTP status code fieldname

Specify the name of the HTTP status code field

12

Response time (milliseconds) fieldname

Specify the name of the response time field


Authentication Tab: Enter authentication details for a resource in the Authentication tab.

No.

Field Name

Description

1

HTTP Login

Specify the username required to access a resource

2

HTTP Password

Specify the password associated with the provided HTTP Login user name

3

Preemptive

Enable checkbox  to send the authentication credentials before a server gives an unauthorized response

4

Proxy Host

Specify the name of a proxy host, if proxy authentication is required

5

Proxy Port

Specify the port number of a proxy host, if proxy authentication is required


SSL Tab: Provide authentication details for accessing a resource that requires SSL certificate authentication

No.

Field Name

Description

1

Truststore file

Specify the location of a truststore file

2

Truststore password

Specify the password associated with the provided truststore file


Headers Tab: 

No.

Field Name

Description

1

Field

Define the content of any HTTP headers using an existing field. Populate the list of fields by clicking the Get fields button.

2

Name

Provide a name 


Parameters Tab: 

No.

Field Name

Description

1

Parameter

Choose the parameters to set value.

2

Parameter

Provide parameter values for POST, PUT, and DELETE requests. GET parameters should be part of the URL directly.


Matrix Parameters Tab: 

No.

Field Name

Description

1

Parameter

Choose the parameter to set value.

2

Parameter

Provide parameter values for POST, PUT, and DELETE requests. GET parameters should be part of the URL directly.


Stream Value Lookup

Description

Stream Value Lookup is a step in the Lookup Plugin for Process Studio WorkflowsThe Stream lookup step looks up data and adds it to information coming from other source steps in the workflow. The data coming from the Source steps is cached into memory and then a common key field is used to look up data from the lookup table.

In the example below, database table input is the primary input. The workflow looks up data in a text-file based on a common key field.


Note: Consider using the Database Lookup step when the Lookup step originates from a table. In this case, it is faster to use the Database Lookup step and enable the option. Load all data from table that preloads the cache.

Configurations

The table below describes the features available for configuring the stream lookup:

Content Tab: Current Options Section

No.

Field Name

Description

1

Step name

Specify the  name of the step this name has to be unique in a single workflow

2

Lookup step

Specify the step name where the lookup data is coming from

3

The keys to lookup...

Specify the names of the fields that are used to look up values. Values are always searched using the "equal" comparison

4

Fields to retrieve

Specify the names of the fields to retrieve here, as well as the default value in case the value was not found or a new field name in case you didn't like the old one.

6

Preserve memory

Enabling this checkbox encodes rows of data to preserve memory while sorting. 

7

Radio Button 1: Key and value are exactly one integer field

Preserves memory while executing a sort by. Note: Works only when "Preserve memory" is checked. 

Cannot be combined with the "Use sorted list" option below. 

8

Radio Button 2: Use sorted list

Enable to store values using a sorted list; this provides better memory usage when working with data sets containing wide row. Note: Works only when "Preserve memory" is checked. Cannot be combined with the "Key and value are exactly one integer field" option. 

9

Button: Get fields

Automatically fills in the names of all the available fields on the source side (A); you can then delete all the fields you don't want to use for lookup.

10

Button: Get lookup fields

Automatically inserts the names of all the available fields on the lookup side (B). You can then delete the fields you don't want to retrieve

Table exists

Description

Table Exists is a step in the Lookup Plugin for Process Studio Workflows. You can use the Table Exists step to verify if a certain table exists in a database.  The result is a Boolean flag field that gets added to the input fields in the output. 

Configurations

No.

Field Name

Description

1

Step name

Specify the name of the step as it appears in the workflow workspace. This name has to be unique in a single workflow.

2

Connection

Specify the database connection to use

3

Schema name

Specify the schema of the table to look for 

4

Table name field

Specify the name of the field that will contain the database table name to look for.

5

Result fieldname

Specify the name of the field that will contain the Boolean result flag in the output of this step.

Web Services Lookup

Description

Web Services Lookup is a step in the Lookup Plugin for Process Studio Workflows. This step performs a Web Services lookup using the Web Services Description Language (only SOAP WSDL requests / responses are understood.  And some WSDL XML dialects and formats are not easily read, you may manually need to specify input and output fields. As a workaround Use SOAP UI, generate the XML you need and you may push that to the service using the HTTP Post step). 

 

Configurations

No.

Field Name

Description

1

Step name

Specify the name of the step as it appears in the workflow workspace. This name has to be unique in a single workflow.

2

URL

Specify the base URL pointing to the WSDL document that will be retrieved 

3

Operation

Choose an operation to call.

4

Operation request name (optional)

On occasions the WSDL gives back the wrong operation request name to put in the body. Put in the correct one(as expected by the service) here.

5

Load (button) 

The load button loads the WSDL at the specified URL and tries to automatically populate the input and output tabs and fields. 
NOTE: If this doesn't work, you can still try to manually specify the input and output fields using the "Add Input" and "Add Output" buttons. 

6

The number of rows per call 

Specify the number of rows to send with each WSDL call 

7

Pass input data to output

If you disable this, the input will be eaten and only the WSDL output will be passed along to the next steps. 

8

v2.x/3.x compatibility mode 

Enable this checkbox to support engine v2.x as well as 3.x. The parsing engine is 3.x.  For compatibility reasons, 2.x is around to make sure older steps would still work OK. 

9

Repeating element name

Specify the name of the repeating element in the output XML (if any). 

10

HTTP authentication 

Specify the username and password if these are required for the web service. 

11

Proxy to use (optional)

Specify the proxy host and port information if necessary. 

12

Add Input / Add Output 

These buttons will allow you to manually specify the input and output specifications of the WSDL service. 
Note: The data of the input fields can be escaped by the Calculator step and the function "Mask XML content from string A" 





      Links to better reach 

            Bot Store

             EPD