Defining Conditions

Learn to define new conditions.

To add a new condition to a query:

  1. Load a query, see Loading Queries.
  2. Click on to the left of Conditions and select Add Condition from the menu. The following window opens:

    The fields that you can access based on your Aurea CRM access rights are displayed. Your administrator is responsible for defining your access rights.

  3. Click on a column heading to sort the list.
  4. Select a field and click on Next. You can also enter part of the name of the field in the Field name field:
    • Enter one or two letters to list all the fields that begin with those letters.
    • Enter more than two letters to list all fields that contain the sub-string in their name.
  5. Define your condition. Depending on the Field Type, you can define the following conditions:
  6. Click on OK. This condition displayed in the overview.

Conditions for Catalogs

To define a condition for a catalog:

  1. Select a catalog field. A dialog box is opened, allowing you to define the condition for the field.

    The available catalog values are listed to the left. Use as Parameter is at the top of the list. You can use this value if the comparison value should be entered whenever the query is run. For further details, see Parameters and Predefined Queries.

  2. Choose the appropriate operator, e.g. = (equal) to determine that the condition should return records where the value of the catalog field matches the comparison value.
  3. Select Fixed to compare the contents of the field with a fixed value from the list of entries. Select Field to compare the contents of a field, see Comparing Field Contents. The Text option is also available for variable catalogs, see Conditions for Variable Catalogs.
  4. Double-click on an entry under Available fields to use it as a comparison value. The entry is moved to the right under Selected Fields.
  5. Double-click on an item in the right area to remove it.
  6. Click on OK to return to the query.

Conditions for Variable Catalogs

When applying conditions to variable conditions, you can also use wildcards in comparison values. This allows you to apply conditions to sub-strings. To do so:

  1. Select a variable catalog field. The dialog used to define the condition is displayed.

  2. Click on Text.
  3. An input field is displayed. Enter the comparison value here. You can use the * (any number of characters) and ? (single character) wildcards. All catalog values that match your comparison value are returned by the query.

Conditions for Child Catalogs

When defining a condition applied to a child catalog (e.g. the Interest field in the Interest info area), you need to specify the parent catalog value (e.g. Interest Group) before you can define the condition for the child catalog.

The following example uses the Interest field in the Interest info area:

Conditions applied to child catalogs are otherwise defined the same as for all other catalogs. For further details, see Conditions for Catalogs.

Conditions for Boolean Fields

To define a condition for a Boolean field:

  1. Select a Boolean field.

  2. Specify the type of comparison (equal, not equal).
  3. Select Fixed to compare the contents of the field with a fixed value from the list of entries. Select Field to compare the contents of a field. For further details, see Comparing Field Contents.
  4. Select either Yes, (true) No (false) or Use as parameter as the comparison value. For further details, see Parameters and Predefined Queries
  5. Click on OK to return to the query overview.

Conditions for Date Fields

To define a condition applied to a date field:

  1. Select a date field.

  2. Select the type of comparison (equal, not equal etc.).
  3. Date fields allow you to use fixed and variable comparison values. Use the Fixed option to compare the value with a specific date or to a value entered by the user (if the Use as Parameter option is enabled). Use the Variable option to enter a date relative to the date the query is executed on. Select Field to compare the value with the contents of another field. For further details, see Variable Conditions, Comparing Field Contents and Parameters and Predefined Queries.
  4. Click on OK to return to the query overview.

Conditions for Numeric, Alphanumeric and Time Fields

To define a condition applied to a numeric, text or time field:

  1. Select either a numeric, text or time field.

  2. Define the type of comparison (equal, greater etc.).
  3. Determine what to compare the contents of the field with. Choose between Fixed, Variable, Field (compare with the contents of another field). For further details, see Variable Conditions and Comparing Field Contents. If you select Fixed, you can enable Use as parameter (requires user input) to allow users to enter the comparison value when executing the query. For further details, see Parameters and Predefined Queries.
  4. Enter the comparison value in the Value field.
  5. Enable the Use as Parameter option if you want the comparison value to be entered by the user upon starting the query. For further details, see Parameters and Predefined Queries. Wildcards (*, ?) may also be used for text fields.
  6. Click on OK to return to the query overview.

Conditions for Rep Fields

To apply a condition to a rep field:

  1. Select a rep field.

  2. Specify the type of comparison (equal, not equal).
  3. You can define both Fixed and Variable conditions for reps. Use the Field option to compare the rep to an entry in another rep field. For further details, see Comparing Field Contents. Enable the Use as Parameter option if you want a comparison value to be entered by the user upon starting the query. For further details, see Parameters and Predefined Queries.

    If you have selected Fixed, you need to select the rep used in the condition.

    Note: You can select several reps as comparison values. The reps are linked with a logical OR.

    Select the rep(s), see Selecting Reps.

    The selected reps are listed in the rep field. Reps are separated using a semicolon.

Variable Conditions

If the same query is used by various reps or is carried out regularly, you can define variable conditions that are applied to rep and date fields (e.g. the current rep or current date).

Variable Reps

To define a variable condition applied to rep field:

  1. Select a rep field and enable the Variable option.

  2. Select an option from the drop-down list. The appropriate user is used by the query as a comparison value when it is executed.

Variable Date Fields

Variable dates are dates relative to the date on which the query is executed, e.g. 40 days from today.

To define a variable condition for a date field:

  1. Select a date field and enable the Variable option.
  2. Select the reference date from the drop-down list.
  3. Determine time period added (Plus) or subtracted (Minus) from the reference date.

Variable Time Fields

The time at which the query is executed can be used to define the comparison value for time fields.

To define a variable condition for a time field:

  1. Select a time field and choose the desired comparison type.
  2. Select the desired time format from the drop-down list.

Comparing Field Contents

When defining conditions, you can choose to compare the value stored in fields in a record.

You can compare the following fields:

  • Fields of the same type, e.g. two numeric fields
  • Catalog fields that access the same catalog

To define a condition that compares the contents of two fields:

  1. Define a condition.
  2. Select the Field option.

  3. Click on (Select). All fields of the same type in the info area are listed.
  4. Select the desired field for the comparison.