InfoPath Dev

Hi, I have created a form based on a webservice.

When the form is submitted the webservice populates a SQL database with the information entered in the form. Currently, we have also set it up to save the form to a Sharepoint library, so when a form is reopened it does a query on the database and retrieves it's data from the database thus ensuring that the information in the form is up to date (we have a 3rd party application that can edit the form as a sort of back office program), however we have other plans for this.

Our main aim is to create a list which references the database and populates a "dashboard" based on outstanding work to be done on the form with various columns linked to the form to show the user the status of the form. I would also like the user to be able to click on a link (or just the row) and for the form to open up for editing.

So far I have created a view in SQL and connected it using an External Content Type. However, this only allows me to produce a list and clicking on it, only opens an edit form to edit the details shown on screen.

Am I going about this the wrong way? Would a Library and workflows (something I've not worked with much so far) be better for this?

Any advice of this would be gratefully accepted.

Thanks

Hi GreeNaWayr,

Some random thoughts ...

InfoPath is great because it validates XML data and reduces the need to validate data going to SQL via a Web Service.

However, you lose that benefit if you modify the data on the backend SQL database using some third-party app and then rehydrate the XML. The XML data types lose information and they can be invalid. SharePoint has a "demotion" command that allows this but it is turned off by default and it's just a bad idea since it can wreck the validity of the forms.

Using a Web Service to submit to SQL is a best practice. Good job there. Using a data-driven Web service that maps XML to SQL based on a data driven mapping is even better since you can use one Web service for all of your form templates.

ShP2010 has BCS support to show SQL data in a list. You can add XML links to the BDC data view web part (Jo-Anne's recent blog has an example of this).

Bottom line: having multiple writers is bad because you lose the XML validation feature of InfoPath. If you can roundtrip to InfoPath, that's good. Libraries and workflows are not required.

Hi Patrick,

It's a neccessity to have a third party app (developed in-house) in this case as it goes beyond editing the form and performs a whole host of business logic tests which are not required at the form stage, I won't go into too much detail, sufficed to say, we were forced into this position.

Our webservice basically takes the validation from the SQL server or field types and NotNull, and then builds a Dataset based on these rules. We apply some extra validation where required in the form, and that validation is replicated in the third party app. The form is being filled out by people who are "less tech-savvy" to be kind, so we've kept it simple. The Third party app is for managing the data and is done by compliance teams who are a lot more "strict" on the details passed. The aim is, in the long run, to change the culture of our form users from just filling in nonsense to bypass the validation in the form (some will do this cos they're lazy), the compliance team get an incorrectly filled in form (i.e. it doesn't match the business logic requirements) flagged up to them by the 3rd party app and can look over the form and decide whether the form should be  rejected back to it's creator to fix, or whether it's simply a small issue they can fix. The main aim is to get the majority of forms with errors fixed by the creators, so that they start to understand that if they don't fill them in correctly first time, they'll have to fix them themselves. Our form creators aren't paid by us so it's not like we can sack them for being useless.

What we're trying to do with this list is create a list of forms in a users library that have been filled in incorrectly. Being simple folk, we've made the /myItems.aspx page the home page when they navigate to the sharepoint site. We've added columns to the library which contain the reference of the form, and textual status, which is effectively a workflow, telling the users at what point their form is, once it reaches the final status "Finished" with a statusID of "10", the library simply filters out the form. I think this will work for us for now.