Skip navigation
All Places > Getting Started > Blog > 2016 > April
2016

I had a requirement whereby no users would have access to input into a list in SharePoint, but that my workflow needed to create an item in it.  Perfect for using a web service call with a service account I thought!

I created a service account and added the credentials into my workflow constants for the site.

To start with in my workflow I created a build string action to create the XML to pass into the web service call:

vMTextBatchXML.PNG

Originally I only needed to add text fields in to the list, so I hooked up my web service call as follows:

  • Call web service action
  • URL = Web URL/_vti_bin/lists.asmx
  • Username and password = managed credentials input earlier of the service account
  • web method = UpdateListItems
  • Editor mode = SOAP builder
  • listName (string) = Requests
  • updates (xml) = vMTextBatchXML (the XML we created earlier)

 

I ran the workflow and it created the item perfectly.  I was super happy with this.

Then the requirement changed (development wouldn't be the same without a spot of scope creep ) - I needed to take the requester from the originating list and pass it to the people picker field of the destination list.  Easy I thought.  I added my field name into the XML in the build string and ran again.  WRONG.  it kept failing and I couldn't work out why.  I tried everything possible in the people picker value to pass across, but it wouldn't work.

What I didn't know until now is that the XML wanted me to pass the person or group field in this format:

<userID>;#<userLoginName>

where userID is the local ID of the user being passed in the web service call.

So then I needed to work out how to get the userID to be able to pass my value in the correct format.

What I did is here (but please shout if there is an easier way):

  • Call web service action
  • URL = Web URL/_vti_bin/usergroup.asmx
  • Username and password = managed credentials input earlier of the service account
  • web method = GetUserInfo
  • Editor mode = SOAP builder
  • userLoginName (string) = item property requester
  • Web service output, store result in = vMTextXMLOutput

So now I had the output of the web service call for the requester GetUserInfo.

I then used Query XML action to pull out the ID:

QueryXML.PNG

 

So now I had the user ID I could revisit my original build string action and pass the user in the format required.

I have written this here so that I can revisit next time I need to use this.  I don't know about you guys but I am always thinking to myself, "I know I have done that before, but where is it!!" so thought this might be an easier option for me!

Please let me know if anyone has taken a different approach for this.  I searched this community and google for answers when I was stumped but I couldn't find anything - but you never know if you search the correct terms!

Hope this helps someone, if not I know it will help me in the future!

leej

Lookupify my List

Posted by leej Support Apr 25, 2016

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:

Lookup Quantity

(Single/Multiple)

Single Line of Text ColumnLookup Column
SingleCascadingLookupItem22;#CascadingLookupItem2
MultipleCascadingLookupItem2A; CascadingLookupItem1B2;#CascadingLookupItem2A;#3;#CascadingLookupItem2B

 

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

Lookup Quantity

(Single/Multiple)

Single Line of Text ColumnLookup Column
Single{Title}{ID};#{Title}
Multiple{Title[0]}; {Title[1]}{ID[0]};#{Title[0]};#{ID[1]};#{Title[1]}

 

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.

ActionPurpose/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:

  1. Using the current item's lookup data you have (e.g. CascadingLookupItem2)...
  2. Find the ID that matches that data in your source (lookup) list
  3. 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.

ActionPurpose/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:

  1. "CascadingLookupItem2A"
  2. "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 ("\A\s+") and trailing ("\s+\Z") 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

I recently decided to take it upon myself to really push Nintex in our company and spread the good word of what it is capable of. I realized that this would require training on Nintex, but also on SharePoint and process in general. Below is what my initial approach has been thus far!

 

SharePoint, and Workflows, and Forms, Oh My!

For some, SharePoint is exciting, but for others it is dreadful! Many users have a predisposition to it and use it for specific purposes. My first step is to discuss SharePoint at a high level and give them a broad view of its capabilities. This allows me to segway into workflows, forms, and process; all of which ties in Nintex. This also lets me understand the level of exposure to these areas and what I need to focus a bit more of or glaze over if needed.

 

Understanding SharePoint and Processes

I like to ask questions and get participation from my audience so it keeps them engaged and thinking. I generally ask the following to get it started:

  • How much exposure to SharePoint do you have?
  • Do you understand the difference between a site and a site collection?
  • What about a page and a site?
  • A library vs a list?
  • What about workflows or processes?
  • What about your customers (other business areas)?

 

This gets them really thinking about just how much they truly understand and how they can improve upon it. I also like to make logical connections; tell a story, or example to drive home your points. I typically talk about my morning routine with my 3 kids and if the process breaks in the morning, well...hungry kids, late to work, you know the drill! Again, this gets them thinking about processes in a different way and that everything has a process, and, again, lets me tie Nintex into the conversation nicely.

 

What is Nintex?

 

Talk about Nintex, seems simple, but really just talk about the company. What they are doing, what they have done, what they can do. One thing I always mention is "Workflow for Everyone", it really drives the idea home to users. Highlight the areas that most interest your company and how they can solve issues you are currently facing. For me, I am pushing mobile platform because we have half of our work force in the field. Giving them the ability to connect to processes in the field (big or small) is a win! I like to hit on product features for those I am training to ease them into it and assure them that they do not need to know code, or request additional software.

 

Showing off Nintex

Always have examples of what you are currently using Nintex for. This shows off the product and provides you a platform to stand on when demoing something. It could be something small to demonstrate functionality, or something complex to show off how easy Nintex is to use and step through. Either way, it gives your audience something to look at and see in action, and not just you talking to them! Also, I like to walk them through a "how to use Nintex" if time allows (or if another session is required), but this lends more towards specific training on the products.

 

Questions

Again, seems simple, but allow time for questions. I found that if you try the "ask anytime" approach, your timing is always off. Bake in 15 minutes at the end of any questions. I also like to have some questions for the audience as well to kick it off. I generally ask what they want to use Nintex for as well as what their customers have been asking for that we could do with Nintex.

 

 

So how do you approach training/teaching users about Nintex? Any "templates" that you use? I like to see how others handle sessions so let us know how we can all improve our own training!

 

Until next time!

Some it's needed to cut the file extension from a file name.

Here are 2 steps to do it.

We 'll use the actions named:

  • Regular Expression
  • Get Item From Collection
  • Log To History List ( to show results )

 

 

 

Used Variables

     Use the following variables for this sample

 

 

Action 1 - Regular Expression Action Configuration

 

Set the String attribute with your filename. In my sample I am getting it from a Sharepoint Column name named "ItemwithExtension"

Then set the String operation to "Extract"

 

The pattern used is [^/]*(?=\.[^.]+($|\?)) with ignore case checked

Finally set a collection variable ( varWithout2 in this case ) to get the results

Finally set a collection variable ( varWithout2 in this cae) to get the results

 

 

 

Action 2 - Get Item From Collection

 

This action is set to extract he first value from the collection variable (varWithout2) and set the result on the variable named varWithoutExtension

 

 

 

 

Action 3 - Log To History List

 

In my sample I wanted to view the results on the History list from a string with the value fhunth.photo.jpg and the results showed is fhunth.photo

 

 

The result

 

I came upon a stumbling block a few days ago during an effort to re-engineer some old InfoPath forms into Nintex for O365 forms. The culprit was the Choice Field Control. It does NOT work the same in both applications.

 

Initial Control Display

I got this when I dragged and dropped the choice control onto the Nintex Form for O365 canvas - and was initially stumped. Where are my choices??

ChoiceDefault.JPG

 

Looking at the control's properties, I knew it had more than one choice.

ChoiceSettingsDefault.JPG

 

So, I figured that it must be a sizing thing - and I was correct. I was able to see all of the choices by re-sizing the boundaries of the control.

ChoiceResize.JPG

 

Vertical vs. Horizontal Layout

That's great, but I wanted the layout of the choices to be horizontal, not vertical. That was easy in InfoPath. Each option was separated by a carriage return. Simply deleting the carriage returns between choices produced a horizontal line of choices. Unfortunately that's NOT the case with Nintex forms. In Nintex, you have to do a few more steps.

 

  1. On the form canvas, double-click the Option control to open the Control Settings dialog window.
  2. From the Arrange choices drop down in the General Section, select Across then down.

    ArrangeChoices.JPG
  3. Update the Number of columns value to match the number of options that are in the control. In my example, I set it to 3 - because I have three options.
    NumColumns.JPG
  4. Click Save.
  5. Re-size the control as needed.

 

Now I the control is formatted the way I want. It's certainly not as easy as deleting carriage returns, but its not too difficult to switch between the vertical and horizontal orientation of a Choice control in Nintex Forms for O365.

ChoiceHorizontal.JPG

 

I hope this article helps reduce your stumbling blocks when re-engineering from InfoPath to Nintex.

Filter Blog

By date: By tag: