Filter DataTable by dates provided by user

Help
1

Hello!

I hope, someone can help me with this topic. I have tried some solutions from similar topics but nothing worked for me.
I am trying to filter a data table, which contains one column with dates, based on the input from a user.
Example: user needs to provide two dates (start and end), and the final table output, should contain only rows between these two dates.
I already have converted each row of the datatable column using “Convert.ToDateTime(row(1).ToString).ToString(“dd.MM.yyyy”)”.

Unfortunately I cannot share the xaml of this project due to sensitive data.

Best regards,
Rokko

2

Use filter data table activity and give the input as

image

1 Like
3

Hi @Rokko,

You can use .Net expression to filter the table based on the date just go through this thread.

Best!
Anmol

4

@Rokko Use Filter Data Table or use Select Method to Filter the column , but make sure you have the Column Data Type as Date time.

Or As you Are Already using a ForEach to Convert the Object To DateTime Then Compare the Date Value against the The Conditions you have then if the condition matches, then add that row to a new data table.

5

I used the Filter Data table activity, however it is not working. I still get all the results from the original input.
Here you can take a look on the part of the sequence I’m using to prepare my DataTable:

  1. First trims

    For%20each
    For%20each386×732 12.5 KB

  2. Filter for DT

    Filter%20table
    Filter%20table564×513 13.2 KB

The two variables used in the filter are the DateTime type, I’m collecting them from a user input.

6

Anyone have a solution?

7

Hello,

Can anyone help me with this topic?

8

Have you tried this @Rokko?

9

Yes, above I attached a screenshot from my filter database activity.

10

Are you sure the two variables are in the same format as the values you have in the excel @Rokko?

Click on the value in excel and see the format you have in the formula bar. then try to pass the params as in the excel

11

Well, in the “For each” activity I am converting data from my excel, using assign like this: row(1) = Convert.ToDateTime(row(1).ToString).
The two variables are for sure in the same format (Datetime).
That is why it is a bit confusing for me.

12

Please try to display the value in the for each row as row(“date column heading”).tostring and the value you are getting from the user, and check if they are in the same format including the slashes or the colons you have

13

Sry but 1 question if you already have start and end dates why are you converting a date to a Date ??

You just need to get da information and make it like your system needed.

Like if you already have

10.11.2019
and if you system need to get
10/11/2019 → just use replace to replace . to /

Or if the format is already correct you just need to get the data to a string e get it to you webpage or system …

14

Or try to use that but “Convert.ToDateTime(row(1).ToString).ToString(“dd/MM/yyyy”)”.

And then replace dd/MM/yyyy from / to . → this will work

15

@HareeshMR, here are the SS from my locals panel. As you can see, both variables and dates in my Data Table are in the same format:
Locals_1
From my table:

Locals_2

16

And how do you whant the resut to be ?

10.10.2019 ?

17

@Luis261980, I want to filter out from my DT all rows, which are not inside the range provided by the user.
If the range in this example is start=09/10/2019 and end=09/30/2019, the DT should have only rows in which date is between these two dates.

18

I understand @Rokko

I’m asking you to check whether you are providing the value for filter data table in the same format?

19

@HareeshMR, they should be in the same format. I prepared the new .xaml file when I cut out all sensitive parts of my process. Please, take a look and tell me if something is wrong.
I attached also a sample of my .xls file.TEST.zip (10.8 KB)

20

@HareeshMR, please, let me know if you need more info.