Searching Large Datatable with Queue Item

Help Studio
1

I am bringing a queue item into a workflow and searching for a match of that queue item in a a large datatable. The search seems to be taking a long time just to find an item match in one column. Any tricks on speeding up the search?

I am doing a For Each row with the datatable to look for a match of the queue item.

Thank you

2

Hi @Chris_Livesay

Try our filter data table activity.

You can set the matching criteria and it should be faster than looping over the datatable row by row.

Hope this helps,

Ryan.

1 Like
4

Thank you, I think this is getting me closer. Definitely a lot faster. Followup if you can help.

Once I filter into datatable dtMatched, I need to put a combination of the transaction item and the dtMatched records into the AddRow to get it to a table to output to excel. I have the transaction items in the Row array but I am struggling with the correct syntax to add the items I want from the dtMatched filter. If I start typing I get options like dtMatched.Rows.Item(“ColumnName”) but it doesn’t like that syntax.

Thanks

5

Not sure if I follow correctly; can you share some examples of the data structures (types, format) and what you want to get out and that will help a lot.

If you’re writing that into the addrow, make sure that the datatype matches of the DT you’re trying to add to (e.g. string), this can often help resolve a lot of issues.

Is the relationship 1to1 , 1 to many or many to many. This might also affect the methods you need to use. So for each transaction you pull, could you possibly get multiple results from your filter?

Cheers,

Ryan.

6

I might has explained in a confusing way.

After I search the datatable, I need to be able to have the matching row in the datatable along with the .transaction item info, combined into a single row to output to a spreadsheet.

7

Ah, I understand now.

If your transaction items come in a datatable, you can add a datacolumn before processing. Then as you loop over, just set the row(“ColumnName”) to the matching value.

If not, then at the start of the process you can use the build datatable activity to create an empty datatable, then use the add data row and set the parameters in the ArrayRow property.

e.g. {transactionrow(“columnname”).tostring, Matches.rows(0)(“columnname”).tostring}

Note that arrayrow specifies an array variable (denoted by {}) so there is no need to encase this expression in quotations.

Where your two entries should be the values you want to add, assuming the column type is string for both entries.

That loop should then build you a full datatable which you can write to your excel.

An alternate approach could also be to consider using append range in the excel / workbook activities, and append each result to your workbook as you go through. This would circumvent the need for building a cumulative datatable, and you could instead append a smaller datatable as you go.

Let me know how you get on,

Ryan.