# 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.<br>
* **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`       | User name            |
| `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
```

{% hint style="info" %}
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.
{% endhint %}

### `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
```

{% hint style="info" %}
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).
{% endhint %}

### `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.

{% hint style="info" %}
`PARAM1` is an arbitrary convention; any parameter name may be used.
{% endhint %}

### `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
```

{% hint style="warning" %}
You must respect the syntax in the **Available macros** list.
{% endhint %}

### `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.