Substring Comparison problem in 'List Rows Present in a Table'

[Seank02]

Hello Sir/Madam, 

                           I am building up the task reminder which can be send out through an email automatically, once the end date has reached on. In the Excel sheet, there are two end date's columns. One of them is the original end date which is calculated, based on the formula in Excel. Besides that, another end date is the updated version from the said original one which is for the user to take a leave. Furthermore, the substring contains the future date that the email will be send out according to my time zone. I was able to do the skip empty rows function in the flow of 'List Flow Present in a table' by using filter query, if the said original end date contains empty row. While, doing the comparison between selected end date and the substring by using the filter array. The flows runs completely fine but it send all the emails out without compare to the end date in the substring. The details of my flow are attached as below. Is it the filter array cannot read string type data? 

Any help would be appreciated!

Best Regards, 

Sean. 

[trice602]

Hi @Seank02 ,

I think you are close here.  In your filter array, wrap your END_Date dynamic content in a string() expression so you are comparing string to string.  Right now I think you are comparing date to string and is the root cause.

[Seank02]

Hi, @trice602 

Yeah, I am comparing date to string. So, How do I wrap the END_DATE dynamic content in a string() expression? I have no idea on this. 

[trice602]

Hi @Seank02 ,

Not seeing the entire flow, but I think this is a better approach to implement or at least consider:

I have an excel table with some dates, in my example I have two columns...

Instead of using a text action on top of addDays, I just use the addDays expression as is:

formatDateTime(addDays(items('Apply_to_each')?['Date2'],7),'MM/dd/yyyy')

and I want to return the date format as 05/09/2023.

You can do that with a compose action shown below:

Next my Filter array, I would be comparing date to date and my filter works as requested/designed.

The key here is to format END_DATE as 'MM/dd/yyyy' to match your comparison date.

formatDateTime(items('Apply_to_each')?['END_DATE'],'MM/dd/yyyy')

And in my example, I have one row and it is a match.

[Seank02]

Hi @trice602 

                    I have try it out your approach but it does not work well. It prompts out the error shows that  'Unable to process template language expressions in action 'Compose' inputs at line '0' and column '0': 'In function 'addDays', the value provided for date time string '#VALUE!' was not valid. The datetime string must match ISO 8601 format.'.'. 

                     I have change the date time format to my time zone as 'dd/MM/yyyy' but still the same scenario. Here is the attachment of my entire flow as per attached. I need to convert my time zone according to my region becoz it does not apply any changes on my powerautomate time zone settings. So, I need to change it manually. 

[trice602 · Accepted solution]

Hi @Seank02 ,

Understood.  Ok, here's the string to string approach that might snap into your current model quickly.

string(formatDateTime(item()?['END_DATE'],'MM/dd/yyyy'))

Let me know if this works for you.  In my test, I pulled back my single row that matched.

[Seank02]

Hi @trice602 

                     I have been trying a few days and it did not work it out for me. Moreover, my current solution is replace the empty rows with 'NULL' using the formula. Then, I use the filter query in the excel connector to straight away match the specific date. Thanks for the helping and info. Really appreciate👍