InfoPath Dev

I am working in InfoPath 2010 with SharePoint 2010. I do not have access to any developer tools, so I'm pretty much limited to XPath solutions. I am also fairly new to the world if InfoPath, so please forgive me if this is "way out there". I am using 2 SharePoint lists as data sources. The form is built around List_1 and I am using List_2 as a place to look up Cost information. In List_2, there are is a column for Item_Description and different columns for yearly Costs and the like. Example: Item_Desc, 2012_Cost, 2013_Cost, 2014_Cost, etc. There is a Year field on the form (from List_1) that I wish to pass to the Filter to pull the respective cost from List_2. For example, if the year on the form is 2014, I want to pull the 2014_Cost for the Item_Desc in question. This is a logical representation of what I'm trying to do -- note the attempt to concat the column name together using the Year field and some text: (../my:Number_of_Units) * xdXDocument:GetDOM("List_2")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:concat((../my:Year),"_Cost")[../d:Title = "Item_Name"] Is this possible? edit: Sorry, I'm not sure how to format my post?

I think know what you are trying to achieve, and I would suggest modifying your 'List 2' structure. Having explicitly named fields relating to years is a very bad design.

I would change your list 2 structure to have 3 fields:

Item_Desc - String
Year - Whole Number
Cost - Currency

Then the items in List 2 would look like:

Item_Desc, Year, Cost
Item_1, 2013, $500
Item_1, 2014, $550
Item_2, 2012, $100
Item_2, 2013, $250

To get the cost of Item_2 for 2012 the Xpath would be:

xdXDocument:GetDOM("List_2")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:Cost[../d:Item_Desc = "Item_2" and ../d:Year = 2012]

That makes llokups much easier.  Obviously then you have to manage all the list item in List_2, but that should be easier than having all those explicitly named fields.

Cheers