Flow parsing a multiselect lookup (based on another list item titles) & create a variable

[Anonymous]

Hi everyone,

 I began to use Flow recently, to automate few things and I stumble accross a very nice challenge for which I did not found documentation (official or forum).

 May I get your help ?

Here is the Challenge ! 🙂

 I have two tables (simplified here to the essential):

Table 1: Processes

 In this table, I list (as a title column) the processes name.

Then I have a look-up column named "used data" to select the data related to this process (not mandatorily filled and it is a multiple selection allowed look-up).

Check is a multiple line column - to inser via Flow something like: not working when I build my flow and it fails 😉

Table 2: Data

In this table, I list (as a title column) the data related to all processes.

Then I have a look-up column named "processes linked" to select the processes (it is a multiselection allowed look-up column) and somehow tag my data.

It's the most user friendly way I found (I'm totally not fond of metadata), as it allows in one click to check that it's the right process. Reciprocally from processes I can click on a data to check on which other processes I use it (and in full version, the description of the data to check that's it shall indeed be used here).

To finish the description, the column check is a multiple line of text for the same reason as above or just to launch the flow by modifying an element.

My goal:

I would like that each time I (or a user) create a data or modify a data (in table 2), the flow will:

- for each Process Source (in table 1), so for each line;

- check each line of the Data used table (table 2);

- and each time, for each line, [Process Source] is equal (or is contained) in [Processes linked];

- the flow take the value of 'Data used' of the corresponding line, stores it in a variable;

- then, when it has looped the whole table 2 (data used) lines, it will (through a compose connector) generate the content of the table 1 (processes) column name 'Used Data'

The goal is to allow people to select by themselves, but also to autocomplete it through the Flow I can ensure that any mistake or omission by a user is corrected. It lighten the creation or update process too.

The result should be something like - with the current screens:

Process1 | Data1; Data2; Data3; (still a lookup column, with multiple selection) 
Process2 | Data2;
Process3 | Data4;

Process4 | (empty in current situation

I tested to check if one can inject a variable into a look-up field, it worked (the same way one can bulk edit a list and write down the allowed choice in a look-up column). But I don't succeed in setting up correctly the variable 

I tried several architecture (thanks to several sources I can post below if authorized) but can manage it yet.

The best I get was, for instance with data 2, the variable output was 'Data2; Data2;', because the flow processed line by line the data table instead of all the line of the data table on the basis of the process name before rendering a result.

Thank you in advance ! 🙂

[Anonymous]

The documentation I used to try - among many other - but that were either the most instructive or the most close to what I try to achieve:

• https://docs.microsoft.com/fr-fr/flow/
• https://docs.microsoft.com/fr-fr/flow/data-operations
• https://flow.microsoft.com/en-us/blog/advanced-flow-of-the-week-when-a-new-item-is-created-automatic...
• https://powerusers.microsoft.com/t5/Building-Flows/Comparing-Two-Lists-and-Finding-Differences/td-p/...
• https://veenstra.me.uk/2018/03/19/microsoft-flow-the-hidden-gems-are-you-aware-of-all-of-these/
• https://veenstra.me.uk/2018/07/10/microsoft-flow-shed-some-light-on-arrays/
• https://veenstra.me.uk/2018/12/18/microsoft-flow-filter-data-in-an-array/#comments

My main issue is how to correctly set up a variable that I could then inject to my target field (as look-up column accept to receive variables through Flow - I tried at least string or array (I don't remember exactly));

Any thought ? I'd like to try Array/intersection but fails to set it up.

[v-bacao-msft]

Hi @Anonymous,

To fill the lookup column with multiple selection of this type column, you need to use an array, treat each selected option as an object and store it as an array element.

You could use Append array to variable action to append the following types of objects to the array.

{
    "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
    "Id": 4,
    "Value": "4"
  }

The first property is fixed, the second property is ID of the current item, and the third property is the value of the current field.

Finally, the entire array variable is configured in the lookup field.

This is the basic way to populate the value of the lookup column field, which you can configure in conjunction with your scenario.

Best Regards,

[Anonymous]

Hi @v-bacao-msft and thank you !

That confirmed my guess 🙂

As my nickname says, I'm a "beginner": the only things I know in SP and flow are those I discover by myself 🙂

Consequently, would you mind to show me some screen of how to build the flow ?

I had issues setting up the variable with so many "apply to each" action that I'm not sure to put in the correct order.

I would like to post you some screen of what I tried to built, but the name of the actions would be in French (my organisation's O365 is full french and some translations are kinda weird and does not reflect the english name at all) !

I'll try tomorrow to rename them in english and post what I can achieve;

Thank you for your help 🙂

[Anonymous]

Hi @v-bacao-msft

I appologize, I was less available than I thought I would be.

Here below are the screens of my current flow.

As my company SP / Flow is in French, I translated the trigger and actions into English to help 🙂

And today, it does not even trigger 🙂

EDIT: it did a bit later, and here is the result:

So it did not gave the result I intend to obtain 😞

I can understand what you wrote, but I have absolutely no idea on how to translate it through the GUI,

Could you show me a bit more how to do it please ?

Thank you in advance 🙂

[Anonymous]

Hi @v-bacao-msft, Barry,

Would you mind to give me a bit more details on how to proceed please ?

Besides, my array is not "fixed", in my example I gave it with 4 data, but the list may be extended, does it still work ?

Thank you in advance,