InfoPath Dev

Does anyone know how to filter data connection in InfoPath?

My setup is done as follows:

• An ID is passed to the new item form via querystring (The form is added in the page as a web part and the connection with the ID from the querystring so that the ID can be passed to the form is happening quite well);
• I have added rules that check if there is an ID that is passed via the querystring and if there is I run the action "Query Data" which basically just call the data connection;
• From there I have another rule which populates the fields I need to populate using the ID from query string as a filter;

At the moment this works well when I don't have too much data (over 5000 list items) but then it fails when I have too much data.

I just wish there was a way of filtering the data connection or of querying it using the ID passed to it instead of just querying the whole list.

Any takers out there?

By the way you guys rock, I have been using many advices that I find on this site, its's only today where I haven't really find the answer I am looking for.

Thank you

Regards,

BhutiM

Hi BhutiM,

Sounds like you're almost there.  If queryFields are supported in your secondary data connection, you can set the ID with the value you've retrieved from the query string before you query.  This should only return the values that match your criteria.

In the data source pane, there is a drop down up top which should say Main.  Select the secondary connection to the list you are querying and then expand the queryFields until you find the ID field you are looking for.

Hopefully this works for you!

Hi Anson,

Thank you for your reply and it really makes sense but I have one more question for you.

When you spoke of data source pane I take it you mean in InfoPath under the list of the fields click "Show Advanced View"?

After selecting the secondary connection do you do the filter in the query fields or using the data fields?

What I have done is that in the Query Field ID, I have added a rule which check if the query string exist and if it does then set the Data Field ID to be equals to the Querystring ID;

My problem with this is that I still get the same problem. As soon as I have more data I can't even open the form to edit the item or add a new one. Do you think I should put the filter on Main data connection as well?

My rule checks if the querystring exists, what happens where there is no querystring? Does it just goes and get all the data?

Thank you

 Regards

BhutiM

Hi Anson,

Thank you again for your help it really guided me until I was able to resolve the issue.

Your solution worked 100% the only problem was that I still had the option that says "Automatically retrieve data when form is opened" selected which was causing the query to run before the form is loaded.

After disabling that option my form was not slow anymore even if I had over 5000 records.

What I have done though is that I have a rule that check of the QueryString has a value and if it does, then I Query the data which then I use the secondary data connection to populate my fields.

This has been a very good challenge and thanks for your help that I have been able to resolve it.

Regards

BhutiM

Hi Anson and Everyone in this forum,

I thought my problem was gone but it is back. For some reason once many people have used my form it starts taking long to open and it times out even after applying all the changes we spoke about. What I would like to know is that how does InfoPath clear the queried data? Does it do it automatically or does it store it somewhere?

 The reason I am asking is because I even had to re-do the form and then it works for couple of times and then it start having the problem. Just to recap:

1. I am passing an ID via the URL;
2. I use the QueryString filter to pass this ID to my InfoPath Form and store the value in a field;
3. I have a secondary data connection in my infoPath which has a filter in the query fields which sets the ID of the item to the same ID as the one passed via querystring;
4. I have a field that stores the QueryString ID and I have a rule attached to this field to say if it is not blank then "Query Data Secondary Data Connection";
5. After querying, then set my fields to the results of the Secondary Data Connection;

This has worked but couple of times using it, it started to be slow and it eventually times out that the form does not come out.

I even went as far as creating a separate view in my infoPath for a form that is not the same as the form used for Adding and updating the item.

To me it looks like it loses the filter after a while and return all the records instead.

 Please help.

Thank you

Regards

 Bhuti

I also just wanted to add that when I remove the rule for Querying the data the form loads successfully and I can see the correct passed QueryString ID but when I add the rule to run the secondary data it brings the site to a halt.

I am now thinking of writing a visual webpart page and have the form there in asp.net and that will be much faster I am sure if there is no way in InfoPath I can achieve this.

I just feel that maybe I am missing something in InfoPath, this should work but it is not.

Thanks

BhutiM

I am still not certain what you want to do here, or what you implemented, but I will try to help as best I can.

What I assume is that you want to display items from a sharepoint list that are filtered by a given ID as parameter in the query string and you want to display these items in an infopath form.

First let me say this is probably not the best use of InfoPath, InfoPath's power really comes into play when you use it for the role of forms, not necessarily the best tool for the query of data, reports etc... It does the job but there are other tools better suited when it comes to quering and filtering data in sharepoint.

So I suggest just for a simple filtering on a page from a querystring field. Create a view for your list that has the fields and look you want. Then on the web page you want to display the data you do the following:

1. add the list view webpart for your list
2. edit the list view webpart and select your view you just created apply and close the editor pane
3. add a querystring filter webpart
4. edit the query string web part to use a parameter name that is not ID ID is a key word avoid using it
5. give it a default value of something you know will not bring back any results eg 0 apply and close the editor pane
6. then in the list view web part , from the web part drop downmenu (at the top right corner of the web part) select connections > Get Filter Values From > (your filter web part just added)
7. then map your provider field name to the correct Consumer Field Name ie ID
8. save page and you're done

Now if you absolutely have to use InfoPath then the way I would do it in InfoPath is this:

1. create your form 
2. add your secondary data source (which I am guessing is a SharePoint list)
3. DO NOT check the checkbox for Store a copy of the data in the form template
4. I would also not check the checkbox to automatically retrieve data when the form is opened but that's not required
5. add those fields to your view ie map your fields in the repeater control to the correct fields in the added data source
6. In the Main datasource add a new field for filtering make sure the type is correct
7. Then add a new Action rule to that field you just added
8. Make sure the condition is None - Rule runs when field changes
9. Add to the rule the action to set a field value
1. The field to select is the field you want to filter on under queryFields in the secondary datasource
2. Set it's value to be a "." ie the current field you are adding the rule to, (your main datasource field)
10. Now add another action to query for data from your recently added connection (secondary datasource)
11. Save and publish to your sharepoint server make sure to
1. Enable this form to be filled out by using a browser is checked
2. Add your new field in the Main datasource as a field that will be available as a SharePoint Web Part connection parameter.

Now edit your sharepoint web part page that you want to use to display the form be sure to:

1. Add the infopath form web part to the page
2. edit the web part to select your recently published infopath document apply and close the editor pane.
3. add a querystring filter webpart
4. edit the query string web part to use a parameter name that is not ID ID is a key word avoid using it
5. give it a default value of something you know will not bring back any results eg 0 apply and close the editor pane
6. now in the infopath form web part , from the web part drop downmenu (at the top right corner of the web part) select connections > Get Data From > (your filter web part just added)
7. then map your provider field name to the correct Consumer Field Name ie the field added in the infopath form
8. save page and you're done

 Now you should be able to filter via the query string and not get back values when there is no query string parameter.

Hi Warrenn,

Thank you very much for your reply. I will try it out and will let you know if it works.

Regards

BhutiM

 Hi,

Can you please tell the steps how you have passed query string parameter  in infopath form as I am new to Infopath.

 Thanks in advance!