Before you build a query in the Custom Queries Editor, it helps to know how IMS stores its data. A few simple ideas help you choose the right base view and the right related tables, so your query returns what you expect. This article explains how IMS stores data, how related tables work, and how to choose a base view.
How IMS Stores Data
IMS stores data in tables. Each table holds one type of object, such as Equipment, Schedule, or Component. Each row is one item of that type, for example one piece of Equipment, and each column is one piece of information about it, for example its name.
Why Information Lives in Different Places
IMS doesn't repeat information. A piece of Equipment doesn't store the name of its Equipment Group. It stores a link to the Equipment Group, and the name is stored once, in the Equipment Group table. To list Equipment together with the Equipment Group name, you extend the base view with the Equipment Group. For the steps, see Extend the Base View with Related Tables.
Related Tables: One Value or Many
A related table can give each row one value or many. In the Fields panel, related tables appear as entities under the base view.
Parent Relations
A parent relation leads to the single item that something belongs to. For example, a piece of Equipment has one Equipment Group, one CMMS Type, and one set of Parent Locations: its Site, Plant, Unit, and FLOC. Adding a parent relation adds one value to each row of your results.
Collections
A collection leads to the many items that belong to something. In the relation list, collections are labeled "(Collection)", for example "Component/Circuit (Collection)". A piece of Equipment can have many Components, so Equipment has a Component/Circuit collection.
Each piece of Equipment still appears once in your results. Select the fields you need from the collection. To add all of its fields, select the box to the left of the collection name. A collection can have many fields, so add only the ones you need.
Choose the Right Base View
The base view is the starting point of a query. It decides what one row in your results represents, so the same data can look different depending on where you start. A base view is either a table, which you build on by adding related tables, or a ready-made view whose name starts with "vw", for example vwAllIntegrityOperatingWindows. For the steps to select one, see Choose a Base View.
Start with the object you want one row for. To list Equipment, start with Equipment. To list Schedules, start with Schedule.
You want the list of | Start with a base view |
|---|---|
Equipment with its Equipment Group and Components | Equipment |
Schedules waiting for approval for more than 60 days | Schedules |
ECHs waiting for approval for more than 60 days | Condition History |
Example: Schedules and Equipment
A piece of Equipment can have several Schedules. The base view you choose decides how many rows you get.
If you choose Schedule as the base view, you get one row for each Schedule. EquipmentA appears twice because it has two Schedules:
Schedule | Equipment |
|---|---|
Schedule1 | EquipmentA |
Schedule2 | EquipmentA |
Schedule3 | EquipmentB |
If you choose Equipment as the base view, you get one row for each piece of Equipment, however many Schedules it has. EquipmentC appears even though it has no Schedules:
Equipment |
|---|
EquipmentA |
EquipmentB |
EquipmentC |
Note
If the base view you need isn't in the list, Request Support.
Related Articles
For more, see:
Custom Queries Editor, to build, filter, and save a query.
Queries - Overview, for the ways to work with queries.
AI Query Builder, to create a query from a plain-language question.
Listing all Queries, to see the queries available to you.