You are viewing limited content. For full access, please sign in.

Question

Question

Workflow Query Criteria

asked on July 11

How can I get the workflow Query Criteria to pick up the value of what is in an SQL table without having to tell it exactly what is there?  I want it to pick up any value that it finds.

ISSUE:  I'm working with an SQL table that really only holds one thing:  One number to be used for sequential purchase order numbers.

I have everything working except the workflow Query Data activity, where it picks up the current number prior to the Token Calculator activity where it adds one (+1) to the current number and then pushes the new number into the SQL table to be used for the next purchase order.

I've narrowed it down to the Query Data activity, which doesn't seem to be able to pick anything up unless you tell it the value that is already there.

0 0

Answer

SELECTED ANSWER
replied on July 11

If I'm understanding correctly, you just want to return all of the values in the table, in which case you would not include any Query Criteria at all.

If you select the table and do not include any criteria, it will just return everything in that table.

0 0
replied on July 11

Thanks, Jason. 

When I do a test with nothing in the value, I'm getting zero results ("0 rows affected).

My table has only one row and one column; just a spot for one number.  My form's lookup connection is working and is pulling that one number into the form correctly.  So if Forms can see it, workflow should be able to as well.  Actually, it does see it.  I tested it by entering the Query Criteria with the number that is currently there and in that case, I get "1 row affected" during my test.  So it is finding the target row and the value.  I just can't get it to pull any number.

Any other thoughts?

0 0
replied on July 11

Ahh, wait a minute!  You mean to not even have a line in there.  I deleted entirely and ran another test and got "1 row affected"!  Thanks, Jason!

0 0
replied on July 11 Show version history

Okay, now it's not updating!  Any thoughts on this one?

0 0
replied on July 11 Show version history

It's not updating because nothing in the table matches your "Rows to Update" criteria. The way you have it configured, it is looking for a blank value for Purchase Order Number.

Basically, it is the same problem as before. Either remove the criteria entirely, or set the "Value" equal to the Purchase Order Number returned from the initial query.

1 0
replied on July 11 Show version history

Right!  Remove everything.  Works! Thanks!

0 0
replied on July 11

Out of curiosity, is this a process that can only run one at a time? Because if it is not, there is potential for duplicate PO numbers.

It is "unlikely" because the timing would have to be just right, but it could definitely happen, especially if any of the steps take longer than usual.

For example,

  1. Process 1 and 2 start at close to the same time
  2. Process 1 pulls the PO number (123)
  3. Process 1 Token Calculator generates the "next" PO number (124)
  4. Process 2 pulls the PO number (123)
  5. Process 1 updates the PO number (124)
  6. Process 2 Token Calculator generates the "next" PO number (124)
  7. Process 2 updates the PO number (124)
1 0
replied on July 11

Currently, there is only one person that would ever use this, however, there is the potential that one day it would be opened up across the organization.  Is there a better way to do this?

0 0
replied on July 11 Show version history

If you don't mind having multiple rows in your table, you could do the following.

  1. Create the table with at least two columns
    • An "ID" column using SQL "IDENTITY"
    • One or more columns for some other data (date, instance id, etc.)
  2. Instead of querying and updating the table, use an Insert Data activity, and check the box for "Create Identity Token"
  3. Use the Identity Token as your PO

 

For the SQL table

CREATE TABLE [dbo].[PurchaseOrder](
	[PurchaseOrderNumber] [int] PRIMARY KEY IDENTITY(1,1),
	[WorkflowInstance] [uniqueidentifier] NOT NULL
)

PRIMARY KEY requires "PurchaseOrderNumber" to be unique

IDENTITY(1,1) tells SQL to start at 1 and auto-increment by 1

What this does is create a table that automatically increments the "Identity" column when a new row is added and by default cannot be set manually.

 

In Workflow,

Here, you are inserting data into your other column. The IDENTITY property will automatically assign the "next" unique value (don't try to set it manually).

The "Create Identity Token" will return the auto-generated value as a token that you can then use in the workflow as your Purchase Order Number.

 

By doing this, you:

  1. Reduce it down to 1 query per workflow
  2. Don't have to "manually" generate a unique ID
  3. Allow SQL to take care of "uniqueness" and concurrent requests

 

If you really don't want to keep data long term, you could add a Custom Query to delete the row after you get the identity.

As long as you don't DROP or TRUNCATE the table, the IDENTITY will continue to increment even if you delete the table contents.

 

0 0

Replies

You are not allowed to reply in this post.
You are not allowed to follow up in this post.

Sign in to reply to this post.