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

(0) Share

Report

Posted on by nicccholas

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

Categories:

Building flows

I have the same question (0)

All responses (5)

Answers (1)

v-duann-msft on at

Like (0)

Report

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

Was this reply helpful? Yes No

nicccholas

7 on at

Like (0)

Report

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 Contractor	Contact No.	Address	Contract Start Date	Contract End Date
1	A	1	ABC 123 Street	14/05/2020	13/5/2021
2	B	2	ABC 123 Street	26/06/2020	21/06/2021
3	C	3	ABC 123 Street	26/06/2020	21/06/2021
4	D	4	ABC 123 Street	26/06/2020	28/04/2021
5	E	5	ABC 123 Street	14/05/2020	13/05/2022
6	F	6	ABC 123 Street	26/06/2020	21/06/2021
7	G	7	ABC 123 Street	26/06/2020	21/06/2021
8	H	8	ABC 123 Street	14/05/2020	28/04/2022
9	I	9	ABC 123 Street	14/05/2020	13/05/2023

Was this reply helpful? Yes No

Verified answer

v-duann-msft on at

Like (0)

Report

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:

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')

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

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

Was this reply helpful? Yes No
nicccholas 7 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
v-duann-msft on at

Like (1)

Report

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.

Thanks
Anna

Was this reply helpful? Yes No