We’re replacing one of our critical systems, and one of the system analysts was looking for a way to capture key data entities in the existing system, and every system/form/report that used each entity. Someone suggested SharePoint and I got myself roped into prototyping a solution.
Because of the many-to-one relationship being captured (e.g. one entity may map to fields in multiple systems), a straight out SharePoint list didn’t make sense. I have yet to see a great way to do parent/child relationships in SharePoint lists. So, I proposed an InfoPath form.
I started by building up SharePoint lists of reference data. For instance, I have one list with all the various impacted systems, another with the screens for a given system (using a lookup field to the first list), and another with tabs that are present on a given screen (with a lookup field to the second list). In my InfoPath form, I’d like to pick a system, auto-populate a list of screens in that system, and if you pick a screen, show all the tabs.
Using the InfoPath rich client, one can utilize the “filter” feature and create cascading drown downs by filtering the data source results based on a previously selected value. However for InfoPath Form Services enabled forms, you see this instead:
Son of a! The suggestions I found to get around this included either (a) write custom code to filter the result set, or (b) use a web service. I know that InfoPath Form Services is a limited version of the rich client, but I hate that the response to every missing feature is “write a web service.” However, that’s still a better option than putting code in the form because I don’t want to deal with “administrator approved” forms in my environment.
So, I wrote a freakin’ web service. I have operations that take in a value (e.g. system), and uses the out-of-the-box SharePoint web services to return the results I want. The code looks like this …
Notice that I’m using the GetListItems method on the SharePoint WSDL. I pass in a CAML statement to filter the results returned from my “system screens” SharePoint list. Since I don’t like to complain about EVERYTHING, it is pretty cool that even though my operation returns a generic XMLDocument, InfoPath was smart enough to figure out the return schema when I added a data connection to the service.
What next? Well, I have a drop down list bound to this web service data connection, but chose to NOT retrieve the information when the form opened. It’s data is conditional based on which system was selected, so calling this web service is dependant on choosing a system. So, on my “systems” drop down list, I have a rule that fires if the user actually selected a system. The rule action first sets the input parameter of the web service schema to the value in the “systems” drop down list. Next, it performs the “Query Using A Data Connection” function to call the custom web service.
So what do I have? I’ve got a nice form that gets all its data from external SharePoint lists, and cascades its drop downs like a mad man.
Of course after I deployed this, I was asked about reporting/filtering on this data. The tricky thing is, the list of system mappings is obviously a repeating field. So when publishing this form to SharePoint, and asked to promote columns, I have to choose whether to pick the first, last, count or merge of system fields.
I chose merge, because I want the data surfaced on a column. However, the column type that gets created in the SharePoint list is a “multiple lines of text”, which cannot be sorted or filtered.
So how to see a filtered view of this data? What if the business person wants to see all entities that touch system “X”? I considered about 72 different options (views, custom columns updated by WF on the list, connected web parts, data sheet view, etc) before deciding to build a new InfoPath form and new web service that could give me the filtered results. My web service takes in all possible filter criteria (system name, system screen, system tab) and based on which values came into the operation, builds up the appropriate CAML statement. Then, in my new form, I have all the search criteria in drop down lists (reusing my custom web service from above to cascade them), and puts the query results in a repeating table. One table column is a hyperlink that takes the user to the InfoPath form containing the chosen entity. Had to figure out that the hyperlink control’s data source had be specially formatted so that I could have a dynamic link:
This takes my static URL, and appends the InfoPath XML file name. Now I have another form that can be opened up and used to query and investigate the data entities.
That was a fun exercise. I’m sure there’s probably a better way to do some of the things I did, so if you have suggestions, let me know. I do really like InfoPath Form Services, but once you really start trying to meet very specific requirements, you have to start getting creative to work around the limitations.