infopath form vlookup function based on sharepoint list BUT based on texboxes only (no dropdown)

Question

I am fairly new to InfoPath and trying to create a form now that will link to the SharePoint list. Where I am stuck is that a lot of the steps online show you how to vlookup two fields (where one is a dropdown and another text box)But I need 2 fields to be a textbox field and connect them based on the sharepoint...so one text box field will automatically fill out info based on another Text box field..

So I have 2 infoPath fields:

a)Consumable Part number -> as a textbox - here user can manually enter the partnumber (I cant use dropdown here because we have circa 600 entries here!). All entries are stored in the sharepoint list in the Consumable Part Number column

b)Consumable Description -> as a textbox - here the field fills in automatically as soon as user enters the correct partnumber that indeed is available on this sharepoint list and each partnumber is linked to its unique Consumable Description. All entries are stored in the sharepoint list in the Consumable Part Number column

Is there anyway I could work around this please?

Thanks so much!

Answer 1

Add a data connection to the SharePoint List. Set the data connection to not run on form open - that is a checkbox in the last dialog box when you create the data connection.

On the text box where the user can enter a part number, add the following rules.

1. Rule condition - field bound to the text box for the user entered part number is not blank
2. Set a field's value - select the Part Number Query field from your SharePoint List data connection (be sure you pick the part number field in the "queryFields" folder)
3. Query for data - run the query to your SharePoint list
4. Set a field's value - set the field you want to have the description in to the returned data field Consumable Description in your SharePoint list data connection

Since you are querying based on the user entered part number, you should get back 0 or 1 item(s). You can add other rules to handle for the user entered number not being found (count of items returned in the SharePoint List data connection equals 0, you could display an error on the form, for example)

Edited to elaborate in light of additional comments: The above technique sets a query field and returns items that match only that query field. If you have additional fields that dictate which list data should be returned, you'd set additional query fields.

If, instead, you need all items returned from the list and then you need to use XPath filters to determine which item you need from the list, you'd use the filter data button when picking the field that has the value you wish to use in your set value rule - from there you can add the various filters you need.

I'm afraid that I don't understand what you are attempting to achieve based on your comments.