In a previous post, we looked at creating dynamic queries using QuerySharePoint in repeating tables. In that post, the repeating dropdown field dynamically queries data from a SharePoint list by using current() parameter in the concat rule. We saw the result in the form and each row was able to query the correct data from our SharePoint list based on the value selected in the dropdown:
However, it appears that another problem starts to occur when a user queries data that contains special XML characters:
For example, if the repeating field in your form (the field that sets a static query in a QuerySharePoint connection) queries a value that contains special xml characters (e.g. & and <), you’ll encounter a QuerySharePoint error.
In this case, I have this dropdown which contains a QuerySharePoint connection to query and display the Product Name as item list (static query), then upon selecting an item, another query retrieves the corresponding row values in the repeating fields (dynamic query).
If I select a value in the dropdown that contains the special character &, I get the error below:
The same thing happens when selecting a value that has < in it. In effect, the dynamic query associated with the dropdown will not work and thus gives us an empty row:
This is due to QuerySharePoint rejecting special characters, such as & and <:
To avoid this error, you must edit the rule that sets the dynamic query in the repeating table and use an XML tag in the concat that will allow these special characters.
In this case, I’ll double click the Product Name dropdown field, click on Rules:
I’ll select the first rule (that is, the rule that sets the dynamic <query> for our QuerySharePoint connection):
Click Modify and then click the value button in the Action dialog box. If you’ll recall, our concat query looks something like this:
concat("<query><columns><column name='Product_x0020_Code'/><column name='Category'/><column name='Unit_x0020_Of_x0020_Measure'/><column name='Price'/></columns><filter><eq><column name='Product_x0020_Name'/><value>", current(), "</value></eq></filter></query>")
What we need to do is just add a CDATA inside the query <value> </value>:
concat("<query><columns><column name='Product_x0020_Code'/><column name='Category'/><column name='Unit_x0020_Of_x0020_Measure'/><column name='Price'/></columns><filter><eq><column name='Product_x0020_Name'/><value><![CDATA[", current(), "]]></value></eq></filter></query>")
Note that CDATA should always start with the sequence:
<![CDATA[
And ends with:
]]>
All characters enclosed between these two sequences will be interpreted as characters. We’re adding CDATA so that our query will be able to read special characters like & and <.
After applying this solution to the form, let’s preview and select a Product Name that contains special characters:
It works without any errors. If you are using QuerySharePoint, you might want to use this tip to prevent problems in the future.
NOTES: