SharePoint Integration

Re: Can't filter merged fields in SharePoint 2003

10basetom — Fri, 01 Feb 2008 08:11:42 GMT

Patrick Halstead:

Hi Tom,

Thanks. We will think more about a way to create a control on SharePoint to suppor this. Unfortunately, because it isn't relational this is just hard.

Thanks, Patrick! There are rumours that we are going to upgrade to SharePoint 2007 soon, so I'm pretty excited.

Tom

Re: Can't filter merged fields in SharePoint 2003

10basetom — Fri, 01 Feb 2008 08:09:44 GMT

Clay Fox:

If your users like your stuff then organize them to start lobbying IT to let you install some webservices on your SharePoint server. Then you could really be a hero with all of the powerful capabilities you could employ.

That is what you will need to get from highschool to your advanced degree :)

Have Fun.

HA! There are lots of things that I do wish for, and this is one of 'em. I guess in my case it's the classic chicken-or-the-egg dilemma (brass wants results first as justification, but said results cannot be had unless current constraints are removed).

Tom

Re: Can't filter merged fields in SharePoint 2003

Patrick Halstead — Fri, 01 Feb 2008 06:49:50 GMT

Hi Tom,

Thanks. We will think more about a way to create a control on SharePoint to suppor this. Unfortunately, because it isn't relational this is just hard.

Re: Can't filter merged fields in SharePoint 2003

Clay Fox — Fri, 01 Feb 2008 06:43:00 GMT

Yes, you could create a text field in your data source, which was not in your views. When they select a book it would set field value concat(hiddenfield, ", ", selectionfield) The problem is that when they want to remove one or change the order it gets dicy but that maybe would suffice.

If your users like your stuff then organize them to start lobbying IT to let you install some webservices on your SharePoint server. Then you could really be a hero with all of the powerful capabilities you could employ.

That is what you will need to get from highschool to your advanced degree :)

Have Fun.

Re: Can't filter merged fields in SharePoint 2003

10basetom — Fri, 01 Feb 2008 05:37:50 GMT

Hi again Patrick,

Yes, your post helped a lot; in fact, this whole forum has been extremely helpful in my InfoPath/SharePoint education (started at preschool level and now I'm confident to say I've graduated to high school level). Thanks for providing concrete examples of the taxonomy and SLA concepts -- I think I have a better understanding of them now.

"How many collections do you have? How many items in each collection? It's possible you could just add extra fields to your form and limit the number to say 10-20."

So far I just have two, but may add more as the form is still evolving. The reason I wanted to go with the repeating bulleted list control is because I won't know ahead of time how many books a person would enter. Each collection (assuming by this you mean all the values in a repeating control) can contain anywhere from one to a dozen items. Since the book list can be relevant to more than one genre, I put the drop-down list in a repeating section so that people can add more genres as necessary.

On the SharePoint form library I wanted a concatenated ("merged") list of books in the 'Books' column and a concacentated list of genres in the 'Genres' column. It's too bad that currently these two fields can't be filtered because that's what a lot of people have been asking for :( (e.g., list only books where Genres column contains "Sci-Fi" or "Horror").

I think I will probably have to end up resorting to coding in order to manually concatenate the repeating values and output them as a comma-separated string to a hidden field in the form (I'm already doing this to checkboxes and the concatenated string is filterable on SharePoint).

Tom

Re: Can't filter merged fields in SharePoint 2003

Patrick Halstead — Fri, 01 Feb 2008 05:08:21 GMT

Hi Tom,

Thanks for clarifying! Note: this forum is free :)

InfoPath does in fact flatten the child values. Merge is just concatenating them. I think that SharePoint can't auto-filter this because of the logic in the auto-filter. A field with concatenated values and spaces in between should show the same SharePoint limitation.

How many collections do you have? How many items in each collection? It's possible you could just add extra fields to your form and limit the number to say 10-20. If the number is in the <10 range, this is probably the easiest thing to do, although it is a bit of a hack (and watch out for he SharePoint column limitations)

We have a new tool that converts InfoPath forms into SharePoint lists. We could change your form to insert multiple list items for the elements (e.g. Books) in your form. That would require some coding. We've done that countless times before but it would probably take an hour or two to get working and since you don't have any money, purchasing a support pack for us to help is not an option.

Taxonomy support. You can define a taxonomy of tags to help you filter your list and then use that taxonomy to quickly cut down the forms you want to look at. Here's a quick example of a taxonomy that includes Priority. I have selected documents assigned to Ernesto and further filtered using the taxonomy to just show me the ones which are priority 1.

This is quick way to do filtering. SharePoint supports creating views, but that can take a little time. With a taxonomy, you have one person define the areas that you commonly pivot on and then everyone can combine the pivots quickly to create unique views.

In a human workflow process, SLAs define number of days between each roles. For example, an IT hosting company will give customers a 1 day SLA to respond to all support incidents. For procurement, you could give legal a 5 day SLA to review a contract for a PO. Etc. Lots of examples. Notifications need to be defined based on SLAs. I.e. when the document is overdue (or approaching overdue), that's when you want to send reminders to people.

Hope this helped!

Re: Can't filter merged fields in SharePoint 2003

10basetom — Fri, 01 Feb 2008 04:39:32 GMT

Hi Patrick,

Thanks for the detailed explanation. Before I continue, let me list the constraints that I have to work with:

I don't have access to IT resources, so unfortunately setting up a SQL server is not feasible. I'm pretty much stuck with a solution using InfoPath 2003 and SharePoint 2003.
I don't have a budget to purchase any add-ons that would make my life easier.

With that said, your Web Service Suite sounds exciting! Since I don't want to waste your time, if you can point me to online documentation that shows how to install it that would be much appreciated (though note that if it requires any sort of mucking around in server configs and what not, it's probably out of the question for me).

Back to the situation at hand, what I don't understand is why SharePoint allows me to auto-filter every other column except columns that hold merged data coming from repeating controls in InfoPath. I guess I don't understand how InfoPath transmits (if that's the right terminology) the merged repeating data to SharePoint. My assumption is that, given this bulleted list in InfoPath...

Book ABC
Book XYZ

When I choose the "merge" option (as opposed to "first" or "last"), InfoPath would combine each of the bullets into a "flat" string separated by a space or line break -- at least this is the way it looks on SharePoint. If this is just a plain text string, I don't see why SharePoint can't filter this column when it can filter a column that holds data from an InfoPath text box field. I guess from what you're saying, InfoPath actually doesn't flatten out the repeating "merged" values, but transmits that as a child table object to SharePoint?

I have a solution that I think would work, but it requires JavaScript coding (loop through the repeating nodes to concatenate the values and output to an invisible field) and I want to minimize coding as much as possible because the people who will be maintaining the InfoPath form are non-techies.

"Taxonomy support for browsing documents doesn't exist"

Could you please elaborate on this? I found out that if I create a folder in the folder containing the submitted forms, the folder shows up in the SharePoint view and I can browse it. You are probably talking about something entirely different, huh?

"SLA based notifications not supported"

Could you please give a real-life example of this? I Googled this and found an entry in the Wikipedia about Service Level Agreement, but I can't grasp this concept in concrete terms.

Thanks,
Tom

Re: Can't filter merged fields in SharePoint 2003

Patrick Halstead — Fri, 01 Feb 2008 04:04:52 GMT

Hi Tom,

We've been aware of this issue with collections for many years. It's one of the main reasons why we developed our Web Service Suite to help people move SharePoint data into SQL (but still use SharePoint to view it). Plainly put, SharePoint doesn't handle relational data. It's fine for one table of data, but as soon as you try to add a table within a table (i.e. a collection in a form), it falls down. This problem normally manifests as a reporting problem. In your case, you want an auto-filter like functionality. Your best solution is to move your form to SQL and use a SharePoint data view on top of it to filter the data. You could also use Excel or SQL Reporting Services and get similar functionality.

I can show you how to do this in 30 minutes. It will require installing our free download of the Web Service Suite. Let me know if you are interested.

In addition to the problem you discovered, there are many other limitations with SharePoint:

Number of properties that you can promote is limited
Performance on large form libraries (>2000 forms) is bad
Document-based permissions are not supported
Search can be difficult to configure for forms libraries
Taxonomy support for browsing documents doesn't exist
SLA based notifications not supported
etc.

We developed our Web Service suite to address these issues, and a host of other InfoPath-related limitations.

Re: Can't filter merged fields in SharePoint 2003

10basetom — Fri, 01 Feb 2008 01:54:53 GMT

Hi Clay,

I will give you a concrete example...

In my InfoPath 2003 form I have two repeating controls:

A 'Books' field that uses a bulleted list control to allow users to list books they own.
A 'Genres' drop-down list in a repeating section that allows users to select one or more genres.

I have a data connection to publish this form to SharePoint, and in the form options (Form Library Columns tab), I have the above two fields set to "Choose a function to use on repeating data" = "merge" so that these columns on SharePoint will display all of the repeating data. The data is getting merged all right, but the big problem is that I can't filter these two columns in SharePoint. I believe the issue is that merged data is of a data type that is not filterable in SharePoint.

Tom

Re: Can't filter merged fields in SharePoint 2003

Clay Fox — Thu, 31 Jan 2008 21:35:14 GMT

Hi Tom.

So you have repeating data per form you are trying to promote to SharePoint? If this is the case then yes that is not possible. All promoted data must be flat.

I have still not found a scenario where merge is the answer.

If you are trying to do analysis or something you need to have your data aggregated in a database. Merging and export to Excel is one option to get the data out but that is not real pretty either ussually.

Maybe give me the exact scenario of what you have and what you need to so and I may have an option.

Can't filter merged fields in SharePoint 2003

10basetom — Thu, 31 Jan 2008 19:49:53 GMT

Hello, I've stumbled upon a BIG issue after publishing my InfoPath 2003 form to SharePoint: repeating InfoPath fields that have been merged cannot be filtered in SharePoint! Aside from creating more hidden fields in InfoPath, does anyone know of a way to get around this issue?

Thanks,
Tom