Answered

I would like to setup a boolean variable when a collection records are not in SharePoint list fields

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hi Everyone
I would need your help to setup a local Boolean variable to true when a collection (records fiels) are not found in a SharePoint List, here is the details:

collection: DateRange
field: RequestedDate

Records:
2021-12-06
2021-12-07
2021-12-08
2021-12-09

SharePoint list RequestedTO

Fields: From field and To field

FROM TO
2021-12-06 2021-12-07
2021-12-09 2021-12-15

Set(isApproved,
<query>
)

Condition: isApproved variable will become true when all the dates in DateRange are NOT in SharePoint list RequestedTO, in above example, the value should be false because 2021-12-06, 2021-12-07 and 2021-12-09 records in DateRange are either in From or To fields in the SharePoint list RequestedTO.

Also, I am open to another suggestion if any.

Please help!

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (2)

Sort by

BCLS776 8,994 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
Something like this could do what you are after:

Set(isApproved, true); ForAll(DateRange As aDate, ForAll(RequestedTO As aDateRange, If(aDate.RequestedDate >= aDateRange.FROM && aDate.RequestedDate <= aDateRange.TO, Set(isApproved, false) ) ) ) // Loop starts out with isApproved = true and changes it to false if it encounters any record where the date fits within the date range

Your many-to-many relationship will require some sort of nested operation.

Hope that helps,

Bryan

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi there,
Thank you for your prompted reply, the query is great and easy however, I am getting the error message "This function cannot be invoked within ForAll" in Set(isApproved, false).

Any thought or workaround?
Thank you

Was this reply helpful? Yes No
Verified answer

BCLS776 8,994 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
@Willy-C wrote:

Hi there,

Thank you for your prompted reply, the query is great and easy however, I am getting the error message "This function cannot be invoked within ForAll" in Set(isApproved, false).

Any thought or workaround?

Thank you

Ah yes, I forgot about that wrinkle. There is a way around it by using a collection as though it is a global variable. First, modify your code as shown:

Clear(colVariable); ForAll(DateRange As aDate, ForAll(RequestedTO As aDateRange, If(aDate.RequestedDate >= aDateRange.FROM && aDate.RequestedDate <= aDateRange.TO, Collect(colVariable, {Value: true}), Collect(colVariable, {Value: false}) ) ) ) // Creates a single column collection table with true/false in Value column

Now, you can check whether there are any records with true/false as appropriate using a LookUp():

IsBlank( LookUp(colVariable, Value = true) ) // This statement returns true if there were no dates matching date ranges in the data

Try this out and let us know how it works.

Bryan

Was this reply helpful? Yes No
Verified answer

Community Power Pla... Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!

Hi Bryan,

You solution works well, I just did a small change to have only one record in the collection with true or false value as follows:

ClearCollect(validDate, { isValidateDate: true});
ForAll(DateRange As aDate,
ForAll(RequestedTO As aDateRange,
If(aDate.RequestedDate >= aDateRange.FROM && aDate.RequestedDate <= aDateRange.TO && ,
UpdateIf(validDate, true, { isValidateDate: false})
)
)
)
Thank you

Was this reply helpful? Yes No