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.
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.


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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Is there a SharePoint setup guide for beginners? 18 62
Sharepoint 2010 Audit Logs 11 125
VS2015 Redefinition errors 4 72
How can my static class become undefined?? 8 65
For SharePoint sites, particularly public-facing ones, there are times when adding JavaScript, Meta Tags, CSS Styles or other content to the page <head> section is more practical than modifying master pages.  For instance, you could add the jQuery l…
The vision: A MegaMenu for a SharePoint portal home page The mission: Make it easy to maintain. Allow rich content and sub headers as well as standard links. Factor in frequent changes without involving developers or a lengthy Dev/Test/Prod rel…
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.…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit If you want to manage em…

830 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