none
how to retrieve data from a list and populate the form when it starts? RRS feed

  • Question

  • Hi there,

    I have a form which needs retrieve the data from a list automatically at the moment it opens. This is not the situation of dropdown list retrieval. I use textbox for all these fields to be filled and make them ReadOnly. I think I may write code in page load up for this purpose. Any reference or ready codes for that? Thanks in advance.


    allan
    Wednesday, February 3, 2010 11:40 PM

Answers

  • Allan, your first scenario is very easy:

    1) Create a receive data connection for the SharePoint list.
    2) Create 3 fields in your form - one text and two numbers (whole number/integer probably)
    3) Drag each field to the form canvas so that it creates 3 text boxes
    4) Change the first text box to a dropdown list box.  Change the other two so that they are formatted as currency with delimiters.
    5) Double-click the dropdown box to get to its properties
    6) Set it to receive from an external data source (3rd radio button), and choose your SharePoint list data connection in the dropdown
    7) In the first field, choose the repeating group from your SharePoint list that contains the 3 columns
    8) In both the Display Name and Value fields, select the Name field from SharePoint
    9) Add a rule to this field (through the control itself or through the data element in the data source pane on the right).  In the rule, have two actions where each one sets the value of the other two fields (from step 2) to the values of Salary and Bonus.  When you tell it what value to send to these fields, you not only select the Salary and Bonus nodes from the SharePoint data connection, but you also click the filter button and create a "where" statement that says "Name(SharePoint) is equal to Name(InfoPath)."  You're basically telling it to find the row that matches the value chosen by the user when selecting a name in the dropdown, then go across to find Salary/Bonus, and place those specific values into the InfoPath fields.

    This is how the scenario plays out:
    1) Allan opens the form
    2) Allan sees a dropdown and two empty fields
    3) Allan chooses Jim Caro from the dropdown, which immediately causes the two empty fields to be populated with Jim's Salary and Bonus, respectively.
    4) Allan is happy

    For your other requirement, if you want to show the secondary data all at once, then simply create the data connection and drag the repeating folder to the canvas, which will prmpt you to create a repeating table.  InfoPath will create it for you based on the fields within that group that have been downloaded.  When you open the form, you will see all rows from the data source.  If you only want to see some of the rows, then you need to add conditional formatting to the table that HIDES rows where a certain value matches or doesn't match, but you haven't explained that part of the logic yet.  However, I may not understand what you are truly trying to do here.
    SharePoint Architect || My Blog
    • Marked as answer by allan8964 Thursday, February 4, 2010 4:20 PM
    Thursday, February 4, 2010 7:10 AM

All replies

  • I don't think you need code.  You simply create a data connection to the SharePoint list, and set it to retrieve automatically.  Then, add a rule to the Form Load (Tools > Form Options > Open&Save or Browser > Rules) that has actions for "Set a Field's Value" for each of the ReadOnly fields.  Set them to the respective fields in the list that correspond to the form fields, and filter out the proper row from the list based off whatever pre-defined criterion you have.  The data set retrieved from the list will obviously have multiple rows of data, so you must have something somewhere that defines which row of data you want to populate.  You haven't mentioned that, so what is it?  Typically, the user chooses something upon opening the form, which then queries the list and sets the field values based on the user's selection, but you say the fields should automatically populated, so what piece of metadata defines the row that should be selected from the SharePoint list?
    SharePoint Architect || My Blog
    Wednesday, February 3, 2010 11:59 PM
  • //Get items from SharePoint list.





    SPSite mySite = SPContext.Current.Site; SPWeb myWeb = SPContext.Current.Web; string text = ""; using (SPSite site = new SPSite(mySite.ID)) { using (SPWeb web = site.OpenWeb(myWeb.ID)) { SPList list = web.Lists["Your_List"]; SPListItemCollection collListItems = list.Items; foreach (SPListItem listItem in collListItems) { text += listItem["Your_Field_Name"]; } } }
    // Assigning value to InfoPath node. Replace "Xpath" with your text box


    XPathNavigator root, node; root = this.MainDataSource.CreateNavigator(); infopathNode = root.SelectSingleNode("Xpath", this.NamespaceManager); node.SetValue(value);


    백상하
    Thursday, February 4, 2010 12:02 AM
  • Thanks Clayton for reply.
    You are right, I have not mentioned which row must be chosen from the multiple ones.  For example I have a list like this:

    Name             Salary        Bonus
    John Smith     14,902        1120
    Jim Caro        17800         1025
    Kevin Diaz      20980         1130

    If I have already known the name of Jim Caro, can I type in Jim's name in a text box and click some buttoon or not, then the salary and bonus fields will be populated with 17800 and 1025 ? 

    Other requirement is the case of the same table (actually other table but here just for the easiness to explain) will be automatically filled at the opening in three rows of three fields of name, salary and bonus. (Maybe I can use repeating table but now I know the row numbers.)
    Thanks again.

    allan
    Thursday, February 4, 2010 3:56 AM
  • Thanks Sangha for reply. Please see my reply to Clayton for detailed notes. These are two situations I want to retrieve the data.
    Would be more specific on the codes? Do you mean the codes in FormEvents_Loading method? Thanks.

    allan
    Thursday, February 4, 2010 4:02 AM
  • Allan, your first scenario is very easy:

    1) Create a receive data connection for the SharePoint list.
    2) Create 3 fields in your form - one text and two numbers (whole number/integer probably)
    3) Drag each field to the form canvas so that it creates 3 text boxes
    4) Change the first text box to a dropdown list box.  Change the other two so that they are formatted as currency with delimiters.
    5) Double-click the dropdown box to get to its properties
    6) Set it to receive from an external data source (3rd radio button), and choose your SharePoint list data connection in the dropdown
    7) In the first field, choose the repeating group from your SharePoint list that contains the 3 columns
    8) In both the Display Name and Value fields, select the Name field from SharePoint
    9) Add a rule to this field (through the control itself or through the data element in the data source pane on the right).  In the rule, have two actions where each one sets the value of the other two fields (from step 2) to the values of Salary and Bonus.  When you tell it what value to send to these fields, you not only select the Salary and Bonus nodes from the SharePoint data connection, but you also click the filter button and create a "where" statement that says "Name(SharePoint) is equal to Name(InfoPath)."  You're basically telling it to find the row that matches the value chosen by the user when selecting a name in the dropdown, then go across to find Salary/Bonus, and place those specific values into the InfoPath fields.

    This is how the scenario plays out:
    1) Allan opens the form
    2) Allan sees a dropdown and two empty fields
    3) Allan chooses Jim Caro from the dropdown, which immediately causes the two empty fields to be populated with Jim's Salary and Bonus, respectively.
    4) Allan is happy

    For your other requirement, if you want to show the secondary data all at once, then simply create the data connection and drag the repeating folder to the canvas, which will prmpt you to create a repeating table.  InfoPath will create it for you based on the fields within that group that have been downloaded.  When you open the form, you will see all rows from the data source.  If you only want to see some of the rows, then you need to add conditional formatting to the table that HIDES rows where a certain value matches or doesn't match, but you haven't explained that part of the logic yet.  However, I may not understand what you are truly trying to do here.
    SharePoint Architect || My Blog
    • Marked as answer by allan8964 Thursday, February 4, 2010 4:20 PM
    Thursday, February 4, 2010 7:10 AM
  • If I understand your question, claton's response is correct. 
    Create a data connection to your list
    Create your text box for the search string and an associated button, call it something like "Search"
    Create text boxes to hold the results

    bind a rule to the button that uses the Set Field Value action.  Set the value to your results field.  Set the value to the appropriate column from your data connection, filter the data using the "contains" condition to select a row that contains your search string.

    This will not work for form-services forms
    if the filter returns more than one match, you’ll have to account for that as well.

    Is there something we are missing?

    Thursday, February 4, 2010 7:14 AM
  • My method has no need for a button, and it does work in browser forms.
    SharePoint Architect || My Blog
    Thursday, February 4, 2010 4:01 PM
  • Thank you so much Clayton. I already figured it out. The tricky thing is just the step 7, setting the filters. I failed several times and finally made it. Thanks.

    For the 2nd requirement I did it too. But a new situation I need your guide. Look at the following table which already pulled out with repeating section:

    Name                 Bonus (dropdown list to pick up a bonus number manually)
    John Smith        
    Jim Caro 
    .
    .
    .
    Kevin Diaz

    Now I already have all the name fields populated but because I want to assign bonus MANUALLY using dropdown list, so I need a dropdown list stand at the right side of each name. How can I do that? Thanks.




    allan
    Thursday, February 4, 2010 4:19 PM
  • You can't assign anything that way, because that data is secondary and comes from the list.  You can't change it.  You can only affect data in your main data source, and you can't combine main data source fields within your secondary data repeating table.  You would have to use CopyTable (Qdabra qRule) to copy the whole table from your secondary source to main source, and then you could make selections like you want to do, but even then, you will have to come up with a method to write back to your list, because InfoPath does not have that ability natively.  You'd have to use one of 3 methods:

    - Use workflows (SharePoint Designer or Visual Studio) that fire when the form is submitted to the library, and take the relevant data from the form library, then use it to create or update list items in the other SharePoint list(s)
    - Use the Qdabra qRules method for submitting to SharePoint lists: Qdabra: Products: qRules (Item #1)
    - Use the CAML method: Submitting Data from InfoPath 2007 to a SharePoint List


    SharePoint Architect || My Blog
    Thursday, February 4, 2010 4:28 PM
  • Thanks Clayton for reply.

    I am afraid I have not explained it clear. I want to a dropdown list appear beside the repeating section items. It works now. The only thing bothers me is I need to click Insert button to create a new one .... possibly there is a way to create as many dropdown list as I expect at the form opening. Thanks again for all your time.


    allan
    Thursday, February 4, 2010 9:32 PM
  • Hi Clayton,

    I am sorry to interrupt you, i have been struggling to get the data from list into infopath. i dont want to use code. the abvoe 9th point i dont understand, can you please..................explain in details step by step. if you dont mind. can you do it as soon as possible.


    Ramanjulu Naidu N


    Friday, June 22, 2012 9:54 AM