Filter list by array and set values

[PBarrett]

Condition: 2 SP lists;

• "Ticket" list has a choice column, "Type", that can contain multiple values (A,B,C);
• "Resource" list has numeric columns whose names match those values from "Ticket" list "Type" column (Column A, Column B, Column C).

When an item is created in the "Ticket" list, I need to output an array of items (ID and Title) from the "Resource" list that have values of more than '.7' in the columns that are indicated in a "Ticket" item's "Type" column.

[VictorIvanidze]

Show your flow.

[PBarrett]

The only flow I can make that I believe would work involves multi-layered conditions or Switches that give up to 24 possibilities. Type column has 4 choices, and there are 4 columns in the Resource list with values ranging from 0 to 1 (decimals included). That's why I came here. I don't have anything remotely that works. I can Get Items and set array variables, and filter arrays all day long, but I don't know how to filter items based on specific columns named by a different array.

[wskinnermctc]

If the Type values from your Ticket List are the same name as the columns from your Resource list; I think you could get away with filtering a Get Items (Resource List) by the outputs of the Type values?

Are the names the same?

[PBarrett]

Making the names the same is easy enough, the problem is, each "ticket" item is unique in that it can have between 1 and 4 values in the "Type" column. I need that checked against all items in the Resources list, checking columns that match the values in the ticket. So it could check 1, 2, 3 or all 4 columns.

[grantjenkins]

A few questions to clarify what you're looking for.

1. You said the Choices are exactly the same as your Column headers, but in your question, you put (A, B, C) as your choices and (Column A, Column B, Column C) as Column headers. Can you confirm the exact values?
2. Did you want to sum up the values across the columns and if the total was more than 0.7 you would want to return that row, or just if at least one of the values were more than 0.7?
3. You mentioned When an item is created as the trigger. Just confirming you want to generate a list of all records as soon as you create a new item. What if you update one of the items after it's created?
4. Is there a reason why you are using Columns for each choice, and not individual items that contain the same choices as your Ticket list? See example below:

[PBarrett]

1. I can confirm. I'm in the early stages of this and can re-create columns as needed. The strict name of the column(s) will be the same as the value(s) in the ticket.

2. Each of the 4 columns are already calculated values based on over a dozen other columns.

3. Yes, as the resources list will be much smaller than the ticket list, that's okay that it pulls every item. It won't matter if the ticket gets updated, as this flow is for notifications and initial assignment.

4. It is simple for our brains to go, "okay the ticket has type B and D, let me look for any name in the resource list that has a score higher than .7 for B and D (which is calculated by other columns' data), and output those names to a list and email that or just assign them. If I did what you proposed, I would have 4 entries for every resource. Each resource has an email address, phone number, links, comments, notes, etc.

[PBarrett]

Update:

I tried to use Filter Array to filter the get items (because Get Items can't be filtered by calculated columns), and It turns out, calculated columns' values are strings, not integers or floats. New Solution: use Excel to calculate first, then output to a Number Column, which can be filtered.

[grantjenkins]

Hopefully this is what you're looking for.

For this example, I'm using two SharePoint Lists.

Tickets List has a Title column and a Choice column set to multiple select.

Resource List has a Title column, 4 x columns of type Number, and 4 x columns of type Calculated that just display the values from the Number columns. The key for making this work is that for your Calculated columns, you will need to create them without spaces in the names, and then go back in and add the spaces. This means that the internal name of the columns won't have spaces.

The settings for Column A are below. Note the internal name of the column (ColumnA) at the end of the URL.

See full flow below. I'll go into each of the actions.

When an item is created is fairly straightforward.

Select will extract out the Ticket Types (Choice values) and concatenate them into individual filter conditions that we will embed into our XPath expressions later. The expression used in the Select is below. It also removes any spaces between the values, so they match the internal column names in the SharePoint List.

concat(replace(item()?['Value'], ' ', ''), ' > 0.7')

Get items returns all the items in the Resources List. As you know, due to your fields being Calculated columns we can't filter directly in here.

IMPORTANT: Note that Get items will only return the first 100 items by default. If you want to return more than this you can go into Settings, turn on Pagination, and set a Threshold to the number of items you want. This should be a number larger than the number of items you would expect your list to have over the next few years.

Select Results is where the magic happens. It uses XPath expressions and uses the filters you created in the initial Select. Below are the expressions used.

//From
xpath(xml(json(concat('{"root": { value:', outputs('Get_items')?['body/value'], '}}'))), concat('//root/value[', join(body('Select'), ' or '), ']'))

//ID
xpath(item(), 'string(//*/ID/text())')

//Title
xpath(item(), 'string(//*/Title/text())')

If we ran the flow, selecting Column A, Column B and Column D, we should only see Beta returned since it has 0.75 in Column B.

Selecting Column A, Column B and Column C would return Alpha and Beta.

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.

[PBarrett]

I will give this a try, in the mean time, I have the person inputting items into the list do the calculation prior to list item generation (removing calculated columns entirely), so a simpler flow is being used until I can get this tested.