Queries allow for users to search in AiM with a defined set of criteria. The query results will display a specific set of data based on the criteria selected. The search criteria fields contain operators that can be used to search records in different ways, such as “Starts with”, “Ends with”, or “Contains”.
🔖 Page Summary
The Page Summary section serves as a quick reference point of the steps, after going through the detailed Step-by-Step Tutorial with screenshots.
\uD83D\uDCD8 Step-by-Step Tutorial
🟢 Query Overview
Why will a Query help me? When using a database full of information you will want to retrieve very specific records. These records can be obtained by utilizing a query.
🟢 Anatomy of a Query/Search Screen
Queries allow for users to search in AiM with a defined set of criteria. The query results will display a specific set of data based on the criteria selected. For example, the search criteria fields contain operators that can be used to search records in different ways, such as “Starts with”, “Ends with”, or “Contains”.
Basic Overview
1. From the WorkDesk, you can click on any module in the left hamburger icon Menu.
In the next screen, you will find additional left menu options, some of which have a magnify glass icon next to them.
Click on a magnify glass icon next to your menu choice for which you would like to create a query for.
These three buttons can be found at the top-left of a query screen:
Execute - Runs the query
Basic Search/Advanced Search - This is a toggle. The button will show which option you will go to by clicking it. NOT which option you are on.
Basic vs Advanced Search
Basic Search - Gives the user a limited number of options for searching. This is often sufficient for more users.
Advanced Search - Gives the user access to more query options to do more complex queries.
By default, all users start with Basic Search. Your System Administrator can change your default for you.
Reset - Resets the entire query screen. This is useful when your search results appear to be wrong. You can click Reset and start over knowing all fields have been reset to their default settings.
Below is a screenshot of the search page for Asset Package, under the Asset Management module. Under Action in the left menu, you will find the link to open and begin creating a New Query.
Below Action, View will be empty until the Query has been saved.
Queries you have already created can also be edited and either replace the original query or saved as a completely new query.
Display Order
Display Order is a functionality of the Query screen that allows you to set the order of the columns of information that will show up in the search results screen. Here, we’ll start with a basic search query to walk through the details of using the Display Order feature.
From your WorkDesk, select the Module under the Menu to perform a search. In this example, Work Management is selected.
In the screen for the Module you selected (in this example, Work Management), under Menu, select Phase by selecting the magnify icon.
The Phase search has the most options.
In the next screen, click on Search.
You can toggle between Basic Search and Advanced Search. Basic Search will not allow you to sequence the way the phase search results are displayed (explained in Step 5, Display Order).
AiM often remembers information from your last search. Whenever creating a new search, click on Reset.
The Display Order column allows you to sequence the search results and display the columns of data in a specific order.
When sequencing numbers for the display order don't enter 1, 2, 3, 4, 5, etc. It is better to allow space between your numbers 10, 20, 30, 40, etc. in case you want to go back and modify the search by displaying another field.
In this example, we want the columns of data in our search results to be presented in this order: Work Orders, Phase, Phase Description, Status, and Shop.
[Display Order 5] Displays the Work Order number first/in the 1st column of our search results because 5 is the smallest number we enter in a Display Order field for this search. Typically all that is needed in a search at the work order level is the work order number.
[Display Order 10] Display the Phase number next/in the 2nd column of our search results because 10 is the 2nd smallest number we enter in a Display Order field.
[Display Order 20] Display the phase Description. The phase Description will show up in the 3rd column of our search results because 20 is the 3rd smallest number entered in a Display Order field for this search.
[Display Order 30] Displays the Status in the 4th column. Select the operator to make the search more meaningful. In this example Closed, Closing, Incomplete, Deferred, and Complete - Pending R are excluded by changing the Operator to not in.
[Display Order 40] Displays the Shop in the 5th column. Select the shop desired for the search. In this example, Electrical is the shop search.
When done select Execute.
Notice the display order of the columns is the sequencing that was added to the Display Order fields.
Under the Action menu, the search can be exported to excel by clicking on Export.
🟢 Query Screens Overview
Below are detailed descriptions for navigating the screens found when creating a Query.
Starting a Search
From your WorkDesk, click on the hamburger icon on the left to expand your top-most search options/how you want to start your search.
The Module - The top level category of records
The + Sign - Starts a New Record
The Magnify Glass - Takes you to the search screen
The Record Name (under the Module) - Takes you to a new blank screen
Opening a Record & Starting a New Query/Search from that Record
Opening a Record
What the screen looks like after opening with the Record Name (under the Module level of data)
In the next screen, the Record Name can be found at the top: In this example, Personal Query is the record name.
Next you would click on Search to dive deeper into this screen.
The Search/Query Screen (for the Record Name Selected: Personal Query)
See the previous section, Anatomy of a Search/Query Screen, for more details.
Execute - This will run the query.
Basic/Advanced Search - Toggling between the two will show which option you will go to, NOT which option you are on.
Reset - Resets the entire query screen.
Under Action on the left of the query screen, click on New Query.
The New Query Screen
In the New Query screen, first complete the Title field. The Description field below it is optional.
When Query Listing and Query Count are set to Yes, the Query can be displayed on the Dashboard.
Setting the yellow and/or red flag fields will make the query flag turn yellow or red on the WorkDesk or when it reaches the set number. These fields are optional.
The Chart Type will identify the different charting options to display a visual representation of the data you are about to build a query for.
When you are finished and click on Done, the search will open in build mode.
After all criteria are entered, click Save to save the query.
Selecting Execute will take you to the results/Browse View of the query.
Query/Search Results (aka Browse View)
In your query search results, click on a Record link to display the details of that record.
You can also click the blue Search button to return to the Query screen for the name you chose on the record level under the module you chose (in this example, Personal Query).
How the Query Screen Looks After Saving 1+ Queries
Before we saved a query, the only option under Action was New Query. Now, Edit Query has been added.
Under View, there were previously no options. But after saving 1 or more queries, those saved queries are listed under this section.
Queries can be built and saved for future use. Queries you have already created can also be edited and either replace the original query or saved as a completely new one.
Query Screen Structure & Display Order
Query Screen Structure
Some query screens might have multiple tables involved.
The primary table is listed first.
After that, additional tables will have its own headings.
If the header is indented, the table is subordinate to the table above it.
Display Order
Display Order functionality allows you to specify which fields you want to see and the order you wan to see them in, in the Query Results screen.
Ascending/Descending Display Order
Ascending (Asc) or Descending (Dsc) are additional Display Order options.
Special Operators
Special Operators will be used with new WorkDesk queries to make them easier to share across the units.
These operators (=, <, >, null, …) work with dates and allow the query to use the characteristics of dates to pull records in a more focused manner.
Qualifier | Actions | Examples |
|---|---|---|
= | Equals To Only open work orders will return in this search |
|
> | Greater Than The dates that will return in this search are later than later Feb, 27, 2024 |
|
< | Less Than The dates that will return in this search are before Feb, 27, 2024 |
|
<= | Less Than OR Equal To |
|
>= | More Than OR Equal To |
|
<> | Not Equal To |
|
Starts With | The field must start with the data typed This example would return phases that start with Bean Hall |
|
End With | The field must end with the data typed This example would return work orders created by user who ends in “Pitts” |
|
Null | Do not type in the search field, it must be blank This example would return all the work orders where the problem code wasn’t entered Null means “missing or blank” |
|
Not Null | This example would return all the work orders where the problem code was entered
|
|
In | Allows the user to select multiple values. For this example multiple shops are selected. |
|
Not In | Allows the user to exclude multiple values. For this example the selected shops will be excluded from the search results |
|
Between | The search results will return work orders within the chosen date range |
|
Within | The field must be within the listed timeframe (date fields only) Options: year, month, week, day, hour |
|
Newer Than | The field must be within the listed timeframe (date fields only) Options: year, month, week, day, hour Record returned must be more recent than 4 days ago |
|
Older Than | The field must be within the listed timeframe (date fields only) Options: year, month, week, day, hour Record returned must be older than 3 weeks ago |
|
Last | The field must be within the listed timeframe (date fields only) Options: year, month, week, day, hour Record returned within last the month |
|
Next | The field must be within the listed timeframe (date fields only) Options: year, month, week, day, hour Returns records with desired completion date in the next 5 days |
|
Column | One of the more powerful operators that compares two different database columns Returns phases where the actual cost has exceeded the estimated cost. Find this in the phases costs: Options: =Column, >Column, >=Column, <Column, <=Column, >=%Column, <=%Column |
|
User | Returns all records created by whoever is logged in at the time |
|
🟢 Personal Queries
Personal Queries can be created under any Module in AiM.
You can also view All Personal Queries created under All Modules
From the WorkDesk, click on the hamburger icon on the left and click on the System Administration Module
Click on the Magnify Glass icon next to Personal Query
In the next screen, click Execute to view all your Personal Queries. You will see a list of your personal queries (example screenshot, below).
Click on one of the Query links to open that page.
Here, you can Edit the chosen query and share the query to group(s).
By default, Personal Queries are “personal” to the user that created them. Personal Queries are not visible to other logins.
\uD83D\uDCCB Related articles
Filter by label
There are no items with the selected labels at this time.