Unanswered

NOT Function

(1) Share

Report

Posted on by Datenshi

I have a SharePoint list and dropdown in the Canvas app.
I want to get all the records except those containing "Dev".
The list is going to have more than 2000 records.
Is there any alternative for NOT/! function?

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (0)

poweractivate 11,078 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
I think the delegation issue is because containing (i.e. "something" in column) is not delegable for the SharePoint List data source - and that would be whether or not you are excluding or including records.

If you are using in right now,
try like this

//adjust the below as needed ForAll(SharePointList,someText in myTextColumnName)

I generally don't recommend using ForAll on the whole data source because it could be a performance issue depending on the scenario, however, I am not sure of another way to get around this limit, or at least not a simple way, so if it looks like it works and you don't really notice a problem, you may want to consider it.

If you are concerned about performance, another way involves rethinking your strategy and not having to use in. Another strategy involves intentionally ignoring any delegation issues and then trying to pull the records no more than X records at a time, i.e. pulling the records in batches of no more than the delegation limit (in your case, 2000 at a time). Since both of these concepts are substantially more complex than the one-line formula above and depend on your scenario, I won't go into them for now, you can try to think of them in general too in case it helps though.

Was this reply helpful? Yes No
Datenshi 12 on at

Like (1)

Report
Copy link

Link copied!

I'm Using the NOT function.
Distinct(Filter('PersonnelList-', Active = Not("Yes")),'EMPLOYEE NAME').
Here "NOT" is a non-delegable function for SharePoint.

Was this reply helpful? Yes No
poweractivate 11,078 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
@Datenshi

Yes, you are right, NOT is also not delegable for SharePoint data source.
Then to keep the query delegable, you may need to only get those records which match, even it means stating all possible matches.
So instead of

Active = Not("Yes")

you can use:

//example Active = "No"

I am not sure of another way.

You can also try the below to bypass the limit and see if it works. I don't recommend ForAll on the whole data source, but if you have to use it and it works and you notice no issue:

(Distinct(ForAll('PersonnelList-', Active = Not("Yes")),'EMPLOYEE NAME').

Besides the above, you should look at other approaches such as pulling the data source records in small batches at a time, combining them into a Collection (note that with respect to Collections and variables, while the Collection itself may contain more total Records than the delegation limit, the actual single calls to Set, Collect, and ClearCollect themselves are subject to the delegation limit and these do not give any delegation warning, so make sure that each time these are called, if you use them, the batch they are called with is within the delegation limit each time).

The approach for doing this in batches is more complex though, so if you can use ForAll for now, even if it is discouraged to do so generally, and see if it works for you and if it does so without you noticing much issue.

Besides the above, the only other way is to make sure to do whatever you possibly can to make the query delegable - that means even rethinking the whole strategy and rewriting the formula to only match for all possible column values in the Filter (as tedious as that may be, but if it works correctly, this would be one of the preferred solutions).

Another solution is to change the whole data source to one that NOT is supported in. For example, you can try to use Dataverse, . This would be preferred to using ForAll in SharePoint, and it may be the most preferred solution for you if you know you must use the Not function- however, I am aware this solution may not be reasonable as it could take considerable time to setup the whole data again in a new data source and migrate the whole thing there too. Considering that, if ForAll works, and it works quickly, and you don't notice any issue, any theoretical internal performance issues could be ignored (for now - because although you may have to face these issues eventually, depending on the scenario over time - you can't be sure you have to worry about it, so you may end up not even ever having to worry about it in your case), in favor of not having to switch the data source, and not having to take considerable time avoiding to use the Not function or trying to use a complex approach to split it up in batches with complex formulas, etc.

See if it helps @Datenshi

Was this reply helpful? Yes No
RandyHayes 76,299 Super User 2024 Season 1 on at

Like (1)

Report
Copy link

Link copied!

@Datenshi

There is no other way to delegate those types of criteria. You need to plan for this in your data.

Getting the NOT of a field is not supported for delegation. So, instead of that, again, it can be accounted for in the data.

For example, changing your yes/no to numeric - 1 yes, 0 no. Then if you want all the Not Yes, then you can filter for 0.

@poweractivate keep in mind that delegation and record limit are two different things. EVERY function in PowerApps is limited to return only the maximum number of records specified in the record limit (2000 max).

So functions like ForAll, Distinct, AddColumns, etc...will all NOT report a delegation warning (because there is nothing to delegate), but will still only work on the "record limit" number of records.

Only functions that have criteria can delegate. And even those delegable criteria are still limited to the maximum number of records specified.

If record limit was set to 10 and you delegate criteria to a list that is 3000 records big that will return records past the 2000 point (let's say there are 200 records that meet the criteria)- even if the criteria delegates fine, you would still only get 10 records.

I hope this is helpful for you.

Was this reply helpful? Yes No
poweractivate 11,078 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

@RandyHayes

I think I will triple check about this especially ForAll because I believe you are right about that. In that event, that's a really clear way to view delegation versus the data row limit!
In that case @Datenshi - you cannot work around it really, you have to consider only other ways, I recommend you to use the suggestions provided by @RandyHayes . If you must use Not, change the data source to Dataverse. However, do this as a last resort. Keep SharePoint, use the suggestions from @RandyHayes , this would be the best for now @Datenshi

Was this reply helpful? Yes No
RandyHayes 76,299 Super User 2024 Season 1 on at

Like (1)

Report
Copy link

Link copied!
@poweractivate

The clearer way to view this is a formula like this:

Set(tableVar, ForAll(dataSource, {ID: ID, Title: Title } ) )

The record structure of the ForAll table is not relevant in this case, only for example.

In the formula, ForAll needs its table of records to iterate over. That is specified as dataSource. So, that means it needs to get the datasource. There is no criteria, so nothing to delegate. It just gets records. And...that Limit is 2000!

If the datasource is 3000 records, then the resulting table will only have 2000 records.

NOTE: There will be NO delegation warnings!

In contrast - this formula...

Set(tableVar, ForAll(Filter(dataSource, val=1), {ID: ID, Title: Title } ) )

In this one, the Filter function is introduced. So, now the iteration table for ForAll is the resultant table from the Filter function. The criteria in the filter is delegable. If the datasource has 500 records with val=1, then 500 records are returned in the Filter table...which then because the iteration table for ForAll.

If val=1 produces for than 2000 records, then the resultant table will still only be 2000 records as that is the record limit.

In regards to warnings in the formula editor:

Functions that allow criteria - Filter, UpdateIf, RemoveIf, etc. will all provide delegation warnings if their criteria cannot be delegated. Whereas, functions like, ForAll, AddColumns, GroupBy, etc. do not use criteria, so there will never be a delegation warning.

However...outside of delegation, ALL functions will only return the record limit.

Was this reply helpful? Yes No