Filtering Data Table Activity

Thank you so much! Both of you!

UiPath is stating that something must come after the Convert, so I am working through that.

I appreciate the explanations!

1 Like

I hope that should work, we should not mention the integer between quotes, i think that was the error @Sara_Car

yourdataTable.Select(“Convert([BCHSTTUS],'System.Int32:)>0 AND [Company Name]=’“+presentcompay.ToString()+”’ AND [BACHNUMB] like ‘WS’ " ).Copytodatatable()

1 Like

Just popping in to say your original code will pull in an Array of DataRows that are either Greater Than 0 or not a number. The Select expression you are working on will essentially end up accomplishing the same thing with different syntax.

But, I want to mention that you should change the True to a False, so if the value is Not a number, then it won’t pull in those rows.

The LINQ expression should end up looking like this:
apbatchesws.AsEnumerable.Where(Function(r) If(IsNumeric(r(“BCHSTTUS”).ToString.Trim), Convert.ToInt32(r(“BCHSTTUS”).ToString.Trim) > 0, False) And r(“BACHNUMB”).ToString.Trim.StartsWith("WS") ).ToArray

That will pull in an Array of DataRows that are Greater than 0 And StartsWith “WS”, assuming that’s the conditions that are needed. If you want Equals or Less Than 0, just adjust it accordingly (Note if you do Equals to 0, then change the False to True so it picks up the rows where no value is in the cell).

This should be used in an Assign activity to an Array of Rows or directly in a ForEach (if you want to skip the Assign step). If no matches are found and the count of the array is zero, the ForEach will automatically not run, but you can also use an If condition to check the count like: arrRows.Count > 0 - The same goes for using a Select expression.

I hope this helps too.

Regards.

@ClaytonM You talked about a LINQ expression. I’ve done some research but not finding anything material to understand this. How is this used? When would this be used?

Thank you!

It is used in the same way as the Select expression is, but Select uses syntax similar to SQL and LINQ uses available .net expressions.

For example, a condition in LINQ can use str.Equals(), str.Contains(), and so on, whereas Select uses conditions differently.

A benefit of using a Select expression is that it is in a string form, so you can concatenate conditions as a string to form your query, whereas a LINQ it’s all in .net

I suppose it’s more preference. To me, the query syntax for Select is more difficult to understand, rather than just using what .net provides to manipulate the data you are comparing.

Both can be used to form an Array (with .ToArray) or a DataTable (with .CopyToDataTable). They can be used in an Assign activity or anywhere that let’s you use an expression.

A common use for me is to place it directly in a ForEach.
For example,
For each row In dt1.AsEnumerable.Where(Function(r) r("col").Contains("abc") ).ToArray
similarly,
For each row In dt1.Select("[col] Like 'abc'").ToArray

Using a For each like this allows you to execute a process on particular items while keeping the original data intact. There are also times when you must check that it found DataRows, otherwise it will throw an exception, which you can do by Assigning it to a variable first then using .Count on the end to check if it’s > 0. If you were to use .CopyToDataTable, you would A) lose the other rows in the data that didn’t match and B) throw an exception if no DataRows were found that match that criteria (from what I understand).

I can’t say I know the query syntax for Select expressions that well, but hopefully this helps explain your questions.

Regards.

1 Like

@ClaytonM thank you for that!

batchestopost.Select(“CInt([BCHSTTUS] ) AND [Company Name]=‘“+presentcompany.ToString+”’ " AND [BACHNUMB] like ‘WS’ " ).Copytodatatable

This is my current code. I’m getting an expression error. I’ve been stuck for a while. What else could I be missing? I have this in an assign activity and don’t know how to troubleshoot.

1 Like

You have an extra quotation. The query string should be that maroon color that signifies a string.

Other than that, can you share what the error says?

2 Likes

@ClaytonM Ahh, thank you! That fixed that error, on to the next.

I did change the CInt because of the error to a Convert.ToInt32 but still receiving this:

image

1 Like

Try using @Palaniyappan suggestion for converting to integer, like this:
batchestopost.Select("Convert([BCHSTTUS],System.Int32)>0 AND [Company Name]=’"+presentcompay.ToString+"’ AND [BACHNUMB] like ‘WS’ " ).Copytodatatable

Also, if that works, make sure you test if a cell value is empty, so it doesn’t throw a conversion error when there is blank cells.

Regards.

3 Likes

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.