Query a secondary source in InfoPath 2010 form

Question

I am developing a web-enabled form in which the user has to answer questions. For example, there is a Yes/No question which when "Yes" is selected reveals a section with more questions. One of these answers must be a name selected from a SharePoint list of > 20,000 names. What my customer wants is for the user to be able to type in part of the name and have the form return matching values. Then the user would select the correct name and that value would be passed to the corresponding answer field in the form.

As I am not a developer, I have tried to do this by creating a field for the user to type in the search term for the name. Then below that, I have a repeating section where matching names appear, using formatting values. I have indexed the field in the secondary source to increase the search speed. So the user types in the search term, clicks the "Search" button and the query runs, showing only names "containing" the search term and hiding the rest through formatting rules. the user then clicks a "Select" button next to the desired name and that is passed to the answer field. This works as a separate form (I just pass the selected value to a text field for testing). Not the most efficient way, I know, but the only way I have found that I can do with my limited skills.

My problem is that, when I try to add these same fields and query action rules to the rest of the form, where the user answers other questions and then searches for the client, the repeating section does not appear. It is supposed to appear in a table cell to the right of the other fields. The search term field is there but not the repeating section. I have tried to attach a "query the secondary source "Search" button" next to the search term text field but nothing happens when it is clicked. The only way I can get the secondary data to load is to load it when the form opens (I did this just to check and it is there) but that is not desired and will still not filter even when the "Search" button is clicked.

Does anyone have a suggestion on how I can add this functionality to the complete form? BTW, this same name search has to happen for 7 other fields on the form.

Thanks in advance.

Answer 1

 

Hi SharePoint4You,

We can also use drop down list control, it also allow select value by typing a part of string. But need to enter a string from the begining. You can test it.

And there is query for data action allow us to query data source when needed. You can design your form like this:

Search 1, Search button1,  Repeating table 1.

Search 2, Search button2, Repeating table 2.

....

And rule to each Search button:

Search button 1:
1) Action rule: Query data source1.
2) Formatting rule: If repeating table1 field not contains Search 1, hide it.
Similar for Search button 2 and the other search parts:
1) Action rule: Query data source2.
2) Formatting rule: If repeating table2 field not contains Search 2, hide it.

Let me know if there is any question.

Thanks & Regards,
Emir

---

Emir Liu

TechNet Community Support

Answer 2

Thanks, Emir. On option 1, this might work although they were hoping for a search that "Contains" the search term while the drop-down only works on the starting letters of the name. Also, when I add additional fields to the repeating table, like the client number, their phone number, etc., those remain blank even after I select a name. How can I bind those to the selected name to show the rest of the client's record? And how would I pass the selected client name to the main data source?

For the second option, that is exactly what I was trying to do. But my problem is that the repeating table never appears on the form. Not even the outline of it. I have this set as a Secondary data source on the form with the action to query the data source when the "Search" button is clicked. The rows that do not contain the search term should be hidden. Then I put a "Select" button next to each row in the repeating table for the user to select one of them. But the repeating table is not there at all.

Answer 3

Hi SharePoint4You,

1, Use Drop down control (named it field1), we can also add some other fields to present phone number (name it "field2'), client number (field3).

Add default value to field2: Get phone number field from second data source, add filter (ID=Main: field1)
Similar for field3: Get Client number field from second data source with filter: ID= Main: field1.

2, Use repeating table.

Please insert the repeating table by right clicking the repeating group in the second data source. (But not create a new repeating table in Main data source.)
Without add any rule to the repeating table, click query data button, can you get data in the repeating table? If no, please kindly check if you have permission to access the list and list already contains data.

After you can receive the data, add formatting rule to it. And add a button within a row within the repeating table. Add action rule to the button: Set field2, field3 in Main data source with the value:

Field2= phone number in second data source.
Field3= client number in second data source.

I got it work on my side. Please let me know if you have any question.

Thanks & Regards,
Emir

---

Emir Liu

TechNet Community Support

Answer 4

Thanks, Emir, but I still cannot get this working

Main datasource = Clients list

Secondary datasource = Clients List

field1 = drop-down control linked to Main: Column2 (renamed to Client Name), Value=D:ID, Display name=d:Column2 - This works, I can choose a Client Name by typing, then pressing enter on my choice

field2 = text box, default value = Title[ID = field1] - Title is renamed "Client Number"

After I choose a value in field1, field 2 never shows the Client Number

What am I doing wrong?

BTW, I found that the repeating table appears only after waiting for the system to parse > 20,000 names for the selected text which is just not acceptable performance-wise. So the drop-down is definitely the way to go if I can get it to show the other fields needed.

Answer 5

Hi SharePoint4You,

When you use drop down control, please make sure the second data source is retrieve automatically when form is opened.

Then recheck the default value set to field2 = Secondary data source: Title field [Second data source: ID = Main: field].

Thanks & Regards,
Emir

---

Emir Liu

TechNet Community Support

Answer 6

Thanks, Emir. I finally figured it out. I could not manually add columns to the secondary data source that was created by the first dropdown. So, I added three more dropdowns (even though I am not going to use them) for the other fields and that added the columns to the field list.