Skip to content
KiweelyKiweelyKiweely

Escaping Single Quotes for Multi-Value IN Clauses and Fixing Double URL-Encoded Unicode in PMD WQL

Dynamic WQL queries in Workday Extend PMD fail when comma-separated attribute strings are wrapped in a single pair of quotes or when instanceListQuery is double URL-encoded. Split and quote each ID individually, and call string:urlDecode(instanceListQuery) before building the WQL string.

  • Checked against Workday on
  • Applies to Workday Query Language
  • 3 min read
  • Published
  • By Kiweely
On this page

Problem 1: Multi-value IN (...) clauses fail with Specify a valid value for the field

When an Extend app stores multiple Workday IDs (WIDs) or reference IDs in a single comma-separated App Attribute (attrstore) or page variable—such as 2ad818e8149b4bf88aa6deb6ad4162ca,b4f7687c800442b6b2bddc9f7e70f6ba—iterating directly over the attribute in a Presentation Metadata (PMD) script treats the entire comma-separated string as a single element or produces an empty list.

If you run:

javascript
var trackTypeIds = [];
var q = String.fromCharCode(39);
for (var id : attrstore.academicTrackTypes) {
  trackTypeIds.add(q + id + q);
}
var trackTypeIdList = trackTypeIds.join(',');

the resulting WQL query wraps the entire comma-separated string inside a single pair of quotes instead of quoting each ID separately:

sql
-- Broken output: treated as one literal 65-character ID
SELECT academicTrackTypeName, referenceID1, workdayID
FROM academicTrackTypes
WHERE workdayID IN ('2ad818e8149b4bf88aa6deb6ad4162ca,b4f7687c800442b6b2bddc9f7e70f6ba')

Workday rejects this WQL request with HTTP 400:

text
{error=invalid request: WQL error., errors=[{error=Specify a valid value for the field. The field value is empty or invalid.}]}

Conversely, if you use an arrow function with braces inside .map(id => { ... }) without an explicit return statement, every element evaluates to null and produces WHERE workdayID IN (), which fails with the same Specify a valid value for the field error.

Fix: Split the string on commas, trim each ID, and wrap each item in single quotes

Before building the IN (...) list, split the raw string with .split(','), trim whitespace from each token, wrap each individual ID in single quotes '...', and join with a comma:

javascript
<%
  var raw = attrstore.academicTrackTypes;
  var academicTrackTypes = raw ? raw.split(',') : [];
  var trackTypeIds = [];
  for (var id : academicTrackTypes) {
    if (!empty(id.trim())) {
      trackTypeIds.add("'" + id.trim() + "'");
    }
  }
  var trackTypeIdList = trackTypeIds.join(',');
  var queryString = "SELECT academicTrackTypeName, referenceID1, workdayID FROM academicTrackTypes WHERE workdayID IN (" + trackTypeIdList + ")";
  return "/data?query=" + string:urlEncode(queryString);
%>

This produces the valid WQL syntax required by /wql/v1/data:

sql
SELECT academicTrackTypeName, referenceID1, workdayID
FROM academicTrackTypes
WHERE workdayID IN ('2ad818e8149b4bf88aa6deb6ad4162ca','b4f7687c800442b6b2bddc9f7e70f6ba')

If a user-supplied string value inside a WQL literal can itself contain a single quote (such as an organization or last name like O'Connor), escape embedded single quotes before wrapping the value so the WHERE parser does not terminate the literal early.

Problem 2: Chinese and non-ASCII characters return 0 results in instanceList WQL search endpoints

When you configure an instanceList widget with a server-side searchEndPoint backed by WQL, the user’s search term is passed into your inbound endpoint via instanceListQuery. If a user types a Chinese, Japanese, Korean, or accented name—for example, 陳安迪—running the raw WQL query in View WQL Query Result returns the matching worker, but typing 陳安迪 into the instanceList search box on the Extend page returns zero matches.

Inspecting the generated WQL query in the PMD logs reveals why:

sql
SELECT workdayID AS id,
       fullName AS descriptor,
       company,
       supervisoryOrganization,
       cf_CFEXTTXTWorkerDisplayName
FROM workersForHCMReporting (dataSourceFilter = allActiveWorkers)
WHERE (
  employeeID CONTAINS '%E9%99%B3%E5%AE%89%E8%BF%AA'
  OR fullName CONTAINS '%E9%99%B3%E5%AE%89%E8%BF%AA'
  OR cf_CFEXTTXTWorkerDisplayName CONTAINS '%E9%99%B3%E5%AE%89%E8%BF%AA'
)

Workday’s presentation runtime passes instanceListQuery into the search endpoint already URL-encoded (%E9%99%B3%E5%AE%89%E8%BF%AA for the UTF-8 bytes of 陳安迪). When your endpoint script concatenates instanceListQuery into the WHERE clause and then calls string:urlEncode(query) on the full WQL statement, every % sign in %E9%99%B3%E5%AE%89%E8%BF%AA is encoded a second time into %25E9%2599%25B3%25E5%25AE%2589%25E8%25BF%25AA. Workday then decodes the query string once on arrival at /wql/v1/data and searches for the literal ASCII characters %E9%99%B3%E5%AE%89%E8%BF%AA instead of 陳安迪.

Fix: Call string:urlDecode(instanceListQuery ?? '') before constructing the WQL query

Always decode instanceListQuery back into a UTF-8 string using string:urlDecode(...) (or the instance method .urlDecode()) before concatenating it into your WQL condition, and then call string:urlEncode(...) exactly once on the complete query string at the return statement:

javascript
{
  "name": "workerSearchEndpoint",
  "baseUrlType": "WORKDAY-WQL",
  "authType": "sso",
  "deferred": true,
  "url": "<%
    var searchText = string:urlDecode(instanceListQuery ?? '').trim();
    var selectStatement = 'SELECT workdayID AS id, fullName AS descriptor, company, supervisoryOrganization, cf_CFEXTTXTWorkerDisplayName ';
    var dataSource = 'FROM workersForHCMReporting (dataSourceFilter = allActiveWorkers)';
    var searchCondition = '(employeeID CONTAINS \'' + searchText + '\' OR fullName CONTAINS \'' + searchText + '\' OR cf_CFEXTTXTWorkerDisplayName CONTAINS \'' + searchText + '\')';
    var query = selectStatement + dataSource + ' WHERE ' + searchCondition;
    return '/data?query=' + string:urlEncode(query);
  %>"
}

Decoding instanceListQuery first is safe for ASCII input as well: ASCII characters pass through string:urlDecode unchanged, encoded spaces (+ or %20) become normal spaces inside the CONTAINS '...' literal, and multi-byte UTF-8 characters are restored to their actual Unicode characters before the final string:urlEncode(query) encodes the entire query parameter once.

How to verify the fix in your tenant

  1. Add console.info(query); right before return '/data?query=' + string:urlEncode(query); in your PMD endpoint script.
  2. Preview the page, open the instanceList dropdown, and search for a worker using non-ASCII characters (such as 陳安迪 or an accented name) and a space-separated full name.
  3. Check the developer console log to confirm the unencoded query string contains the readable Unicode script characters and properly quoted 'id1','id2' values in IN (...), and verify the dropdown populates with the matching worker records.

Sources checked

Related guides

All guides

Let Workday check the work.

Kiweely writes WQL and builds orchestrations in your own tenant, and Workday, not Kiweely, says whether they passed.

Get the next guide in your inbox.

One email a month with new guides and one Extend tip. Nothing else.

We’ll email you once to confirm. Unsubscribe in one click from any issue.