Developing Query Definitions

Reusable queries are defined by the Query Definition item and found as follows: Cocntnents --> Administration --> Configuration --> Query Definitions.

When you click Create New Query Definition, the dialog shown in Figure 3 appears.

Each Query Definition starts with an initial Context Item before building it out to a more extensive structure. This specifies the root of the query, from where query of structures and property values will begin.

The basic steps for creating a Query Definition are as follows:

Define the Context Item.

Build the Relationship Structure.

Apply Search Conditions.

The following example creates a Query Definition Item for the Part ItemType as a Context Item. Create a new query definition and specify a unique Name and a Context Item Type.

Click

. The Show Editor icon appears in the ribbon.

1. Click the Show Editor icon on the left sidebar to go into Query-Editing Mode.
2. Right-click the Context Item and select Edit Query Element --> Selected Properties.



Select the desired properties that the query should return. In this example they are: id, Part Number, Name, State, and managed_by_id.



Extended properties enable you to add values such as cost to an object in the database.

3. Right-click on the Context Item again and select Edit Query Element --> Sort Order.
4. Choose the properties that should be used for the Order By command and specify whether they should be in ascending or descending order.



5. Save the Query.

It is possible to define a Relationship from the context item that can go either up or down the data structure. ‘Down’ refers to a relationship direction starting from the Item representing the source side of the relationship and following the related content to the related Item. ‘Up’ by contrast refers to a relationship direction starting from the Item representing the related side of the relationship and following the related content to the source Item. It is also possible to set up a recursive structure, such as the Part --> Part BOM --> Part, to reuse the same logic for multiple levels. The following procedures show how to expand the query both up and down for a Part Query and introduces recursion.

1. Right-click on the Part Context Item and select Add Related Item --> Using Relationship.



2. Select the Relationship to include in the structure. In this case, select BOM,check Include Relatedand click Add.



The query now looks like shown in figure 10. Note that the Part_1 alias is assigned to the related Part Query Element in the list.



3. Right-click the Part Context Item and click Add Related Item --> Using Relationship. In this case, click while pressing Ctrl to select Documents and CAD Documents. Select Include Related.



4. Right-click on the top Context Item and select Add Related Item --> Using Item Property. Select an Item Property to add to the query structure. In this case, select created_by_id.



5. Use the following procedure to specify the desired Properties for each new element in the Query Definition:
Note
Optionally, you can also set Order By settings as well.
1. For [created_by_id] User, specify the following properties:

Item IDFirst NameLast NameLogin Name

2. For [Relationship] Part CAD, specify the following property:

Sequence

3. For [Related] CAD, specify the following properties:

idDocument NumberNameState

4. For [Relationship] Part Document, specify the following property:

Sequence

5. For [Related] Document, specify the following properties:

idDocument NumberNameState

6. Change the Alias on the CAD Documents element to “Part CAD”:
1. Right-click CAD Documents and click Change Alias.



2. Enter Part CAD as the name and click the check mark.



7. Use the same procedure to change Documents Relationship to Part Document.
8. Use the same procedure to change User Relationship to Part Created By.
Note
Each element on a given level must have a unique alias.
9. Save the Query Definition.

1. Right-click the top Part element and click Add Related Item --> Using Referencing Item.



2. Select an appropriate Referencing item from the structure. In this case, select Simple MCO Part.



3. Right-click the Simple MCO Part element and click Add Related Item --> Using Item Property to select source_id.



4. Set Selected Properties on the Simple MCO element to:

idMCO NumberStatusDate Originated

5. Save the Query Definition.

Recursive structures can reuse sections of the already defined queries. The following example shows how to set up a recursive call for the Part --> Part BOM --> Part relationship structure.

1. Right-click on the top-level Part element and add the Part BOM --> Part structure.



2. For the BOM element, select the following properties:

SequenceQuantityrelated_id

3. Right-click on the child Part element and click Reuse Query Element Definition.



4. Select the top-level Part element and click Add.



5. Save the Query Definition item.

The child Part item now includes a search for the same elements as the top-level Part, including CADs, Documents, other Parts, etc. It also includes the same Properties and Order By logic as specified above.

You can specify Search conditions for every level of the Query Definition Structure. These search conditions can refine the set of data the Query returns. The query logic can include the components shown in Table 2. Query elements

Use the following process to add Conditions to the Query Definition item:

1. Right-click the CAD element and click Edit Query Element --> Condition.



2. Enter a search condition, such as [Name] = ‘CAD_1'Na.



Note
Conditions written in Query Builder must be separated by white space. For example, you can use the condition Cost >= 0 but not Cost>=0.
3. Right-click the Document element and click Edit Query Element --> Condition.
4. Enter a search condition such as [Document Number] like ‘A27%'.



Note
Brackets are placed around multi-word properties in the condition window of Query Builder. You may notice that these brackets automatically disappear for single-word properties.
5. Right-click the top Part element and click Edit Query Element --> Condition.
6. Enter a search condition that enforces the CAD and Document Relationships to exist:
7. (Count([[Relationship]] Part CAD) >= 1) AND (Count([[Relationship]] Part Document) >= 1)



8. Save the Query Definition.

The following example walks you through the process of defining query parameters. It uses Test Query as an example.

Click the Edit Parameters button

. The Query Parameters dialog box appears.

Click the New button

to add a parameter. Enter the following information:

Name: doc_state

Label: Document State

Default Value: *

1. Close the dialog box to save the parameter information.
2. Select the Document level of the relationship structure and click the associated Filter button to filter using the parameter you just created.
3. Set any other filters and sort orders that you need.
4. Save the Query definition.

The parameters that you add to the Query definition appear in the Map Parameters dialog box in the Tree Grid View.

Once mapped, the parameters become available in the Tree Grid View under the Modify Parameters

button.

This allows the entry or selection of the configured parameters from the Tree Grid View, setting query parameters in the underlying Query Definition from the Tree Grid User Interface at runtime. Parameters can also be used for previewing query results when Executing a Query in the Query Builder. See Section 3.0 “Executing Queries” for an example of this.

For structures of versionable Items, like Part and Part BOM Structures, it is useful to examine query results in a Tree Grid View by either the latest versions, or by released versions only, or a combination where if no released components exist then the latest (un-released) version is displayed in the overall structure. The default is to display the structure as it is stored in the database without any optional resolution. These Resolution Modes are provided to Part BOM Query Definitions (and Tree Grid Views) by a special Parameter which is pre-configured OOTB. Structure Resolution can be applied to any Item Relationship structure of versionable Items that follows the same rules as Part BOM structures: the Released Life Cycle State (is_released Boolean), incremental Revisions with interim versioned generations, etc.

The Default Resolution Mode uses the versions of related child Parts as stored on the BOM Relationships. This is the standard resolution in Aras Innovator.

The Default resolution mode

When the Latest resolution mode is selected, the structure is resolved starting from the top using the latest versions of child items. When a child item is resolved to its latest version, then its child items are resolved using this resolution mode. This is done recursively. The Latest resolution mode does not consider the released state of the child Parts.

Latest resolution mode

The latest revision D of Child B.2 (Preliminary) is shown where this Part version is in the Preliminary state, although the default structure under revision B of Child A (this structure) refers to revision B of Child B.2 (Released) as stored in the database. In other words, the Latest Resolution Mode dynamically provides a view to in-work changes to released parts that are not yet committed to the released structure.

When the Latest Released resolution mode is selected, the structure is resolved starting from the top using the latest versions of child items that are in Released state. When the child item is resolved to its latest Released version, then its child items are resolved using this resolution mode. This is done recursively. If the related child item has no versions that are in Released state, then a blank row is displayed for that item. There is no further processing on that item.

Latest Released resolution mode

The latest Released versions of child parts are resolved. A blank row is displayed for Child C because it has no version that is in the Released state.

When the Latest Released or Latest resolution mode is selected, the structure is resolved starting from the top using the latest versions of child items that are in Released state. If there are no versions that are in Released state, then the latest version is used. When the child item is resolved to a version, then its child items are resolved using this resolution mode. This is done recursively.

Latest Released or Latest resolution mode

The latest Released versions of child parts are resolved. Child C does not have any version that is in the Released state. Therefore, its latest Preliminary version is resolved.

In Aras Innovator, the BOM Structure grid (a TGV under the BOM Structure tab in the Part form) is pre-configured with a Structure Resolution Parameter, thus it supports the Latest, Released, and Released or Latest modes out of the box.

In the event you need to enable Structure Resolution for your Query Definition, you will need to add a Parameter that has a default value of Aras.Resolution.EntryPoint;Default, and then also follow the additional detailed steps for configuring Structure Resolution Parameters described in Section 7.

For more details and extensive examples of how Resolution Modes are applied in BOM Structures, Effectivity, and 3D Visualization please refer to the following documents: Aras 3D Visualization - Administrator Guide Aras Innovator Product Engineering - User’s Guide