Copy list items from one list to another with different data types and lookups

[burkslm]

I have a SharePoint 2013 list. I need to copy all of the items from that list to another list in the same site. However the other list has different field data types and it has lookups. So for example, I may have field x in both list but in one list it may be a string field but in the other list that is getting copied to it may be a number or a lookup. I have 126 columns so I need this to be a fast solution. How do I copy these items using Nintex 2013 workflow?

[andrewg]

Are all of the TO-BE number fields, actually a number value in the source list?

[burkslm]

There are text that need to be dates, text that needs to be currency, strings that need to be lookups

[andrewg]

In a create item action, you should be able to reference the source SLT field in the non date/lookup fields. For the ones that have to become dates, they will need to be translated into that type. For lookups, it depends on the type. Sometimes using a SLT reference can work, other times the id/value pair is required in which a query could be necessary.

[burkslm]

I was hoping I didn't have to reference about 100 columns when I had to do a Query List action.

[andrewg]

I agree, I wouldn't do it either. You can break it up by creating the item with known fields, then update the item with custom fields after.

[burkslm]

That was exactly what I started. I'm glad we think alike. I'm not used to updating multiple items in a table. Can you please show me a screenshot of like updating multiple items? For some reason my Foreach isn't working.

[andrewg]

You can do this in two ways.

You can use one workflow that has a foreach loop by first query the list and fetch all of the item ID values in a collection. Foreach on the collection and the current item variable will be the single item ID. Then query again on the list to fetch the value of the single item with a hundred variables to store values in.

Or, have the workflow run on each item. Maybe using a single site workflow to start the list workflow on each item. (starting a few manually for testing). Then your context is different, it's on the individual item and you will not have to store all of the values in variables.

[burkslm]

Ok don't think I am dumb....but I'm more of a visual learner. I absolutely prefer the 2nd option because I don't want to have to create all of those variable I was seeing that was becoming necessary. Pretty please show me a screenshot of the second option.

[andrewg · Accepted solution]

no worries. So the workflow that starts on every item will not have a lot of actions. Minimum of two. One to initially create the item, another to update with custom fields. In between these two, you will have the conversion actions like taking a string to a date field.

• In the Create action, store the item ID created into an Integer
• Use some conversion actions to get secondary values
• Update the created item filtering the source list by the saved item ID, and update with the converted values.
• Now you just need a service workflow to start this workflow on every item in the list. But first, test if you can get this far with one item.