How to custom sort a datatable

Help
1

Hi,

Like Excel function “custom sort”, I’d like to sort by several columns.How can I get it ?

Thanks

2

You can do this by creating a DataView on your table. Suppose you have a table named myTable that is to be sorted first by Column1 in ascending order and second by Column2 in descending order. This can be done as follows:

  1. Assign myView = New DataView(myTable) as a DataView variable;
  2. Assign myView.Sort = "Column1, Column2 DESC".

myView now has the desired result, which you can access by a For Each loop, setting its TypeArgument to DataRowView. They are similar to normal DataRow objects, so you can get items in a row by their column index or name as usual. If you need to have the sorted table as a DataTable object, you can get it with myView.ToTable.

5 Likes
3

Hi @sfranzen

Thank you for this description. I have tried to put together a workflow based on your suggestion but am doing something wrong and am getting error “Unable to cast object of type ‘System.Data.DataRowView’ to type ‘System.Data.DataView’.”

Please will you let me know what I am doing wrong.

sortBpmData.zip (7.7 KB)

sortTableLearn.xaml (9.6 KB)

Thanks in advance

1 Like
4

I asked too soon and have now worked it out as per the attached. Thank you so much @sfranzen

sortTableLearn.xaml (8.5 KB)

2 Likes
5

Main.xaml (10.3 KB)

Hi,thanks for your help.
As a new-hand, I had a try but… failed,could you please tell me where I was wrong~

BR

6

Where do you get an error? I personally didn’t try a complete workflow with data from Excel, just the datatable manipulations, and I don’t have it installed on this PC. My hunch is that the column names are not A, B, C and so forth, and this will raise an exception where you assign to myView.Sort. You can verify this with the Output data table activity, which will give you a String representation of the table that you can write to output.