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
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.
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.
- Enter the URL to your site and click “Next”
- Select the list you would like to customize and click “Next” and hold on a second.
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
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”.
- 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…”
Activate both checkboxes and type your text if you like instead of “Insert item”
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”
- Click “Add” to create a data source for our “temporary container”
Fields of our Repeating section will consume values from our container, so we will “Receive Data” from it.
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.
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
Open the drop down box to switch to secondary data source and select “Header” (name of your data source created before”
Drag & Drop the “root” node from your “Header” data source into your form
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”
“right click” on “Employee Name” and select “Textbox Properties”
- Click on the formula button
near the “Value:” field. - Click on “Insert Field or Group” button, select “Header” data source from drop down and select “EmployeeNumber” from the tree.
- 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.
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.
- Publish your form to SharePoint by clicking on Qucik Publish button at the left top corner
- 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.
DONE
