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. 

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. 

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. 

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.

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.

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.

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.

((@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.

Update Views

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

<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 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 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.

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.