Passing Filters in URLs

Sometimes it is convenient to pass a filter string in the url to a page.

This is especially useful for:

  • TabularReport.aspx
  • ExportTabularReport.ashx

Each of these urls have a ‘where’ query parameter.

For example:

TabularReport.aspx?where=[Assignee]=CURRENT_USER

Be aware that you will need to encode your ‘where’ parameter appropriately. There are many tools on the web that can do this for you (e.g. http://meyerweb.com/eric/tools/dencoder/). You may also use the platform formula function URLENCODE.

This will pre-filter the tabular report to only show records that are assigned to the current logged in user.

Some examples

Example 1

"TabularReport.aspx&where= "& URLENCODE("[Column1]=" & DB_ENCODECONSTANT([A local field]))

Example 2

This example would be inserted into an entry portal or dashboard, and be used to generate a url to send the user to. This portal has two date fields: [From Date], and [To Date] which the user can select.

The user will then follow the link to a tabular report. The tabular report will be filtered when [Initial Term Date End] is within the above date range.

LET $filters := ARRAY(
        "[Initial Term Date End] >= " & DB_ENCODECONSTANT([From Date])),
        "[Initial Term Date End] <= " & DB_ENCODECONSTANT([To Date]))
    );

// put the filter parts together, separated by ' AND '
LET $where := ARRAY_JOIN($filters, " AND "); 
 
RETURN EBMS_GET_BASE_URL() & "/TabularReport.aspx?EFormType=Contract+Management&profile=Contract+Expiry+Report&where=" & URLENCODE($where)

For extra fun: A more realistic scenario would have the date fields as optional fields. In this case we need to exclude them if they are not supplied.

LET $filters := ARRAY(
        IF(ISBLANK([From Date]), "", "[Initial Term Date End] >= " & DB_ENCODECONSTANT([From Date])),
        IF(ISBLANK([To Date]), "", "[Initial Term Date End] <= " & DB_ENCODECONSTANT([To Date]))
    );

// remove the blanks from the array
LET $nonBlankFilters := ARRAY_WHERE($filters, $(i, item) NOT(ISBLANK($item))); 

// put the remaining items together, separated by ' AND '
LET $where := ARRAY_JOIN($nonBlankFilters, " AND "); 
 
RETURN EBMS_GET_BASE_URL() & "/TabularReport.aspx?EFormType=Contract+Management&profile=Contract+Expiry+Report&where=" & URLENCODE($where)

Related Articles