Parent queries


The parent queries define homogeneous part information of a database, allowing working in the same theme (e.g.: Parent queries to work on incidents or equipment). They allow identifying:

  • the search criteria of the global search and the quick filter;
  • the selection criteria proposed in the filters in assisted mode (eg.: filter Incidents not solved this Week made out of the parent query Incidents);
  • the fields proposed in the views (eg.: views Equipment by department and List of equipment made out of the parent query Equipment); 
  • the fields proposed in the value lists

They also serve as support for the definition of:

Notes

  • The parent queries are delivered by Logo - EasyVista.png and are contextual in the menus options.
  • On all editing screens, the fields of the parent query are displayed under their alias name (eg.: Phone); if this one is not specified, they are displayed under their physical name (eg.:  PHONE_NUMBER).
  • You may create a parent query only on the reporting forms, when you are in Creation mode - Open url.png  See Procedures.
  • Only a profile owning edit rights to the module to which is attached the related option of menu using the parent query may change a parent query.
  • You may add fields to the parent query manually - Open url.png  see Best practice of creation
    • The fields will then be available in filters, views, values lists, forms and reporting tools attached to this query:
      • fields belonging to a table/jointure of the parent query;
      • fields belonging to a related table to one of the tables of the parent query;
      • fields belonging to an SQL view owning a jointure with one of the tables of the parent query.
  • Once added, they will be displayed on the bottom of the list and are preceded by Delete icon.png (allowing deleting them from the query).

Caution

  • Do not confuse the Product name - ev itsm.png views and the SQL views that are used as tables at the level of the parent queries.

Best Practice big icon.pngBest Practice

  • To ensure fields added manually are not impacted during an upgrade, they must comply with the following rules:
    • Type of the field:
      • Necessarily one of the following types: nvarchar, nvarchar(max), float, int, datetime, uniqueidentifier, bit, real, smallint
    • Name of the field:
      • It must be prefixed with E_.
      • It must be upper case.
      • It must not contain accented characters.
      • It must not contain space character. To insert a space, use the underscore character _.
      • It must not exceed 31 characters (constraint due to Report Designer).

Example documentation icon EN.png  E_BIRTHDAY_DATE

Example

The view List of employees uses the parent query Employees. To display the language of the employee, we must add it to the parent query manually:

1. Proceed to the menu option Asset Management > Directory > Employees.

2. Edit the parent query Employees: click on Tools icon.png then on Edit icon.png [ Edit ] (Note: You need to own a profile with the edit rights to the module to which is attached the menu Asset Management).

3. Add the table of the languages AM_LANGUAGE manually to the parent query (Note: At the Product name - ev itsm.png installation, this table is not in the list of tables of the parent query. In the data model, it is related to the table AM_EMPLOYEE by the relation Language Identifier):

  • Click on [ ADD TABLE ];
  • Click on Plus black icon.png to display the contents of the table AM_EMPLOYEE;
  • Click on the relation AM_LANGUAGE ===> AM_LANGUAGE.LANGUAGE_ID = AM_EMPLOYEE.LANGUAGE_ID;
  • Eventually rename the proposed default alias (AM_LANGUAGE);
  • Click on [ OK ].

4. Click on [ ADD FIELD ] then:

  • Click on Plus black icon.png related to the table added previously AM_LANGUAGE;
  • Click on the field LANGUAGE_SYSTEM_ALIAS_$lng (multilingual labels of the language) and eventually rename its physical name by an explicit alias;
  • Click on [ SAVE ].

5. click on Edit icon.png to the right of the drop-down list View (Note: You need to own a profile with the edit rights to the views to the module which is attached the menu Asset Management): 

  • The field LANGUAGE_SYSTEM_ALIAS_$lng is displayed in the list of available fields;
  • It can also be used in any filter, form and reporting tool related to this parent query.

Screens description

         Parent query - General.png

Access: in List mode, Tools icon.png > Edit icon.png [ Edit ]

Name: List of fields defined in one of the tables of the parent query, eventual jointures with SQL views and eventually related tables that have been added manually. To add others fields: [ ADD VIEW ] or [ ADD TABLE ]; then [ ADD FIELD ].

Field Name (Note: Cannot be changed): Physical name of the field in the data model.

Search by (Note: Not available for fields resulting from a calculation): Used to indicate if the field can be used as a search criteria in the quick filter (displayed via Filter icon.png in List mode) (box is checked) or not (box is not checked).

Search By – Default Value: Used to indicate if the field is displayed by default as a criteria in the quick filter (radio button is selected) or not (radio button is not selected).

Mobile Display (Note: For certain forms and grids related to equipment, incidents/requests, actions.): Used to indicate if the field can be displayed on a smartphone screen (box is checked) or not (box is not checked).  Open url.png See EasyVista Mobile

[ ADD VIEW ]: Creates a jointure between one of the fields defined in an SQL view and one of the fields of a related table to the parent query.
         Parent query - Add view.png

  • The jointure will be selectable as any table of the parent query. 
  • An alias name is assigned to the jointure via a window displayed once you have selected the jointure fields.
     

[ ADD TABLE ]: Creates a jointure between one of the tables of the parent query and one of the related tables in the database model.
         Parent query - Add table.png

  • The jointure will be selectable as any table of the parent query. 
  • Each related table is followed by the description of the jointure: key field in the related table, followed by the key field of the table of the parent query.
  • An alias name is assigned to the jointure via a window displayed once you have selected the jointure fields.
  • Options OUTER / INNER (Note: Displayed depending on the parent query): Specifies the type of records to return:
    • OUTER: All the records of the main table, even if there is no correspondence with the joined view;
    • INNER: Only the main table records that correspond to the joined view.
       

[ ADD FIELD ]: Adds a field among those defined in one of the tables of the parent query, the eventual jointures with SQL views and the eventual tables that have been added manually.
         Parent query - Add field.png

Procedures

How to change a parent query

1. Click on the option menu using the parent query to change, then click on Tools icon.png and on Edit icon.png [ Edit ] (Note: You need to own a profile with the edit rights to the module to which is attached the option menu): the list of the fields in the parent query grid is displayed.

2. Select the fields that should be used as criteria in the quick filter (columns Search by...).

3. You may add other fields in the parent query grid:

  • Field belonging to one of the tables/jointures of the parent query:
    • Click on [ ADD FIELD ] ;
    • Click on Plus black icon.png to display the list of fields available and select the one you want to use.
  • Field which do not belong to one of the tables/jointures of the parent query:
    • if the field is referenced in a table related to one of the tables of the parent query:
      • Click on [ ADD TABLE ];
      • Click on the Plus black icon.png to display the list of tables related to each table of the parent query and select the one you want to use;
      • Specify the type of records to return by selecting one of the options INNER or OUTER;
      • Enter the alias identifying the new jointure;
      • Add the desired fields belonging to this new jointure by clicking on [ ADD FIELD ] (Note: The jointure is displayed on the bottom of the list of the tables).
         
    • if the field is referenced in an SQL view:
      • Click on [ ADD VIEW ]; select the desired SQL and specify which table of the query is used for the relation;
      • Click on [ JOINTURE ] and specify the fields allowing to create the jointure between the SQL view and the table;
      • Specify the type of records to return by selecting one of the options INNER or OUTER;
      • Click on [ JOIN ] and enter the alias identifying the new jointure;
      • Add the desired fields belonging to this new jointure by clicking on [ ADD FIELD ] (Note: The jointure is displayed on the bottom of the list of the tables).

5. Click on [ SAVE ]: the fields added manually will then be available in filters, views, values lists, forms and reporting tools attached to this query.

How to create a parent query in the reporting tools

1. Proceed to one of the reporting tools:

  • Trends: Administration > Trends > Trends
  • Reporting: Administration > Reporting > Reporting or Scheduled Reportings
  • Alerts: Administration > Alerts > Scheduled Alerts

2. Click on Add icon.png to create a new form.

3. Click on Add icon.png related to the field Parent query (Note: Not available if you had select a parent query)

4. Define the new parent query:

  • enter its name;
  • select its main table;
  • add the fields that should be displayed in the list of available fields in the parent query grid: see the previous procedure, step 3;
  • click on [ SAVE ]: the new parent query will now be available in the Parent Query list.

How to use relations from an existing parent query

1. Execute the query allowing to get the content of the XML field of the C_QUERY table in the CONFIG environment.

2. Right-click on the results window. Click on "Save result as..." and save the result as a TXT file.

Example documentation icon EN.png  Query allowing to use relations with the HD_ACTIONS parent query (QUERY_ID = 49)

SELECT [query_id],[name_fr],[xml]
FROM   [EVO_CONFIG40000].[EVO_CONFIG40000].[c_query]
WHERE  query_id = 49  

Result:
         Parent query - Example relations.png

Tags:
Last modified by Unknown User on 2018/05/31 20:42
Created by Administrator XWiki on 2013/03/25 18:12

Shortcuts

Recent Updates

Haven't been here in a while? Here's what changed recently:

-   Product name - ev itsm.png
-   Product name - ev sas.png

Interesting Content

How to Automate Integration
Add a Shortcut to an App
History
Quick Dashboard
Full text search - Stop Words

Powered by XWiki ©, EasyVista 2018