Advanced Search
The Advanced Search feature allows custom reports to be created from any data in the database. The queries are written in Entity SQL, which is similar to the 'standard' SQL. Entity SQL queries are outside the scope of this document, and are not covered by the patriot support assurance policy but there are many books and online references available on how to write them.
Entity SQL Queries can be created from Reports > Queries > Stored Queries, or directly from the query selector in the Advanced Search screen. Example queries are also available.
Once the query has been written, click run to show a preview of the output. This is a standard report, and can be saved, printed, or exported as required.
Customising Report Output
The advanced search window contains a default report output format, which shows the column headers and data from the query. The report format can be modified as required by selecting a new output format. Select Customise Output Style and choose the required output format. Only generic reports can be selected. See the Generic Reporting document for more information on how to create new generic reports.
Client Search Query Builder
To assist in writing ESQ that queries the clients (memalarm) table, the graphical tool Advanced Client Search can be used. To access this, open the client search menu and select the advanced search arrow next to the search input box.
From here, conditions can be added to build a query. First, select the field or property of the client that you want to test. Criteria have different options for how the value must match. For example, text fields can be set to 'Contains' which will match if the entered value is contained anywhere in the field on the client. It is also possible to test for greater or smaller values for numeric fields such as warranty months, or invert a condition by selecting the 'Not' checkbox. Once all conditions have been chosen, click the search button to run the query and show results. Clicking on a client in the result list will open the client for viewing or editing.
Generally clients must meet all criteria before being shown, so queries with more criteria will filter more clients out of the results. The exception to this is when a single search field is used more than once. In this case each duplicated field will apply the OR operator, not the AND operator. For example in the search below, if it instead contains Port ID contains 01, Port ID contains 02, Client ID contains 0004, this would result in the search Port ID is 01 or 02, and Client ID is 0004.
Update and Delete Clients in Bulk
Once you have performed an advanced search, the search results appear in a list on the right side of the screen. There are a few ways to interact with the search results.
- Double-click on one of the search results to Open the selected client.
- Select multiple clients from the list and click the Delete button to remove all the selected clients.
- Select multiple clients from the list and click the Export button to export all the selected clients to a file.
- Select multiple clients from the list and click the Bulk Update button to change values for all the selected clients.
Your operator account must have appropriate permissions in order to use one or both of these bulk operations.
Bulk Update
Allows you to choose which settings to change for all the selected clients. There are two modes:
Client Fields used to update simple client fields like the client alarm tolerance or open out of hours schedule monitoring:
- Pick a field to update by clicking the Plus button in the bottom left of the popup window.
- Choose a new value for the field.
- Once you are happy with the selected options, click the Save button to apply the changes to all the selected clients.
Public Holidays used to insert public holidays (specifically extended closing holidays) in bulk for all selected clients.
Reporting on searches
After an advanced search has been run and records are returned, the print button will be enabled.
This allows the results to be viewed in the print preview window, which allows them to be printed or emailed etc.
The formatting of this report can be customised by changing the System Settings > System Wide settings > Search Settings > Advanced Search Report. All available Generic Reports will be available for selection. A generic report designed to be used for these advanced search is available from the Downloadable Custom Reports Layouts Page.
Save and Load Client Search Template
You can save and load Client Search Templates in the area below. Search templates allow you to quickly restore commonly used search fields (and optionally included values).
Save and Run Client Search Queries
After creating a query using the advanced client search window, it is possible to save it to re-run later, or for use as a generic report. First, select the Export / Convert to Stored Query button. This will open the stored queries window where you can name and save the new query. Queries saved using this method can be re-run using the Stored Query selector on the advanced client search page, but cannot be manually edited or modified. Other than the read-only status, these queries can be used in any place a stored query is required, e.g. standard advanced search or generic reporting.