I have list which has a lookup column which contains multiple values of the lookup id.For e.x. In a list Department, I have a lookup column called Comments which hold multiple values of lookup ids of several Comments in a Comment list like 1;2;3. I need to retrieve the actual comments in the workflow instead of the lookup id. How can this be done?
3 Answers
In SharePoint Designer, when you pick the column you want from the current item, there should be three drop-down boxes. The bottom one should be "Return field as:" and you can choose to return the Lookup IDs or the Lookup Values.
If that doesn't work, another option is to modify your lookup column so that it includes additional columns from the source list. These columns will then show up on the destination list like "Comment:Body" or "Comment:Title" or "Comment:ID", etc (assuming your source field is called 'Comment'). Then, in SPD you can choose the additional source column as your value instead of the primary lookup value.
-
for some reason, I am not able to add comment in the site so i had to post it as an answer. Hi Omegacron, I tried the first approach but they both gave id's instead of values. In the second approach, I am not able to see the additional column Comment,I can see only Title, Modified, Created,Version,Title(linked to item), ID. I cannot see Comment column... :(– user23706Commented Feb 15, 2014 at 3:04
-
It may be that multi-line text fields aren't available this way. I'll try a test and check that theory. Commented Feb 17, 2014 at 13:33
You're halfway there. The IDs you get in that field represent the IDs of the items in your list that contain the comment field. From there you can do something like this:
int itemID; //I'll leave the task of pulling the ints out from the string in question to you
SPWeb web = properties.Web; //or add your own way of accessing the web you're pulling the list from
SPList list = web["Put the name of your Comments List here"];
SPListItem listItem = list.GetItemById(itemID);
string comment = (string)listItem["Comment"];
Note that when you're indexing to the Comment field of your listItem, you need to use the internal name of the field, not the display name. If you called it "Comment", then you're probably fine; however, if you called it something like "My Comment Section", it's going to be some much longer string with the spaces replaced by something like _ 00x0_, and there's a maximum length to those things as well. What I usually do when traipsing through lists like that is open up a SharePoint Powershell session and review the internal names of the fields there. You might also use the 3rd party program CamlQueryHelper to get that info if you're not good with PowerShell (although I would highly, highly recommend learning to use PS if you're going to program in SharePoint).
-
Thanks for the reply. Unfortunately I am not allowed to do custom coding. I can use only client side object model.How do i proceed ? Also, in a workflow can we do coding like you have shown??– user23706Commented Feb 15, 2014 at 18:29
-
This is C# .NET and would require access to the server to add in a custom page that would grab these values and manipulate them as is shown here. If the OP can only use SP workflow to grab the values, this wouldn't work. It's useful if server access was permitted, though, and may help someone, so I won't vote it up or down.– vapcguyCommented Jul 28, 2021 at 16:39
I know this is an old question, but better would be to convert the lookup to bring in the actual comment values than leaving them as IDs and having to go retrieve them. Seems like a poor design and wouldn't be human-readable by someone looking at the list directly, anyway.
But say you definitely needed to leave them as IDs. You can fill a workflow variable with the values from that lookup field. You can also get each comment into its own variable from the Comments list: comment1, comment2, etc.
For ex. comment1:
List: Comments
Field: Body
Where: ID
Equals: 1
And repeat for the others, changing the ID.
Then, if the lookup contains an ID that matches one of those ("If 'mylookup' contains '1'..."), you can append that comment (i.e. comment1) to another workflow variable (i.e. comments) and return it.
