cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nicccholas
Frequent Visitor

Problems on filtering array with dates form Excel for sending email alert to user

Dear All

 

Here is what I would like to achieve.

Send the email alert to user if the column - Contract End Date is 31 days prior to the expiry date.

The problem is no matter what date inputted in the Excel (online), the results still return empty = true.

I am wondering if this is because of the Excel (online) saving the date as integer.

Filter Array :

 

@And(greater(addDays('1899-12-30', int(item()['Contract End Date']), 'dd-MM-yyyy'), addDays(utcNow(), 31,'dd-MM-YYYY')),less(addDays('1899-12-30', int(item()['Contract End Date']), 'dd-MM-yyyy'), addDays(utcNow(),0,'dd-MM-YYYY')))

 

I have changed the integer to date by addDays. Still, no clues.

On the other hand, the date appears as integer when sending the email after "Create HTML Table"

Is there any way to tackle this also ?

Thank you all.

Below are the flows.

 

 

Screenshot 2021-04-13 at 4.12.45 PM.png

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @nicccholas 

 

Thank you very much for your nice cooperation

 

If that’s the case, I believe the below part is what you want.

Excel:

v-duann-msft_0-1618374014229.png

 

Flow overview: (I didn’t use condition to check whether any data existing after filter array in my example. If it’s necessary to you, you can still keep that part.)

Expression:

formatDateTime(addDays('1899-12-31', int(item()?['Contract End Date'])), 'yyyy-MM-dd')

formatDateTime(addDays(utcNow(),31),'yyyy-MM-dd')

item()?['Contract Name']

formatDateTime(addDays('1899-12-31', int(item()?['Contract End Date'])), 'yyyy-MM-dd')

v-duann-msft_1-1618374014242.png

 

Once tested, I’m able to get information as required.

v-duann-msft_2-1618374014247.png

 

v-duann-msft_3-1618374014248.png

 

You didn’t configure expression with ‘formatDateTime’ formula which makes you unable to filter any result. Because if we only UTCNow(), it will always return date with detailed time with HH:mm:ss. 

 

Best regards,

Anna

View solution in original post

5 REPLIES 5
v-duann-msft
Community Support
Community Support

Hi @nicccholas 

 

Thank you for posting.

 

According to your description, you would like to send html result via email if Contract End Date is 31 days prior to the expiry date. 

 

Is that possible that you can show me how the excel file look like? I would like to confirm the data format first. Then, I can assist you figure it out.

 

Hope to hear from you soon.

 

Thanks

Anna

Sure. The excel is as follows.

The format of the date is in short-date (UK) --> 'dd-MM-YYYY'

Thank you.

Ref No.Name of ContractorContact No.AddressContract Start DateContract End Date
1A1ABC 123 Street14/05/202013/5/2021
2B2ABC 123 Street26/06/202021/06/2021
3C3ABC 123 Street26/06/202021/06/2021
4D4ABC 123 Street26/06/202028/04/2021
5E5ABC 123 Street14/05/202013/05/2022
6F6ABC 123 Street26/06/202021/06/2021
7G7ABC 123 Street26/06/202021/06/2021
8H8ABC 123 Street14/05/202028/04/2022
9I9ABC 123 Street14/05/202013/05/2023

 

Hi @nicccholas 

 

Thank you very much for your nice cooperation

 

If that’s the case, I believe the below part is what you want.

Excel:

v-duann-msft_0-1618374014229.png

 

Flow overview: (I didn’t use condition to check whether any data existing after filter array in my example. If it’s necessary to you, you can still keep that part.)

Expression:

formatDateTime(addDays('1899-12-31', int(item()?['Contract End Date'])), 'yyyy-MM-dd')

formatDateTime(addDays(utcNow(),31),'yyyy-MM-dd')

item()?['Contract Name']

formatDateTime(addDays('1899-12-31', int(item()?['Contract End Date'])), 'yyyy-MM-dd')

v-duann-msft_1-1618374014242.png

 

Once tested, I’m able to get information as required.

v-duann-msft_2-1618374014247.png

 

v-duann-msft_3-1618374014248.png

 

You didn’t configure expression with ‘formatDateTime’ formula which makes you unable to filter any result. Because if we only UTCNow(), it will always return date with detailed time with HH:mm:ss. 

 

Best regards,

Anna

View solution in original post

Really appreciate for you help.

One more question, may I ask if it possible to filter the contracts between 31 day prior to the contract end date and today as I would like to do this flow by monthly basis that some of the contracts might not be 31 days but maybe 1x days.

 

Once again, thank you for your help.

Hi @nicccholas 

 

Thank you for update.

 

Actually, I use 'is greater or equal to' in filter array which has already included days less than 31days.

v-duann-msft_0-1618557653218.png

 

Thanks

Anna

Helpful resources

Announcements
Process Advisor

Introducing Process Advisor

Check out the new Process Advisor community forum board!

MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Top Kudoed Authors
Users online (48,984)