Browser Forms

Re: Filtering a secondary data source on a person column

jeestirling — Mon, 20 Jun 2011 08:42:14 GMT

Hmm that's a bit of a pain really. As a workaround (although it probably should be classed as a solution given the constraints) I have set a workflow that populates a string column with the username from the person column. Using a content type means that the string column can be hidden.

Re: Filtering a secondary data source on a person column

Patrick Halstead — Mon, 20 Jun 2011 06:57:44 GMT

You will need to either a) enter the username column into the list or b) make sure that the user is the last person to edit their Examiner Name rows (and use ModifiefBy as username comparison).

Re: Filtering a secondary data source on a person column

jeestirling — Sun, 19 Jun 2011 18:18:46 GMT

Hi Patrick and thanks for the welcome.

I am afraid that I may not have made myself clear in the initial post.

I have a SharePoint list that has the following columns

1) "Examiner Name" (type = person)

2) "Faculty" (type = single line of text)

3) "Course" (type = single line of text)

When the user opens up a new form I want to show a dropdown list of Courses that he/she is the examiner for.

I already have a field on the form which captures the UserName() and this is what I am trying to use as the filter for the SharePoint list.

The problem is that the datasource associated with my SharePoint list does not return the UserName, instead it returns the Preferred Name (at least it does with owssvr.dll). The problem is that in our domain Preferred Names are not unique so we can't really filter using Preferred Name.

What I am looking for is a way to expose the UserName element from the person column in the SharePoint list so that we can filter the datasource using the UserName() in InfoPath.

Re: Filtering a secondary data source on a person column

Patrick Halstead — Sat, 18 Jun 2011 21:01:27 GMT

Hi JeeStirling and welcome,

Glad to see you have explored this in depth. InfoPath includes the username function as part of it's xpath arsenal - add a rule that sets a field's value when your form loads to the value of username() - use the Fx button to look it up.

Username() assumes that people are joined to the domain. Then you can use it to get the preferred name or you can just create a separate ShP list that indexes username to Preferred Name. If you have a lot of users, that may be time-consuming and is not an auto option. Another approach is to dig into SharePoint's UserProfile service and see if there is a map from username to Preferred Name there. Or, better yet, use Active Directory which is bound to have the property. Qdabra provides a free trial to it's Active Directory Web service as part of DBXL and you could download and install that if you can't figure it out using UserProfile.

Hopefully, you can just use owssvr though. qRules provides a simpler way to call FilterOwssvr and pass it a parameter. We have a free trial of that too.

Lots of options and we do this kind of lookup thing all the time in our forms so let me know if you'd like a quick demo. Just email me.

Cheers!

Filtering a secondary data source on a person column

jeestirling — Sat, 11 Jun 2011 14:22:32 GMT

I have a secondary data source that retrieves a SharePoint list that includes a person-type column. I want to filter that list to show only records that have the current user as the person named in the person column.

From what I can see ...

If I create the secondary data source as a SharePoint list the person column appears as the person's Preferred Name. In our situation this is not a suitable value to filter on as there are duplicate Preferred Names in the organisation.

If I create the secondary data source using a web service or owssvr.dll XMLDATA the person column data appears in the form "ID;# Preferred Name". This looks very promising as the ID is clearly unique, but I cannot figure out how to determine the ID of the user in this context. It appears to be specific to the site collection, but I cannot find a web service or anything else that will return that ID for that site collection.

I am convinced this not an unusual thing to want to do, but I have not been able to find a way. Any suggestions?