DISCLAIMER/WARNING: Running this, or any derived "fix" workflow on every item in a large list will be a significant load on your environment. When extending or modifying this workflow for your list, consider batching your "fixes" or putting looping logic in the existing workflow such that it will process every item in your list in order. Also: always remember to test your workflow and keep backups of data!

I ran across an interesting situation in the course of my casework and made a fix that I felt might help others as well.

Problem

I've got Cascading drop downs set up in Nintex Forms, but there was a small problem where the columns (configured as "Single Line of Text") type fields are "disconnected" from the Form in that editing the data outside of Forms doesn't reflect in the Form!

Alternate problem that brought you here

I made text columns in my SharePoint list, but my business needs have changed and I have to have lookups instead!

(Manual) Solution

- Add new columns configured as lookups, and manually fix each list item.

(Workflow) Solution

- Have a Nintex Workflow that updates the list items that needs to be fixed.

Before we begin: Lookup Complexity

Lookups are stored as text in a somewhat complicated format. To understand the solution, it would help to understand how lookups are stored.

My preliminary search brought me to a community thread​​ that gave me a good starting point for my research. Thanks Caroline Jung​!

After some digging in that thread, and some tests with the GetListItems method of the Lists Web Service, I had a good lead.

Let's dissect some output (indentation manually added to make it easier to read):

<rs:data ItemCount="1"> 

  <z:row

  ows_LinkTitle ="TestItem"

  ows_CCL_x0028_TEXT_x0029_ ="CascadingLookupItem2"

  ows_CCLN_x0028_TEXT_x0029_ ="CascadingLookupItem2A; CascadingLookupItem2B"

  ows_CCL_x0028_MLookup_x0029_ ="2;#CascadingLookupItem2"

  ows_CCLN_x0028_MLookup_x0029_ ="2;#CascadingLookupItem2A;#3;#CascadingLookupItem2B"

  ows__ModerationStatus ="0"

  ows__Level ="1"

  ows_Title ="TestItem"

  ows_ID ="7"

  ows_UniqueId ="7;#{43F5BE5A-8795-4059-B1F5-9728D33940AF}"

  ows_owshiddenversion ="6"

  ows_FSObjType ="7;#0"

  ows_Created_x0020_Date ="7;#2016-04-08 10:52:54"

  ows_Created ="2016-04-08 10:52:54"

  ows_FileLeafRef ="7;#7_.000"

  ows_PermMask ="0x7fffffffffffffff"

  ows_Modified ="2016-04-19 11:40:26"

  ows_FileRef ="7;#jason/Lists/MyList/7_.000"

  />

</rs:data> 

And let's look at lines 4 through 7:

  ows_CCL_x0028_TEXT_x0029_ ="CascadingLookupItem2"

  ows_CCLN_x0028_TEXT_x0029_ ="CascadingLookupItem2A; CascadingLookupItem2B"

  ows_CCL_x0028_MLookup_x0029_ ="2;#CascadingLookupItem2"

  ows_CCLN_x0028_MLookup_x0029_ ="2;#CascadingLookupItem2A;#3;#CascadingLookupItem2B"

"CCL(TEXT)" (ows_CCL_x0028_TEXT_x0029) is the single line of text column that is bound to a single item list lookup control.

"CCLN(TEXT)" (ows_CCLN_x0028_TEXT_x0029) is the single line of text column that is bound to a multi item list lookup control.

In our problem scenario, the two single line of text columns are bound to list lookup controls in Nintex Forms by the "Text connected to" control setting.

This is all well and good if all we want to do is edit the list data purely in Nintex Forms, but might be problematic if you need to add in lots of entries using SharePoint's quick edit mode, because that won't bind the information necessary to reflect list-side changes in the Nintex Forms interface.

The start of our solution is to have the list lookup controls in our Form bound to list lookup columns in Nintex Forms by the "ID connected to" control setting.

To that end, I've added to list lookup columns. One is a single-item list lookup column, and the other is a multiple-item list lookup column.

"CCL(MLookup)" (ows_CCL_x0028_MLookup_x0029) is the list lookup column that is bound to a single item list lookup control.

"CCLN(MLookup)" (ows_CCNL_x0028_MLookup_x0029) is the list lookup column (permitting multiple items) that is bound to a multi item list lookup control.

To illustrate the point, I bound the list lookup controls initially as text to generate the data that went into the single line of text columns using "Text connected to". Afterwards, I unbound those and rebound them to the lookup columns in list using "ID connected to".

Here's the difference we see:

With that, we know the syntax to "convert" single line of text columns to lookup columns. Have another table!

The rough rule for lookup column representation can be generalized as:
"{ID};#{Text}" for the first item, and additional ";#{ID};#{Text}" appended to the first for every subsequent item.

With that logic figured out, I had enough information to go and create a workflow.

Solution Part I

Let's start with the Single Line of Text to Single Item Lookup conversion problem.

Action	Purpose/Logic
	Probably good practice to put a grouping of actions doing some logical tasks in an action set.   The programming analogy that comes to mind here is that an Action Set is the equivalent of a grouping of a logic inside a method.   Additionally, it would appear that lookupify isn't a word. Oh well.
	The real piece of information we need is the ID of the item in the source lookup list. This uses a Set a Variable action that does the following: Using the current item's lookup data you have (e.g. CascadingLookupItem2)... Find the ID that matches that data in your source (lookup) list Save that ID into your variable   Configuration in practice:
	This takes the ID (e.g. "2") and the data that we have in our list (e.g. "CascadingLookupItem2") and combines them. This uses a Build String action to do the actual crafting. The syntax is: "{ID};#{Text}", so let's craft that in the action.   Here's the data: - Workflow Variable CurrentIndexID contains the ID that we found in our previous action. - ItemProperty CCL(TEXT) is representing the current item's single line of text field.
	This just takes the string we built in the previous action and sets our nice and new lookup column with our crafted string.

And we find that our single item list lookup conversion worked!

Solution Part II

Now let's figure out* the single line of text to multiple item list lookup conversion...

*For the sake of what little brevity I can achieve with this already long post, I won't duplicate the logic already described in the single item example.

Action	Purpose/Logic
	Same reason as the action set described above.
	Because we need to process each item in the multiple-item lookup, we're going to need to split the list of items we have. Let's take our example item: "CascadingLookupItem2A; CascadingLookupItem1B". Somehow, that needs to turn into "2;#CascadingLookupItem2A;#3;#CascadingLookupItem2B"   With the Regular Expression action in Split mode, flat text turns into a collection with two items: "CascadingLookupItem2A" "CascadingLookupItem1B"   Hint: A semicolon "; " is the delimiter! **Note, the trailing space here is NOT a typo! See the further explanation in the 2 regular expression actions immediately after the For each action below.
	We need to have this For each action to be able to do the proper processing for each item. We'll For each over each item in the collection we made in the previous action.
	For the next two actions, we revisit the Regular Expression action, but this time, in replace mode. The replace is used to remove leading ("As+") and trailing ("s+") whitespace.   NOTE: There's definitely a point to make here that this step might break your lookup crafting logic if the list data you're using for the lookup actually has trailing or ending whitespace. As such, the actions are currently disabled (properly grayed out in the example image to the left) and are left in the sample workflow as a reference. For now, assuming "well-formed" text was generated by Nintex Forms for the text representation of the multi-item lookup, these actions aren't necessary. The format of well-formed text is expected to be {item}; {item}; {item}. Note the space after the semicolon, which was used in the logic for the "Split the Multiple Items" Regular expression action above.
	Same logic as the "Get Single Item ID" Set a variable example from the single-item analysis above. Notably, we're using workflow data (the For each variable) for this bit instead of the list lookup of the current item.
	Building our SharePoint-lookup-compatible string using Build String as in the same action as the single item example above.   For the attentive, you'll notice that this slightly differs from our single item example in that this has a leading number sign (or pound sign or hashtag, depending on your preferred term). This is because each item after the first has a leading number sign. You might be asking yourself "Doesn't this break the syntax then? We're adding this to the first item too, aren't we?" The answer is yes, but we'll account for that in a bit.
	We'll leverage the Build String action again here. The purpose for this instance is for saving our crafted string "#{id};#{text};" from the previous step.   This accumulates the string for the loop. Example for those who understand better that way: - Loop 0: #1;#A; - Loop 1: #1;#A;#2;#B; - Loop 2: #1;#A;#2;#B;#3;#C; ~~ and so on and so forth.   Technically, this logic could have been added to the previous action to just append our newly crafted string to our existing one all in the same action by referencing the accumulator variable in there. I figured I'd split out the logic for the sake of clarity.
	You might have noticed that "#1;#A;#2;#B;#3;#C;" isn't actually valid syntax. The first item in a properly formed text representation of a lookup doesn't have a number sign (#) in the first "item", and does not have a closing semicolon (;). Instead of building in custom logic for the first case and the last case within the loop or outside of it, I just treated each item as being neither the first or last item. At the end of the loop, I just do the simple "fixes" to make the string "compliant".   Another example: Invalid, work-in-progress string: "#1;#A;#2;#B;#3;#C;" "Compliant" string: "1;#A;#2;#B;#3;#C"
	And we're done. Always make sure to save your work!

And it worked!

Reference materials

Now that you've made it this far, here are your rewards (all conveniently zipped up):

(Note: If you want the full "demo", you'll need to import in the following order: CascadingLookup.stp, CascadingLookupNext.stp, LookupifyThisList.xml, workflow and form to LookupifyThisList.)

1. The reference workflow itself
2. The form that has a cascading lookup to test the successful "Lookupification"
3. The necessary list templates to play around with
   1. Our primary list ("Lookupify This List")
   2. Our first lookup list ("CascadingLookup")
   3. Our second lookup list, looking up our first lookup list