Home > Infopath, NoCode, Office, SharePoint > Inserting multiple list items at once into a SharePoint list

Inserting multiple list items at once into a SharePoint list

Couple of weeks ago I was asked if it is possible to insert multiple list items into a SharePoint list at once where some fields of inserted items are the same, like a header, and others are different?

The answer is “Yes it is”.

In my example I will use a custom list called “Inventory”. In my scenario this list is managed by internal IT where they track devices for each employee like phones, notebooks etc.  A guy from IT department needs to insert the “Employee Name” and “Employee Number” of new colleague for each entry in the list again and again the only difference is in the “Article”, “Quantity” and the “Serial Number”

 

List definition

image

 

Relaying on standard capabilities of SharePoint you can edit the list in a Datasheet View, place the name of the new employee, the employee number and then just fill in the fields for the “Article”, “Quantity” and  “Serial number” and save it.

image

This is a valid approach for small list with view fields. If you have bigger list than my example this might be not the best way to insert your information. In case of a complex list, the end user might want to see some guidance near a field so he/she can make a decision what value to place into this field. There is no good way to show such guidance in a “Datasheet View”.

 

Solution

We can use InfoPath to achieve our goal. This solution works in SP 2010 and InfoPath 2010 as well as in SP 2013 and InfoPath 2013 environments. 

InfoPath allows you to create a form which can insert multiple entries into a list. To be able to use this feature you need to start from InfoPath and not from the List. DON’T CLICK ON “Customize Form” button in the ribbon of the SharePoint list. This will end you up with a form to insert a single entry only.

Create custom Form for the List
  • Open InfoPath Designer and select “SharePoint List” from the backstage view and click on “Design Form” button.
    image
  • Enter the URL to your site and click “Next”
    image
  • Select the list you would like to customize and click “Next” and hold on a second.
    image
    In my scenario this is the “Inventory” list.
  • Enabling “Management multiple list items” capability
    Make sure you activate the checkbox in the last step of the wizard
    image
    If you are not able to see this step you probably started from the list clicking on “Customize Form” button. Close InfoPath, start InfoPath designer and follow the steps above.

    At the end you should end up with something similar to following screenshot. Please note that all controls are encapsulated by a “Repeating Section”.

image 

  • Enabling “Repeating Section”
    The “Repeating Section”, which allows us to insert multiple items, is deactivated by default. To “enable” the repeating section, click with right click into the repeating section and select “Repeating Section Properties…”

    image

    Activate both checkboxes and type your text if you like instead of “Insert item”
    image

Separating header and Details

Our goal is to insert the “Employee name” and “Employee number” only once even if we are inserting 5 items into the list. We need to “copy” the values into each item. There is no way to iterate through items of a repeating section without code. The trick is to use a “temporary container” to store the values and to point to this container from fields of our repeating section.

  • Create temporary Container
    As we are creating a form for a list we cannot create “temporary” fields inside our main data source. InfoPath allows us to interact with different data sources called “secondary data sources”. We will use an XML file to build our container. This XML file will contain all our fields from our header. In our case these are “Employee name” and “Employee Number”.

    <?xml version="1.0" encoding="utf-8"?>
    <root>
        <EmployeeName/>
        <EmployeeNumber/>
    </root>

    Save this XML into a file called “Header.xml”

  • Adding temporary container to our form
    Select “Data” from the Ribbon in InfoPath and click on “Data Connections”
    image
  • Click “Add” to create a data source for our “temporary container”
     image

    image
    Fields of our Repeating section will consume values from our container, so we will “Receive Data” from it.

    image

    image

    image
    Our form will be hosted in SharePoint so there is no way to access our xml file from SharePoint. This dialog informs you that the xml file will be “included” into the form.

    image
    Make sure the checkbox is activated, otherwise you want be able to use the container.

  • Inserting fields from “temporary container” into the Form
    Click on “Show Advanced view” (bottom right). This will allow you to see secondary data sources and to place fields from this data sources into your form
    image

    Open the drop down box to switch to secondary data source and select “Header” (name of your data source created before”

    image

    Drag & Drop the “root” node from your “Header” data source into your form
    image

Connecting the dots

Now it’s time to connect your “temporary container” with our fields for new list items.
“right click” on “Employee Name” and select “Textbox Properties”
image

  • Click on the formula button image near the “Value:” field.
  • Click on “Insert Field or Group” button, select “Header” data source from drop down and select “EmployeeNumber” from the tree.
    image
  • Click “OK” several times so all dialogs close.
  • Repeat the same for “Employee Name”
  • Verify your work
    Preview your form (Home – Preview). Insert some text into “Employee Name” and “Employee Number”. You should see the same value in fields of the repeating section.

    image

Cleaning up the form and publishing to SharePoint

Now you can remove the rows from your repeating section, so the user is not confused about same values. Removing the rows from your view do not affect your data. Everything will still work just as if the fields are there.
image 

  • Publish your form to SharePoint by clicking on Qucik Publish button at the left top corner image
  • Go to your list and click on “new Item” link. Your new form should show up and you should be able to create multiple items where you need to type the “Employee name” and “Employee Number” only once.

image

 

DONE Smile

Advertisements
Categories: Infopath, NoCode, Office, SharePoint
  1. Srinivas Vangala
    December 12, 2013 at 6:50 am

    I have a Employee Trainings list which I need to update a Training which is common to all the employees.I have to select all the employees and update a training.Training list contains Employee Full name , Emp number,first name,last name , Training Topic, Trg start date,Trg hrs and comments. i have created a infopath form to add and update the training for each employee.But there is no provision in this form to add a training which is common to all employees at once.Can you please help me to create the form and update the sharepoint list as per the above requirement.

    • January 16, 2014 at 8:19 am

      Hi Srinivas,

      In your scenario I would recommend you to use 2 lists. One List to store your employee names and the other to store trainings. Add multi select lookup field to the trainings list to point to the employee list. Using to lists you will only have one record for a particular training and can select which employee will attend. Updating the trainings will also be much easier because you will need to update only one record.

      hope this helps

      bg Andrej

  2. Dima
    December 21, 2013 at 9:52 am

    Great info, thanks for sharing. I faced a problem though. The properties of the XML fields are greyed out and I can’t add formulas. Any guesses why?

    • January 16, 2014 at 8:25 am

      Hi Dima,
      fist thing coming into my mind is that you are trying to update a field from a secondary data source.

      bg Andrej

  3. Manoj
    January 30, 2014 at 12:23 pm

    hi,
    actually i am getting the same problem as dima,but i am not able to understand your response.as i am a newbie in this,could you please explain it in way that i understand it better.

    • January 30, 2014 at 1:51 pm

      Hi Manoj,
      In InfoPath you differentiate between Main Data source and Secondary Data source. Main Data source is our form, in our case this are the fields of the list where we would like to insert the values. This data source is named “Main”. you can see it in the screenshots above. In this article is “Header” a secondary data source. Fields of secondary data source cannot be changed.

  4. manoj
    February 3, 2014 at 12:21 pm

    hi,then how to make the value for employee name,number field be populated with the value we have entered before(when we edit the item that we have created)?

  5. Ammar
    April 2, 2014 at 12:53 pm

    Hi Andrej,
    I think there is some conflict in your answers ,, please more details about Dema and Manoj issue

  6. Lucas
    May 8, 2014 at 6:21 pm

    Very helpful tip about using an embedded XML as a temporary repository – thanks!

  7. Azra
    June 5, 2014 at 1:04 pm

    Hii
    Thanks for the post, Can I achieve the same without using infoPath?
    Thanks
    Azra

    • June 11, 2014 at 9:38 am

      You can create an App, Cloud Business App would be a solution I would look into. Don’t be confused by “Cloud” it works on Prem as well.

  8. Martin
    July 29, 2014 at 2:43 pm

    Hi Andrej,
    This is great, just what I was looking for. I’ve created a new infopath view for editing an entry. How can I get it to pick up the data from the list rather than the header.xml file?

  9. K
    August 31, 2016 at 12:00 pm

    Hi Andrej,
    thanks for this, it is very well explained and makes me finally understand how to use the XML-file as temporary container. However, I was wondering whether you know a workaround if the “Employee”-fields were not free text fields but a lookup from another list. E.g. instead of ‘creating a new employee’ you link to an existing employee-list and then this link is multiplied into each article-list-item.

  10. John
    October 18, 2016 at 3:51 am

    Hi, thanks for the solution it worked great. However, when I try and view item the items are not showing up. I think this is because we are now pointing to an empty container. Is there a solution for this?

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: