Unanswered

Using ForAll inside a Filter?

(0) Share

Report

Posted on by CU10040401-0

I have a situation where a data source is getting filtered by multiple conditions - however, my stakeholder for the PowerApp keeps changing what the conditions are. I would like to give them the power to change them using a SharePoint list instead of the app getting updated. The way I imagined this might work is with a ForAll which goes over each condition in the SharePoint list (code below). However, this doesn't work as I get the error 'expected boolean', since ForAll returns a table and not a boolean result (it is, however, a table of boolean results...)

Does anyone know how I can achieve what I'm after here?

Filter(

AddColumns('My Data Source', CreatedDate, Created),

ForAll('My Conditions List', Status.Value = ConditionStatus && CreatedDate < DateAdd(Now(), SLA, Unit))

)

ConditionStatus, SLA, and Unit are a text, number, and text value in the 'My Conditions List' data source. Some example data might be "New", 24, and "hours", or "Assigned", 3, and "days".

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (0)

Sort by

WarrenBelz 156,454 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @AlexanderJLeach ,

Firstly there are a number of things to address here, so I will start with the basic ones. You cannot refer to any function, field or list dynamically in Power Apps, so the Unit would have to be hard-coded

Switch( Unit, "Days", TimeUnit.Days, "Months", TimeUnit.Months", . . . . . )

SLA you could use as DateAdd is expecting a number as can ConditionStatus as Text is expected.

You have also left out the field in 'My Data Source' that the value generated needs to be compared with (or is the Status.Value ? ) Can you please tell me which fields are in which list, with possibly screenshot of the second list.

Was this reply helpful? Yes No
CU10040401-0 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @WarrenBelz ,

Thanks for your reply. Regarding the units, you're not correct... TimeUnit.Days is just an inbuilt string with the value "days", so you can actually just use that string as the unit. For example:

DateAdd(Date(2024, 1, 1), 5, "days")

...returns 1/6/2024.

The fields I'm trying to compare to are Status.Value (you're correct) and Created in 'My Data Source'. Status is a choice column. So essentially, I want to filter by rows which have the corresponding 'ConditionStatus' in my second list, and where the Created date is less than now plus the SLA value. I did neglect to mention in my first post that the SLA values will be negative - thus, making the result of DateAdd in the past - and, any rows that have a value before this date should qualify. For example:

(Status.Value = "New" && Created < DateAdd(Now(), -24, TimeUnit.Hours)) Or
(Status.Value = "Assigned" && Modified < DateAdd(Now(), -24, TimeUnit.Hours)) Or
(Status.Value = "Customer Review" && Modified < DateAdd(Now(), -14, TimeUnit.Days))

I'm just trying to get these conditions brought into the app via a SharePoint list, rather than hardcoding them into the app.

Was this reply helpful? Yes No
WarrenBelz 156,454 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @AlexanderJLeach ,

The TimeUnit function surprises me that it still works with a string as just about every other similar nature parameter does not - I would not be surprised it is stops doing so in the future. Anyway assuming the top bit below works, the rest is structured to keep it Delegable, although the second part needs to output on each one (lists can be of any size) records numbers under your Data Row Limit.

With( { _Date: DateAdd( Now(), SLA, Unit ) }, With( { _Data: Filter( 'My Data Source', Created < _Date ), _Cond: 'My Conditions List' }, Filter( _Data, Status.Value in _Cond.ConditionStatus ) ) )

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications) Visit my blog Practical Power Apps

Was this reply helpful? Yes No
CU10040401-0 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @WarrenBelz,

Thanks again for your reply. A solid try at a solution, but missing a critical element... the 'SLA' time varies for each status; it's not the same for all of them... if you look at the example I had in my last post, it must filter by 'New' items that were created over 24 hours ago... but for 'Customer Review' items, it must filter by rows last modified over 14 days ago. This is the crux of the solution, as they need to be able to provide a different time filter for each status.

As an aside, the string parameters actually work for most things... for example, when using 'SortByColumns' you need to provide the sort order, e.g. SortOrder.Ascending. This however just returns the string "ascending", so by providing "ascending" as a string, you can do the same thing as suppling SortOrder.Ascending.

Was this reply helpful? Yes No
WarrenBelz 156,454 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

@AlexanderJLeach .

The string references have worked for a heap of things in the past, however MS are constantly changing things to require explicit references, so I am now in the habit of using these, even if I have to hard-code using a Switch() statement somewhere, to "future proof" the app.

Anyway back to your question, before I attempt any more code gymnastics, is the desired SLA value in a field in 'My Conditions List' (if so what is the name) or do you have a set of "rules" that can be hard-coded ?

Was this reply helpful? Yes No
CU10040401-0 on at

Like
a
(0)

Report
Copy link

Link copied!

@WarrenBelz Yes, the desired SLA values are in the list 'My Conditions List'. As I mentioned in my first post:
'My Conditions List' has three columns: ConditionStatus, SLA, and Unit which are a text, number, and text value.

Some examples of what might be in this list are:
"New", 24, and "hours"
"Assigned", 3, and "days"
"Customer Review", 14, and "days"

Currently, all of these conditions are hard coded into the app (e.g. my second post)... but as I keep trying to explain, I want to UN-hardcode them, so that the client can change the conditions simply my modifying the SharePoint list, which the PowerApp will then use.

Was this reply helpful? Yes No
WarrenBelz 156,454 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
@AlexanderJLeach ,

I know what you want - I am just trying to see how/if it is possible and needed all the information before I attempted something. The logic below may be reversed at the bottom, but I think this is the direction you need to take - calculate a date in relation to the created date (as this is in the list) and then compare it with the current date. If you do not do this, you will be trying to compare two values in the same record, which is not possible. Also none of this is Delegable, so I have grabbed the newest record set up to your Data Row Limit.

With( { _Data: Filter( AddColumns( Sort( 'My Data Source', ID, SortOrder.Descending ), SLAType, LookUp( 'My Conditions List', ConditionStatus = Status.Value ).SLA, UnitNo, LookUp( 'My Conditions List', ConditionStatus = Status.Value ).Unit ) ) }, Filter( AddColumns( _Data, DateTest, DateAdd( Created, SLAType, UnitNo ) ), Today() < DateTest ) )

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications) Visit my blog Practical Power Apps

Was this reply helpful? Yes No