Using less function in FilterArray

[RajSingh]

Hi guys,

I was wondering if anyone can help me with understanding what the below statement does? I am taking data from sharepoint site and using this to check the modified date. Will it show all records where modified date is less than 7 days from todays date?

less(item()?['Modified'], addDays(utcNow(), -7))

I'm seeing all records from 26th Feb 2024 and also 1st Dec 2023 etc. after running this. 

Thanks

[SamLed]

Hi @RajSingh,

As you already substract 7 days from today :

addDays(utcNow(), -7)

No need to use less() but equals() to get all items that were modified 7 days before today (also format to get rid of time):

equals(formatDateTime(item()['Modified'], 'yyyy-MM-dd'), addDays(utcNow(), -7, 'yyyy-MM-dd'))

______________________________________________________________

If I have answered your question, please Accept the post as solution.
If you like my response, please Thumbs Up.

[RajSingh]

Thanks @SamLed - How can I exclude weekends from the data calculation? 

[SamLed]

Hi @RajSingh,

Yes it possible but is a bit more tricky. You'll have to identify each days of week and exclude the ones which equals 0 or 6 (week starts on sunday (0) and ends on saturday (6)). That would give a flow like this:

"Get day before" compose expression:

addDays(variables('TargetDate'), -1)

Increment variable DayCount expression:

if(and(greater(dayOfWeek(variables('TargetDate')), 0),less(dayOfWeek(variables('TargetDate')), 6)), 1, 0) 

"Target date" compose expression:

formatDateTime(variables('TargetDate'), 'yyyy-MM-dd')

So, set the number of days you want before today in loop condition:

It will return the date 7 working days before today (excluding today, if you want to include today in count, initialize DayCount variable to 1). Example: Today 02/05/2024 minus 7 working days gives 02/23/2024. There are 2 week-end between.

And your filter expression becomes:

equals(formatDateTime(item()['Modified'], 'yyyy-MM-dd'), formatDateTime(variables('TargetDate'), 'yyyy-MM-dd'))

_____________________________________________________________

If I have answered your question, please Accept the post as solution.
If you like my response, please Thumbs Up.

[RajSingh]

Thanks @SamLed - My filterArray is as below. Is it possible to exclude the weekends from all date filters in below query:

@or(and(equals(item()?['Progress'], 'Follow up with Customer'),less(item()?['Modified'], addDays(utcNow(), -7))),and(equals(item()?['Progress'], 'Refer to KYC'),less(item()?['Modified'], addDays(utcNow(), -3))),
and(equals(item()?['Progress'], 'Application form returned to BBCS'),less(item()?['Modified'], addDays(utcNow(), -2))),
and(equals(item()?['Progress'], 'Follow up with customer - Reminder 1'),less(item()?['Modified'], addDays(utcNow(), -4))),
and(equals(item()?['Progress'], 'Day 2/3 checks'),less(item()?['Modified'], addDays(utcNow(), -5))),
and(equals(item()?['Progress'], 'Sent to CAO'),less(item()?['Modified'], addDays(utcNow(), -5))),
and(equals(item()?['Progress'], 'Refer to Standard Legal'),less(item()?['Modified'], addDays(utcNow(), -5))),
and(equals(item()?['Progress'], 'Follow up with customer - Reminder 1'),less(item()?['Modified'], addDays(utcNow(), -4))),
and(equals(item()?['Progress'], 'Follow up with customer - Reminder 2'),less(item()?['Modified'], addDays(utcNow(), -7))),
and(equals(item()?['Progress'], 'Follow up with customer - Reminder 3'),less(item()?['Modified'], addDays(utcNow(), -14))),
and(equals(item()?['Progress'], 'Refer to RM'),less(item()?['Modified'], addDays(utcNow(), -7))),
and(equals(item()?['Progress'], 'Refer to RM – Reminder 1'),less(item()?['Modified'], addDays(utcNow(), -2))),
and(equals(item()?['Progress'], 'Refer to RM – Reminder 2'),less(item()?['Modified'], addDays(utcNow(), -2))),
and(equals(item()?['Progress'], 'Refer to RM – Reminder 3'),less(item()?['Modified'], addDays(utcNow(), -14))),
and(equals(item()?['Progress'], 'Refer to HRA'),less(item()?['Modified'], addDays(utcNow(), -5))),
and(equals(item()?['Progress'], 'Refer to MLRO'),less(item()?['Modified'], addDays(utcNow(), -5))),
and(equals(item()?['Progress'], 'Refer to FATCA NI'),less(item()?['Modified'], addDays(utcNow(), -1))),
and(equals(item()?['Progress'], 'Phonecall to customer required'),less(item()?['Modified'], addDays(utcNow(), -1))),
and(equals(item()?['Progress'], 'Pending with sig ver'),less(item()?['Modified'], addDays(utcNow(), -5))),
and(equals(item()?['Progress'], 'Pending with BFC'),less(item()?['Modified'], addDays(utcNow(), -1))),
and(equals(item()?['Progress'], 'Peer Review Approved'),less(item()?['Modified'], addDays(utcNow(), -1))),
and(equals(item()?['Progress'], 'Peer Review Rejected'),less(item()?['Modified'], addDays(utcNow(), -1))),
and(equals(item()?['Progress'], 'New Application Received'),less(item()?['Modified'], addDays(utcNow(), -1))),
and(equals(item()?['Progress'], 'Immigration Check Required'),less(item()?['Modified'], addDays(utcNow(), -1))),
and(equals(item()?['Progress'], 'Immigration Check Required'),less(item()?['Modified'], addDays(utcNow(), -1))),
and(equals(item()?['Progress'], '*** Onshore Referral Complete'),less(item()?['Modified'], addDays(utcNow(), -1))),
and(equals(item()?['Progress'], 'Onshore Referral'),less(item()?['Modified'], addDays(utcNow(), -1))),
and(equals(item()?['Progress'], 'Offshore Referral'),less(item()?['Modified'], addDays(utcNow(), -1))),
and(equals(item()?['Progress'], 'Offshore Referral complete'),less(item()?['Modified'], addDays(utcNow(), -1))),
and(equals(item()?['Progress'], 'Peer Review 2 Approved'),less(item()?['Modified'], addDays(utcNow(), -1))),
and(equals(item()?['Progress'], 'FOR ACTION - Customer Response Received'),less(item()?['Modified'], addDays(utcNow(), -1))),
and(equals(item()?['Progress'], 'FOR ACTION - Docs Returned to Branch'),less(item()?['Modified'], addDays(utcNow(), -1))))

[SamLed]

Hi @RajSingh,

First, using less() you'll get any item modified n day(s) ago + all before. If you want to get only items modified n day(s) before replace less() by equals() and format dates to 'yyyy-MM-dd" to get rid of time.

Actually, you won't be able to get rid of WE using such expression, showed you previously how to exclude WE from date range and as far as I know we can't get equivalent result using only expression functions. So I would adapt my previous proposition that way to get all required past modified dates as you want different (-1, -2, -3, -5, -7 and -14):

Where "Apply to each" from: 

variables('PastDayCountArray')

Right "Do until" condition operand:

items('Apply_to_each')

"Past dates array" expression: 

outputs('Target_Date')

 results:

Then, adapt your filter expression that way:

@or(and(equals(item()?['Progress'], 'Follow up with Customer'),equals(formatDateTime(item()['Modified'], 'yyyy-MM-dd'),outputs('Past_dates_array')[4])),
and(equals(item()?['Progress'], 'Refer to KYC'),equals(formatDateTime(item()['Modified'], 'yyyy-MM-dd'),outputs('Past_dates_array')[2])),
etc...

Remark: just figured out as I was adapting your expression that I forgot "4" in the array, but it was to illustrate how I would do 😉

______________________________________________________________

If I have answered your question, please Accept the post as solution.
If you like my response, please Thumbs Up.

[RajSingh]

@SamLed - What's in Reset TargetDate and Reset DayCount?Is in initialize variable action?

[RajSingh]

Also if I try to add Initialize variable for Reset TargetDate then it says Initialize variable can only be used at the top level. 

[SamLed]

Hi @RajSingh,

Initialize values for thos variables are useless as we need to reset for each date.

______________________________________________________________

If I have answered your question, please Accept the post as solution.
If you like my response, please Thumbs Up.

[SamLed]

Use "Set variable" not "initialize variable" inside loop

[RajSingh]

@SamLed  - Which action is Get day before? Thanks

[SamLed]

@RajSingh It's a compose one, can't set a variable referencing the same variable, so need to use an intermediate action.

[RajSingh]

Thanks @SamLed - Just wondering what's the purpose of 4 and 2 in below:

@or(and(equals(item()?['Progress'], 'Follow up with Customer'),equals(formatDateTime(item()['Modified'], 'yyyy-MM-dd'),outputs('Past_dates_array')[4])),
and(equals(item()?['Progress'], 'Refer to KYC'),equals(formatDateTime(item()['Modified'], 'yyyy-MM-dd'),outputs('Past_dates_array')[2])),
etc...

My existing FilterArray is as below. Do I need to keep the numbers same as below:

@or(and(equals(item()?['Progress'], 'Follow up with Customer'),less(item()?['Modified'], addDays(utcNow(), -7))),and(equals(item()?['Progress'], 'Refer to KYC'),less(item()?['Modified'], addDays(utcNow(), -3))),
and(equals(item()?['Progress'], 'Application form returned to BBCS'),less(item()?['Modified'], addDays(utcNow(), -2))),
and(equals(item()?['Progress'], 'Follow up with customer - Reminder 1'),less(item()?['Modified'], addDays(utcNow(), -4))),
and(equals(item()?['Progress'], 'Day 2/3 checks'),less(item()?['Modified'], addDays(utcNow(), -5))),
and(equals(item()?['Progress'], 'Sent to CAO'),less(item()?['Modified'], addDays(utcNow(), -5))),
and(equals(item()?['Progress'], 'Refer to Standard Legal'),less(item()?['Modified'], addDays(utcNow(), -5))),
and(equals(item()?['Progress'], 'Follow up with customer - Reminder 1'),less(item()?['Modified'], addDays(utcNow(), -4))),
and(equals(item()?['Progress'], 'Follow up with customer - Reminder 2'),less(item()?['Modified'], addDays(utcNow(), -7))),
and(equals(item()?['Progress'], 'Follow up with customer - Reminder 3'),less(item()?['Modified'], addDays(utcNow(), -14))),
and(equals(item()?['Progress'], 'Refer to RM'),less(item()?['Modified'], addDays(utcNow(), -7))),
and(equals(item()?['Progress'], 'Refer to RM – Reminder 1'),less(item()?['Modified'], addDays(utcNow(), -2))),
and(equals(item()?['Progress'], 'Refer to RM – Reminder 2'),less(item()?['Modified'], addDays(utcNow(), -2))),
and(equals(item()?['Progress'], 'Refer to RM – Reminder 3'),less(item()?['Modified'], addDays(utcNow(), -14))),
and(equals(item()?['Progress'], 'Refer to HRA'),less(item()?['Modified'], addDays(utcNow(), -5))),
and(equals(item()?['Progress'], 'Refer to MLRO'),less(item()?['Modified'], addDays(utcNow(), -5))),
and(equals(item()?['Progress'], 'Refer to FATCA NI'),less(item()?['Modified'], addDays(utcNow(), -1))),
and(equals(item()?['Progress'], 'Phonecall to customer required'),less(item()?['Modified'], addDays(utcNow(), -1))),
and(equals(item()?['Progress'], 'Pending with sig ver'),less(item()?['Modified'], addDays(utcNow(), -5))),
and(equals(item()?['Progress'], 'Pending with BFC'),less(item()?['Modified'], addDays(utcNow(), -1))),
and(equals(item()?['Progress'], 'Peer Review Approved'),less(item()?['Modified'], addDays(utcNow(), -1))),
and(equals(item()?['Progress'], 'Peer Review Rejected'),less(item()?['Modified'], addDays(utcNow(), -1))),
and(equals(item()?['Progress'], 'New Application Received'),less(item()?['Modified'], addDays(utcNow(), -1))),
and(equals(item()?['Progress'], 'Immigration Check Required'),less(item()?['Modified'], addDays(utcNow(), -1))),
and(equals(item()?['Progress'], 'Immigration Check Required'),less(item()?['Modified'], addDays(utcNow(), -1))),
and(equals(item()?['Progress'], '*** Onshore Referral Complete'),less(item()?['Modified'], addDays(utcNow(), -1))),
and(equals(item()?['Progress'], 'Onshore Referral'),less(item()?['Modified'], addDays(utcNow(), -1))),
and(equals(item()?['Progress'], 'Offshore Referral'),less(item()?['Modified'], addDays(utcNow(), -1))),
and(equals(item()?['Progress'], 'Offshore Referral complete'),less(item()?['Modified'], addDays(utcNow(), -1))),
and(equals(item()?['Progress'], 'Peer Review 2 Approved'),less(item()?['Modified'], addDays(utcNow(), -1))),
and(equals(item()?['Progress'], 'FOR ACTION - Customer Response Received'),less(item()?['Modified'], addDays(utcNow(), -1))),
and(equals(item()?['Progress'], 'FOR ACTION - Docs Returned to Branch'),less(item()?['Modified'], addDays(utcNow(), -1))))

[SamLed]

@RajSingh  that's the index of date in the "Past dates array"

If you have set [1,2,3,4,5,7,14] as PastDayCountArray then outputs of "Past dates array" compose is:

So:

• outputs('Past_dates_array')[0] is the date corresponding to addDays(utcNow(), -1)
• outputs('Past_dates_array')[1] is the date corresponding to addDays(utcNow(), -2)
• ...
• outputs('Past_dates_array')[6] is the date corresponding to addDays(utcNow(), -14)

Now you just have to replace your:

less(item()?['Modified'], addDays(utcNow(), -N))

By:

equals(formatDateTime(item()['Modified'], 'yyyy-MM-dd'),outputs('Past_dates_array')[<index of corresponding date>])

______________________________________________________________

If I have answered your question, please Accept the post as solution.
If you like my response, please Thumbs Up.

[RajSingh]

@SamLed - Also I will be using "less" as I need all cases modified n day(s) ago + all before. Can you please help me to fix the error? Thanks so much for your help so far.

[SamLed]

@RajSingh What error ?

[RajSingh]

Please see below the error:

[RajSingh]

@SamLed - Please see above the error message. I have shared FilterArray with you already. Please see below the screenshots of the flow as well

[SamLed]

@RajSingh first issue I can see in your expression is how you are using or(). It takes 2 parameters, so you'll have to nest them.

[RajSingh]

@SamLed - I was using the below "or" expression in FilterArray before and it was working perfectly fine. The only issue was it was including weekends in calculation as well. 

@or(and(equals(item()?['Progress'], 'Follow up with Customer'),less(item()?['Modified'], addDays(utcNow(), -7))),and(equals(item()?['Progress'], 'Refer to KYC'),less(item()?['Modified'], addDays(utcNow(), -3))),
and(equals(item()?['Progress'], 'Application form returned to BBCS'),less(item()?['Modified'], addDays(utcNow(), -2))),
and(equals(item()?['Progress'], 'Follow up with customer - Reminder 1'),less(item()?['Modified'], addDays(utcNow(), -4))),
and(equals(item()?['Progress'], 'Day 2/3 checks'),less(item()?['Modified'], addDays(utcNow(), -5))),
and(equals(item()?['Progress'], 'Sent to CAO'),less(item()?['Modified'], addDays(utcNow(), -5))),
and(equals(item()?['Progress'], 'Refer to Standard Legal'),less(item()?['Modified'], addDays(utcNow(), -5))),
and(equals(item()?['Progress'], 'Follow up with customer - Reminder 1'),less(item()?['Modified'], addDays(utcNow(), -4))),
and(equals(item()?['Progress'], 'Follow up with customer - Reminder 2'),less(item()?['Modified'], addDays(utcNow(), -7))),
and(equals(item()?['Progress'], 'Follow up with customer - Reminder 3'),less(item()?['Modified'], addDays(utcNow(), -14))),
and(equals(item()?['Progress'], 'Refer to RM'),less(item()?['Modified'], addDays(utcNow(), -7))),
and(equals(item()?['Progress'], 'Refer to RM – Reminder 1'),less(item()?['Modified'], addDays(utcNow(), -2))),
and(equals(item()?['Progress'], 'Refer to RM – Reminder 2'),less(item()?['Modified'], addDays(utcNow(), -2))),
and(equals(item()?['Progress'], 'Refer to RM – Reminder 3'),less(item()?['Modified'], addDays(utcNow(), -14))),
and(equals(item()?['Progress'], 'Refer to HRA'),less(item()?['Modified'], addDays(utcNow(), -5))),
and(equals(item()?['Progress'], 'Refer to MLRO'),less(item()?['Modified'], addDays(utcNow(), -5))),
and(equals(item()?['Progress'], 'Refer to FATCA NI'),less(item()?['Modified'], addDays(utcNow(), -1))),
and(equals(item()?['Progress'], 'Phonecall to customer required'),less(item()?['Modified'], addDays(utcNow(), -1))),
and(equals(item()?['Progress'], 'Pending with sig ver'),less(item()?['Modified'], addDays(utcNow(), -5))),
and(equals(item()?['Progress'], 'Pending with BFC'),less(item()?['Modified'], addDays(utcNow(), -1))),
and(equals(item()?['Progress'], 'Peer Review Approved'),less(item()?['Modified'], addDays(utcNow(), -1))),
and(equals(item()?['Progress'], 'Peer Review Rejected'),less(item()?['Modified'], addDays(utcNow(), -1))),
and(equals(item()?['Progress'], 'New Application Received'),less(item()?['Modified'], addDays(utcNow(), -1))),
and(equals(item()?['Progress'], 'Immigration Check Required'),less(item()?['Modified'], addDays(utcNow(), -1))),
and(equals(item()?['Progress'], 'Immigration Check Required'),less(item()?['Modified'], addDays(utcNow(), -1))),
and(equals(item()?['Progress'], '*** Onshore Referral Complete'),less(item()?['Modified'], addDays(utcNow(), -1))),
and(equals(item()?['Progress'], 'Onshore Referral'),less(item()?['Modified'], addDays(utcNow(), -1))),
and(equals(item()?['Progress'], 'Offshore Referral'),less(item()?['Modified'], addDays(utcNow(), -1))),
and(equals(item()?['Progress'], 'Offshore Referral complete'),less(item()?['Modified'], addDays(utcNow(), -1))),
and(equals(item()?['Progress'], 'Peer Review 2 Approved'),less(item()?['Modified'], addDays(utcNow(), -1))),
and(equals(item()?['Progress'], 'FOR ACTION - Customer Response Received'),less(item()?['Modified'], addDays(utcNow(), -1))),
and(equals(item()?['Progress'], 'FOR ACTION - Docs Returned to Branch'),less(item()?['Modified'], addDays(utcNow(), -1))))

The only change I have made is replacing addDays functions with below to exclude weekends:

outputs('Past_dates_array')[<index of corresponding date>]

 Can you please help me in this? Thanks