Solved

Using database fields in InfoPath

Posted on 2009-05-15
3
681 Views
Last Modified: 2012-05-07
I have an InfoPath form with a data connection to a Sharepoint list. One field (UserName) allows the user to select their name from a Sharepoint list field. I have a few other text fields.and would like their default values to pull their data from the Sharepoint list record selected. For example, the user's address, phone number etc. The user could then modify their contact information as needed.

How do you have the text fields reference the values in the SP list of the record selected in the UserName field?
0
Comment
Question by:Omer Maor
  • 2
3 Comments
 
LVL 10

Accepted Solution

by:
Greg Burns earned 250 total points
ID: 24406015
I did something similar to this once. I had a dropdown list that pulled a list of names from a SharePoint site; when a user selected a name, that person's phone number and email were displayed in two other fields. Here is how I did it:
Add fields to the form:
  1. Add a combo box. This will contain the dynamic list of names to be used. Give this control a meaningful name, as it will be referenced by other fields on the form. I named mine "NameOfRequestor"
  2. Add two text boxes and make them read only. these will display the Phone and Email addresses. Since they are lookup values, I didn't want the user to change them. I named these fields "RequestorPhone" and "RequestorEmail".
Add a data connection:
  1. Go to Data Sources and add a new connection to Receive Data.
  2. Select SharePoint Library or List
  3. Enter the URL of the site you're receiving data from.
  4. Select the list / library.
  5. Specify the columns you want to include in your resultset (In my case, it was Full Name, Phone, and Email)
  6. You can optionally choose to save a copy of the downloaded results in the form template, so that it will contain a snapshot of list items that will be available when the form is filled out offline.
  7. Provide a name for the data connection (I named mine "Requestors") and click Finish.

Click on Properties to Bind the Data Connection to the Combo Box.
  1. Under Llist box entries, select "Look up values from an external source"
  2. Select the data source you created above.
  3. In the "Entries" field, click the Browse button and drill down to MyFields-->dataFields-->(Your data connection). Click OK.
  4. The "Value" and "Display Name" fields will suggest a field to be used, If it is not the right one, browse to the correct fields. In my case it was @Full_Name.
Set up a Rule to update other fields when this field is updated.
  1. In the Combo Box properties, click the "Rules" button. Add a new rule.
  2. Do not set a condition (this rule always applies).
  3. Add an action to update the RequestorPhone text box.
  • Action: "Set a field's value".
  • Field: This part is a little tricky. You want to update the text box, so be sure the "Data source" is set to "Main". Drill down to the "RequestorPhone " field and click OK.
  • Value: Click the formula button.
    • Click the Insert Field or Group button and select the Requestors Data source you added above.
    • Drill down to the "MyFields-->dataFields-->Requestors" and select the field for "Business_Phone" (this is the name of the field in my SharePoint list).
    • Click the Filter Data button.
      • Add a filter for when "Full_Name" "is equal to" and in the criteria field, click "Select Field or Group". In the "Main" data source, select the "NameOfRequestor" combo box.
      • Click OK.
      • Click OK.
    • The formula should read something like "@Business_Phone[@Full_Name = NameOfRequestor]".
    • Which translates to "Show me the [Business Phone] value from my data set where the [Full Name] value matches whatever value is in the Combo Box [NameOfRequestor].
  • You're not done!

    Now keep adding other actions and do the same thing for all other associated fields you want to update whenever the Combo Box is updated.   They will all trigger at once and display associated information.
hope that helps!
0
 

Author Comment

by:Omer Maor
ID: 24423487
Thanks for such a tremendously detailed response. I'll check it out and let you know how it went.
Sam
0
 

Author Closing Comment

by:Omer Maor
ID: 31582094
Perfect - worked like a charm. Thanks.
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SharePoint 2013 Searchbox Branding 11 113
Sharepoint KB 2 82
File size limit in SharePoint 2010 5 50
Port SharePoint 365 Add-In Developed in Visual Studio to Site 2 26
Last week I faced a strange issue recently, i have deployed SharePoint 2003 servers for one project and one of the requirements was to open SharePoint site from same server. when i was trying to open site from the same server i was getting authentic…
Summary In SharePoint 2010 it is easy to create custom color themes to jazz up a site. Theme colors can also be created in PowerPoint 2010 with a few clicks. But how do the chosen colors actually look in the SharePoint site? The attached PowerPoint…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

839 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