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

Question

Question

Can workflow tell me what numbers are missing

asked on February 26, 2018

Can workflow be configured to run through documents in one folder, compare numbers in one metadata field, and report back on any numbers that are missing?

EXAMPLE CASE:  We recently discovered one of our accounts payable records was missing.  We had not developed any procedures to ensure every record ended up scanned properly.  We still don't know what happened to this one, we know it was scanned, but the record is not there.  These records are all filed with an entry name that ends in the cheque number, plus there is a Cheque Number field that could be viewed regularly to make sure there are no missing numbers.

Any ideas on how I can get WF to do this checking for me?

0 0

Answer

SELECTED ANSWER
replied on March 1, 2018

Move the track tokens to the end of the workflow and run it.  Then check your token tab and make sure things are populating.  Is your increment by 1 working?  Are you getting numbers for the token values (make sure your tokens have the number and not the string tag when you make them).  It will be your number one way to see if the values are where they should be for troubleshooting and learning.  Whipped it up again quick with descriptions (didn't include the email)

Starting folder:

Workflow:

Token results in the token tab of the workflow:

1 0

Replies

replied on February 27, 2018

Workflow should be able to do it.  You can query a spreadsheet if you setup the appropriate ODBC.  I have done something like this before.  It goes something like the following:

Create 3 tokens.  MissingNumbers(multivalue), StartNumber, LargestNumber (set to 0)

I assign the first number to the start number token.  So the smallest check number.  In my scenario I didnt know the end number so had to find that as well.  I did a search for all documents with the numer I needed (happened to be the name of my documents).  You will need to get it from the metadata.  Then for each result I simply checked if the current number is greater than the Largest Number.  This will essentially tell you what the biggest number is in your range.  

 

Next part (though still part of the same workflow).  You are going to repeast as long as your startingnumber is less than our equal to your largestnumber.  Search for the document with the check number that has the starting token.  Check to see if the document exists.  If it does, do nothing.  If it doesnt, add its number to the missingnumber multivalue token.  Then add 1 to your starting number and update the starting number token with that incremented value.

 

The very last thing I have is an email that emails me the multivalue missingnumber token.

In short, you get your smallest number and then your largest number.  Search for your number starting with the smallest number and then increment by 1 and repeat.  If you find it, do nothing, if you dont find it, log it. 

2 0
replied on February 28, 2018

Thanks for your response, Chris! I think you've got me on the right track!  But I'm not quite there yet.  I have an email reporting to me that there is a missing number (test run was on Cheque #'s 25126 to 25140 and there are two missing cheques, 25127 and 25129.  For some reason, it is only reporting to me about #25129.  What might I be doing wrong?

My experiment is using just the second half of what you showed above.  I did not even need an Excel spreadsheet.  I just put a cheque number in a metadata field on a specific folder (target test folder) for the repeat activity to grab each round.  At the end of each run through, it changes that field number to the next number (plus one) for it to grab at the top of the repeat activity each round.

 

I'm wondering if it's due to the token configuration in the email:

0 0
replied on March 1, 2018

Can't quite tell from the picture.  If its only showing you the last one, are you updating the missing token values with and append or replace?  Only getting the last one usually means replace, make sure it is append.

Also, move your track tokens to the very end of the workflow outside of any loops and run it again.  Then check your token tab in the workflow designer.  Does your missing number token hold both numbers or just one?

1 0
replied on March 1, 2018

Chris, my Assign Token Values window looks different from yours and doesn't have the option for Append to existing values.   ??

0 0
replied on March 1, 2018

That is because you are in the create token area and not modify.  You want to create the missing numbers token first and then update it.  Create the missingnumber token up in the same Assign Token Values activity as your starting and ending numbers (above search).

In your Missing branch, your Assign Token values is creating the token.  It should be modifying it.  Instead of Create, pick Modify.

Then select the MissingNumbers token up in the Assign Token Values activity.  It should read something like MissingNumber: Append 

1 0
replied on March 1, 2018

Okay, then.  Learning things today!  So, I made the changes and tried again.  Now the email coming to me is blank.  Here is what I have now (just clipped the pertinent parts):

0 0
replied on March 1, 2018 Show version history

And now I've tried this and the email only gives me the last number it was to check on:

0 0
SELECTED ANSWER
replied on March 1, 2018

Move the track tokens to the end of the workflow and run it.  Then check your token tab and make sure things are populating.  Is your increment by 1 working?  Are you getting numbers for the token values (make sure your tokens have the number and not the string tag when you make them).  It will be your number one way to see if the values are where they should be for troubleshooting and learning.  Whipped it up again quick with descriptions (didn't include the email)

Starting folder:

Workflow:

Token results in the token tab of the workflow:

1 0
replied on March 1, 2018

Will have to leave the office now, but I'm close!  It's now tracking the numbers correctly and finding the right results correctly, but the Missing Numbers token is coming up blank!

The missing numbers shows it did find the two missing numbers correctly, but the email does not reflect the two numbers:

Also tried the semi colon:

0 0
replied on March 1, 2018

Its most likely empty because it is not appending and the last number found was true.  The multi value token should appear like my screenshot above as one token with multiple numbers.  Your token list shows the missing number multiple times.  I am not certain if it is the retrieve fields (in my example the check number is in the field but notice I am not doing the retrieve fields) or the order.  Maybe just hard code in your first check number?  If you build it out like the example it should work for you.

1 0
replied on March 2, 2018

When I tried hard coding in the first cheque number (last thing I tried yesterday) the result was that it ran through the process once and did not continue through repeating the steps with each incremental number.

0 0
replied on March 2, 2018

Okay, I got it grouping the numbers by moving the first Assign Token activity outside of the Repeat cycle.

Then removed the Retrieve Field Value activity.... HA, SUCCESS!  Taking the first token outside of the Repeat cycle and removing the Retrieve Field Value worked, hard coding the starting number.  I guess I'll just have to enter the start number each time, which I don't think is a big deal at this point.  I got an email with the correct answers!  Thanks so much, Chris!!!

0 0
replied on March 2, 2018

Interestingly enough, even though I removed the Retrieve Field Value activity, the WF did not run properly unless I kept the related folder ID in the Start WF window:

0 0
replied on March 2, 2018 Show version history

Okay, it's not stopping at the right number:

  even though I have this condition:

It's just doing the 100 runs the Repeat activity is allowed.  Is this typical?

0 0
replied on February 26, 2018

Workflow can search for all documents in a folder, for each document do a SQL query of the Cheque number.  That is assuming you have a list of cheques in a database somewhere.

0 0
replied on February 26, 2018

We do have another piece of software that all the financial information is in, from which the cheques are generated etc, however, we have not gone so far as making a connection between the two (Serenic and Laserfiche) yet.  It would take some programming, but I've been told it can be done.

So, is there a way to have WF check against a spreadsheet (or a Word document with a table of numbers) and have it report on any matches/non-matches?  

I wonder if that new Append to Word document feature could be used to have it fill in the cheque numbers it finds on a line where it finds a matching cheque number on the Word document.  At least that way, it would be easier to spot a blank spot in the list.

I know I can just sort the columns in LF and view the cheque number fields, however, it is just so easy to not spot where a number might be missing.  Currently, this is what we are expecting to be forced to do as an attempt to spot anything missing.

0 0
replied on February 26, 2018

Workflow can only do lookups against external tables created in the laserfiche admin console.  If you have a spreadsheet you can import it into a SQL table.  Or save a step and make a SQL view of your Serenic DB. 

Use workflow to query that external table.  If you don't find results you have a missing check.  Have workflow send you an email with that list.

0 0
replied on August 9, 2018

This workflow is working now.  It is successfully only emailing me the correct missing numbers:

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

Sign in to reply to this post.