InfoPath Dev

I have an existing form where the users enter the account number and name into two text boxes. They want to enter the account name in a text box

and have Infopath lookup the account name. I have the query built (in access) that returns this info,  but how would I implement this in the existing form.

What would I have to change. I know I would add a data connection to the access database that houses the account name query.....

Any help would be great. Thanks

Hi Ed.

If you have the data connection set up. I would put a rule on the field where they enter the account number. The rule would set the name field in the view witht the name from the data connection. For the value you would select the name field from the DC but then add the filter, which is in the lower left when you select the field.  The filter woul be account number in the form = account number in the secondary DC.

 Should be an easy add for your form.

Let me know if you need anything else.

Hi

Thanks for your response. Is there an example of this in a book or somewhere. Can you recommend a book that has examples.

Thanks

Here is an example on how to set up a filter.

http://www.infopathdev.com/search/SearchResults.aspx?q=populate&g=6

Hi Clay...

Thanks for your help. I am trying your suggestion regarding the rule. However its not returning the value.

I enter the account number in the account number field but nothing populates in the name field after I press enter, DO I NEED A QUERY BUTTON.

I set up the rule exactly as you wrote previously.....

 I put the rule on the account number field. I set the name field from the view (txtAccountNum) with the name from the data connection (ACCOUNT_NME)

For the filter, I set the account number from the form (txtAccountNum) = to the account number of the data connection (ACCOUNT_ID).

It all seems to make sense, but its like its not running. Do I need to set any thing else where. Thanks

Typically in the learning / testing phase I would either make a new form and replicate this or create another view in your copy of the original to do some testing.

First I would drag your secondary data connection onto the view so you can verify exactly what is being loaded.

The data connection should have the defualt to load automatically so you should not need to query it again. Unless it changes while the form is open it should just load witht he form, that way you do not have to query it specifically later. But you should verify it is working and it has data to compare against.

Then you should be able to validate your logic, which look correct to me.  One thing to be careful of is whether there are any extra spaces or anything in your data. The matching is simplistic and sometimes depending on the data it may look like a number but have leading or trailing space that need to be handled witht he normalize function or added to your source.  SO verify you are getting a data match hwen you should be.

Clay,

One thing I neglected to mention is the underlying query is a secondary data source. I noticed in the help system on the filter that  it says ...

Note  You can only filter data from a primary data source. Because you cannot filter data from a secondary data source, the Display tab for a control connected to a secondary data source does not include any filter options.

Am I dead in the water or is there another way to accomplish this, thanks for all your help 

Hi Ed,

Well, you could copy the data into the main data source, or just write some code to filter it. The default filtering may not support 2DS (secondary data sources), but that shouldn't be a "dead in the water" event. Are you pulling your 2DS from a database? If it's SQL, why not just install our Database Accelerator and get the filtering for free? If it's Access, I suppose you will have to write some code. If you would like, we can do this for you. Let me know.

 Best of luck!

Hi Patrick,

I'm pretty new to Infopath...how do you copy the data into the main data source? And if I had to write code, what language? I am pulling my secondary data from an Access query.

Where can I find examples of the type of code I would need to write..

Thanks Ed

To All...

Hopefully you can clear up some confusion regarding designing a new Infopath form with a database connection.

It seems if you don't include all your data fields in the beginning, you are out of luck if you need to add something at a later time. (IE secondary data connections). In my case, someone built the form (with no data connection), They gave me the form to build the look ups for accounts etc... It looks like I can't do this in a secondary connection. Do I need like 1 connection containing all of my data lookups set up before the form is built so I wont have to add something later. I thought it would be more straight forward to do this...Now it seems I have to write code (what language), buy a third party product....just to do lookups.....I cant find examples of this in any book.

Yah your predicament is not uncommon.

To use the default database functionality in InfoPath, to query and submit you have to design the form from scratch around the data connection. There still are limitations , such as only one table structure can be used, that may or may not be a problem down the road.

So one option is to start from the beginning and configure this and then replicate your existing form in this new form.

I had recommended the Database Accelerator before since with Web Services you are not bound by the default database rules and have the freedom to have multiple queries and submits along with some other advantages.  It is additional middleware, but the tools provided, make developing enterprise solutions much faster and easier.

With DBXL you could convert your existing form over pretty easy.

You can create your own webservices or write code to possibly do some things. You may want to pay for some support help to get you past this hurdle and gain some knowledge.

It is extra hard when you inherit a form rather than architecting it from the beginning.