Question

Check number of rows returned by Query Data Activity in Workflow

Workflow Version 9

Updated January 14, 2014

asked on November 8, 2013

I want to use the Query Data activity in a workflow to query our CRM but I need to check that only one row has been returned and not more than one. Depending on the result I would handle the document differently.

How would I go about checking the number of rows returned by the Query Data activity? Or is there a better way to do this?

0 0

Answers

APPROVED ANSWER

replied on November 11, 2013

You can get the count value directly by writing a SQL query for it:

http://www.w3schools.com/sql/sql_func_count.asp

2 0

SELECTED ANSWER

replied on November 8, 2013 • Show version history

You can use a "For Each Row" and then inside it use a token calculator and assign token values activity to create a counter token.

Once you have the counter token, you can go ahead and use that in your conditional/routing decisions and/or sequences to determine how many results you have from the query.

2 0

Replies

replied on January 13, 2014 • Show version history

I know this is an old thread but for completeness I will throw out that using a SQL 'SELECT COUNT(*)' query is much more resource friendly than the loop counter option. A quick test on my demo system shows the following; when using a query that returns 160 records the SQL 'SELECT(*)' query completed in 460ms. Using the loop counter method it took 1891ms to return that same count.

The syntax for a SQL COUNT(*) query is as follows;

SELECT COUNT(*) AS RecordCount FROM toc WHERE toc.name LIKE 'D%';

Where the count of records returned by the query will be in the 'RecordCount' token exposed by the Custom Query activity.

You can also add token parameters to the query just like any other SQL query;

SELECT COUNT(*) AS RecordCount FROM toc WHERE toc.name LIKE ?

Where the token parameter '?' is set by an earlier activity to D% in this example.

Even though you might be doing two SQL queries (the first to get the RecordCount, and the second to return the recordset if the RecordCount = 1) you will still potentially be saving workflow cycle time by using the query method.

1 0

replied on January 14, 2014

Thanks for doing this test! Will have to test to see if this improves the workflow efficiency.

0 0

replied on November 8, 2013

Thank you very much Kenneth! That solution will work perfectly.

0 0

replied on November 11, 2013

It would be nice if like with a Search repository Activity, that the query Data activity returned a value count token. Small thing to add, but a great help in increasing efficiency considering they give you a value count if you test the query inside of the workflow designer

0 0

replied on November 22, 2013

Would this be the same result if I used the "Results Found" token that the Query Data activity creates? This seems like it would be easier but I will have to find out if it returns the same results.

0 0

replied on November 22, 2013

Results Found is a Boolean. This means it has 1 of the two following values: true, false

1 0

replied on November 22, 2013

Ok I understand now. The first solution you posted works great I just thought I could shorten my workflow a bit. As you said previously it would be a great feature to have built in like the Search Repository activity.

Thanks again!

0 0

You are not allowed to follow up in this post.

Question

Question

Check number of rows returned by Query Data Activity in Workflow

Answers

Replies

Sign in to reply to this post.