Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Infopath 2010 Wildcard Search

Posted on 2010-08-20
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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.
Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.


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 1500 total points
ID: 33535346
I do not know what code to use. You might find some examples on www.infopathdev.com.

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Note:  There are two main ways to deploy InfoPath forms:  Server-side and directly through the SharePoint site.  Deploying a server-side InfoPath form means the form is approved by the Administrator, thus allowing greater functionality in the form. …
We had a requirement to extract data from a SharePoint 2010 Customer List into a CSV file and then place the CSV file into a directory on the network so that the file could be consumed by an AS400 system. I will share in Part 1 how to Extract the Da…
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. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

719 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