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

[Willy-C]

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!

[BCLS776]

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

[Willy-C]

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

[BCLS776 · Accepted solution]

---

@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

[Willy-C · Accepted solution]

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