SP 2010 Column lookup w/ prepopulating other columns within a form

Question

Hoping you can help me out as I can't seem to find the exact answer on any site. I am currently using SharePoint 2010/Infopath 2010.

ISSUE: Not being able to do a column lookup (without a dropdown) that prepopulates other fields in the form.

BACKGROUND: I have one list called VISITOR LOG that the user fills out a form entry and submits for approval. This list is visually on the page as an Infopath Form Webpart. Once submitted, certain column information is set into another list called VISITORS via a SharePoint Designer Workflow.

REQUIREMENT: Have the form displayed on the page so users can fill it out...the user would enter a SSN (we are dealing with the PII req.) and click a button to search in the VISITORS list for that visitor. Once found, then 8 other columns/fields in the form would be prepopulated with that information that was pulled form the VISITORS list. The user can then finish filling out the rest of the form that isn't populated from the VISITORS list and then submit for approval.

RESEARCH DONE: I have found information on doing a Lookup column. Which works great but I can't have a drop down there where everyone can see the SSN's. And I haven't found anything wrt Lookups that prepopulates other fields in a form that you can still make edits to. I have found where it prepops but only after submmitting the item. I have also found a great article on using data connections within your Infopath form. But that doesn't allow me to link two different lists. it only queries within the same list from what I have been able to see/do.

Please let me know if you have any questions on this. Looking for any links to articles, videos, etc.

Answer 1

You can set up a data connection that querys the VISITORS list and gets all the fields that you want; let's call this data connection VISITORS Data Connection or VDC and the SSN textbox/field, SSNF. While building the VDC, you can select all the items you want queryed by checking off the fields.

Now, instead of using a drop down box which will reveal the PII, you can use a regular text box and set a rule to Set a field's value whenever the value of SSNF, like say after you type an SSN. Within the rule, set the Field: textbox to whatever one of the 8 items you want to populate. Now it gets tricky...in the Value: field, click the fx button and click Insert Field or Group. In the Select A Field or Group dialog box, set the Fields drop down to the VDC, expand all the nodes under dataFields and select the data that you want to put into the field you selected, BUT DON'T CLICK OK. Instead, click Filter Data... and add a new filter. Inside the filter on the left hand side, select the field from the VDC that is your search key (in your case, it would be SSN or whatever you are calling it) and set it to equal the value in the SSNF. Repeat the steps until you have populated all your search boxes. To make the 8 fields unchangeable, right click on them, select properties, and make them Read Only.

Basically what is happening is that the form retrieves all the items from the list and caches them in the data connection. When you make your selections, it searches through that cached data connection. It is kind of heavy-handed in my opinion but it is the easiest way to do what you are looking for. You could also try using ListData.svc and the REST based data connection, but I haven't tried it yet so I am not sure if it will work.