For Each Row Help

asked on November 20, 2014

I have a For Each Row activity in a workflow that was not working correctly. The way it is supposed to work is that first a query is run to search for records that have a value of false for the completed column. For each returned row it then needs to take the SSN and look up an employee in another database. From that set of results, it should then take the NameKey and lookup another employee in the same database. From the NameKey lookup, it will provide a supervisors email address that will be used to send an email notification. I made some changes to what had previously, but was wondering if someone could look over what I have done before I publish to see if I am missing something.

1 0

replied on November 20, 2014 • Show version history

Blake,

You don't specify exactly where the workflow is failing (unless I missed it). The only thing that sticks out to me is that I would probably do a single look-up to get the supervisor's email address versus two look-ups. Without looking at the table structures I would be surprised if you couldn't use a JOIN in the SQL SELECT statement to get the email address with a single query.

0 0

replied on November 20, 2014 • Show version history

On a closer look my guess is that you could actually craft a single SQL SELECT at the start of the workflow that had a WHERE 'Completed = False' and actually returned the supervisors email address as part of the query. That way you could iterate through the returned rowset and just pull the email field as a token when needed later in the For Each Record loop.

If you can provide the table structures I can help with the actual query if necessary...

0 0

replied on November 20, 2014

Cliff, thank you for being willing to help. Below is the structure of the database tables we are using.

The table the first query runs off of has the following fields:

The table the 2nd and 3rd queries run off of has a lot of fields, but the ones we need to worry about are SSN, Position Supervisor Namekey, and Nalphakey.

1 0

replied on November 20, 2014 • Show version history

I had to improvise since I don't have the actual table and field names. Here is my 'Reviews' table (I only added the pertinent fields);

Here is my 'UserProfiles' table that contains both the Users and Supervisors;

Here is the SQL query to get the names of the Users and their Supervisor for the Users that have not had a review;

SELECT Reviews.Completed,
Users.LastName + ', ' + Users.FirstName AS UserName,
Supervisors.LastName + ', ' + Supervisors.FirstName AS Supervisor,
Supervisors.Email
FROM Reviews
LEFT JOIN UserProfiles AS Users ON Reviews.SSN = Users.SSN
LEFT JOIN UserProfiles AS Supervisors ON Users.SupervisorNameKey = Supervisors.NameKey
WHERE Reviews.Completed = 0

Here is the resulting rowset;

NOTE: In my Reviews table the 'Completed' field is a bit field. SQL bit fields are 0 for False and 1 for True

I hope you can pull the relevant parts from the example to build the query for your application. If you need additional help you are welcome to email me at cprimmer@qfiche.com and I can help offline...

Good luck!

0 0

Question

Question

Replies

Sign in to reply to this post.