Filter Get Items - Extract only needed columns / values

(0) Share

Report

Posted on by Bacmanov

Hi there,

Im new to this community.

I have some simple problem (i hope is simple 🙂 )

I have a SharePoint list in which I'm collecting answers on some Form I got.

There are 100 questions with possible answers "Yes" and "No"

The flow is triggered when a new set of answers in the Form are submit.

The issue is that I want to send an email in which are shown / filtered only the Columns that have value "NO"

I'm creating the Item successfully.

I'm getting the ID of the Item successfully.

I'm creating and send the email successfully.

I just cant filter the columns with the values I need so I can put them in the email.

I tried to use ODATA filter, Arrays ... no success.

I couldn't find something similar or something that could help me.

I hope someone from this great community can help.

Thanks

Categories:

Building flows

I have the same question (0)

All responses (3)

Answers (0)

eliotcole 4,363 Moderator on at

Like (0)

Report

Hi, @Bacmanov, I have a feeling that if you're using ODATA then you would need to perform an OR filter on all 100 columns ... which could be a bit difficult! (and may not be possible if there's limits)

~~When you've created the SharePoint list, did you use Yes/No type column types, or did you use Choice column types?~~

~~If you used the latter,~~ Then you may have some luck using a calculated column to do a lot of the work for you here. Ie. if any of these values are 'Yes' then return 'Yes'. 🙂

There's also some complicated things that you could do with Filter and Select (possibly just the latter), but I'm not sure that's wise, here.

I think you may be better off looking at whether you can make this easy with a calculated column ... it might be a long calculation, but it will definitely save you a *lot* of time elsewhere.

Was this reply helpful? Yes No
Bacmanov 4 on at

Like (0)

Report

Hi @eliotcole,

Thanks for your answer, I will try to explain better.

I have around 100 columns. Each of them represents a Question.
I have 1st column - Location, 2nd. User, 3th. Date/Time
From 4th - 100 column are questions which are Choice in the Form submitted but in the SharePoint list they are stored in a Single Line of Text column (I thought it will be easier to filter them as string(i was thinking to compare them with a string / Equal), I can change them if that's an issue)

So I want to take the last stored submission (lets say ID 3, or row 3)
And I want to extract the Column Name + Value (where value = NO) and put that in email

Example:
Dear, this questions have negative result:
Column Name1, Column Name 5, Column Name 10 ...
(* so here will be listed only the Questions with negative result or value NO ("НE") in the SharePoint list.

Thanks for your effort to help!

Was this reply helpful? Yes No
eliotcole 4,363 Moderator on at

Like (0)

Report
OK, then this makes it a bit easier, actually ... just make a calculated column which is a yes/no, and inside it CONCATENATE() all the answers, then FIND() "HE", and make that a boolean with ISNUMBER():

=ISNUMBER(FIND("HE",CONCATENATE(question1,question2,question3 ... )))

Where you will have the 30 question columns referenced there instead of what I have.

The limit for internal amounts in these formula is 30 of a given type, but you can work around this by creating 4 OR statements, so you'll then have:

=OR(ISNUMBER(FIND("HE",CONCATENATE(questions...))),ISNUMBER(FIND("HE",CONCATENATE(questions...))),ISNUMBER(FIND("HE",CONCATENATE(questions...))),ISNUMBER(FIND("HE",CONCATENATE(questions...))))

This *should* then provide a simple boolean value that will indicate if any of the answers were 'no' ("HE")!

If you decide to make the values all boolean, then it all becomes slightly easier, as you'd have 30 NOT() values in an OR:

=OR(NOT(question1),NOT(question2),NOT(question3) ...)

This basically makes each boolean the opposite of what the actual value was, and then the OR() will return true if any of those NOT() values report true (false!).

Which then just drops into 4 sets of those OR() statements:

=OR(OR(NOTs),OR(NOTs),OR(NOTs),OR(NOTs))

If it gives you trouble about putting multiple sets of 30 in one bigger set ... then just make 4 booleans, and you can much more easily use 4 values in a logic flow. 🙂

Was this reply helpful? Yes No