Are you a DBXL user who uses QuerySharePoint in your form? You might encounter a problem when using QuerySharePoint to dynamically retrieve values from your SharePoint list into a repeating table, as one of our clients experienced this.
To illustrate the scenario, imagine a form similar to this:
Basically, we want to query from a SharePoint library with a Product list that looks like:
The Product Name dropdown populates using a QuerySharePoint connection and works perfectly:
When the user selects a Product Name, the goal is to set the Code and the rest of the repeating table fields with the corresponding product values. A second QuerySharePoint connection is created to dynamically query this. The problem? The query (as generated by QueryBuilder) will not get updated when inserting new rows. The query always used the Product Name from the first row and returns the same value.
I’ll give a quick walkthrough to show you an easy fix.
With this form opened in design mode, I’ll double click on the Product Name dropdown field inside the Repeating Table, and then click Rules.
To get the product fields, I had six actions already set as follows:
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, which in turn gives me the formula dialog box:
The formula to set the value was created using Query Builder.
The concat formula that we used shows a query that looks like:
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>", ProductName, "</value></eq></filter></query>")
Notice that it uses the value ProductName to compare to the value of Product Name column in the SharePoint list. All the other columns (Product Code, Category, etc) are then filtered based on the value of the ProductName. This type of query normally works when used in a non-repeating field. In this case, using a repeating field leads to the error we saw above. If you have the same issue, what you need to do is just replace ProductName with current():
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>")
After applying this solution to the form, let’s preview and select a Product Name from the dropdown:
It does query the correct code for the Product Name I just selected. Now if I insert a new row, and select a different Product Name:
This time with no duplicates! The trick is done by the current() parameter which gets the correct value in the repeating Product Name dropdown.
If you'd like to try QuerySharePoint, you can download a free trial package of DBXL which includes the QuerySharePoint web service.
NOTES:
-
If you are using repeating tables with dynamic queries on QueryDB and you encountered a similar issue, please use the solution advised above.
-
To fix the problems caused by user input that contains xml characters, use CDATA in the concat.
Example:
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>"