GETUSERSFROMDIR

Overview

The GETUSERSFROMDIR workflow application lets you retrieve a username list, a user email list, or a user ID list. These lists are obtained from a WorkflowGen automatic system action that executes SQL queries on the WorkflowGen database. They can either be used for automatic notifications or to define the users of an action in a WorkflowGen process (by storing the output of GETUSERSFROMDIR as a data element and using the resulting data element to specify the users in the notification or action assignment).

Definitions

• The x character: The x character in some parameter names means that there can be more than one instance of the parameter. For example, QUERYx_CMD means there can be QUERY1_CMD, QUERY2_CMD, QUERY3_CMD, etc.

• Action: You must create a GETUSERSFROMDIR type WorkflowGen action to use this application.

List of available fields and macros for queries

Listed below are the fields and macros available for the queries created in the QUERYx_CMD parameters. They can be used in the conditions for these queries to filter their results.

📌 Examples

This example returns a username list of users in the province of Quebec:

QUERY1_CMD: STATE = 'QC'  
RESULT_LIST

This example returns a username list of users that have email addresses ending with advantys.com:

QUERY1_CMD: EMAIL LIKE '%ADVANTYS.COM'  
RESULT_LIST

This example returns a username list of users in my group:

QUERY1_CMD: {ISMEMBER(MYGROUPNAME)}  
RESULT_LIST

Available fields	Definition
LASTNAME	Last name
FIRSTNAME	First name
USERNAME	Username
EMAIL	Email
PHONE	Telephone
MOBILE	Mobile
PAGER	Pager
FAX	Fax
OFFICE	Office
DEPARTMENT	Department
COMPANY	Company
JOBTITLE	Job Title
PERSONALTITLE	Title
EMPLOYEENUMBER	Employee number
EMPLOYEETYPE	Employee type
POSTALADDRESS	Postal address
POSTALCODE	Zip code
CITY	City
STATE	State/Area
COUNTRY	Country
EXTATT_1	Extended attribute 1
EXTATT_2	Extended attribute 2
EXTATT_3	Extended attribute 3
EXTATT_4	Extended attribute 4
EXTATT_5	Extended attribute 5

Available macros	Definition
{ISMEMBER(groupname)}	Get the group’s members

Using additional parameters

QUERY1_CMD: Executing an SQL query

Description

To execute an SQL query, you must add the QUERY1_CMD IN parameter to the action. If the parameter is empty or non-existent, no operation will be performed.

📌 Example

This example returns the Montreal username list in the RESULT_LIST parameter:

QUERY1_CMD: CITY = 'MONTREAL'  
RESULT_LIST

RESULT_LIST / QUERYx_RESULT_LIST: Retrieving queries results

Description

To get the result of all the queries (username list in string format), you must add the RESULT_LIST OUT parameter to the action. To retrieve the result by query, you have to add the QUERYx_RESULT_LIST OUT parameter for each query (associated to their process data).

📌 Example

This example returns the username list of Montreal, Toronto, and New York in the RESULT_LIST parameter. QUERY1_RESULT_LIST contains the username list of Montreal, QUERY2_RESULT_LIST contains the username list of Toronto, and QUERY3_RESULT_LIST contains the username list of New York. RESULT_LIST contains the usernames of all three QUERYx_CMD parameters.

QUERY1_CMD: CITY = 'MONTREAL'  

QUERY2_CMD: CITY = 'TORONTO'  

QUERY3_CMD: CITY = 'NEW YORK'  

QUERY1_RESULT_LIST  

QUERY2_RESULT_LIST  

QUERY3_RESULT_LIST  

RESULT_LIST

As of WorkflowGen version 7.15.0, the result of a query returned into a TEXT process data no longer has a 4000-character limit for MS SQL Server database.

QUERY1_DIR: Directory specification

Description

You can specify the user directory on which the query will be executed. You have to add the QUERY1_DIR IN parameter in the action. If this parameter has a NULL value or does not exist, the default directory is WORKFLOWGEN (or the directory specified as the default by an Administrator).

📌 Example

This example returns the username list of users in Montreal from the YourCompany directory into the RESULT_LIST parameter:

QUERY1_CMD: CITY = 'MONTREAL'  

QUERY1_DIR: YourCompany  

RESULT_LIST

If you manage users in WorkflowGen with multiple directories, you must use this parameter to specify the directory to search, and may need to use more than one query of a similar nature to specify all possible directory users (one query per directory).

QUERYx_CMD: Using more than one query

Description

You can add queries in the same WorkflowGen action and retrieve all of the results in one complete list or in one list per query. You must add the QUERYx_CMD IN parameters, where x is the number of the query. To get the result of each query, you have to add the QUERYx_RESULT_LIST OUT parameters.

📌 Example

This example returns the username list of users from Montreal in the WORKFLOWGEN directory into the QUERY1_RESULT_LIST parameter, the username list of users from Montreal in the INTRANET directory into the QUERY2_RESULT_LIST parameter, and the complete username list from the two queries into the RESULT_LIST parameter:

QUERY1_CMD: CITY = 'MONTREAL'  

QUERY1_DIR: WORKFLOWGEN  

QUERY2_CMD: CITY = 'MONTREAL'  

QUERY2_DIR: INTRANET  

QUERY1_RESULT_LIST  

QUERY2_RESULT_LIST  

RESULT_LIST

QUERYx_TOP: Specify results maximum

Description

You can specify the maximum number of records returned by your queries by adding QUERYx_TOP as an IN parameter of the action. The query will not be able to return more results than the number specified in this parameter.

📌 Examples

This example returns only the first two usernames of the query:

QUERY1_CMD: CITY = 'MONTREAL'  

QUERY1_TOP: 2  

RESULT_LIST

This example returns all usernames from this query:

QUERY1_CMD: CITY = 'MONTREAL'  

RESULT_LIST

QUERYx_DEFAULT_VALUE: Specify a default value

Description

You can specify a default value to be returned for queries that return no values. You must add the QUERYx_DEFAULT_VALUE IN parameter in the action.

📌 Example

In this example, the query returns no value because the company entered does not exist, so the RESULT_LIST parameter will contain default1:

QUERY1_CMD: COMPANY = 'COMPANY_DOES_NOT_EXIST'  

QUERY1_DEFAULT_VALUE = default1  

RESULT_LIST

RESULT_SEPARATOR: Separator specification

Description

You can specify the character that will separate the result in the returned list by adding the RESULT_SEPARATOR IN parameter. The default separator is , (comma).

📌 Example

In this example, the RESULT_LIST parameter value is: name1***name2***name3:

QUERY1_CMD: CITY = 'MONTREAL'  

RESULT_SEPARATOR: ***  

RESULT_LIST 

This example returns the value: name1, name2, name3:

QUERY1_CMD: CITY = 'MONTREAL'  

RESULT_LIST

RESULT_COUNT / QUERYx_RESULT_COUNT: Number of records returned

Description

You can retrieve the number of records returned for each query or for all queries. For the total number of records returned for all queries, you must add the RESULT_COUNT OUT parameter to the action. For the number of records per query, you must add the QUERYx_RESULT_COUNT OUT parameters to the action.

📌 Example

The following example returns the total number of records in the RESULT_COUNT parameter and the number of records of each query (QUERY1 and QUERY2) in the QUERY1_RESULT_COUNT and QUERY2_RESULT_COUNT parameters:

QUERY1_CMD: CITY = 'NY'  

QUERY1_DIR: WORKFLOWGEN  

QUERY2_CMD: CITY = 'NY'  

QUERY2_DIR: INTRANET  

RESULT_LIST  

RESULT_COUNT  

QUERY1_RESULT_COUNT  

QUERY2_RESULT_COUNT

Using parameter conditions in SQL queries

Description

You can also use parameters in the SQL conditions contained in the QUERYx_CMD parameter. You must add IN parameters with different names for the following reserved parameters:

• QUERYx_CMD

• QUERYx_DIR

• QUERYx_TOP

• QUERYx_RESULT_COUNT

• QUERYx_RESULT_LIST

• RESULT_LIST

• RESULT_COUNT

• RESULT_SEPARATOR

You can then call these parameters in the condition by prefixing them with the @ character.

📌 Example

This example returns the username list from Montreal in the WORKFLOWGEN directory:

QUERY1_CMD: CITY = @PARAM1  

PARAM1: MONTREAL  

RESULT_LIST

Remember that the parameter name used in the condition must have the same name as the one defined in the WorkflowGen action.

PARAM1 is an arbitrary convention; any parameter name may be used.

QUERYx_CMD: Using macros in SQL query conditions

Description

You are also able to use macros in the SQL conditions contained in QUERYx_CMD by adding an available macro in the condition.

📌 Example

The following example returns the username list of users who are members of the Dev group, members of the WORKFLOWGEN directory, and are in Montreal:

QUERY1_CMD: CITY = 'MONTREAL' and {ISMEMBER(Dev)}  

RESULT_LIST

You must respect the syntax in the Available macros list.

RESULT_LIST_EMAIL / QUERYx_RESULT_LIST_EMAIL: Retrieving the queries result in an email list format

Description

If you want to retrieve the emails of the usernames returned by the queries, you must add the RESULT_LIST_EMAIL OUT parameter to the action. If you want to retrieve the emails by query, you must add the QUERYx_RESULT_LIST_EMAIL OUT parameter to each query.

📌 Example

This example returns the Montreal and Toronto email list of Montreal and Toronto in the RESULT_LIST_EMAIL parameter. QUERY1_RESULT_LIST_EMAIL contains the Montreal email list and QUERY2_RESULT_LIST_EMAIL contains the Toronto email list:

QUERY1_CMD: CITY = 'MONTREAL'  

QUERY2_CMD: CITY = 'TORONTO'  

QUERY1_RESULT_LIST_EMAIL  

QUERY2_RESULT_LIST_EMAIL  

RESULT_LIST_EMAIL

Error management in WorkflowGen

To manage execution errors, you must add a corrective (debug) action linked to the GETUSERSFROMDIR action. This corrective action is a manual action and is executed upon an execution error. You must add this exception on the transition between the two actions. The following table lists the most frequent errors returned by the application.

Error message	Solution
This directory does not exist	Check if the directory entered in the query is valid.
XML context is empty or incorrect	Verify if the application has been launched by WorkflowGen.
Database connection error	Check if the database connection string in the config.inc file is valid.
Query error	Check if the fields or macros used in the QUERYx_CMD parameter are valid.
The queries return too many values	Add conditions in the QUERYx_CMD parameter to filter the results.
The parameter [parameter name] has the same name as a WorkflowGen-reserved parameter. You must change the name.	Change the name of this parameter for a name other than the reserved parameters’ names.
The queries return too many records	Add conditions in the QUERYx_CMD parameters to filter the results.
The parameter [parameter name] must have a value	Check in the WorkflowGen action if the parameter is empty.
[Parameter name] parameter is required	Add this parameter to the WorkflowGen action.

Maximum returned records number

The GetUsersFromDirMaxResultNb parameter in the WorkflowGen web.config file specifies the maximum number of records that can be returned by a query.

This limit reduces the risk of a time limit expiration error that may occur when query results are too large. If the number of returned records is higher than this constant value, an error message stating that the queries returned too many records will be displayed.