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

Question

Question

search active forms processes

Workflow Forms Version 10 How To
Updated April 17, 2023
asked on September 25, 2019

We have a workflow to kick off the annual reviews of documents which runs on a weekly basis.  Currently, if the annual review has not been completed, thus causing the review by date to not be updated, this workflow will start a new instance of the forms process for the document due for review.

We are looking for a way for workflow to query forms to determine if that document is already under review.  With that, I can use a conditional decision to determine if it starts a new review process or not.

Thank you,

Jason

0 0

Answer

SELECTED ANSWER
replied on October 4, 2019 • Show version history

I’m attached three files.

Database View - Query.txt is the actual query that I’m running in the LFForms database in SQL Server.

Database View - Create Script.txt is the script that SQL Server generated for me that should take the query and actually make it a view on your LFForms database.

Business Process Report - LFForms Processes Assigned by User.xml (I changed the file extension to txt so it would upload, you'll need to change it back to xml in order for Forms to import it) is the business process I set-up in LFForms to search the view for all tasks assigned to any particular employee. If you haven’t already, you’d want to add the LFForms database as a data source in LFForms before you tried to import the file.

Once you have the View added to your database, you can use Workflow to query it too, which is how I do the email reports to our staff to let them know they have forms outstanding that are over a certain age.

 

EDIT TO ADD: If you are using Forms Version 11 Update 2 or later, the user_id field on the team_members table has been renamed to user_group_id.

Database View - Query.txt (7.31 KB)
Download
Database View - Create Script.txt (10.86 KB)
Download
Business Process Report - LFForms Processes Assigned by User.txt (117.91 KB)
Download
3 1

Replies

replied on September 26, 2019

Could you have another field on the document for Status: Under Review? When the WF triggers the review, it can set that status field, but users could also set the status field to under review if it's reviewed manually. 

6.2.5
0 0
replied on September 26, 2019

Jared,

 

This could work, will have to map out the scenarios to ensure everything is handled properly, but in theory it is a distinct possibility.  Thanks for helping me think outside of the box!

 

Jason

0 0
replied on September 27, 2019

I built a View that I added to our LFForms SQL database that mimics the "Monitor" page in LFForms.  I can then use Workflow to easily search the contents of this View, rather than having to try to query the multiple tables involved every time.

I use it for automated email reports of past due forms and several other processes.

If the identifying information is part of the task name or details (like what is displayed in your LFForms inbox) then it would be included in this View and thus easily accessible via a SQL Query from Workflow.

I can share the query that is used in the View if you'd like.

0 0
View 6 previous replies
replied on October 3, 2019

Matthew, sorry for the delayed response, was pulled away for another project.  I would be very interested in this query as this is exactly what I was envisioning.

1 0
SELECTED ANSWER
replied on October 4, 2019 • Show version history

I’m attached three files.

Database View - Query.txt is the actual query that I’m running in the LFForms database in SQL Server.

Database View - Create Script.txt is the script that SQL Server generated for me that should take the query and actually make it a view on your LFForms database.

Business Process Report - LFForms Processes Assigned by User.xml (I changed the file extension to txt so it would upload, you'll need to change it back to xml in order for Forms to import it) is the business process I set-up in LFForms to search the view for all tasks assigned to any particular employee. If you haven’t already, you’d want to add the LFForms database as a data source in LFForms before you tried to import the file.

Once you have the View added to your database, you can use Workflow to query it too, which is how I do the email reports to our staff to let them know they have forms outstanding that are over a certain age.

 

EDIT TO ADD: If you are using Forms Version 11 Update 2 or later, the user_id field on the team_members table has been renamed to user_group_id.

Database View - Query.txt (7.31 KB)
Download
Database View - Create Script.txt (10.86 KB)
Download
Business Process Report - LFForms Processes Assigned by User.txt (117.91 KB)
Download
3 1
replied on October 23, 2019

Matthew,

This looks like it will work.  I am working with our Database administrator to get the query implemented and for him to give me access to work with it. I will let you know when we have results.  Thank you again!

Jason

1 0
replied on October 23, 2019

Sounds good!

0 0
replied on October 24, 2019

That did it, the process is working as intended.  Our DBA actually had a very similar view, so once he saw what you did, he made some modifications to it and gave me access.  Thanks for your help!

1 0
replied on October 24, 2019

Fantastic!

0 0
replied on April 17, 2023

Just came across this while looking at ways to check on Forms processes that are in progress.  I noticed that you will have to change

team_users.user_id = team_members.user_id

to

team_users.user_id = team_members.team_id

for it to work. Possibly something changed in the table structure after a particular version upgrade.

 

1 0
replied on April 17, 2023

You are close @████████- with Forms Version 11 Update 2 or later, the user_id field on the team_members table has been renamed to user_group_id.

So you are right that it needs to be changed, but it is incorrect that it should be changed to team_members.team_id - it should instead be changed to team_members.user_group_id.

I have edited my original answer to mention this change that Laserfiche made.

1 0
replied on April 17, 2023

Perfect; thanks for the correction!

 

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

Sign in to reply to this post.

Details
Attachments
Related Posts
Loading ...