The Search Console is a powerful tool enabling users to search and sort inspections by many variables.
Searches can be limited to a specific template by selecting it from this dropdown menu.
Searches can focused on a specific Database Field in the template/s.
Search results can be sorted by a specific Database Field.
This box is used to input specifc data values, which can be modified by the dropdown below it. The options for Data Value search are equals, greater than, greater than or equal, less than, less than or equal, not equal to or like.
This box is checked by default. For databases with more than 1000 inspections, uncheck this box. It is checked by default to increase speed when opening the Session Management window.
Checking this box will sort search results from greater to less than.
Search value in the Data Value Is Box is a Date.
Search value in the Data Value Is Box is a Boolean (True or False).
Search value in the Data Value Is Box is a Number.
Search value in the Data Value Is Box is a Value.
To search by date, set the Database Field to Date, this will pop-up the Calendar window. To select a date range first select the Month and Year to display, then click on the day that is desired. To set the start date, click Set Start. To select the end date, click Set End. To speed up searches, the Month button can be selected to select a whole month and the year button can be selected to select the whole year. When the Close button is pressed, the search box will be filled with the dates that have been selected.
To use it select like from the Operator drop down below the data value is box. So if you want to search Project Names for all that start with DC, just type DC into the box. When it searches it puts the % at the end for you. You can also put your own wildcards into beginning if you want it to search for stuff with wildcard info in the front of it by just placing a % before your value.
Note: This is using unmodified, default POSM templates.
Below are some common examples of searches. To search for other data it is useful to review the template being used in the Database Template Editor. Review the template/s you are working with. When searching set the Database Field to designated Special Field that matches the Field Name you are searching for.
Template Name: NASSCO PACP, NASSCO LACP or NASSCO MACP.
Database Field = Taps.
Database Value is: 1.
Modifier is greater than or equal to.
Press Search button.
This will show all inspections than have 1 or more tap observations.
Template Name: All Templates.
Database Field = Count Group 1..
Database Value is: 1.
Modifier is greater than or equal to.
Press Search button.
This will show all inspections than have 1 or more tap observations.
Template Name: NASSCO PACP, NASSCO LACP or NASSCO MACP.
Database Field = Roots.
Database Value is: 1.
Modifier is greater than or equal to.
Press Search button.
This will show all inspections than have 1 or more Root observations.
Template Name: All Templates.
Database Field = Count Group 2..
Database Value is: 1.
Modifier is greater than or equal to.
Press Search button.
This will show all inspections than have 1 or more Root observations.
Template Name: NASSCO PACP, NASSCO LACP or NASSCO MACP.
Database Field = Cracks / Fractures.
Database Value is: 1.
Modifier is greater than or equal to.
Press Search button.
This will show all inspections than have 1 or more Cracks / Fractures observations.
Template Name: All Templates.
Database Field = Count Group 3.
Database Value is: 1.
Modifier is greater than or equal to.
Press Search button.
This will show all inspections than have 1 or more Cracks / Fractures observations.
Template Name: NASSCO PACP, NASSCO LACP or NASSCO MACP.
Database Field = Broken / Holes / Collapse.
Database Value is: 1.
Modifier is greater than or equal to.
Press Search button.
This will show all inspections than have 1 or more Broken / Holes / Collapse observations.
Template Name: All Templates.
Database Field = Count Group 4.
Database Value is: 1.
Modifier is greater than or equal to.
Press Search button.
This will show all inspections than have 1 or more Broken / Holes / Collapse observations.
Template Name: NASSCO PACP, NASSCO LACP or NASSCO MACP.
Database Field = Deposits.
Database Value is: 1.
Modifier is greater than or equal to.
Press Search button.
This will show all inspections than have 1 or more Deposits observations.
Template Name: All Templates.
Database Field = Count Group 5.
Database Value is: 1.
Modifier is greater than or equal to.
Press Search button.
This will show all inspections than have 1 or more Deposits observations.
Template Name: NASSCO PACP, NASSCO LACP or NASSCO MACP.
Database Field = Obstruction.
Database Value is: 1.
Modifier is greater than or equal to.
Press Search button.
This will show all inspections than have 1 or more Obstruction observations.
Template Name: All Templates.
Database Field = Count Group 6.
Database Value is: 1.
Modifier is greater than or equal to.
Press Search button.
This will show all inspections than have 1 or more Obstruction observations.
Template Name: NASSCO PACP, NASSCO LACP or NASSCO MACP.
Database Field = Abandoned Survey.
Database Value is: 1.
Modifier is greater than or equal to.
Press Search button.
This will show all inspections than have 1 or more Abandoned Survey observations.
Template Name: All Templates.
Database Field = Count Group 7.
Database Value is: 1.
Modifier is greater than or equal to.
Press Search button.
This will show all inspections than have 1 or more Abandoned Survey observations.
Check the Auto Fill Data Value.
Template Name: All Templates.
Database Field = AssetID.
Modifier is equals.
Press Search button.
This will populate all AssetID's in the Data Value Is dropdown. Select the AssetID then press the Search Button.
All inspections using the specified StartID will be displayed.
Check the Auto Fill Data Value.
Template Name: All Templates.
Database Field = StartID.
Modifier is equals.
Press Search button.
This will populate all StartID's in the Data Value Is dropdown. Select the StartID then press the Search Button.
All inspections using the specified StartID will be displayed.
Check the Auto Fill Data Value.
Template Name: All Templates.
Database Field = EndID.
Modifier is equals.
Press Search button.
This will populate all StartID's in the Data Value Is dropdown. Select the EndID then press the Search Button.
All inspections using the specified EndID will be displayed.
Check the Auto Fill Data Value.
Template Name: All Templates.
Database Field = OperatorName.
Modifier is equals.
Press Search button.
This will populate all OperatorName's in the Data Value Is dropdown. Select the OperatorName then press the Search Button.
All inspections using the specified EndID will be displayed.
Check the Auto Fill Data Value.
Template Name: All Templates.
Database Field = City.
Modifier is equals.
Press Search button.
This will populate all Cities in the Data Value Is dropdown. Select the City then press the Search Button.
All inspections using the specified City will be displayed.
POSM Server and Storage use a SQL Database. This opens up a lot of opportunity for specific custom, SQL searches.
For All Structural Sessions that are a 5 search for:
Left(PacpQuickStructRating,1) = '5'
For All Structural Sessions that are greater than 4 search for:
Left(PacpQuickStructRating,1) >= '4'
Search for VCP Pipe within a date range and latest inspection
(Date >= '10/1/2020' AND Date < '10/1/21') AND (AssetType = 'Vitrified Clay Pipe' or AssetType = 'VCP') AND (IsLatestInspection = 1)
Custom search to look for different grade structure defects by a date range.
(Date >= '1/1/2006' AND Date < '1/1/20') AND Left(PacpQuickStructRating,1) = 5
Custom search to look for different grade maintenance defects by a date range.
(Date >= '1/1/2006' AND Date < '1/1/20') AND Left(PacpQuickMaintRating,1) = 5
Most recent inspections with structural grade of 4 or more.
IsLatestInspection = 1 AND Left(PacpQuickStructRating,1) >= 4
There are 5 hot buttons that can save search queries.
To save a custom search, set the Database Field to Custom Search.
Right click on one of the 5 hot buttons you wish to save the query to.
Enter the SQL query into the box and press ok.
The SQL query will be saved. In this example the query has been saved to Custom SQL Search 1.
Press button 1 under Custom SQL Search and the query will fill into the search box.
