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.
|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.|
|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.|
If using a type of options then you can set this attribute to
|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.|
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
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.
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.
|Web Service Call||
A web service should return an JSON array of objects with name and value properties.
|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.
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.
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
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.
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
type="options" and providing the acceptable values in the
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
to allow for the selection of multiple values from the options list. The
multiple attribute accepts a value of either true or
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
IN operator and you are responsible for properly quoting your values. See the next example for how you would
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
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:
|Clas-Art & Art History||11-1030|
|Clas-Asian&Slavic Lang & Liter||11-1040|
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.