Usage

When you are building your populations, you might have a need to for multiple populations that are the sample except they differ very slightly. A prime example would be if you had a database query to get students in a major, but you didn't want to have to have multiple versions of that population that only differed by the major. You can use a queryparam tag in your population to prompt the content creator for the major.

The queryparam tag is supported on Database Query and Web Service Call datasource types. You must check the Parameterized checkbox on the Population Details panel. Combined datasource types can also utilize parameterized populations.

Tag Attributes:

Attribute Required Description
name Required The name of the variable the queryparam content will be prompted for and stored. Must be only alphanumeric characters and not start with a number. If using parameterized populations within a Combined datasource, the names of the queryname must be unique if you want to discrete values to the populations contained within the Combined population.
type Required
string:
A simple text string
number:
A number
options:
Predefined options that the content creator can choose from.
maxlength Optional The maximum length the value the content creator can provide for the parameter. Not applicable when type = options.
options Optional If using a type of options this is a static delimited list of options you want to present the content editor with to choose from. See Providing options for parameters for more details.
multiple Optional If using a type of options then you can set this attribute to true so that the content editor can choose multiple values. Omit the attribute or set to false to permit only a single value to be selected from the option list. See Providing options for parameters for more details.
delimiter Optional If using a type of options then you can set this attribute to a single character that represents where to break about your options. The delimiter needs to be a printable character. The default delimiter is a comma. Changing the delimiter is useful when your options might include a comma and wrapping your delimited strings with single or double quotes would cause issues with the queryparam tag structure. See Providing options for parameters for more details.
population Optional If using a type of options and you want to dynamically pull your options from another population, you can supply the NAME of that source population in this attribute. The population must return an array of strings or an array of objects where with the attributes name and value. See Providing options for parameters for more details.

Providing options for parameters

Prompting for the content editor to provide a string or number is fine if the person providing that value is knowledgeable about the impact of that value on the database query or web service call. If you don't want a free-form input for the content editor to provide a value, you can change the type attribute to options. This will allow for the prompting of a drop-down list of defined options to choose from.

The options can sourced from either the options attribute as a delimited list of values, or they can be sourced from another population by providing a value for the population attribute.

Using a static list for options

A simple way to provide a list of values for the options is to provide a comma-delimited list of values in the options attribute.

<@queryparam name="performing_arts_study_area" type="options" options="theatre,music,dance" />

If necessary for your SQL, you can wrap the values in single quotes assuming your queryparam tag has its attributes wrapped in double-quotes.

<@queryparam name="performing_arts_study_area" type="options" options="'theatre','music','dance'" />

You can also change the delimiter from the default of a comma to another character if your values might need to retain a comma. The delimiter needs to be a printable character (i.e. you cannot use tabs, spaces, or line breaks).

<@queryparam name="president" type="options" delimiter="|" options="Washington,George|Jefferson,Thomas|Adams,John" />

Using an another population as a source of dynamic options

A more advanced way to source the population parameter options is from another population by providing a value for the population attribute. The value should be the exact name of the population you want to use to provide the options. The source population you use should have a data source type of: Database Query, Web Service Call, or Manual CSV list.

The source population must return results with the attributes: name and value.

Datasource Type Notes
Database Query You will need to alias your columns so the columns that are returned are named name and value. The example SQL below would be run against the Data Warehouse to retrieve all the departments in the College of Liberal Arts and Sciences using data from the HR system. The name of the department would be the name visible to the user in the drop-down list and the value would be the value passed through the queryparam tag to your target population.
select
        d.descr as NAME,
        d.deptid as VALUE
from
        hris.ps_dept_tbl d
where
        d.effdt = (select max(z.effdt) from hris.ps_dept_tbl z where z.deptid = d.deptid)
        and d.deptid like '11-%'
order by
        d.deptid
Web Service Call A web service should return an JSON array of objects with name and value properties.
[
   {
       name: "Clas-African-American Studies",
       value: "11-1000"
   },
   {
       name: "Clas-American Studies",
       value: "11-1010"
   },
   {
       name: "Clas-Anthropology",
       value: "11-1020"
   }
]
Manual CSV List A Manual CSV List should be composed of a header line that defines the name and value columns with the subsequent lines containing the data to be returned.
name,value
Clas-African-American Studies,11-1000
Clas-American Studies,11-1010
Clas-Anthropology,11-1020

You cannot use both the options and population attributes.

You might also want the content editor to be able to select multiple values from the list. Enabling a multi-select list can be accomplished by setting the multiple attribute to true. It is false by default.

Examples

Example 1: Prompt for a Org/College in an IDW view query

You want to query for all faculty in a college.

select
    mail as toAddress,
    displayName as toName
from directory.v_myview
where
    eduPersonPrimaryAffiliation = 'faculty' and
    uiowahrorgid = '11'

That above query would get all the current faculty in the College of Liberal Arts and Sciences. That's all and good, but what if you wanted another population to get all the faculty in another college? You could duplicate the query and change the last line to say uiowahrorgid = '12' to get the College of Business. Imagine, you needed separate populations for every Org. That would be over 40 populations that were identical except for that last line. That's a lot of replicated SQL.

Instead of using a literal in that last line that limits the population to a single Org, we can use a queryparam tag. Since Orgs are always two digit numbers, we set type="number" and provide a value for maxlength.

select
    mail as toAddress,
    displayName as toName
from directory.v_myview
where
    eduPersonPrimaryAffiliation = 'faculty' and
    uiowahrorgid = '<@queryparam name="org" type="number" maxlength="2" />'

When you create your communication and you choose your population that contains the queryparam tag, you will be prompted for any of the parameters in your population. For reference, the SQL or URL of the webservice is displayed to the right of the parameter fields.

Example 2: Use options to provide acceptable values.

You have a webservice and it only accepts certain values for one of its parameters on area of study.

https://api.uiowa.edu/someservice?area=theatre

Just like the first example, we don't want to maintain nearly identical copies of populations when only some values change between them. We can use a queryparam tag to prompt the content creator to choose from the acceptable values by setting the type="options" and providing the acceptable values in the options attribute.

https://api.uiowa.edu/someservice?area=<@queryparam name="area" type="options" options="theatre,music,dance" />

Now, when the content creator sets up their communication, they will be prompted to choose from the list of areas.

Example 3: Select multiple values

Looking back at the last example, suppose we want the content editor to be able to select multiple areas to send a message to from the population. We can combine the options attribute with the multiple attribute to allow for the selection of multiple values from the options list. The multiple attribute accepts a value of either true or false.

https://api.uiowa.edu/someservice?area=<@queryparam name="area" type="options" options="theatre,music,dance" multiple="true" />

On the communication edit panel, they will be allowed to select one or more values.

Please note, that if you are using a queryparam tag that allows for multiple values in a SQL query, you must use an IN operator and you are responsible for properly quoting your values. See the next example for how you would do that.

Example 4: Escape quotes in options

Let's go back to our first example again and change it a bit. Instead of getting faculty in an Org, let's retrieve people in particular departments. That wouldn't be that hard, we would just add another statement to the WHERE clause. However, let's limit users to only select from certain departments.

select
    mail as toAddress,
    displayName as toName
from directory.v_myview
where
    eduPersonPrimaryAffiliation = 'faculty' and
    uiowahrorgid = '11' and
    uiowahrdeptid = '<@queryparam name="department" type="options" options="1000,1080,1040,1150,1180,1360" />'

That would prompt the content creator to choose a single department. However, what if wanted to group certain departments together. Our list of department above includes foreign language departments. Perhaps we want to treat them like a big department so we would use an IN operator like so:

select
    mail as toAddress,
    displayName as toName
from directory.v_myview
where
    eduPersonPrimaryAffiliation = 'faculty' and
    uiowahrorgid = '11' and
    uiowahrdeptid IN ('1040','1150','1180','1360')

How would we do that with a queryparam? Well, it gets kinda gross because we have to deliminate the different options with double-quotes and then escape the single-quotes needed for the SQL.

select
    mail as toAddress,
    displayName as toName
from directory.v_myview
where
    eduPersonPrimaryAffiliation = 'faculty' and
    uiowahrorgid = '11' and
    uiowahrdeptid IN (<@queryparam name='department' type='options' options='"\'1000\'","\'1080\'","\'1040\',\'1150\',\'1180\',\'1360\'"' />)

That's a lot of quotes to keep track of. Those extra quotes would also cause a problem for certain strings if they contained single or double-quotes. A better solution would be to change the delimiter. You can change the delimiter by specifying a character for the delimiter attribute.

select
    mail as toAddress,
    displayName as toName
from directory.v_myview
where
    eduPersonPrimaryAffiliation = 'faculty' and
    uiowahrorgid = '11' and
    uiowahrdeptid IN (<@queryparam name="department" type="options" delimiter="|" options="'1000'|'1080'|'1040','1150','1180','1360'" />)

Example 5: Sourcing your options from another population

To take things one step further, instead of supplying a list of options in the queryparam tag, we can alternatively source the options from another population.

Let's assume we have a population that will be supplying our options. This population has been named Source Department Options. This is the same example SQL from the Providing options for parameters section above. It retrieves all the current departments from the HR system in the College of Liberal Arts and Sciences.

select
        d.descr as NAME,
        d.deptid as VALUE
from
        hris.ps_dept_tbl d
where
        d.effdt = (select max(z.effdt) from hris.ps_dept_tbl z where z.deptid = d.deptid)
        and d.deptid like '11-%'
order by
        d.deptid

When this population is rendered it will return data like so:

NAME VALUE
Clas-African-American Studies 11-1000
Clas-American Studies 11-1010
Clas-Anthropology 11-1020
Clas-Art & Art History 11-1030
Clas-Asian&Slavic Lang & Liter 11-1040
Clas-Inter-Departmental Units 11-1910

In our population we are adding the queryparam tag, we would structure the tag like so to fetch the options.

select
    mail as toAddress,
    displayName as toName
from directory.v_myview
where
    eduPersonPrimaryAffiliation = 'faculty' and
    uiowahrorgid + '-' + uiowahrdeptid = '<@queryparam name="department" type="options" population="Source Department Options" />'

Finally, when a content editor creates their communication, they will be prompted with a drop-down that contains the department's friendly name rather than its number as we did in the other examples above. The department number would still be passed to the target query as it is the value bound to the drop-down option.

Related topics: