Repeating tables in Info Path is no doubt a wonderful control that gives the users the suppleness to enter information in a table. But when it comes to use the individual rows/columns of this very table in SharePoint, all doubts and fears surface.
Same was with me so I decided to write about it.
Problem statement – How to promote a column of a repeating table and how to extract the individual row of data.
Repeating table gives four options for field promotion
- first – promoting the very first row’s column
- last – promoting the last row’s column
- count – giving the count of the rows in the table
- merge – merging a column’s data and promoting (we’ll be dealing with this one, rest all being unpretentious and straightforward)
Say my table here consists of details such as the product name, quantity and cost.
The Info Path fields look like below
It is always advisable to rename the Group1, Group2 to your project specific relevant names. Here, it is ProductsGroup and Product respectively to identify the fields with ease.
Now I fill up the form’s repeating table with some data
In order to be able to use the fields in SharePoint, it is always sagacious to use a separator for individual data. Our goal is to write a rule to achieve the same. To get the data in this format – “Laptops;Mouse;”, add a text field “ProductNameMerged”
Goto the properties of the text field and click on fx to write the formula
Write the formula as below:
eval(eval(Product, ‘concat(my:ProductName, “;”)’), “..”)
or if you check the “Edit XPath” check box, the formula should look like
xdMath:Eval(xdMath:Eval(../my:ProductsGroup/my:Product, ‘concat(my:ProductName, “;”)’), “..”)
This will result in the text box having values separated by a semi colon as and when data/rows are being entered in the repeating table. For the scenario above, the result shall be “Laptops;Mouse;”.
Detailed explanation of the XPath formula
concat(my:ProductName, “;”) – Adds a semicolon to each product name
eval(Product, ‘concat(my:ProductName, “;”)’) – Loops through each Product to create a list of product names
eval(eval(Product, ‘concat(my:ProductName, “;”)’), “..”) – Gets the parent of the product names, and converts them to a string.
Here you go…. Now that you have a text field with repeating table’s column separated by a semi colon, you can use regular expressions or the inbuilt string functions to extract the data.