There are times that you have data that you would like to sort to use in other parts of SharePoint. For Example, you may want to take data from an SQL database and store it inside of a SharePoint List.
The way to make this work is by using a for each loop and a switch action to break up the data, then you can use variables to iterate through and create the newitems.
I will give you an example of a similar scenario that I helped someone with a while back. In this scenario they had a string that was separated by “;“ that they wanted to break up to store as fields in a list.
Essentially, to make this work you need to do the following:
- Create a regular expression to split the query to each one of their respective fields. (\; split)
- Create an Index (number variable) that the for each action can use to keep its place during each iteration.
- Create a for each loop (which you said that you have) and add a Switch action with possible values to iterate through the lines, separating the fields and
storing each one as its own separate text variable.
- Use text variables as needed to update the SharePoint list.
In the example below I used a set variable in the beginning of the workflow to emulate one of the lines from the String and added Log in History list actions so that I could keep track of the format.
All you will need to do is add the regular expression action and for each loop with switch action, then you can do update list/etc. at the end of the loop.
Variables are as follows;
SQL Output (Single line of the SQL output used for testing in this case)
Single Item (Storage of each field)
Collection (Storage of regex split text)
Text 0-5 (individual fields)
NumIndex (Number variable used as an index for the for each action)
The screenshot below shows the output of each one of the text variables once they have been processed by the switch action. The top line of the workflow history is the Collection before any modification, the line below is the output of each one of the variables one per line. A log in history list action was added to the end of the workflow so that you could see the collection as a whole, and each one of the text variables after it was broken up. If you create a similar configuration as above and then pass the variables on to the web service call using references, you should be able to get what you are looking for.
For example; going by the variables that were used in the above example, you would put the following into the web service call.
Inside For Each ( web service Call WebURL/_vti_bin/Lists.asmx) >
UpdateListItems Web Method
<Method ID="1" Cmd="New">
<Field Name="ID"> CurrentListItemID </Field>
Depending on the rest of the workflows design, you may be able toadd the web service call to the bottom of the for each loop (like in the
example above) and it will iterate through each item, split up the fields, and use the call to create the item in the new folder.
I hope this example gives you a place to start, Please let us know if you have any other questions.