Filtering Expiration Dates from my Html Table

[2201559]

Hi there,

I have been trying to separate my Html Table based on 'Expiration Dates', the picture below show all of the dates lump together (which is not what I want).

This is my filter array after my Html Table, is there something wrong?

Please help me out, thanks!

[v-yueyun-msft]

Hi , @2201559 

You can use this expression in the "Filter array" action:
@equals(replace(split(item()?['Html Table'], '</h1>')[0], '<h1>', ''), formatDateTime(split(item()?['Expiration Date'], 'T')?[0], 'dd/MM/yyyy'))

If this reply can help you , you can click mark this reply as solution (Accept solution) which can help more people, thanks in advance! 

Best Regards,

Yueyun Zhang

[2201559]

Hi

@v-yueyun-msft 

I input your expression in my Filter Array, but how do I narrow it down to getting only one of the dates?

For example, I only want 17 May 2024 information and remove out the rest 

[v-yueyun-msft]

Hi , @2201559 

Thanks for your response! If you want to get the Date in loop , you can refer to this test flow:

split(items('Apply_to_each')?['Expiration Date'], 'T')?[0]

If this reply can help you , you can click mark this reply as solution (Accept solution) which can help more people, thanks in advance! 

Best Regards,

Yueyun Zhang

[2201559]

Hi @v-yueyun-msft ,

I did managed to split the dates but I want to get both the Expiration Date and Html Table data.

The thing I am trying to get is the 17th May grey circle (picture below) and not the rest of the others.

Is there a way to do so?

[v-yueyun-msft]

Hi , @2201559 

This expression is used to get the Expiration Date:

split(items('Apply_to_each')?['Expiration Date'], 'T')?[0]

You can use this expression to get the  Date in Html Table:
replace(split(items('Apply_to_each')?['Html Table'], '</h1>')[0], '<h1>', '')

If I misunderstand what you mean, you can describe your needs and your expected outcomes in detail so that we can better help you. If this reply can help you , you can click mark this reply as solution (Accept solution) which can help more people, thanks in advance! 

Best Regards,

Yueyun Zhang

[2201559]

Hi @v-yueyun-msft 

Sorry if I was not clear in my message, I appreciate your replies a lot

Currently I am receiving this Output in my email, which shows all the Expiration Date (both seen on my Black Header and one of the column of my Html Table) in a single email (not what I want)

The Output that I want is when it is about 14 days before my Expiration Date of this specific item, to send me that email, which only contains the information for that. Below is my expected outcome:

Situation: Today is 1st May and the Expiration Date of xxx items is 15th May (14 days later)

Expected outcome: A reminder email will be sent today (1st May) and contains information of only items that have the Expiration Date on the 15th May

Expected Email to look like (pardon my poor writing)

If you need more information, do let me know @v-yueyun-msft 

[v-yueyun-msft]

Hi, @2201559 

Thanks for your response! According to your description, this is my understanding for your need:

If this , you can add the filter condition here:

@and(equals(replace(split(item()?['Html Table'], '</h1>')[0], '<h1>', ''), formatDateTime(split(item()?['Expiration Date'], 'T')?[0], 'dd/MM/yyyy')),
equals( split(item()?['Expiration Date'], 'T')?[0] , addDays(utcNow(),14,'yyyy-MM-dd')))

The result is as follows in my side:

If this reply can help you , you can click mark this reply as solution (Accept solution) which can help more people, thanks in advance! 

Best Regards,

Yueyun Zhang

[2201559]

Hi @v-yueyun-msft ,

I tried your expression but noting came out of it?

My Input:

My Output with your expression:

I not too sure what went wrong, do you have the expression when editing in the 'Basic Mode' instead of the 'Advance mode' for the Filter Array part?

[v-yueyun-msft]

Hi , @2201559 

I put this code in the advanced mode :

And your output is blank due to today is 2024/05/21, it will filter only the [Expiration Date] =2024/06/04.

Best Regards,

Yueyun Zhang

[2201559]

Hi @v-yueyun-msft ,

I fixed the Output and it worked.

However, I got one last issue which is the same email has been sent to me multiple times (many duplicates)

I not too sure where I went wrong, this is the rest of my flow

Left output is ComposeStyle while Right output is Compose

[v-yueyun-msft]

Hi , @2201559 

According to your screeshot, you loop the [value] to send the email. As you have filtered the array above, in my thought you do not need to use the "Apply to each" to loop to send the email. You can directly send the email after the "Compose Style" action.

If this reply can help you , you can click mark this reply as solution (Accept solution) which can help more people, thanks in advance! 

Best Regards,

Yueyun Zhang

[2201559]

Hi @v-yueyun-msft 

It worked out but now my Condtion for the email can be used? The error keeps appearing to be the value is not a type string but an array? Not too sure what it means

The output on the right is 

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

[v-yueyun-msft]

Hi , @2201559 

In my understanding , you do not need to add this condition due to it has been added in the "Filter array" action:
@and(equals(replace(split(item()?['Html Table'], '</h1>')[0], '<h1>', ''), formatDateTime(split(item()?['Expiration Date'], 'T')?[0], 'dd/MM/yyyy')),
equals( split(item()?['Expiration Date'], 'T')?[0] , addDays(utcNow(),14,'yyyy-MM-dd')))

If this reply can help you , you can click mark this reply as solution (Accept solution) which can help more people, thanks in advance! 

Best Regards,

Yueyun Zhang

[2201559]

Hi @v-yueyun-msft ,

I tried without the condition, however the issue I have now is that it is unable to 'Test Run Automatically'

Can I still add the Condtion function to ensure that the email is only sent if Expiration Date of the item is 14 days ahead of today's date?

Or is there no other way around it @v-yueyun-msft 

Best regards,

2201559

[2201559]

Hi @v-yueyun-msft,

I not too sure why but I kept receiving an error with your expression after trying to run it a few time

My Input:

Error received

The execution of template action 'Filter_array_3' failed: The evaluation of 'query' action 'where' expression '@and(equals(replace(split(item()?['Html Table'], '</h1>')[0], '<h1>', ''), formatDateTime(split(item()?['Expiration Date'], 'T')?[0], 'dd/MM/yyyy')),
equals(split(item()?['Expiration Date'], 'T')?[0] , addDays(utcNow(),14,'yyyy-MM-dd')))' failed: 'In function 'formatDateTime', the value provided for date time string '' was not valid. The datetime string must match ISO 8601 format.'. 

[v-yueyun-msft]

Hi , @2201559 

This error code means that the ['Expiration Date'] contains the blank value in your array.

You can try to use this expression:
@and(
not(equals(length(replace(split(item()?['Html Table'], '</h1>')[0], '<h1>', '')),0)),
equals(replace(split(item()?['Html Table'], '</h1>')[0], '<h1>', ''), formatDateTime(split(item()?['Expiration Date'], 'T')?[0], 'dd/MM/yyyy')),
equals( split(item()?['Expiration Date'], 'T')?[0] , addDays(utcNow(),14,'yyyy-MM-dd'))
)

If this reply can help you , you can click mark this reply as solution (Accept solution) which can help more people, thanks in advance! 

Best Regards,

Yueyun Zhang

[2201559]

Hi @v-yueyun-msft 

Oh I see, but now I not sure why the Output is empty?

I did change your expression from 14 to -14 (since I wanted 14 days before the current date), could that be the reason?

The expression I used is 

@and(not(equals(length(replace(split(item()?['Html Table'], '</h1>')[0], '<h1>', '')),0)),
equals(replace(split(item()?['Html Table'], '</h1>')[0], '<h1>', ''), formatDateTime(split(item()?['Expiration Date'], 'T')?[0], 'dd-MM-yyyy')),
equals( split(item()?['Expiration Date'], 'T')?[0] , addDays(utcNow(),-14,'yyyy-MM-dd')))

[v-yueyun-msft]

Hi, @2201559 

Thanks for your response! If you update the addDays functions' parameter to -14.

For example ,today is 2024/5/23 , it will only filter the Date = 2024/5/9.

Best Regards,

Yueyun Zhang

[2201559]

Hi @v-yueyun-msft 

Sorry but I keep getting empty Output, not too sure why?

I used this

@and(
not(equals(length(replace(split(item()?['Html Table'], '</h1>')[0], '<h1>', '')),0)),
equals(replace(split(item()?['Html Table'], '</h1>')[0], '<h1>', ''), formatDateTime(split(item()?['Expiration Date'], 'T')?[0], 'dd/MM/yyyy')),
equals( split(item()?['Expiration Date'], 'T')?[0] , addDays(utcNow(),-14,'yyyy-MM-dd')))