Adding a Fixed Query to a Data Table from Instance Definition Widget
24.03.2023
In a Data Table widget, the configured filter is displayed in the form of «breadcrumbs» by default. While an internal list can be provided with a so-called «fixed query», any filter displayed in the breadcrumbs is removable by the user. Although the display of the breadcrumb filter can be completely disabled, this causes further problems. Because now a user can't remove even the filters he set himself (via the «show matches» and «filter out» actions). In the following tutorial I show a possible solution to be able to give a Data Table a fixed filter and still allow the display of breadcrumbs and the removal for user-defined filters.
Filtering using the «Filter» field
When creating a new Data Table from Instance Definition, you can easily add a query to filter the records shown in the data table. Below is an example showing a data table that only shows records assigned to current user. This is achieved by adding the following query to the «Filter» field of the data table:
assigned_toDYNAMIC90d1921e5f510100a9ad2572f2b477fe
Unfortunately, queries specified in the «Filter» field of the data table can easily be removed by the user with a single click as you can see below:
Adding fixed query to a data table with a relationship
Often times, we do not want to allow the user to remove a fixed part of the query and not want to show him this fixed query at all. Some recommend disabling breadcrumbs completely, however this is not very user friendly, as filters set by the user himself with «Show matching» and «Filter out» will also not be visible and cannot be removed again by the user.
In our cases, the list of «My Incidents» should always show only the incidents assigned to myself.
This can easily be achieved with the following steps without adjusting any code in the data table widget:
1.Create a new relationship in table [sys_relationship]. We will use this relationship as «fixed filter» in our data table. As we will not use this relationship as a default relationship added to a form, we leave «Applied to table» blank and also ensure we do not access the «parent» variable in the script (as there will be no parent in the data table). As we want to filter the incident list for incidents assigned to the current user, we would specify the following:
Queries from table: Incident [incident]
Query with: current.addQuery(‘assigned_to’, gs.getUserID());
2. Submit the relationship and copy the sys_id of the newly inserted record.
3. Open the sp_instance-Record of your data table in the Classic UI by finding it in “/sp_instance_list.do” or by open the instance options with “Ctrl + Rightclick” on your widget, select “Instance Options” and then select “Open in platform” in the menu as shown below:
4. In the field «Additional options, JSON format», add the following two options as shown below:
{
“relationship_id”: “<sys_id of your newly created entry in sys_relationship>”,
“apply_to”: “<table name of records displayed in the data table>”
}
5. Remove the part of the filter that you added to the sys_relationship record from the «Filter» field of your sp_instance-Record.
If you followed the steps above, you will now have a data table with a fixed filter. The fixed filter will not be shown to the user and cannot be removed. As you can see below we can still only see incidents assigned to Beth Anglin:
Also we can use «Right-click -> Show Matching”» and «Right-click -> Filter-out» to filter out records and will still not see the fixed query defined in the relationship.
Also clicking on «All» will still keep the data table filtered for incidents assigned to the current user:
Further improving your custom portals
If you are building custom portals and are looking for an advanced data table and form widget with additional features like:
- Dynamic styling of cells and rows in data tables
- Displaying conditional process flows in portal forms
- Displaying annotations in portal forms
- Support for embedded and related lists in portal forms
- Support for tabs in portal forms
- List, list banner and list bottom buttons in data tables
- Client-side buttons in your portal (e.g. for confirmation dialogs or performing redirects on submission)
- Greatly improved attachment handling
Then please check out the Swiss Tool application available in the Store.
Using Swiss Tool, you can greatly increase the speed for developing custom portals and you can prevent cloning widgets and therefore vastly increase maintainability and security of your portals, as you will not have to take care of migrating/fixing your cloned widgets with every upgrade from ServiceNow. Swiss Tool uses an unique approach of building on top of the out-of-the-box form and data table widgets and will ensure you always get the newest updates from the underlying widgets with each release.
The screenshot below demonstrates various features of the SwissTool, which are easily configurable with a no-code/low-code approach:
– Philemon