Alec Pojidaev's InfoPath Blog
Stuff published for no reason (cool codeless infopath solutions mostly)

Combining two datasources in XPath

This post is a result of solving real life problem for one of my coworkers. The task was to make a multiple choice from secondary datasource and then present this choice on the summary page as a concatenated string. Not too complex, right? The problem was that the multiple choice control was displaying titles while the stored values had to be IDs. So to display concatenated titles somewhere else you need to build query against your secondary datasource applying choices made at multiple choice control. If it would be SQL the solution is clear – it’s called INNER JOIN. The capital letters here doesn’t mean to indicate how great this INNER JOIN thing is nor to indicate that this INNER JOIN thing is kind of luxury unattainable for poor InfoPath developers. :) No, it’s just a SQL notation. So here is the formula I come up with during my attempt to lower the level of suffering of another fellow codeless programmer:

substring-after(xdMath:Eval(xdMath:Eval(xdXDocument:GetDOM(“CompetitiveWorkloads”)/dataFields/harePointListItem_RW[contains(xdMath:Eval(xdMath:Eval(xdXDocument:get-DOM()/my:myFields/my:compworkload/my:competitive_workload, 'concat(., ";")'), ".."), ID)], ‘concat(“;”, Title)’), “..”), “;”)

Looking at this formula it’s not too hard to realize that another animal called the OUTER JOIN can be killed with the similar formula:

substring-after(xdMath:Eval(xdMath:Eval(xdXDocument:GetDOM(“CompetitiveWorkloads”)/dataFields/harePointListItem_RW[not(contains(xdMath:Eval(xdMath:Eval(xdXDocument:get-DOM()/my:myFields/my:compworkload/my:competitive_workload, 'concat(., ";")'), ".."), ID))], ‘concat(“;”, Title)’), “..”), “;”)

There are two key elements of these formulas:
1) Using function “contains” to provide evaluation of a given ID belonging to the set of selected IDs
2) You have to start XPath for the multiple selection repeating field from xdXDocument:get-DOM() to reset current relative XPath.

The FORM to try.

P.S. Here is another formula for INNER JOIN, this formula maybe more preferable because it’s simplier:
substring-after(xdMath:Eval(xdMath:Eval(xdXDocument:GetDOM(“CompetitiveWorkloads”)/dataFields/harePointListItem_RW[xdXDocument:get-DOM()/my:myFields/my:compworkload/my:competitive_workload = ID], ‘concat(“;”, Title)’), “..”), “;”)

Happy codeless programming!

About these ads

One Response to “Combining two datasources in XPath”

  1. Hello fantastic web page information within your web-site I hope you preserve penning this top quality well written articles or blog posts Cheers:)


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 40 other followers

%d bloggers like this: