none
SharePoint Designer 2013 - Extracting values from a multi-value enabled lookup column into a dictionary as seperate items

    Question

  • Hi All,

    I have a requirement to take a lookup column that allows multiple fields i.e. "New Zealand;Australia;USA", and using a SPD 2013 workflow extract, split and then insert each country into a Dictionary as separate items so I can iterate over them and update/add items to another list based on each split country.

    Looping is fine with the new functionality available in 2013 as is creating the new list items, my question is how to split out each country and insert into the Dictionary as a separate item?

    Cheers Folks

    Monday, August 26, 2013 1:46 AM

Answers

  • Ok Solved it.  (See workflow at bottom)

    I actually did not need to use a dictionary. I could loop through the string returned from the look up column, and looking for comma's, look for one of 3 cases:

    1. no comma but string is non-empty ( 1 country defined)

    2. at least one comma so there is at least two or more countries so loop

    3. (in the loop) we have consumed all the commas so we have found the last country. 

    Things to note:

    1. "Find string in string (output to Variable:index)"  will return -1 if doesn't find the searched for string.

    2. In the opening statement "Set Variable: Countries to Current Item:Destinations" set the return field as  "Lookup Values, Comma Delimited" or else you will get back column item meta-data as well as the actual values you are after.

    If any once has any questions just post a reply and I will try and answer them.

    Cheers

    Stage:Countries Workflow
     Set Variable: Countries to Current Item:Destinations
     Step: If we have at least one country
      If Variable: Countries is not empty value
       Find , in Variable: Countries (Output to Variable: index )
       If Variable: index is less than 0
        Step: There is no comma so there must only be one country
        Do something sensible with "Countries"
        
       Else
        Set Variable: loop to Yes
        Loop: There is at least one comma, hence at least two countries so we will loop over them
         The contents of this loop will run repeatedly while: Variable: loop equals Yes
          Copy from Variable: Countries , starting at 0 for Variable: index characters (Output to Variable: substring-country )
          Do something sensible with "Substring-countries"
          Replace [%Variable: substring-country%], with   in Variable: Countries (Output to Variable: Countries )
          Find , in Variable: Countries (Output to Variable: index )
          If Variable: index is less than 0
           Step: We have found the last country, so do somthing with it and then stop
            do something sensible with "Countries"
            Set Variable: loop to No
       
    Transition to stage
     Go to End of Workflow

    • Marked as answer by BruceB001 Wednesday, August 28, 2013 9:35 PM
    • Edited by BruceB001 Wednesday, August 28, 2013 9:51 PM
    Wednesday, August 28, 2013 9:28 PM

All replies

  • Hi,

    Base on the description, you want to extract values from a multi-value enabled lookup column into a dictionary as separate items using SharePoint Designer 2013.

    I try to reproduce the issue as follows:

    1. Create a custom list named Lookup1, add items, Title: New Zealand; Title: Australia; Title: USA.
    2. Create a custom list named Lookup2, create a lookup column named lookup.
    3. Create a custom list named Lookup3, create a column named country.
    4. Create a workflow associated to Lookup2.
    5. Add actions:
    6. Add item to Lookup2, set lookup column to Australia; New Zealand; USA.
    7. Start workflow, then three items would be added to Lookup3.

    More information:

    Understanding Dictionary actions in SharePoint Designer 2013: http://msdn.microsoft.com/en-us/library/jj554504.aspx

    Read Values From Multi Value Lookup And Choice Field In SharePoint List:http://www.ashokraja.me/post/Read-Values-From-Multi-Value-Lookup-And-Choice-Field-In-SharePoint-List.aspx

    Best Regards,

    Linda Li

    Tuesday, August 27, 2013 9:27 AM
  • Thanks for your reply Linda.

    Perhaps I should have been clearer but your solution assumes that there will always be three items. We are dealing with a look up column that the user is free to add as many items (Countries in this case) as they like.

    So we have to determine how many items there are in the lookup column first so we can create an index and loop dynamically. If this is possible then your solution will work.

    Does anyone know how to count the items in a multi-value enabled lookup column using SPD 2013?

    Remember this is being done in SharePoint Designer so the solution is not being done in code.

    Cheers

    Tuesday, August 27, 2013 8:59 PM
  • Ok Solved it.  (See workflow at bottom)

    I actually did not need to use a dictionary. I could loop through the string returned from the look up column, and looking for comma's, look for one of 3 cases:

    1. no comma but string is non-empty ( 1 country defined)

    2. at least one comma so there is at least two or more countries so loop

    3. (in the loop) we have consumed all the commas so we have found the last country. 

    Things to note:

    1. "Find string in string (output to Variable:index)"  will return -1 if doesn't find the searched for string.

    2. In the opening statement "Set Variable: Countries to Current Item:Destinations" set the return field as  "Lookup Values, Comma Delimited" or else you will get back column item meta-data as well as the actual values you are after.

    If any once has any questions just post a reply and I will try and answer them.

    Cheers

    Stage:Countries Workflow
     Set Variable: Countries to Current Item:Destinations
     Step: If we have at least one country
      If Variable: Countries is not empty value
       Find , in Variable: Countries (Output to Variable: index )
       If Variable: index is less than 0
        Step: There is no comma so there must only be one country
        Do something sensible with "Countries"
        
       Else
        Set Variable: loop to Yes
        Loop: There is at least one comma, hence at least two countries so we will loop over them
         The contents of this loop will run repeatedly while: Variable: loop equals Yes
          Copy from Variable: Countries , starting at 0 for Variable: index characters (Output to Variable: substring-country )
          Do something sensible with "Substring-countries"
          Replace [%Variable: substring-country%], with   in Variable: Countries (Output to Variable: Countries )
          Find , in Variable: Countries (Output to Variable: index )
          If Variable: index is less than 0
           Step: We have found the last country, so do somthing with it and then stop
            do something sensible with "Countries"
            Set Variable: loop to No
       
    Transition to stage
     Go to End of Workflow

    • Marked as answer by BruceB001 Wednesday, August 28, 2013 9:35 PM
    • Edited by BruceB001 Wednesday, August 28, 2013 9:51 PM
    Wednesday, August 28, 2013 9:28 PM
  • I didn't catch this the first time, but in this step:

    Replace [%Variable: substring-country%], with   in Variable: Countries (Output to Variable: Countries )

    The comma needs to be a part of the string that you are looking for and replacing (otherwise the remaining string will always start with a comma and send the loop to infinity... and beyond)

    Wednesday, July 02, 2014 10:22 PM