Link to home
Start Free TrialLog in
Avatar of E_Gordon
E_Gordon

asked on

Infopath 2010 Wildcard Search

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...)
Avatar of Clay Fox
Clay Fox
Flag of United States of America image

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.

Avatar of E_Gordon
E_Gordon

ASKER

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...

Thanks.
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.
Just put the returned data into a repeating node.

Then use a repeating table to display the search results.
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)
  CREATE NEW NODE
  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?

Thanks.
ASKER CERTIFIED SOLUTION
Avatar of Clay Fox
Clay Fox
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
The solution pointed me in the right direction to accomplish what it was that I was trying to do.