Sharepoint 2010 and Infopath form : lookup fields are displayed by their ID instead of text value

Question

I'm trying to build a custom Infopath form for a list that contains 2 fields that I would like to get auto-completed:

1. Person: people picker field, filled with the current user
2. Team: lookup to the Teams list, filled with the team of the user logged in which can be found in the Persons list (each Person is bound to one team)
3. List item

To resolve 1), I used the userName() function in Infopath. For 2), I created a new data source that fetches the 'Title' (Text) and 'Team' (lookup) fields in the Persons list. I also created a filter : Form.Person.AccountID = DataSource.Person.AccountID. The logic seems to work when I create a new form in Sharepoint, however the teams get displayed by their IDs (1, 2,...) instead of their text values (team A, team B,....). Moreover, this does not select any value, I also need some kind of formula for the 'default value' box so that it picks up the only available team.

Any help will be greatly appreciated !

Answer 1

From reading your post, I am bit confused with exactly what you want to do, however if you are having an issue in which you want to alter the name of the column of a list view, and are unable to edit it, I suggest creating a "hidden field."

1. Right click on the myFields folder in the Fields window of InfoPath, and choose Add. It will give you an option of creating a field or group.
2. Choose Field
3. Name the Field what you want the column to be called
4. Under Functions for the field set the field equal to field that you want to modify
5. When you publish it to SharePoint, include the new field that you created, you can then hide the older field by deselecting it in the Preferences part of the List

I hope this helps a little…

Answer 2

I eventually managed to solve the problem by building a default formula using a XPath expression that fetches the teamID in the Persons list :

xdXDocument:GetDOM("Persons")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:Team[../d:Person/pc:Person/pc:AccountId = xdXDocument:get-DOM()/dfs:myFields/dfs:dataFields/my:SharePointListItem_RW/my:Person/pc:Person/pc:AccountId]