Skip to main content

SharePoint 2019

Version: 5.2.10.0 - International

I am having difficulties populating a SharePoint list lookup column with multiple choices that orignate from a NAC form. I can get the first value to populate. But as soon as I add another value, it only populates the first and does not give an error.

Requirements:

  • Reason for NAC form and internal SharePoint Nintex form is that we need to allow both external users and internal users to complete the form. The final, “full” list is the SharePoint list.
  • Original data comes from a NAC form. That form doesn't use the same lookup list as the SharePoint form and only uses choices.
  • I need to convert these NAC form choices to the multi-choice lookup column in the SharePoint list so workflows will run on the new item.  (I want one place that workflow runs from; so by sending all data to the SharePoint list, I can run the workflows.
  • When an admin views any item from the SharePoint list, it’s a Nintex form that will show the selected value(s) in the lookup column.

Steps:

  1. I’m applying the comma-delimited multi-choice values from the NAC form to a mutli-line of text  column in the SharePoint list.
    • I was going to use a Nintex workflow on the SharePoint list item to “select” (enter) all multi-choice values on the lookup column based on the selections of the NAC form field.  
    • The output of the NAC form is the choice in double quotes, separated with a comma and in square brackets.
      Example: /"Career Path Planning","ESL (English as a Second Language)","Transportation"]
  2. I know the SharePoint list lookup column needs the ID as well, so I have the workflow replacing the original NAC form text so the final output text is what the SharePoint list column needs to use in the “Update List Item” or “Update field” workflow action. I can’t seem to get the syntax correct for these actions to work.


Some examples I've tried:

//works using this data and "As String(Default)"
1;#Career Path Planning

//partially works using this data and "As String(Default)" - only selects first item
1;#Career Path Planning,2;#Dependent Care

//partially works using this data and "As String(Default)" - only selects first item (added ;# to end)
1;#Career Path Planning,2;#Dependent Care;#

//partially works using this data and "As String(Default)" - only selects first item (added ,;# to end)
1;#Career Path Planning,2;#Dependent Care,;#

//partially works using this data and "As String(Default)" - only selects first item (added ,space to end)
1;#Career Path Planning,2;#Dependent Care,

//partially works using this data and "As String(Default)" - only selects first item (added ;#space to end)
1;#Career Path Planning,2;#Dependent Care;#

//partially works using this data and "As String(Default)" - only selects first item (added space to end)
1;#Career Path Planning,2;#Dependent Care

//partially works using this data and "Choices, Comma Delimited" - only selects first item (added space to end)
1;#Career Path Planning,2;#Dependent Care

//Using "Lookup Values, Comma Delimited" - Error: Coercion Failed: Unable to transform the input lookup data into the requested type.
1;#Career Path Planning,2;#Dependent Care

//Using "Lookup Ids, Comma Delimited" - Error: Coercion Failed: Unable to transform the input lookup data into the requested type.
1;#Career Path Planning,2;#Dependent Care

//did not work using this data and "As String(Default)"; nothing entered in lookup field and no error
1;2

//did not work using this data and "Lookup Ids, comma delimited"; error: Coercion Failed: Unable to transform the input lookup data into the requested type.
1;2

//Using "Lookup Ids, comma delimited" - only selects first item
1,2

//Using "Lookup Ids, comma delimited" - only selects first item (comma at end)
1,2,

//Using "Lookup Ids, comma delimited" - only selects first item (comma space at end)
1,2,

//Using "Lookup Ids, comma delimited" - only selects first item (comma space comma at end)
1,2, ,

I have searched all over the internet for this answer. I know it’s out there!!!

Hi,

What version of SharePoint are you using?  I’ve successfully tested this in SharePoint online but if you’re using a different version of SharePoint then you might get a different result.

 

TL:DR: just use a text string with the lookup list item ID delimited by a semi colon e.g. 1;2

 

Full answer:

I created a data lookup to your lookup list in SharePoint.

That means you always know what your IDs are - even if you make changes to that list. 

 

 

 

You can then use a multi-select control in your form and refer to that list:

 

I display the name of the job and save the ID as per the picture above.  If you don’t want to use a dynamic lookup like this then you can hard code the display names and values as per the picture below but just be aware the forms developer will need to update these any time one of those options changes.

 

Now you’ve got the form all set up, those choices will come through similar to the text string you mentioned above.  e.g. if they selected Career Path and Transportation it would come through as l“1”,”3”]

 

You want to change that to 1;3 with a little bit of string manipulation - I used the modify string action a few times to do this:

 

 

 

 

 

Now you can save that string (“The list of items” in my example) to your SP list.

 

Cheers,

Chris

@Chris_Ben , Thanks for your reply.

I’m using SP 2019. This is basically what I did. I didn’t use the connection on the NAC form to the SP lookup list because it didn’t provide the list item ID anyway. So, via NAC workflow, I get the etext,text2,text3] result.

In the SharePoint list workflow I then use similar Replace functions to get just the ID of the item in the string to have the final result of 1;2;3 (as an example). The output variable of the final Replace function is EBG_supportsLtext and it’s a Single line of text.

 

Per one of my test text strings about, I used 1;2 to try to populate the SP list lookup column and it didn’t work. In the Set field value action, the “Select return type” option is set to "As String(Default)". Nothing is entered in the lookup field and there was no error. I also tried the "Lookup Ids, comma delimited" and got the error, “Coercion Failed: Unable to transform the input lookup data into the requested type.”

When configuring the Set field value action in the SharePoint workflow, here is where I use the “Select return type” setting:

 

I don’t have a SP on-prem environment to test this in I’m sorry so I’m not going to be able to contribute much more but for the others who are reading, can you provide more context on your setup?

 

My understanding is for internal users, they fill in a Nintex for SharePoint form which automatically updates the list and all the lookup values so the workflow only needs to cater for the submissions that are received via the Nintex Automation Cloud (NAC) form.

 

When the data is captured via a NAC form, how do you populate your SP 2019 list?  Are you using NAC to update your SP 2019 list (via the Gateway) or are you using Nintex Workflow installed on SharePoint?  Your screenshots and description suggest the latter but I’m interested in how the data gets to the SP list in the first place and if it is via a NAC workflow, why not update the list lookup column via the same workflow?

@Chris_Ben  I appreciate your attempts to help!

Yes, there is actually a “Run If” action in the SP list workflow that has all of the Replace functions and “Set Field Value” action. (The workflow does other things like sending a notification email.)

When capturing the NAC form data, I populate the SP list using the “Create an item
SharePoint on-premises” action (Gateway). I can’t update the list lookup column via the NAC workflow because the data isn’t in the format that the SP lookup column needs. No error; just the NAC data doesn’t populate the SP lookup field.)

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings