Defining Queries

Learn how to define queries in Aurea CRM.

In Aurea CRM.Web and CRM.Designer you can define and edit queries using the AQL syntax, see AQL Syntax and Queries in the Aurea CRM.Web User Interface documentation.

To define a query in CRM.Designer:

  1. Select a query or create a new one. For further details, see Designing Configuration Units.

    When creating a new query you need to select the root info area first:

  2. Specify the following:
    • Readonly: If checked, users cannot edit the query in Aurea CRM.Web.
    • Invisible in lists: If checked, users cannot select the query in Aurea CRM.Web.
      Note: Use these settings for queries called via menu actions or used by predefined analyses. Alternatively, you can use the Query.Rights Web Configuration parameter to determine whether users can execute and/or edit predefined queries. For further details, see Query.Rights.
    • Include Associations: Determines whether affiliations should be taken into account. For further details, see Global Switch / Query-Specific Switch.
    • CRM Processes: Assign one or more CRM processes to the query. For further details, see Roles & CRM Processes.
    • Description: Add an internal description (only visible in CRM.designer).
    • Used by Mail Merge: Select this option to make the query appear on the template document used for Mail merge.
      Note:
      • The flag Use for Mail Merge is automatically set by the system when a query is used by Office Add-in
      • The flag Use for Mail Merge is explicitly set by users in the Query Definition screen.
      Note: On deleting a query with flag Use for Mail Merge set, the following additional pop-up message is shown:

      This query is selected to be used with Word Single- & Serial Letters. Deletion of the Query can cause different Word Templates stop working. Do you still want to delete the 'ExampleQuery' query?

  3. Fields: Define the query's output fields.
    Note: By default, rep ID fields (field type "uid") are transformed to contain the rep name (string) when displaying and exporting a query result. To get the rep ID instead, use the Export.EmitRepNames Web Configuration parameter or export option. For further details, see Export.EmitRepNames.

    After adding your fields, you can expand them using the button to define which fields is shown as links to records by checking desired the Link-Field boxes.

    Link fields are displayed like hyperlinks when a user executes a query or analysis, on the QueryRun page for queries used in forms and in analysis results.

    The link opens the info area the field is defined for. If a Z-field (i.e. a read-only field consisting of several fields from another info area) is defined as a link, this associated info area is opened when a user clicks on the link. In the example the link to the Z-field Person opens the Person (KP) record.

  4. Sort: Define the query's sort fields. Click asc to change the sort order to desc and vice versa.
  5. Filter: Define the filter criteria. For further details, see Defining Filters, Parameterized Queries and Advanced Filter Criteria.
    Note: Filters on decoded fields (Z-fields) are not supported. For further details, see Data Types in Aurea CRM.
  6. To add a (child) info area, click (Add) beside the root info area and select the desired info area. Specify the following:
    • LinkId: If you want to use another than the default link, specify the LinkId. For further details, see Link IDs.
    • Max Rows: Defines how many linked child records are read for each parent.
    • Optional: Check Optional to display the parent record only if a child record exists in at least one of the sub-info areas.
  7. Select the desired Relation type:
    • WITH: Returns the parent record only if at least one child record exists.
    • WITHOUT: Returns the parent record only if no child record exists; allows advanced conditions on the child records to be defined.
    • PLUS: Returns the parent record even if no child record exists. (This allows to implement the UNION operator from SQL.)
    • WITH SUMMED: Returns the parent record only if at least one child record exists; aggregates data from child records.
    • PLUS SUMMED: Returns the parent record even if no child record exists; aggregates data from child records.
    • HAVING: Returns the parent record only if at least one child record exists; allows advanced conditions on the child records to be defined.

      The query definition in CRM.Designer also supports extended conditions, but it does not include Aurea CRM.Web's restrictions on extended conditions. Therefore, you must define queries that can actually be executed.

      Extended conditions can only be used for tables that are related with the "HAVING" or "WITHOUT" types. For these types, you can use the SUM and COUNT fields at the end of the field list. Do not mix standard conditions and extended conditions and combine them with "AND".

  8. Define output fields, sort fields and filter criteria for the child info areas as applicable.
  9. Click Save.

    Queries defined in CRM.Designer are always saved as configuration units in the CRM.Designer database.

AQL syntax for the above example is visible in the AQL editor and is shown below:
select (Company, Country, ZipCode, Employees, Revenue, FIMA.Contact, FIMA.Date, FIMA.Time, FIMA.Duration, FIMA.Unit, FIMA.Subject, FIMA.Person) from (FI)
where (ABC="A")
with (MA as FIMA)
where (RepID="$curRep" OR ParticipantID="$curRep")
orderby (Company, FIMA.Contact, FIMA.Date)