ADixon
Novice

Split logic involving SQL Data

Jump to solution

Hi all,

 

New to K2... less than a month in,  and hoping for some of you more experienced individuals to make a suggestion around splits and data evaluation to help me with a workflow I'm putting together.  

 

I have a process that is going to rely on a SQL stored procedure to evaluate a set of records from a data table, and then update a specific column on that table with an error/warning code based on what (if any) problems exist with each given record:

 

  • If no problems exist, the column is NULL 
  • If there is an error, the column will contain an integer between 1-499
  • If there is a warning, the column will contain an integer between 5*personal details removed*

 

I am trying to get my workflow to evaluate that column, run some matching against it for patterns to identify if the code exists, then route the flow based upon that evaluation to either prompt the user to take action or proceed. Logic I'm trying to achieve is:

 

  • If any record in a set contains a value in this column and that value is in the Error range, send the whole set it to a human for task action (don't want to just loop through and send 100 review tasks - just 1 with the list). 
  • If not, send it to a second evaluation to check for warnings. 
    • If they exist, prompt action (again, send whole data set in 1 task);
    • if not, send through to completion.  

If the user does take action, the table will update to wipe out the error, and at which point we run it back through another check to ensure the fixes didn't inadvertantly create more errors.

 

No matter what type of logic I try in the split step custom rule, I am having a hard time figuring out how to do this.  After having spent the better part of a day trying various things, figured I'd try to see if the community has suggestions.  I haven't tried doing a loop that individually evaluates each record (read method vs. list), so maybe I will try that, but was hoping for some other way to solve this using expressions and the smartfield composer.

 

Any suggestions would be appreciated!

 

0 Kudos
Reply
2 Replies
tbyrne777
Contributor

Re: Split logic involving SQL Data

Jump to solution

If you're already using a stored procedure in some capacity within your workflow, might I recommend that you write another stored procedure that does all of this logic and returns a simple value to your workflow that you can use to make your decision? I always try to unload heavy-lifting somewhere other than K2 to keep the workflow simple - makes things easier to maintain in the long-run in my experience.

0 Kudos
Reply
ADixon
Novice

Re: Split logic involving SQL Data

Jump to solution

Thanks, tbyrne777!  I had thought about that after I posted as well.  

 

That said, I figured out how to make it work via a loop step, but to your point it's a little more complex perhaps than having my DBA write another Stored Procedure.  Should anyone want to know how I did it through a loop step, details are contained in the attached PDF (created this for internal reference in our team since we're learning the software, but maybe it will help someone here).

 

 

It may not be pretty or especially elegant, but it seems to work.

 

 

0 Kudos
Reply