GETUSERSFROMDIR Workflow Application

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:
1
QUERY1_CMD: STATE = 'QC'  
2
RESULT_LIST
Copied!
This example returns a username list of users that have email addresses ending with advantys.com:
1
QUERY1_CMD: EMAIL LIKE '%ADVANTYS.COM'  
2
RESULT_LIST
Copied!
This example returns a username list of users in my group:
1
QUERY1_CMD: {ISMEMBER(MYGROUPNAME)}  
2
RESULT_LIST
Copied!
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:
1
QUERY1_CMD: CITY = 'MONTREAL'  
2
RESULT_LIST
Copied!
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.
1
QUERY1_CMD: CITY = 'MONTREAL'  
2
​
3
QUERY2_CMD: CITY = 'TORONTO'  
4
​
5
QUERY3_CMD: CITY = 'NEW YORK'  
6
​
7
QUERY1_RESULT_LIST  
8
​
9
QUERY2_RESULT_LIST  
10
​
11
QUERY3_RESULT_LIST  
12
​
13
RESULT_LIST
Copied!
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:
1
QUERY1_CMD: CITY = 'MONTREAL'  
2
​
3
QUERY1_DIR: YourCompany  
4
​
5
RESULT_LIST
Copied!
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:
1
QUERY1_CMD: CITY = 'MONTREAL'  
2
​
3
QUERY1_DIR: WORKFLOWGEN  
4
​
5
QUERY2_CMD: CITY = 'MONTREAL'  
6
​
7
QUERY2_DIR: INTRANET  
8
​
9
QUERY1_RESULT_LIST  
10
​
11
QUERY2_RESULT_LIST  
12
​
13
RESULT_LIST
Copied!
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:
1
QUERY1_CMD: CITY = 'MONTREAL'  
2
​
3
QUERY1_TOP: 2  
4
​
5
RESULT_LIST
Copied!
This example returns all usernames from this query:
1
QUERY1_CMD: CITY = 'MONTREAL'  
2
​
3
RESULT_LIST
Copied!
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:
1
QUERY1_CMD: COMPANY = 'COMPANY_DOES_NOT_EXIST'  
2
​
3
QUERY1_DEFAULT_VALUE = default1  
4
​
5
RESULT_LIST
Copied!
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:
1
QUERY1_CMD: CITY = 'MONTREAL'  
2
​
3
RESULT_SEPARATOR: ***  
4
​
5
RESULT_LIST 
6
​
7
This example returns the value: name1, name2, name3:
8
​
9
QUERY1_CMD: CITY = 'MONTREAL'  
10
​
11
RESULT_LIST
Copied!
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:
1
QUERY1_CMD: CITY = 'NY'  
2
​
3
QUERY1_DIR: WORKFLOWGEN  
4
​
5
QUERY2_CMD: CITY = 'NY'  
6
​
7
QUERY2_DIR: INTRANET  
8
​
9
RESULT_LIST  
10
​
11
RESULT_COUNT  
12
​
13
QUERY1_RESULT_COUNT  
14
​
15
QUERY2_RESULT_COUNT
Copied!
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:
1
QUERY1_CMD: CITY = @PARAM1  
2
​
3
PARAM1: MONTREAL  
4
​
5
RESULT_LIST
Copied!
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:
1
QUERY1_CMD: CITY = 'MONTREAL' and {ISMEMBER(Dev)}  
2
​
3
RESULT_LIST
Copied!
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:
1
QUERY1_CMD: CITY = 'MONTREAL'  
2
​
3
QUERY2_CMD: CITY = 'TORONTO'  
4
​
5
QUERY1_RESULT_LIST_EMAIL  
6
​
7
QUERY2_RESULT_LIST_EMAIL  
8
​
9
RESULT_LIST_EMAIL
Copied!
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.