System Tables: Searches
The Searches system-table provides a mechanism for Admin-users to add to the options for users to view records in your App.
The records of each BusinessObject can be viewed in data-grids. If your system is at all large there may be too many records in one data-table for users to see them all at the same time, or they may only want to see certain records, for example "Products currently in production".
In any of these cases the Admin-user can add a new record to the Searches system-table. This will create an option for users to click on in the System Entities screen, which will show the records chosen by the Admin-users SQL code.
When users actually run a search, Orixa pulls together the VIEWSCRIPT for that BusinessObject and adds the Search SQLStr as a WHERE statemet. The two parts are then run together to generate the output that the user sees.
Note that Searches SQLStrs are actually WHERE statements, not full SQL Statements. This is useful as it means Admin-users do not have to have a full understanding of the complexity of SQL SELECT statements in order to create new Searches. They only have to understand how to craft a WHERE statement, and they can use other records in the Searches system-table as examples of how to do this.
Note that Searches often take advantage of Orixa SQL Extensions, which make it possible for users to dynamically add to the search at run-time. A help topic on Orixa SQL Extensions is here:
Accessing the Searches system-table
From the System menu, select "View / Edit System Searches (1)
Main features of the Searches view-grid and edit form
Searches System Table view-grid and edit form
- User has opened the Searches view grid, note it is showing "All Records"
- The Grid shows the "LinkTable" which is used to link each search to a BusinessObject in the App.
- Each search has a name, which is used to show to the user when a list of searches is displayed in the App.
- The SQLStr of the Searches are listed. This makes it possible to search and filter by these from the view-grid.
- The Searches Edit Window, with a single SQL WHERE statement displayed.
- The ObjectProperties data for a Searches record. The use of this is explained below.
Guide to writing and creating Searches
- All BusinessObjects need at least one search, to allow the system to create a way for the user to view records.
- It is usual for an "All Records" search to be added for every BusinessObject. This search would have an empty SQLStr. This makes it possible to view all the records for that data-table. If a data-table contains a very large number of records this should perhaps be discouraged.
- The Orixa System-modeller includes tools to automatically generate useful searches when you create or edit a BusinessObject. This allows Admin users to automatically generate searches without any knowledge of SQL.
- Try not to create too many searches. 3 - 10 is probably ideal, more than this users may find confusing. Remember that you can query multiple columns in the data-table in a single WHERE statement, and this can simplify searches. For example you may have multiple text fields in a data-table, a search might look in all these fields, rather than just one.
- Ask users how they want to work with the system, and which sub-sets of records they regularly want to look at. This will help a great deal with selecting which Searches to create.
- Review existing searches and use these as examples of how to add on your own ones.
The Searches table also stores view-grid customization details in the ObjectProperties field
Searches System Table Object Properties |
The Searches Edit Window includes 3 long-text fields. The SQLStr is used to store the WHERE statement used by the search. The Description field is used by the Admin-user to add extra details about the search. The ObjectProperties field (shown at 1.) is used to store data about the customization of the view-grid. The Orixa view-grid has very extensive capability to be customized. Admin-users can do this, and create different views of data which are useful for their users. Full details of this are covered in this help topic:
|
View Grid Save Settings |
Once a view grid has been customized the Admin-user can select "Save Settings" (2) from the "toolbar" menu (1). When this is done the system stores all customization data for the current view-grid to the current search's ObjectProperties. Note that this means every search can be given a unique customization of the view-grid. |
Useful Actions in the Searches Edit Form
Searches System Table Actions
The Searches edit window includes some useful Actions to help the Admin-user do their job.
- Show BusinessObjects View Script: Clicking on this action will open a window containing the View Script for the selected Search's LinkTable. This can be very useful, as in a SQL WHERE Statement table-correlation names and field-names must be used, if the Admin-user is not familiar with these the resulting SQL may not work. By viewing the View Script the Admin-user can immediately see exactly which field and table names are used in the original script that this WHERE statement will run with.
- Test ALL and Test SQLStr: These two actions bring together the selected Searches SQLStr with it's LinkTable's ViewScript and attempt to run the resulting SQL. If any failures or errors occur these are reported in the Message Log.
- Test ALL: Behaves like the above tests, but runs through every Searches record in your App. This can be useful during development if the structure of your database is changing and you want to confirm that changes have not resulted in any unforeseen errors.
Output of the Searches system-table as shown in the System Entities Screen, and view-grid
Once you have worked with Orixa for a while, the interaction between Searches and the User Interface will become obvious, but at first it is a bit mysterious. The following section is intended to make the operation of the Searches table as obvious as possible. Once you have used an Orixa system for even a short amount of time, the way this works will become quite obvious.
Searches System Table Filtered
The above image shows the Searches system-table, filtered so that just 1 data-table's records are visible. Note that there are 3 searches.
Searches Grid Panel
The above image shows 2 separate views from an Orixa App, for the same data-table. The "Contracts" Entity in the system-entities screen (1) and the views search-box in the Contracts view-grid (2).
Note that the lists of searches exactly match the names listed for the Searches records shown above.
When a user clicks on a search in the system-entities screen or the views search-box (3) the same search is run, and the user sees the same data returned as the View-grid is opened.
If any customization has been done to the View-grid and saved in the ObjectProperties for the search, this is applied as the grid is first shown.