Infopath, Repeating tables and removing duplicates

I was recently engaged by a client to assist them with creating and managing a role based access request system for their SAP implementation. The current process that was entirely paper based, consisting of a form that was completed by the requesting user, taken around each of the role approvers, finance checks and then finally submitted for processing.

As a whole the process lacked visibility of everything from the number of requests in process at any one time to the number of rejections and completions in a given period. This made the process time consuming to audit when it came to external audit time. This process screamed AUTOMATION!

As this client already had a significant investment in InfoPath, we decided to build the system with an InfoPath form at its heart to gather the relevant information about the roles requested by the user. The number of roles requested would never be a fixed amount, so InfoPath’s repeating table functionality was well suited to the task. It was this use of repeating tables that actually forms the basis of this blog post, as it caused us a very interesting problem with the subsequent workflow and required a fairly complex set of rules to resolve without resorting to using code in the form.

In order to explain what the problem was, consider this sample form (With the XML layout embedded).

The Roles in the role drop down, are sourced from another SharePoint list that consists of simply the Role name in the Title column and a single Person field that contains the role approver. When we select the Role name in the drop down, a rule on the field updates the RoleApprover and RoleApproverID columns with the information from the SharePoint list.

In order to use these approvers names in a workflow, I need to collect all of the values from the RoleApproverID fields, concatenate them with a semi-colon and the place this value into the promoted field so that the SharePoint workflow can make use of them.

Using the double eval technique, we can use the following rule in our submit actions to set this field:-

xdMath:Eval(xdMath:Eval(my:group1/my:RoleGroup, 'concat(my:RoleApproverID, ";")'), "..")

This results in the above selections placing the following into AggregatedApprovers:-

Whilst this has done exactly what we needed and extracted all of the approver IDs out of the repeating table, it has included my account 3 times. If this field was then used to populate a custom task in a workflow, this would result in me receiving 3 tasks to complete! As this wasn’t the behaviour that the client wanted, we needed to find a way to aggregate the approvers list, and remove duplicates at the same time.

I did remember an article by S.Y.M. Wong-A-Ton who wrote a great series of Infopath Cookbooks and it was his use of combing the COUNT function along with the following-sibling selector to create a duplicate flag that set me on the path to solving this.

Firstly,we need to add a new field to the Rolegroup called “DuplicateApprover”. This field receives a default value calculation that looks at the following-siblings in the Rolegroup and flags a 1 if it finds a duplicate. If it doesn’t, or it’s the last field, then it’s value will be 0.

Then we add the following Default Value to the field….

count(../my:RoleApproverID[. = ../following-sibling::*/my:RoleApproverID])

With this default value in place and a new column added so that we can see it, you’ll notice that the earlier entries with my name on them are now flagged as duplicates, except for the last one.

What we can now do is amend our original double eval in the submit function, to only include entries where DuplicateApprover is 0. Unfortunately, xPath doesn’t provide us an easy way to do this, so instead we have to hack things slightly using substring and multiply by 0!

If we take our earlier double Eval statement, we’re going to replace the ‘concat(my:RoleApproverID, “;”)’

With

'substring(concat(my:RoleApproverID, ";"), 1, (my:DuplicateApprover < 1) * string-length(concat(my:RoleApproverID, ";")))'

Complex looking I know, but if we break it down, you’ll see it’s actually quite simple. The whole thing is a SUBSTRING function that looks like SUBSTRING( SourceString ,StartCharacter,EndCharacter)

Each of these elements are formed of:-

And if we now look at our AggregatedApprovers value, you can see that we’ve been able to extract only the Unique approver IDs, removing all of the duplicates.

This took me the best part of a day to resolve, so I hope it proves useful to someone, if nothing else this will be on my blog to remind me how I achieved it in a years’ time if I need to come back to this or a similar piece of work!

Paul.