Infopath 2010 Wildcard Search

Posted on 2010-08-20
Last Modified: 2012-05-10
I am trying to develop an InfoPath 2010 form for Purchase requisitons and have a requirement that I'm having some trouble with...
We would like to do a wildcard search on Vendor name and number.  I have a vendor list in SQL and created a concatenated column that contains both fields (for easier searching).  
What I would like to happen is have a text box in the form that the user would enter the partial text string into (say %mel%, or 429%) and a button, that when pressed, would open a new window with the results (like the people picker does) or even a new view (probably easier).  They would then pick the correct Vendor name/number combination from the list and it would populate the fields in the main form.  The fileds in the SQL table are standard vendor stuff (Name, phone, fax, email).  I'm looking for a way, primarily, to display the result set...VB is the code type for the template, as this almost certainly looks like a programatic solution will be required.  I saw a code snippet in a post on here for a similar issue with InfoPath 2003, but it appears the script editor is not availble in 2010, and that post does not explain how the result set gets displayed and used (that I could see/undertand...)
Question by:E_Gordon
  • 4
  • 3
LVL 28

Expert Comment

ID: 33491476
It has to be code since InfoPath only allows queries with an equals to do a true wildcard search. However there are a lot of ways to simulate a wildcard search to the user.  
How many vendor records are there?Is your data set very large?  

Sometimes you can create a secondary query which maybe is just vendor name and number.  You query that and use a filter to then show the user the names and numbers which match their wildcard, probably a contains function. Once they select one the form requeries the full data set for that particular vendor.  

Another way at this is to have cascading dropdowns to narrow down to the selection. Say select Vendor Type, then Names of that type, you can use the first letter in the name to filter.

Alot of it depends on the number of records and performance.


Author Comment

ID: 33491757
The dataset is 'relatively' small; ~5000 records...I don't think cascading dropdowns would work because we don't really have any 'sub-criteria'; just name and number.  The issue (and why we need a search) is that the rank-and-file filling out the form would typically have no idea what the vendor number is, and there are similar vendor names.  Dell, for example, may show as "Dell", Dell Inc.", and "Dell Computer Services", each with it's own number.  The user would enter %Dell% and be presented with those three options (Name and number would be displayed) and pick the correct one. The form would then populate the phone/fax/email based on their selection...
I've been looking around and think I may have found a way to generate and submit the search string, but I get lost in returning and displaying the data...Do I just feed the dataset rows into a list box on a separate view? And never having used list boxes (relatively new to InfoPath), I don't know how 'grab' the item that the user would select...Presumably, once I have that, it is just a matter of assigning the text box values for phone/fax/email using inherent InfoPath filters in the 'value' property of the field itself...

LVL 28

Expert Comment

ID: 33500239
Just put the returned data into a repeating node.

Then use a repeating table to display the search results.

Add a button to the repeating table rows.

WHen they select it, it copies the ID or Vendor number to a field in your main data source called selection or whatever. Once you have this captured you can use that to do a query of all the information for that vendor or whatever.

Usually you want some field to store the information that the user selects or is working with to pass it between stages of your form.
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails


Author Comment

ID: 33534636
Just put the returned data into a repeating node.

Then use a repeating table to display the search results.

Author Comment

ID: 33534717
sorry...poor editor skills...was trying to get this result:

    "Just put the returned data into a repeating node"
    "Then use a repeating table to display the search results."

This is what I do not know how to do programatically...can you give me some sample lines?  I'm assuming that it woudl be something of the format:

WHILE/ FOR EACH (values in the dataset)
  WRITE (value to the new repeating node)
NEXT (value in the dataset)

Of course that 'English' translation has nothing to do with actual code (and may not even be correct)...but that's my issue...I'm simply not familiar enough with VB/C# to know what methods to use...
(Caution, scope creep ahead) I've also seen, while researching this, to be sure to remove all the values when doen, otherwise the repeating table will just grow each time...makes sense, but how is it actually accomplished, in practice?

LVL 28

Accepted Solution

clayfox earned 500 total points
ID: 33535346
I do not know what code to use. You might find some examples on

That was why I gave you options to aviod the wildcard. :)

Your form should be reopened each time. Do not save your xml. Just start a fresh form and they can run the queries. That way their should not be a concern of latent data.

Author Closing Comment

ID: 33663648
The solution pointed me in the right direction to accomplish what it was that I was trying to do.

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

If you create your solutions on SharePoint sooner or later you will come upon a request to set  permissions of the item depending on some of the item's meta-data - the author, people assigned as approvers, divisions, categories etc. The most natu…
A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA.…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now