Agile Product Lifecycle Management Web Services Guide Release 9.3.3 E39310-02 |
|
![]() Previous |
![]() Next |
This chapter describes how to work with the Agile PLM Searches and provides sample code snippets.
Agile PLM Searches can have multiple search criteria (like an Advanced Search in the Agile Web Client), or a simple search that specifies a single criterion.
You can narrow the number of objects returned from a search by specifying search criteria. If you specify * as the search criteria, the query returns references to all objects in the specified query class. It is a good practice to limit the search criteria as much as possible, as the amount of data returned may be excessively large, resulting in decreased performance.
You can use the setCriteria(criteria) method to specify query criteria, which sets the search criteria from a specified String. This String references one or more parameters.
Example: To specify the search criteria
advancedSearchRequestType.setClassIdentifier("Part"); advancedSearchRequestType.setCaseSensitive(false); String criteria = "[Title Block.Number] contains 'P0' && " + "[Title Block.Description] is not null"; advancedSearchRequestType.setCriteria(criteria); String attribute1 = "Title Block.Number"; String attribute2 = "Title Block.Description"; String attribute3 = "Title Block.Lifecycle Phase"; advancedSearchRequestType.setResultAttributes(new String[]{attribute1, attribute2, attribute3} ); advancedSearchRequestType.setDisplayName("Search123"); AdvancedSearchResponseType advancedSearchResponseType = agileStub.advancedSearch(advancedSearchRequestType);
The Agile Web Services provides a simple yet powerful query language for specifying search criteria. The query language defines the proper syntax for filters, conditions, attribute references, relational operators, logical operators, and other elements.
Search criteria consist of one or more search conditions. Each search condition contains the following elements:
Left operand - The left operand is always an attribute enclosed in brackets, such as [Title Block.Number]. You can specify the attribute as an attribute name (fully qualified name or short name) or attribute ID number. The attribute specifies which characteristic of the object to use in the search.
Relational operator - The relational operator defines the relationship that the attribute has to the specified value, for example, "equal to" or "not equal to."
Right operand - The matching value for the specified attribute in the left operand. The right operand can be a constant expression or a set of constant expressions. A set of constant expressions is needed if the relational operator is "between," "not between," "in," or "not in."
Following is an example of a search condition:
[Title Block.Description] == 'Computer'
This is another example where the right operand is a set of constant expressions:
[Page Two.Numeric01] between ('1000', '2000')
When you specify a search condition, you must use proper keywords to construct the statement. The following keywords are available:
and | does | less | or | to |
---|---|---|---|---|
asc | equal | like | order | union |
between | from | minus | phrase | where |
by | greater | none | select | with |
contain | in | not | start | word |
contains | intersect | null | starts | words |
desc | is | of | than | NA |
These keywords are not localized. You must use English keywords, regardless of locale. You can use the keywords in lowercase or upper case. In addition to keywords, you can use Agile PLM variables such as $USER (for current user) and $TODAY (for today's date) in Agile Searches.
The "in" operator does not support MultiList in (set) query criteria.
Every Agile PLM object that you can search for also has an associated set of attributes, which are inherent characteristics of the object. You can use these attributes as the left operand of a search condition. The right operand of the search condition specifies the attribute's value(s).
A search attribute must be enclosed within brackets, for example, [Title Block.Number]. The brackets are necessary because many attribute names have spaces.
Caution: If search attributes are not enclosed within brackets, it could cause the query to fail. |
You can specify a search attribute in the following ways:
Attribute reference | Example |
---|---|
attribute ID number | [1001] |
fullyqualified attribute name | [Title Block.Number] |
short attribute name | [Number] |
Note: Agile recommends referencing attributes by ID number or constants because attribute names can be modified. However, many of the examples in this chapter reference attributes by name simply to make them more readable. If you choose to reference attributes by name, use the fullyqualified attribute name instead of the short name. Short attribute names are not guaranteed to be unique and could therefore cause your query to fail or produce unexpected results. |
Note: Specifying the search attributes using Attribute APIName is not supported. |
Attribute names, whether you use the long or short form, are case-insensitive. For example, [Title Block.Number] and [TITLE BLOCK.NUMBER] are both allowed. Attribute names are also localized. The names of Agile PLM attributes vary based on the locale of your Agile Application Server. If you are creating a query that is going to be used on servers in different locales, you should reference attributes by ID number (or the equivalent constant) instead of by name.
If the attribute name contains special characters, such as quotes or backslashes, you can type these characters using the backslash (\) as an escape character. For example: To include a quote character in your string, type \'. For a backslash, type two of them together (\\). If the attribute name contains square brackets, enclose the entire name in quotes:
['Page Two.Unit of Measure [g or oz]'] [Title Block.Effectivity Date] between ('01/01/2001', '01/01/2002') advancedSearchRequestType.setCriteria("[Title Block.Number] starts with %0 and [Title Block.Part Category] in %1 and [Title Block.Description] contains %2"); ParamListType[] params = new ParamListType[3]; params[0] = new ParamListType(); params[0].setParameter(new String[]{"P00"}); params[1] = new ParamListType(); params[1].setParameter(new String[]{"Electrical", "Mechanical"}); params[2] = new ParamListType(); params[2].setParameter(new String[]{"Resistor"}); advancedSearchRequestType.setParams(params);
There are other, perhaps less intuitive, ways to specify attributes. For example, you could pass in an IAttribute reference using a parameter of the setCriteria() method. In the following example, '%0' references the attribute in the params parameter.
You can also use String concatenation to reference an attribute constant:
advancedSearchRequestType.setCriteria("[" + ItemConstants.ATT_TITLE_BLOCK_DESCRIPTION + "] == 'Computer'");
The searchable attributes for a query depend on the specified query class or subclass. However, the searchable attributes for a subclass can differ greatly from searchable attributes for its parent class.
Due to database considerations, not all attributes are searchable. Generally, a few select Page One attribute (namely: Title Page, Cover Page, and General Info attributes) are searchable for each class.
If a tab is not configured in Java Client to be visible, you can still search for an attribute on that tab in Agile Web Services. However, you must search for the Table name that corresponds to the Tab name.
To find the searchable attributes for a query, use the getSearchableAttributes operation.
Even though an attribute is not searchable, you can still include it as a column in the query results. For more information, see "Specifying Result Attributes for a Query."
The table below lists relational operators that are supported by the Agile Web Services search operations.
English operator | Notation | Description |
---|---|---|
equal to | == |
Finds only an exact match with the specified value. |
not equal to | != | Finds any value other than an exact match with the specified value. |
greater than | > | Finds any value greater than the specified value. |
greater than or equal to | >= | Finds any value greater than or equal to the specified value. |
less than | < |
Finds any value less than the specified value. |
less than or equal to | <= |
Finds any value less than or equal to the specified value. |
contains, contains all | NA | Finds any value that includes the specified value. |
does not contain, does not contain all | NA | Finds any value that does not include the specified value. |
contains any | NA | Finds any value that includes the specified value. |
does not contain any | NA | Finds any value that does not include the specified value. |
contains none of | NA | Finds any value that includes none of the specified values. |
does not contain none of | NA | Behaves the same as does not contain any. |
starts with | NA | Finds values that begin with characters in the specified value. |
does not start with | NA | Finds values that do not begin with characters in the specified value. |
is null | NA | Finds objects where the selected attribute contains no value. |
is not null | NA | Finds objects where the selected attribute contains a value. |
like | NA | Performs a wildcard search, finding objects that match a single character or any string. |
not like | NA | Performs a wildcard search, finding objects that do not match a single character or any string. |
between | NA | Finds objects that fall between the specified values. |
not between | NA | Finds objects that do not fall between the specified values. |
in | NA | Finds objects that match any of the specified values. |
not in | NA | Finds objects that do not match any of the specified values. |
contains phrase | NA | Finds objects with files that contain the specified phrase. |
contains all words | NA | Finds objects with files that contain all of the specified words. |
contains any word | NA | Finds objects with files that contain any of the specified words. |
contains none of | NA | Finds objects with files that contain none of the specified words. |
Relational operators are not localized. You must use English keywords, regardless of locale. As with other query language keywords, you can use them in lowercase or upper case.
Agile Web Services Search operations support Unicode escape sequences. The primary usage of Unicode escape sequences in a query string is to search for nonburnable or foreign local character sets. A Unicode character is represented with the Unicode escape sequence \uxxxx, where xxxx is a sequence of four hexadecimal digits.
For example, to search for an item with Unicode 3458, use the following query:
Select * from [Items] where [Description] contains '\u3458'
There is another query operation for "contains' usage for MultiList.
The 'between', 'not between', 'in', and 'not in' relational operators are not supported directly by Agile PLM Java and Web clients. These relational operators provide a convenient shorthand method for specifying 'equal to', 'not equal to', 'greater than or equal to', or 'less than or equal to' operations with a set of values.
Short form | Equivalent long form |
---|---|
[Number] between ('1','6') | [Number] >= '1' and [Number] <= '6' |
[Number] not between ('1','6') | [Number] < '1' and [Number] > '6' |
[Number] in ('1','2','3','4',5','6') | [Number] == '1' or [Number] == '2' or [Number] == '3' or [Number] == '4' or [Number] == '5' or [Number] == '6' |
[Number] not in ('1','2','3','4','5','6') | [Number]!= '1' and [Number] != '2' and [Number] != '3' and [Number] != '4' and [Number] != '5' and [Number] != '6' |
As shown in the table, when you use the 'between', 'not between', 'in', and 'not in' relational operators, each value in the set of values must be enclosed in quotes and delimited by commas. Here are more criteria examples that use 'between' and 'in' relational operators:
[Title Block.Number] in ('1000-02', '1234-01', '4567-89') [Page Two.Numeric01] between ('1000', '2000')
Note: The relational operators any, all, none of, and not all are not supported in the Web Services. |
Several lists in Agile PLM contain business objects, such as Agile PLM Users. To search for an object in such a list, you can specify nested query criteria. Nested criteria are enclosed in parentheses and separated from each other by a logical AND (&&) or OR (||) operator. A comma can also be used to separate nested criteria; it's equivalent to a logical OR.
The following criteria finds a user with the first name Christopher OR the last name Nolan.
[Page Two.Create User] in ([General Info.First Name] == 'Christopher', [General Info.Last Name] == 'Nolan')
The following criteria finds a user with the first name Christopher AND the last name Nolan.
[Page Two.Create User] in ([General Info.First Name] == 'Christopher' && [General Info.Last Name] == 'Nolan')
The parameter query is not supported in nested queries and multiple values for one placeholder in query parameters must be specified in two dimensional arrays as shown in the example below.
Example: Correct and incorrect parameter query in nested query criteria
The parameter query specified in the following nested query criteria will fail to execute:
[Page Two.User1] in ([General Info.First Name] == %0)
However, when it is explicitly specified as a string value, instead of the placeholder, it will succeed:
[Page Two.User1] in ([General Info.First Name] == 'Christopher')
To index the content of files stored on the Agile file management server, use two special attributes, [Attachments.File Document Text] and [Files.Document Text]. If you are hosting your database on Oracle, you can take advantage of a feature that lets you search for words or phrases contained in attachments. When you create search criteria that uses either of these attributes, there are four additional relational operators you can use:
contains phrase
contains all words
contains any word
contains none of
The following table shows several search conditions that search for words or phrases in attachments.
Search Condition | Finds |
---|---|
[Attachments.File Document Text] contains phrase 'adding new materials' | Objects in which any of their attachments contain the phrase "adding new materials." |
all [Attachments.File Document Text] contains all words 'adding new materials' | Objects in which all their attachments contain the words "adding," "new," and "materials." |
none of [Attachments.File Document Text] contains any word 'containers BOM return output' | Objects in which none of their attachments contain any of the words "containers," "BOM," "return," or "output." |
[Attachments.File Document Text] contains none of 'containers BOM output' | Objects in which any of their attachments do not contain the words "containers," "BOM," or "output." |
You can use logical operators to combine multiple search conditions into a complex filter. When you have two or more conditions defined in a set of query criteria, the relationship between them is defined as either 'and' or 'or'.
and narrows the search by requiring that both conditions are met. Each item in the results must match both conditions. The 'and' logical operator can also be specified using two ampersands, '&&'.
or broadens the search by including any object that meets either condition. Each item in the results table needs to match only one of the conditions, but may match both. The 'or' logical operator can also be specified using two vertical bars, '||'.
Logical operators are case-insensitive. For example, 'and' or 'AND' are both allowed.
The following query criteria finds parts that have both a part category equal to Electrical and a lifecycle phase equal to Inactive.
[Title Block.Part Category] == 'Electrical' and [Title Block.Lifecycle Phase] == 'Inactive'
If you replace the 'and' operator with 'or', the query locates all parts with either a part category of Electrical or a lifecycle phase of Inactive, which could be many parts.
[Title Block.Part Category] == 'Electrical' or [Title Block.Lifecycle Phase] == 'Inactive'
Logical operators, including the where-used set operators, are not localized. You must use English keywords, regardless of locale.
If you define a search condition using the 'like' operator, you can use two wildcard characters: the asterisk (*) and question mark (?). The asterisk matches any string of any length, so *at finds cat, splat, and big hat.
For example, [Title Block.Description] like '*book*' returns all objects that contain the word "book," such as textbook, bookstore, books, and so on.
The question mark matches any single character, so ?at finds hat, cat, and fat, but not splat.
For example, [Title Block.Description] like '?al*' matches any word containing "al" that is preceded by a single letter, such as tall, wall, mall, calendar, and so on.
Where-used, set operators have higher priority than and and or logical operators, as shown by the following table.
Priority | Operator(s) |
---|---|
1 | union
intersection minus |
2 | and
or |
Therefore, search conditions joined by union, intersection, and minus operators are evaluated before conditions joined by and or or.
If you use where-used set operators ('union', 'intersect', or 'minus') in search criteria, you can use parentheses to change the order that criteria are evaluated. If only 'and' or 'or' logical operators are used in a search criteria, additional parentheses aren't needed because they don't change the result of criteria evaluation.
The following two criteria, although they contain the same search conditions, provide different results because parentheses are placed differently:
([Title Block.Part Category] == 'Electrical' and [Title Block.Description] contains 'Resistor') union ([Title Block.Description] contains '400' and [Title Block.Product Line(s)] contains 'Taurus') [Title Block.Part Category] == 'Electrical' and ([Title Block.Description] contains 'Resistor' union [Title Block.Description] contains '400') and [Title Block.Product Line(s)] contains 'Taurus'
In addition to its standard query language, the Agile Web Services also supports SQL-like syntax to specify search criteria. If you are familiar with SQL statements, you may find this extended query language more flexible, more powerful and easier to work with. It combines in one operation the specification of the query result attributes, the query class, the search condition, and the sort column(s).
This is a simple example that demonstrates the syntax:
Query result attributes: SELECT [Title Block.Number], [Title Block.Description]
Query class: FROM [Items]
Search condition: WHERE [Title Block.Number] starts with 'P'
Sort column(s): ORDER BY 1 asc
To improve readability, it is recommended that SQL key words such as SELECT and FROM are entered in capital letters and each part of the statement appears on a separate line. This is a convention, not a requirement. SQL key words are not case-sensitive. You can also write the entire query string in one line.
The best way to demonstrate the advantages of SQL syntax is to compare the code for a query that uses standard Agile API query syntax for search criteria with one that uses SQL syntax. The following example shows a query created using the standard Agile API query syntax:
Example: Query using standard Agile API query syntax
advancedSearchRequestType.setCriteria("[Page Two.Nummeric01] between (1000, 2000)"); //Set result attributes String[] attrs = { "Title Block.Number", "Title Block.Description", "Title Block.Lifecycle Phase" }; advancedSearchRequestType.setResultAttributes(attrs);
This example shows the same query rewritten in SQL syntax. Although the example does not have fewer lines of code, you may find that it is more readable than Agile API query syntax, particularly if you are familiar with SQL.
Example: Search criteria using SQL syntax
String criteria = "SELECT " + "[Title Block.Number],[Title Block.Description], " + "[Title Block.Lifecycle Phase] " + "FROM " + "[Items] " + "WHERE " + "[Title Block.Number] between (1000, 2000)"; advancedSearchRequestType.setCriteria(criteria);
The following example shows a query written with SQL syntax that specifies the search criteria.
Example: Using SQL syntax to specify query attributes
try { String statement = "SELECT " + "[Title Block.Number], [Title Block.Description] " + "FROM " + "[Items] " + "WHERE " + "[Title Block.Description] like %0"; advancedSearchRequestType. setCriteria(statement); }
Note: The FROM part of the search condition specifies the query class. If you use the classIdentifier attribute to also specify a query class, the query class specified in the SQL search condition takes precedence. |
In Agile Web Services search operation, you can use both the asterisk (*) and question mark (?) as wildcards in a query that uses SQL syntax. The asterisk matches any string and the question mark matches any single character. You can use wildcards in the SELECT statement (the specified query result attributes) and the WHERE statement (the search condition).
For example, "SELECT *" specifies all available query result attributes.
When you use the operation advancedSearch, it returns several output fields, which are also called result attributes. By default, there are only a few result attributes for each query class. You can add or remove result attributes using the setResultAttributes().
The following table shows the default query result attributes for each predefined Agile PLM class.
Query class | Default result attributes |
---|---|
Changes
Change Orders ECO Change Requests ECR Deviations Deviation Manufacturer Orders MCO Price Change Orders PCO Sites Change Orders SCO Stop Ships Stop Ship |
Cover Page.Change Type
Cover Page.Number Cover Page.Description Cover Page.Status Cover Page.Workflow |
Customers
Customers Customer |
General Info.Customer Type
General Info.Customer Number General Info.Customer Name General Info.Description General Info.Lifecycle Phase |
Declarations
Homogeneous Material Declarations Homogeneous Material Declaration IPC 1752-1 Declarations IPC 1752-1 Declaration IPC 1752-2 Declarations IPC 1752-2 Declaration JGPSSI Declarations JGPSSI Declaration Part Declarations Part Declaration Substance Declarations Substance Declaration Supplier Declarations of Conformance Supplier Declaration of Conformance |
Cover Page.Name
Cover Page.Description Cover Page.Supplier Cover Page.Status Cover Page.Workflow Cover Page.Compliance Manager Cover Page.Due Date Cover Page.Declaration Type |
Discussions
Discussions Discussion |
Cover Page.Subject
Cover Page.Status Cover Page.Priority Cover Page.Type |
File Folders
File Folders File Folder |
Title Block.Type
Title Block.Number Title Block.Description Title Block.Lifecycle Phase |
Items
Parts Part Documentation Document |
Title Block.Item Type
Title Block.Number Title Block.Description Title Block.Lifecycle Phase Title Block.Rev |
Manufacturers
Manufacturers Manufacturer |
General Info.Name
General Info.City General Info.State General Info.Lifecycle Phase General Info.URL |
Manufacturer Parts
Manufacturer Parts Manufacturer Part |
General Info.Manufacturer Part Number
General Info.Manufacturer Name General Info.Description General Info.Lifecycle Phase |
Packages
Packages Package |
Cover Page.Package Number
Cover Page.Description Cover Page.Assembly Number Cover Page.Status Cover Page.Workflow |
Part Groups
Part Groups Commodity Part Family |
General Info.Name
General Info.Description General Info.Lifecycle Phase General Info.Commodity Type General Info.Overall Compliance |
Prices
Published Prices Contracts Published Price Quote History Quote History |
General Info.Price Number
General Info.Description General Info.Rev General Info.Price Type General Info.Lifecycle Phase General Info.Program General Info.Customer General Info.Supplier |
Product Service Requests
Non-Conformance Reports NCR Problem Reports Problem Report |
Cover Page.PSR Type
Cover Page.Number Cover Page.Description Cover Page.Status Cover Page.Workflow |
Programs
Activities Program Phase Task Gates Gate |
General Info.Name
General Info.Description General Info.Status General Info.Health General Info.Owner General Info.Root Parent General Info.Workflow General Info.Type |
Projects
Sourcing Projects Sourcing Project |
General Info.Project Type
General Info.Number General Info.Description General Info.Manufacturing Site General Info.Ship To Location General Info.Program General Info.Customer General Info.Lifecycle Phase |
Quality Change Requests
Corrective Action/Preventive Action CAPA Audits Audit |
Cover Page.QCR Type
Cover Page.QCR Number Cover Page.Description Cover Page.Status Cover Page.Workflow |
RFQ Responses
RFQ Responses RFQ Response |
Cover Page.RFQ Number
Cover Page.RFQ Description Cover Page.Lifecycle Phase Cover Page.Requested Cover Page.Completed Cover Page.Due Date |
RFQs
RFQs RFQ |
Cover Page.RFQ Number
Cover Page.RFQ Description Cover Page.MFG Site Cover Page.Ship-To Location Cover Page.Program Cover Page.Customer Cover Page.Lifecycle Phase Cover Page.RFQ Type |
Sites
Sites Site |
General Info.Name
General Info.Contact General Info.Phone |
Specifications
Specifications Specification |
General Info.Name
General Info.Description General Info.Lifecycle Phase General Info.Jurisdictions General Info.Validation Type General Info.Specification Type |
Substances
Materials Material Subparts Subpart Substance Groups Substance Group Substances Substance |
General Info.Name
General Info.Description General Info.CAS Number General Info.Lifecycle Phase General Info.Substance Type |
Suppliers
Suppliers Component Manufacturer Contract Manufacturer Distributor Manufacturer Rep |
General Info.Supplier Type
General Info.Number General Info.Name General Info.Description General Info.Status |
Transfer Orders
Content Transfer Orders CTO Automated Transfer Orders ATO |
Cover Page.Transfer Order Type
Cover Page.Transfer Order Number Cover Page.Description Cover Page.Status Cover Page.Workflow |
If you run a query and find that the resulting table object does not contain the attributes you expected, it is because you did not specify result attributes. The following example shows how to specify the result attributes for a query.
Example: Setting query result attributes
String attribute1 = "Title Block.Number"; String attribute2 = "Title Block.Description"; String attribute3 = "Title Block.Lifecycle Phase"; advancedSearchRequestType.setResultAttributes(new String[]{attribute1, attribute2, attribute3} );
The ResultAttributes element takes an array of String where you can array of attribute names (such as {"Title Block.Description", "Title Block.Number"} ) or attribute ID constants or attribute API names. The following example shows how to specify result attributes using ID constants.
Example: Setting query result attributes by specifying ID constants
private void setQueryResultColumns(IQuery query) throws APIException { // Put the attribute IDs into an array String[] attrs = { ItemConstants.ATT_TITLE_BLOCK_NUMBER+"", ItemConstants.ATT_TITLE_BLOCK_DESCRIPTION+"", ItemConstants.ATT_TITLE_BLOCK_LIFECYCLE_PHASE+"", ItemConstants.ATT_PAGE_TWO_TEXT01+"", ItemConstants.ATT_PAGE_TWO_NUMERIC01+"", ItemConstants.ATT_PAGE_THREE_TEXT01+""}; // Set the result attributes for the query advancedSearchRequestType.setResultAttributes(attrs); }
When you use the setResultAttributes() method, make sure you specify valid result attributes. Otherwise, the setResultAttributes() method will fail.
The examples below show how to create quick search, advanced search and how to get the searchable attributes.
Operation - quickSearch
QuickSearchRequestType quickSearchRequestType = new QuickSearchRequestType(); quickSearchRequestType.setClassIdentifier("Part"); quickSearchRequestType.setKeywords("P0*"); quickSearchRequestType.setSearchFiles(false); QuickSearchResponseType quickSearchResponseType = agileStub.quickSearch(quickSearchRequestType);
Operation - advancedSearch
AdvancedSearchRequestType advancedSearchRequestType = new AdvancedSearchRequestType(); advancedSearchRequestType.setClassIdentifier("Part"); advancedSearchRequestType.setCaseSensitive(false); String criteria = "[Title Block.Number] contains 'P0' && " + "[Title Block.Description] is not null"; advancedSearchRequestType.setCriteria(criteria); String attribute1 = "Title Block.Number"; String attribute2 = "Title Block.Description"; String attribute3 = "Title Block.Lifecycle Phase"; advancedSearchRequestType.setResultAttributes(new String[]{attribute1, attribute2, attribute3} ); advancedSearchRequestType.setDisplayName("Search123"); AdvancedSearchResponseType advancedSearchResponseType = agileStub.advancedSearch(advancedSearchRequestType);
Operation - getSearchableAttributes
QueryGetSearchableAttributesRequestType queryGetSearchableAttributesRequestType = new QueryGetSearchableAttributesRequestType(); queryGetSearchableAttributesRequestType.setClassIdentifier("Part"); QueryGetSearchableAttributesResponseType queryGetSearchableAttributesResponseType = agileStub.getSearchableAttributes(queryGetSearchableAttributesRequestType);