Filter-based Searching in Portals

While in-page table searching can be a quick way to improve the usability of your portal, these searches can only act upon information already displayed in the table. There may be instances where users need to filter results based on some other data points not included in the table.

Similarly, false positive data may display when using the in-page search. For example, a search for "Mat" could return records with name of "Matthew" but also records applying to major in "Math."

In these scenarios, select options within filters and then execute a search. This functionality is very similar to pinned filters in queries and reports, where users can return all results or, by using a filter, limit the results of the existing query.

Example of portal filter

Important!

To add filter-based search in portals, you must be comfortable with editing HTML, JavaScript, and custom SQL snippets.

Create the Results Query

A portal query can be used to restrict results in the portal. Add the exports and other filters needed to return the full list of results. Later, the query will be edited to add the additional filter criteria selected within the portal. 

The results query in this example starts off with desired exports and filter criteria. 

Results Query

Since this query is returning a list of records, it is also assigned a node, allowing the portal to use Liquid looping to return one row per record in the view. 

Results Query Node

Right now, the query will always return the full list of results. In subsequent steps, a parameter will be added to this query so that it can receive a value selected within the portal, and then filter the results based on that value. A custom SQL snippet filter will also be added to further limit the results based on the parameter's value.

Create a Prompts-based Query

Most filters will be based on prompts, such as filtering by sport or by major. To start building a filter in which the options correspond to a prompts list, create a new portal query using a base of Prompts. This query will generate the list of options that the user can choose from to filter their results. 

  Tip

The Configurable Joins Prompts base can also be used to build this query. Select Type: Configurable Joins Library > Category: System > Base: Prompt.

In the following example, a filtering option will be added to an enrolling students portal, through which students will be able to search for fellow enrolling students by major.

Prompts-based portal query


 The Prompts-based query uses the following filters:

  • Key = Return prompts that have a key of major
  • Active Status = Return only active prompt values

The following export parts are selected:

  • GUID (renamed id) = Return the GUID value of each prompt
  • Value (renamed value) = Return the human-friendly prompt value

The export parts have been renamed to be computer friendly (i.e. all lowercase and no spaces).

Additionally, this query will be assigned an Output Node. The node will then be referenced in the portal view when constructing the dropdown list to filter by major.

Output Node

This query will need to be added as a linked query to the portal method associated with the desired portal view.

Edit the Results Query

In this example, the portal should filter the results list based on major. The results list query has been started, and the major prompts query has been created. Now, the results query must be updated to take in a parameter. The value from the filter selection is passed into this query as a parameter, which the query then evaluates, returning the records that match the filter criteria. This query is linked to a new portal method (more details in the last section below).

The query that returns the results list will need to have a custom SQL snippet filter, as well as a parameter, as the custom SQL snippet is used to leverage the value passed into the parameter.

Adding Parameters

A parameter needs to be defined with this query. The parameter accepts some value that is passed to it; in this case, the value of the filter selection is passed in, which the query then uses to evaluate the records that meet the filter criteria (e.g. has the selected major). 

To define a parameter, open "Edit Parameters" in the query.

Edit Parameters

Custom SQL Snippet Filters

The SQL snippet filter references the parameter. When the method is called and the query is run, the query will return the results list.

Custom SQL Snippet Filter

((@major is null) or (@major in (select [prompt] from [field] where ([field] =
'app_major') and ([record] = a.[id]))))

Here, the filter is limiting the query results to records that have a prompt value of that major ((@major in (select [prompt] from [field] where ([field] = 'app_major') and ([record] = a.[id])))); if no major was selected (@major is null), the query will return the unfiltered results set.

Each custom SQL snippet filter will be different based on the data point used in the filter (e.g. sport, a custom field, etc.) and the query base being used.

In this example, the field is >app_major. The code should be updated to reflect the desired field in your database. Additionally, this field is application scoped, hence matching the field record on a.[id]. The exact syntax may need to be adjusted based on the scope of the field, as well as the query based used, as configurable joins, for example, would have a__JID_.[id] instead.

  Tip

Any parameters referenced in custom SQL snippets must be defined before saving the custom SQL snippet.

The parameter type must also match the type of data being passed into the query. For example, if you are selecting an option value of a prompt ID in the portal filter, the parameter will be a uniqueidentifier. If you are passing in a bit prompt (i.e. 1 or 0) or a free text value, the parameter will be a string.

Update Views

The filter can be constructed in the portal view using HTML.

Major Filter View

<select id="major_id" name="major_id">
<option value="">&nbsp;</option>
% for major in majors %}
<option value="{{major.id}}">{{major.value}}</option>
{% endfor %}
</select>

A Liquid forloop is used to loop over the results from the Prompts-based query and display them as options in the dropdown.

In this example, we use a <button> HTML element with an ID attribute of lookup. Upon clicking the button, a JavaScript function is run which will then call a portal method that runs the results query and displays the results set on the page.

The following JavaScript is added in the source code of the view:

<script type="text/javascript">/*<![CDATA[*/
var fetchData = function(){
var major = $("#major_id").find("option:selected").val();
return (FW.Lazy.Fetch("?cmd=search&major=" + major, $("#results"))); } $("#lookup").bind("click", function(){fetchData()});
fetchData();
/*]]>*/</script>

When the button is clicked, the fetchData function is called, in which the value of the selected major is passed to the filter results query via the method.

In the same view, add a new <div> element with an ID attribute of results. The data returned by the filter results query will be injected into the section of the view where div id="results" is defined.

  Tip

In the script, the search string uses &major=. The name of "major" is the same name as the parameter in the results query. These items must match in order for the parameter to be passed into the results query and used as desired.

Results View

A separate view must be constructed that determines the layout of the filter results data that will be injected into the page. Typically, this will consist of a data table that uses Liquid markup (especially looping) to display the results.

Results view using Liquid markup

Update Method

A new portal method must be created to run the filter results query and deliver the data in the portal view.

Make sure to set the new portal method with the following configurations:

  • Status: Active
  • Type: GET
  • Action: Set a computer-friendly (lowercase, no spaces or special characters) value that can be used with the cmd parameter in the JavaScript added to the source code of the view where the filter was added.
  • View: Select the newly-created results view.
  • Output Type: AJAX Popup/No Branding

When the method is created, the filter results query can be added as a linked query by opening "Edit Linked Queries."

In the portal, the table list updates based on the filter selection.

Using the filter in the portal

Was this article helpful?
4 out of 8 found this helpful