none
Sharepoint Designer: Problems with passing on multiple lookup values.

    Question

  • //TLDR

    Items in 2 Lists (A,B) have a lookup field to the ID of a third List (C). This lookup allows multiple values. Creating a new item in B based on values in A, creates a problem when doing so in workflow 2013. In Workflow 2010 however, it does work. Neither work when the lookup in A does not allow multiple values, but lookup in B only allows at most 1.

    I wish to find out what's causing this and if there's a workaround, both for using workflow 2013 and for allowing only 1 value in B instead of multiple.

    Dear all,

    Example, scenario

    I'm working on a solution where the client can create meetings in sharepoint. Each meeting contains multiple Agenda Items (1 agenda item can belong to multiple meetings). A meeting is unique by its start time+Type. Persons can suggest agenda items, which will be reviewed by the secretary and synced to the actual list with agenda items via a workflow.

    In my (trimmed down) example I have 3 lists.

    Meetings
    - Datetime
    - calc_key (Calculated text creating a unique key based on date+time+Type)
    - Type

    Agenda Items
    - fk_meeting (Lookup to Meetings:calc_key, multiple items allowed for agenda items discussed in multiple meetings) *target
    - Title

    Requested Agenda Items
    - fk_meeting (Lookup to Meetings:calc_key, preferably only 1 item allowed) *source
    - Title

    So in my example users can create a 'requested agenda item' and assign it to a specific (already created) meeting. When the requested item has been approved, a workflow is initiated to create a new item in 'Agenda Items'

    In sharepoint designer, in the workflow, I create a new item in 'Agenda Items', and I can set basically all variables I want.

    Problem

    The problem however, is that I cannot simply set the target fk_meeting the same as currentItem:fk_meeting. This is because one allows multiple values, and the other one doesn't.

    I got it working when I set both fk_meetings to only allow single values. Then I can simply select the new fk_meeting by doing:
    Data source: Current Item
    Field from source: fk_meeting
    Return field as: Lookup Id (As Integer)

    Somehow, this doesn't work when I select the target fk_meetings to accept multiple variables, I cannot do this anymore. The 'Return field as:' only lets me select 'Lookup Value (as Text)'. When I set both source and target fk_meetings to allow multiple instances, sharepoint designer allows me to select Lookup Id's, but it only allows me to select them comma delimered, whilst the target is semicolon delimered.

    So, what would be the most elegant solution to solve this? I would like the target fk_meetings to allow multiple values. And the source fk_meetings to allow only 1 value. 

    Solutions tried thus far

    The only thing I thought of was storing the text-representation of the source fk_meetings in a variable, and then somehow extracting the ID out of it, storing it as a Number and then passing it on to the target fk_meetings.. but it's not really nice and also I have not really found an option in sharepoint designer that lets me do that. I have also tried allowing both source and target multiple values, and changing the comma to a semicolon and passing that on but that also doesn't work unfortunately.

    Any help is much appreciated,

    Best,

    Martin


    signature




    • Edited by lmartinl Thursday, December 01, 2016 12:15 PM tldr
    Tuesday, November 29, 2016 3:04 PM

Answers

  • I guess it´s solved by changing the user requirements and follow the best practises described here. Instead of finding  workarounds for errors I run into with sharepoint. 

    1) Lookups now look up to the ID of a Meeting instead of a calculated column 
    (Recurrent meetings are no longer supported, as a recurrent events share their IDs)

    2) Lookups now are no longer multi-value lookups but single value Lookups
    (It's no longer possible to have 1 agenda item link to multiple meetings)


    signature

    • Marked as answer by lmartinl Monday, December 12, 2016 4:45 PM
    Monday, December 12, 2016 4:45 PM

All replies

  • I guess the first thing to know is that making a Hash of the Meeting DateTime and Type is a waste of effort, and makes a somewhat useless calculated field. instead, the Meetings list has an "autonumber" ID, and you can simply refer directly to it.  (Same goes for any list, as they all have an arbitrary ID field associated to every row.)

    From there, my guess is that many of the hoops that you have jumped through are a result of trying to cobble together a solution based on a calculated unique key.  So, take a few minutes to refactor your solution based on all the ID fields in the lists, and see if that nets some greater progress and less dead ends.


    Steve Clark, MCTS
    Easy Bins Roll-off Dumpster Rentals in Northern VA
    Specializing in:
    Driveway-sized, roll-off dumpster rentals in Fairfax VA | Dumpster Rentals for Junk Hauling in Springfield VA
    Roll-off Rental Dumpsters in Annandale, VA

    Tuesday, November 29, 2016 5:15 PM
  • Thank you for your answer.

    I agree that technically its a nicer solution.

    The reason I went for this solution is that this way a user can manually change the lookup link because it's really easy to find the right meeting based on datetime+type, whilst for selecting the right ID, afaik it would either take additional steps (like first finding the correct meeting and noting down its ID) or (java-) scripting to get the same functionality. 

    To me creating this hash was less effort than having to create dedicated interfaces for each user action, like assigning an agenda item to a new/different/additional meeting.

    So in this situation, how would you go about this? I'm quite new to sharepoint, so not overly familiar with sharepoint's best practices (coming from rdb). A link would more than suffice


    signature


    • Edited by lmartinl Wednesday, November 30, 2016 5:50 PM
    Wednesday, November 30, 2016 5:49 PM
  • Well, I just gave you a best practice, which you have no interest.

    So, you will now spend the rest of your SharePoint life trying to hack around a hack, creating more hacks along the way.


    Steve Clark, MCTS
    Easy Bins Roll-off Dumpster Rentals in Northern VA
    Specializing in:
    Driveway-sized, roll-off dumpster rentals in Fairfax VA | Dumpster Rentals for Junk Hauling in Springfield VA
    Roll-off Rental Dumpsters in Annandale, VA

    Wednesday, November 30, 2016 6:18 PM
  • * Look below in the message for the update before you read all this.

    I think you misunderstood my reply. I thank you for taking the time to reading my message. There is no point in asking for advice if I'm not willing to follow it. I merely tried to explain why I started 'hacking' around in the first place. But it really is not what is causing the problem.

    For the sake of the argument I have linked everything based on IDs. All lookups are now done to ID values.

    So, in the following case everything works fine:

    1 Agenda Item -> 1 Meeting
    1 Requested Item -> 1 Meeting
    If I select a Requested Item, start a workflow, in this workflow create a new Agenda Item and set the values in the new Agenda Item equal to the ones in Requested Agenda Item, everything works fine. When passing on the lookup value of fk_meetings, I can do Data source: Current Item, field from source: fk_meetings, Return field as: Lookup Id (as Integer). I can also select Lookup value, but I want the ID. So this does exactly what I want, only I want to allow multiple values for fk_meetings in 'Agenda Item'

    Now if I allow fk_meeting in 'Agenda Item' list to have multiple values, and fk_meeting in 'Requested Agenda Item' to have 1  value, things go wrong.

    #####

    1 Agenda Item -> * Meeting
    1 Requested Agenda Item -> 1 Meeting

    All I can select in the 'Return field as:' windows i: Lookup Value (text). This is not what I want, and if I run the workflow, it hangs on 

    Suspend this workflow 
    Activity in progress 
    
    Retrying last request. Next attempt scheduled in less than one minute. Details of last request: HTTP BadRequest to https://xxx/meetingsHR/_vti_bin/client.svc/web/lists(guid'7b523237-b486-46bf-b825-410d126a7da1')/Items Correlation Id: 15537cbf-f978-0c22-b5e6-2a8d8c24ce80 Instance Id: b8039083-a30c-4036-81b9-5348899bd137

    #####

    So. Now I try:
    1 Agenda Item -> * Meeting
    1 Requested Agenda Item -> * Meeting

    Now, I suddenly can 'Return field as:' 

    1) String
    2) Lookup IDs, comma delimited
    3) Lookup Values, comma delimited

    Hooray, that looks right. But, running 'Lookup Values, comma delimited' this again causes the workflow to remain in Internal Status: Suspended. Error message:

    Suspend this workflow 
    Activity in progress 
    
    Retrying last request. Next attempt scheduled in less than one minute. Details of last request: HTTP BadRequest to xxx/meetingsHR/_vti_bin/client.svc/web/lists(guid'7b523237-b486-46bf-b825-410d126a7da1')/Items Correlation Id: 15537cbf-f978-0c22-adc8-c6e646da4145 Instance Id: 314f5aff-0388-4cb1-a7cd-7a0c4422a65b Retry now 

    This happens when I select just 1 ID in (req. agenda item:) fk_meetings, and also happens when I select multiple IDs for fk_meetings in 'Requested Agenda Items'.

    The only options I can think of are

    1) I'm doing something that is not meant to be done in Sharepoint
    2) The comma delimeter screws up, as the fk_meetings in 'Agenda Items' is semicolon delimited. It's strange that this also occures when only passing on 1 lookup ID.
    3) I'm missing something obvious

    ######################### /edit: Update 1.

    Right. So, just for giggles I tried this with a Workflow 2010. Guess what, it works (mostly)

    Agenda Items: fk_meetings allows multiple values
    Requested Agenda Items: fk_meetings allows multiple values

    When I put in 1 ID for fk_meetings, and select return field as ID, it copies it perfectly. When I put multiple IDs, and i select return field as ID, comma delimited it finishes the script but only copies over the first ID. When I select return field as text, it behaves exactly as I want.

    So

    1) Is there an option / workaround I can get this to work with Workflow 2013. And is this intended behaviour or a bug

    2) Is there a way I can return field as Lookup IDs, semicolon delimited? Or is there no downside to have it returned as text?

    I'll wait for a bit more info before I mark this as an answer because although it works.. I wish to know what exactly is going on here.


    signature




    • Edited by lmartinl Thursday, December 01, 2016 3:06 AM
    Thursday, December 01, 2016 2:28 AM
  • Anytime that I require multiple children items, then I put them in a child list. I never use MVFs. (Multi-Value Fields). Those things are the worst thing ever invented, and should never be used. Ever.

    So, I would have a [Meeting] list and a [MeetingAgenda] list, where [MeetingAgenda] links to [Meeting] via the [Meeting].[ID].


    Steve Clark, MCTS
    Easy Bins Roll-off Dumpster Rentals in Northern VA
    Specializing in:
    Driveway-sized, roll-off dumpster rentals in Fairfax VA | Dumpster Rentals for Junk Hauling in Springfield VA
    Roll-off Rental Dumpsters in Annandale, VA

    Thursday, December 01, 2016 3:41 PM
  • I guess it´s solved by changing the user requirements and follow the best practises described here. Instead of finding  workarounds for errors I run into with sharepoint. 

    1) Lookups now look up to the ID of a Meeting instead of a calculated column 
    (Recurrent meetings are no longer supported, as a recurrent events share their IDs)

    2) Lookups now are no longer multi-value lookups but single value Lookups
    (It's no longer possible to have 1 agenda item link to multiple meetings)


    signature

    • Marked as answer by lmartinl Monday, December 12, 2016 4:45 PM
    Monday, December 12, 2016 4:45 PM