InfoPath Dev

Hello, i've been exploring the use of Infopath and sharepoint server 2007 as a tool for the company i work for.

I have managed to make real nice Infopath forms with little effort. And they work nicely. But here is the catch, atm its not as dynamic as i would like it to be. I have to manually update all the sharepoint lists i am using to get the infopath forms to work.

What i would love to do is:

Import a Excel spreadsheet as a datasource, problem is it has 18000 rows... The spreadsheet is simple enough, 6 coloums with 18000 rows. First i want to convert this so i can use the informasjon dynamicly in the infopath forms. Meaning the users can put in an ID number, and rest of the information connected to that ID number in the spreadsheet will be filled out automaticly in the infopath form.

This i have tried, and i got it to work, but here's the catch. I want it to be "self learning". Lets say a user fills out the form, and finds out that the information in the form isnt correct, so he edits the information, and sends the form. I want the correction to be updated with the spreadsheet/sharepoint list as he sends the form. Can this be done?

If so, i could set up multible connections and datasources with this feature, and my life as a sharepoint/infopath developer would be much much easyer.

This forum has helped me alot, and i hope some of you guys can come with some tips and idea's on how to implement this into my forms.

You have the right idea, and it is a common scenario, making your life easier and giving the users more capabilities.  The trick is your current architecture.  Your concept is right but what has been designed to handle this is a database, not a spreadsheet. 

If you are going to create enterprise forms, then you need the architecture and the developemnt tools to accomplish that and it is not hard or expensive to do just takes setting it up.

First you need SQL or SQL express, which is free.  This is probably already on your SharePoint server.  You can import your data into a table with a wizard and have your 18000 rows ready to go in a  matter of minutes.

Then I would recommend downloading Database Accelerator Web Services Suite. Qdabra Database Accelerator Suite Version 2.0 - Developer

This can install right on your SharePoint server and there are web parts available for it to integrate with SharePoint.

This way you have the capability of securing your form, identifying your users with the included Active Directory service, and can submiting your data back into the tables when the user needs to make a change.  It is the ultimate environment for creating rich database solutions and removes the issues of standard database forms in InfoPath and also performance issues with SharePoint once you get a lot of forms.

Its a free download for the developer version and there are quite a bit of support documents and of course the forum here.

I have been in your shoes and I would not go back with out DBXL.

Yeah, i know, the spreadsheet is just a file, i would actually like to convert into a database. I was thinking of using the datasource libraries in sharepoint to implement this. But i se where you are going.

I have tried importing the excel file into infopath, then load the xml file into a datasource in sharepoint, but as it is 18000 rows, the infopath wouldnt import it. Found out later that there are restrictions into how many rows you can actually import with infopath :P 999 is the limit actually.

So my scenario futher, getting that spreadsheet into a database wich i can use into a sharepoint datasource library? If i could do that, further development would be easy enough.

Same scenario with all the sharepoint lists i have set up, i think they can be used as sharepoint datasources as well, but i have yet to figure out how.

 So basicly, getting those two scenarios to work, i think my work is basicly done, from there its downhill i would guess.

To add to this post: I dont think i have access to the sharepoint server, unless i actually go and get it, so what i need is the databaseconnection, and username/password?

Ok, I actually managed to import those 18000 rows directly in a sharepoint list library.

Now my problem is that when i try to use dataconnection towards that list, infopath lists up only 100 of those rows....

I might be in the wrong direction here...

I have not worked with database features of SP2007 yet soI am not sure what all can be done with them but I am guessing their are some restrictions.. else I would have heard more about it.

There are a couple options.  When you query a list item it uses the default view, which by default, is limited to 100 items. You can change this in Modify Setting although if users use this list in SharePoint that may be an issue. http://www.infopathdev.com/blogs/patrick/archive/2004/07/05/SharePoint-List-Adapter-Only-Returns-100-Items.aspx

Otherwsie you can get tricky and use the SharePoint webservices to get at other views. http://www.infopathdev.com/blogs/agni/archive/2006/05/15/Query-for-Items-in-SharePoint-Form-Library-Using-GetListItems-Web-Method.aspx

Got it working now. With great thanks to this thread actually.

What i did, i just added the list item as a data connection, and set the limit to 19000 rows. It went ok, untill i published the form, and it was sloooow. so i thought it was something about using the sharepoint lists that had so many rows, so i imported the spreadsheet to our microsoft sql server. (The same boks that runs the sharepoint server). And it was as slow as the datalist in sharepoint was.

Takes about 10 second to load the form, and now when my users input the Costumer Id number, the forms takes about 6 seconds to update itself with the information according to the customer number in the database. Same i wanted the database to automaticly be updated if the users put in data missing, or edit the data in the form, to update the database, but this feature took like 4min to perform.

Been reading up on these forums about performance, and i gotta say this is a setback. I was impressed how easy and simple it was to set up forms in infopath. Then it has a performance issue regarding huge databases. I was planning to use infopath + sharepoint to set up huge workflows towards very big databases if this test was successfull.

Paging the database as written in these forums wouldnt help me either, cause i want the form to automaticly update itself when the user input the costumer id number.

Any other suggestions to how I might speed things up here? Any settings in the database server that can be done? Or some settings in the infopath forms, or sharepoint integration?

I would love for this to go abit faster, even half the time would help alot, I really want this as a complete solution for the company i work for.

How does this qdabra database accelerator work? Will it sqeeze some extra speed out of infopath + sharepoint in this matter?

It is good to hear your making progress.

The form opening time is probably hard to escape, as it is the overhead of InfoPath and opening the application.  The performance once open though should be a different matter. Obviously performance has lots of factors but I think what you describe can be improved.

Database Accelerator was created primarily to meet the needs of organizations where because of performance or limitations with SharePoint was causing issues.  I was just working with a customer who has 30K forms and a database with several tables in excess of 50K records and the views within InfoPath move normally.

Web services are going to give you better performance, it may also give you the ability to architect the form more efficiently. Because Database Accelerator is used in enterprise situations, where InfoPath is being highly utilized, a lot of the development has been centered around performance and being as effecient and tuned as possible. We also have a catalog style form which will be released soon that gives you the ability to open InfoPath once and then navigate to all of your forms inside InfoPath. Thus eliminating the open and close performance hit and providing workflow and assignments, etc.

 You have all the infrastructure you need. Install Database Accelerator on your server, go through a couple tutorials to get acquainted or send me an email, and you can take it for a spin.

Documentation is located in the file section under product support.  

I think the big key is if you want to create a complete solution for your company, we have found you need web services and your forms databased, and we have put all of the web services and tables into one package. It also makes the development much quicker since you do not have to build your own tools and features.

Let me know if you need anything.