Unable to filter datatable using date filter

I am unable to filter a datatable extracted from googlesheets. I need to filter the datatable using date columns.
PFB the datatable format:
[Sno,Resource Name,Contact #,Email ID,Local Computer Name,Domain Account,HDI name,HDI IP,Domain Expiry Date,VPN Expiry Date
1,XXXXXX,XXXXXXX,XXXXXXXX,XXXXXXXxx,XXXXXXXXX,XXXXXXX,1XXXXXXXx0,9/30/2019,9/30/2019]

I need to create a filtered datatable that contains data with domainexpiry or vpn expiry that are about to expire in the next 15 days.
I tried using Assign activity method as well, but that is gives a runtime exception;

Any inputs would be helpful.

Thanks!

2 Likes

Hi @Parikshith_V

Can you share your xaml to easily adress the issue

cheers
Happy learning.

3 Likes

@Parikshith_V
You can try with select method of datatable.

Thanks

2 Likes

@Parikshith_V
Please find the below which has a solution,


Hope this will help you.

Thanks,
Suresh J

1 Like

Hello @pattyricarte,
I would love to do that. Unfortunately, the xaml contains the auth info for my google account.

The following is what has been done:
a) use “Find Files and Folders” - to get the file ID of the file I need
b) Used Read Range activity to create a datatable of a range from the googlesheet file
c) Then i used the filter datatable activity to try and filter based on the column name of the date column like this:
“Domain Expiry” <= Today.AddDays(15)
OR “VPN Expiry” <= Today.AddDays(15)
This gave me no results although the data is available in the dataset.
d) I’ve now removed this activity and trying assign activity with below code, But I get a runtime exception as displayed below:
filteredDataSet = DomainVPNData.Select("[‘Domain Expiry Date’]> Today.AddDays(15)").CopyToDataTable()

Exception:
RemoteException wrapping System.Data.EvaluateException: The expression contains undefined function call Today.AddDays().
at System.Data.FunctionNode…ctor(DataTable table,
String name)
at System.Data.ExpressionParser.Parse()
at System.Data.DataExpression…ctor(DataTable table,
String expression,
Type type)
at System.Data.DataTable.Select(String filterExpression)
at lambda_method(Closure ,
ActivityContext )
at Microsoft.VisualBasic.Activities.VisualBasicValue1.Execute(CodeActivityContext context) at System.Activities.CodeActivity1.InternalExecuteInResolutionContext(CodeActivityContext context)
at System.Activities.Runtime.ActivityExecutor.ExecuteInResolutionContext[T](ActivityInstance parentInstance,
Activity1 expressionActivity) at System.Activities.InArgument1.TryPopulateValue(LocationEnvironment targetEnvironment,
ActivityInstance activityInstance,
ActivityExecutor executor)
at System.Activities.RuntimeArgument.TryPopulateValue(LocationEnvironment targetEnvironment,
ActivityInstance targetActivityInstance,
ActivityExecutor executor,
Object argumentValueOverride,
Location resultLocation,
Boolean skipFastPath)
at System.Activities.ActivityInstance.InternalTryPopulateArgumentValueOrScheduleExpression(RuntimeArgument argument,
Int32 nextArgumentIndex,
ActivityExecutor executor,
IDictionary2 argumentValueOverrides, Location resultLocation, Boolean isDynamicUpdate) at System.Activities.ActivityInstance.ResolveArguments(ActivityExecutor executor, IDictionary2 argumentValueOverrides,
Location resultLocation,
Int32 startIndex)

2 Likes

Hello Suresh,

I already went though these topics and tried using the solution. I am getting an exception when using select.
I’ve posted the exception on another reply below.

1 Like

@Parikshith_V

you are using Today.adddays(15) which holds time (09/17/2019 00:00:00)
Instead, change the format into a date as you need.
By Today.adddays(15).tostring(“MM/dd/yyyy”)

Hope this will help you.
Thanks,
SureshJ

1 Like

No worries
Use this expression in the assign activity

Yourdatatablename = yourdatatablename.Select(“[Domain Expiry Date] >= ‘ # + yourvalue + # ‘ or [VPN Expiry Date] >= ‘ # + yourvalue + # ‘ “ ).CopyToDatatable()

For more info on this

Cheers @Parikshith_V

1 Like