InfoPath Dev

Hello, thank you in advance for any help, its greatly appriciated

First a little background.  Sharepoint 07 has a really neat little feature for filtering views called [ME].  In effect the [Me] trigger checks to ensure that the field being used to filter is the same as the person logged in.  So for example if you where to use the Created By' field that sharepoint generates and filter it using [ME], that particular view would show only records created by that person.  This is how sharepoint is able to create custom views and in turn custom dashboards that show tasks assigned to an individual, or contacts created by an individual and so on.

The Problem

I of course use a series of forms with sharepoint, one of which is a quote and order form for our sales staff.  I would like to create a custom dashboard in which a view of the form exists that shows only quote and order forms that are created by and/or assigned to a particular user.  Now the created by is simple enough since sharepoint automaticaly generates this field and checks who saved the form to the library, so I can use the created by field to filter out the user with the [ME] trigger.  However the issue is that the user that creates the form is not always the person who the form is assigned to.  Another words an order can be created by the operations manager or sales manager and assigned to a sales representative.  So I need to have a field in the info path form that can point to the particular user, lets call it the 'Assigned_To" field but retains the information or formating so that I can use the [ME] trigger for purposes of filtering the form.  Another words I want to be able to use the assigned_to field and filter the view by using the [ME] trigger on it.

The question is how do I capture the information in the info path form and retain the formating so that sharepoint understands that the data in this field is a user ID. 

 I have tried a number of methods none of which work.

The most promising one was creating a contact list for the Sales Representatives with a ID selector field (people from our network).  I then used a SharePoint drop down box with a data connection to that field.  However the formating was not retained, another words when the form is saved to the forms library the Assigned-To field is simply Text, so the [ME] trigger doesn't work.

I thought perhaps there is a way to use an active X control or perhaps the roles function of the forms, but at this point Im at a lost exactly how this is possible and Im really hoping that there is a simple solution for this problem.  It seems, at least to me, that this is something people would probobly run into.

Again thanks for any help in advance.

Chris Kubik

Hi Chris,

You have two options, depending on how you want to format your list. You can use the Content Query Web part (CQWP), which will pull all items from a list that match a certain criteria. You would add the CQWP to the page, Modify Shared Web Part, select your list (or your content type), and tell it what items you want to filter and group by. This gives the user a nice view of items that are assigned to particular people. You can streamline it by placing audience-targeted web parts on the page for all the individuals who will access your site. This part is a little high maintenance, but looks great on the page.

The other option is to use the Data View Web Part. This web part is not included with SharePoint, but does come with SharePoint Designer. If you have access to a copy, you can add the data view to your page and set your filters. This is the least time consuming, but could involve a small learning curve if you are not familiar with it. Here are some links to help you get started (NOTE: the demo in the first link pulls back information from an XML list, but you can connect to any standard SharePoint list.)

http://office.microsoft.com/en-us/sharepointdesigner/HA100948041033.aspx

http://www.sharepointcustomization.com/resources/whitepapers/webpartdocs/dataview_wp.doc

Good luck!

Hi Janice

Thank you so much for your reply, I really appriciate it.

I see where you are going with both solution but there is an underlining issue of the forms ownership which I believe is at the heart of the problem and is preventing me from getting past the filtering step.  Specificaly, how can you create the connection between SharePoint and the Infopath forms so that sharepoint understands who's ID the form belongs to for the purposes of filtering. 

I know that its possible to create a field in sharepoint that can be edited through properties of any given already filled out form and hence linking it to a specific ID which in turn can then be filtered using triggers like [ME].  However the goal is to create ownership of the form through the form itself without having to manually edit the properties after the form is filled out. 

I guess the fundemental question is how to you re-create a network ID selection field in infopath so that this ID carries over to the sharepoint forms library.

Just one more time here for clarity, the goal is to be able to create a single web part page and have views of various lists (in this case forms libraries) that show only forms that where either created by the individual logged in (the created by field using the [me] filter OR a form that was created by an individual who assigns the forms ownership to another individual (this is the issue, how do you maintain the ID properties).  The Created By field is automaticaly filled in by infopath, hence available to sharepoint (and sharepoint understands that hey this is a network id)  but the assigned to field must be created.  How do you create that field so it has the same properties as the created by field.

 Boy I hope that was someone understandable, sometimes its very difficult for me to put technical issues like this into words.

Thanks again for your help Janice.

I found this tutorial for pulling AD information into a form. I think it is a really good walk-through and is probably what you are looking for. Let me know if this is not what you were asking.

http://kwizcom.blogspot.com/2007/11/importing-data-from-ad-to-infopath-2007.html

Hi Janice

First let me just say thank you for all your help and information, it was extremly helpful and got me on the right track.

For anyone else running across this issue I thought I would give some information back here so that this post is a bit more helpful.

The basic problem of FORM ownership is a complex one for many reasons, but most things have work arounds it really just depends on wether or not you can live with them.  SharePoint and Infopath both have their limitations in this way.

The biggest limitation is the fact that 'calculated fields' in SharePoint are done dynamicaly as the page loads, hence the data for that field is empty unless your actually looking at the page.  Programicaly this creates problems as you develop as calculated fields must be stored elsewhere.  The problem is furthered by the fact that SharePoint does not create true table relations which means that using SQL tables store calculations and then call them programically is also not possible.  Finally is the fact that InfoPath data is always captured in XML format and to sharepoint XML is the equilant of basic texts hence things like UserID's from active directory cannot be captured in a form and transfered to SharePoint. 

For all these issues the answer is SLAM a new application being developed by the guys at CodePlex http://www.codeplex.com/SLAM/. 

SLAM to put it simply takes a SharePoint list and creates dynamicaly a SQL table duplicate.  This works seemlessly (hence the data in the SQL table is generated as the List Data is Saved).  Once you have your SQL tables you may now do calculations (real ones unlike what sharepoint has) and of course you can create table relationships and then call those relationships in either InfoPath forms or in SharePoint.  For any developer of SharePoint you probobly already see the possibilities here and it goes without saying that for SharePoint development SLAM has openned doors in ways I can't even put into words.

The work around for this problem, one of form ownership was as followed.

 1. Create a sharepoint list for the potential owners of the form.  Within it you should have a text field for the name and a network ID field so that you can select the Active Directory ID.

2. In the form create a selection box for who will be the owner of the form.  You should then create a seperate view and capture that data in another field (call in net_ID) and use Key ID filtering to link the Rep's Name from the contact list and the equivilant Active Directory ID.

3. In SharePoint instead of using the CreatedBy Field call the Network ID field from the SLAMED SQL table.  While this still does not allow you to use the [ME] function if you are using SLAM to duplicate this table you can create custom views of that sharepoint list and use SQL statements to query the table as normal. 

 Hopefully that makes some sense.

Hi XGuild,

Cool post and thanks for the intro to SLAM. Couple comments on that:

1. Qdabra has a similar product for InfoPath, SharePoint and SQL called DBXL for Database Accelerator. DBXL is meant to map XML data to SQL. SharePoint has native "doclib" support for XML forms, so you can accomplish the same thing using doclibs and DBXL. DBXL provides deep mapping (arbitrarily deep child tables, etc.) and a host of other features. One thing that would be useful is a comparison of DBXL and SLAM. My biased perspective is that DBXL offers a lot of other features and add-ons.
2. For relating ShP lists with SQL, there are rumors out there on the web that Microsoft is building this feature in to the next release of SharePoint. I have not heard anything from Microsoft, but it makes total sense. Try Googline "SharePoint lists Office 14".

We will take a hard look at SLAM in the next week or so.

Best!