InfoPath Dev

I am attempting to create a simple from that will use a dataconnection to a large (200,000 record) MsSQL table for a product pick list.

If I follow the data connection wizard I get a from with the query field at the top and a run query button with results area.

This works fine however I can only call one record and store into my table. The Data source shows query fields and datafields.

I cannot seem to be able to bind a field to the query field. If I attempt to create a dataconnection manually I only see once set of fields and a query seems to take for ever!

How can I bind a drop down control to the datafield so I can get the speed this appear to provide.

Hey there.

On the first "database" type form you created I would drag all the query controls onto the form and then drag your datafields on as a repeating table and do some experimentation in preview mode.

Once the fields are on your form you can right click and convert them to dropdowns and give them some values to choose from.  This works for query fields too but remember the query fields are used to put in your query values the data fields display the results. 

So if you wanted to see the products that were widgets you would put widgets in the product type field in the query section and run the query and then data fields would display any records where product type equals widgets, as an example.

 

If you create a secondary connection to your table then it only queries all records you cannot put in any parameters and you cannot submit back to the table.  So it takes a long time because it is accessing and loading all 200K records into your form.  This is one of the limitations of InfoPath when working with SQL directly that I mentioned before. With web services you can have as many secondary table queries as you want and can submit as well.  Again if you are working with SQL you really need to look at the Database Accelerator package of web services.

Thanks for the quick reply however I cannot get this to work.

I can enter a value into the product query fiels, click on Run query and the record is returned into the datafields in the repeating rable. However I cannot perform another query into any subsiquent rows in the datafields, a new query just overwrites the first row in the datafields? I must be doing somthing wrong here, but cant spot what this is?

Taffman:

Thanks for the quick reply however I cannot get this to work.

I can enter a value into the product query fiels, click on Run query and the record is returned into the datafields in the repeating rable. However I cannot perform another query into any subsiquent rows in the datafields, a new query just overwrites the first row in the datafields? I must be doing somthing wrong here, but cant spot what this is?

After some experimentation I have discovered that I can perform a query on my SQL tables using the query fields and button. The result is returned to the datafields. What I now need to do is add a button that will allow the returned result to be copied into a repeating table so that a list of parts or bill of materials can be built based on the records retrieved from the SQL database, anyone have any ideas on how this can be done?

Glad you are making progress.

On the Queries you need to seed all of the query fields at once. There is no way to do multiple, sequential queries which i think you figured out.

Are you sure you need to keep the returned rows in two places?  What exactly do you want to do in your form?

If you want to have a drop down list or display them you can do this with the datafields. I would assume the user would select which of the returned rows to keep or use.  Sometimes I have added a selection field for this but you could have a button which copies a row from one node to another.

Are you wanting to database the parts list or bill of materials?  If that is the case you are going to run into the InfoPath limit of one submit data connection, which you have already used for your parts.  You may want to look at using web services and the database accelerator to make this solution easier for you to create and and more powerful for your users.

Qdabra Database Accelerator Suite Version 2.0 - Developer

In my form I want to pick records from a very large SQL table (circa 170,000 records) to build a list of ordered items.

I've tried converting a queryfield to a drop down in the repeating table, but the query takes for ever for the reasons you have already identified elsewhere in this thread.

I think that a button which copies a row from one node to another is what I need unles you can think of a better way to do this?

If the query is static per form or only a couple different small ones you can edit secondary data connection's sql statement.  When editing the dataconnection there is a edit SQL button on the right. So possibly you could create the query in SQL and have it only return those rows but this would be the same for all form users. If you want users to enter some informaiton and perform a dynamic query then you either have to use the main dataconnection or use webservices.

You can set your dropdown to lookup up data within the form's data source. Should be the middle option.  Then you can point at your data fields from your main connection if that is all you need.

Otherwise I would recomend using a web service since you have such large data tables. This will improve both performance and function.

Clay Fox:

 

You can set your dropdown to lookup up data within the form's data source. Should be the middle option. 

You should be browsing in your main datasource to DataFields and selecting either that or a repeating group underneath it. Then you would need to set the value and display to the same or different fields within that repeating group.  Think of it like your repeating table and set the dropdown to the table and then select which column to have as the display and then the value, this may be the same. Keep experimenting until you understand what everything does.

Many thanks for all your help on this, Im nearly there with this now.

I have managed to set up a query that returns the search result to the DataFields group.

In my Bill of Materials repeating table I have a combo box that looks up the record in the DataField group and populates the combo box field with the query result value.

This is great by a little awkward for the user. what i really need is a little code to attached to a button that will do this at the click of a button, much like an "Add to Cart" function found on many shopping Card websites. If you or anyone reading this knows of any codes snippets that does this I'd be very grateful for the info.

Many thanks again.