Question

How to Extract Laserfiche Forms Monitor (Instances & Tasks) Data via SQL for Custom Dashboards?

Forms

Updated 15 hours ago

asked one day ago

Hi, we are on Laserfiche 11 and want to build a custom consolidated dashboard/KPIs for a specific Laserfiche Forms process (for upper management). The built-in Forms dashboards are useful administratively, but we need to extract Monitor data via SQL so we can schedule/sync it into our own dashboard tables.

Is it possible to query the Forms database (directly or via supported reporting views) to retrieve the same fields shown in the Monitor → Instances and Monitor → Tasks grids for a specific process?

Instances fields needed (as per Monitor):

Instance, Status, Started by, Start date, Last updated, Current step, Current step start date, Current stage, Assigned to, End step, End date, Duration

Tasks fields needed (as per Monitor Tasks):

Task, Instance, Status, Assigned to, Task creation date, Due date, Priority, Action, Completion date, Completion performance, Duration, Last assigned date, Last updated date, Performance time, Task stage, Team

If yes, please share any sample SQL or the table/view names + joins required to extract these fields for a given process.

0 0

Replies

replied one day ago

Create a scheduled report of the data you need and have it save the excel/csv file to the repository. Either use workflow or have a custom script job somewhere grab the reports and push them to SQL.

0 0

replied one day ago

Thanks Zachary — appreciate the suggestion.

The export-to-Excel/CSV and to SQL part is already sorted on our side. The main challenge we’re facing is specifically around Tasks data. Laserfiche Forms currently allows scheduled reporting for Instances, but there is no equivalent scheduled reporting capability for Tasks, which prevents us from building a complete operational dashboard.

So while your approach works well for Instances, it doesn’t solve the Tasks visibility gap, which is critical for SLA, workload, and team performance KPIs.

If there is a supported way (or workaround) to extract Tasks-level data in a similar scheduled or automated way or SQL directly, that’s what we’re really trying to solve.

0 0

replied one day ago

Here is a script that should help you get the tasks assigned:

SELECT      [Process Name], [Instance name], [Started by], [Last updated], [Assigned to], [Assigned to (username)], [Assigned to (email)], [Current step], [Step start date], [Start date], [Instance ID]
FROM            (SELECT        instance.bp_name AS [Process Name], instance.title AS [Instance name], 
                                                    CASE WHEN start_user_snapshot.displayname LIKE '%WORKFLOW%' THEN 'Workflow' ELSE start_user_snapshot.displayname END AS [Started by], instance.lastacted_date AS [Last updated], 
                                                    CASE WHEN bp_worker_resume.status = 5 THEN assigned_user_snapshot.displayname WHEN bp_worker_resume.status = 2 THEN assigned_user_snapshot.displayname WHEN assigned_user_team_snapshot.displayname
                                                     IS NULL THEN teams.name WHEN bp_worker_resume.status = 1 THEN assigned_user_team_snapshot.displayname END AS [Assigned to], 
                                                    CASE WHEN bp_worker_resume.status = 5 THEN assigned_user_snapshot.username WHEN bp_worker_resume.status = 2 THEN assigned_user_snapshot.username WHEN assigned_user_team_snapshot.displayname
                                                     IS NULL THEN team_users.username WHEN bp_worker_resume.status = 1 THEN assigned_user_team_snapshot.username END AS [Assigned to (username)], 
                                                    CASE WHEN bp_worker_resume.status = 5 THEN assigned_user_snapshot.email WHEN bp_worker_resume.status = 2 THEN assigned_user_snapshot.email WHEN assigned_user_team_snapshot.displayname IS NULL
                                                     THEN team_users.email WHEN bp_worker_resume.status = 1 THEN assigned_user_team_snapshot.email END AS [Assigned to (email)], bp_worker_resume.step_name AS [Current step], 
                                                    bp_worker_resume.assign_date AS [Step start date], instance.start_date AS [Start date], instance.bp_instance_id AS [Instance ID]
                          FROM            dbo.cf_bp_main_instances AS instance LEFT OUTER JOIN
                                                    dbo.cf_user_snapshot AS start_user_snapshot ON start_user_snapshot.id = instance.user_snapshot_id LEFT OUTER JOIN
                                                    dbo.cf_bp_worker_instances AS bp_worker ON bp_worker.bp_instance_id = instance.bp_instance_id LEFT OUTER JOIN
                                                    dbo.cf_bp_worker_instnc_to_resume AS bp_worker_resume ON bp_worker_resume.worker_instance_id = bp_worker.instance_id LEFT OUTER JOIN
                                                    dbo.cf_bp_worker_instance_history AS bp_worker_history ON bp_worker_history.instance_id = bp_worker.instance_id AND bp_worker_history.status = 'assigned' AND bp_worker_resume.owner_snapshot_id IS NULL
                                                     LEFT OUTER JOIN
                                                    dbo.cf_user_snapshot AS assigned_user_snapshot ON assigned_user_snapshot.id = bp_worker_resume.owner_snapshot_id LEFT OUTER JOIN
                                                    dbo.cf_user_snapshot AS assigned_user_team_snapshot ON assigned_user_team_snapshot.id = bp_worker_history.target_snapshot_id LEFT OUTER JOIN
                                                    dbo.teams AS teams ON teams.id = bp_worker_resume.team_id AND bp_worker_resume.owner_snapshot_id IS NULL LEFT OUTER JOIN
                                                    dbo.team_members AS team_members ON team_members.team_id = teams.id AND team_members.member_rights = 3 LEFT OUTER JOIN
                                                    dbo.cf_users AS team_users ON team_users.user_id = team_members.user_group_id
                          WHERE        (instance.status = 1) AND (bp_worker_resume.status = 5 OR
                                                    bp_worker_resume.status = 1 OR
                                                    bp_worker_resume.status = 2) AND (bp_worker_resume.assign_date IS NOT NULL)) AS subquery
GROUP BY [Process Name], [Instance name], [Started by], [Last updated], [Assigned to], [Assigned to (username)], [Assigned to (email)], [Current step], [Step start date], [Start date], [Instance ID]
ORDER BY [Start date]

0 0

replied 15 hours ago

Thanks Angela for the tasks assigned SQL Query. Is it possible to include completed tasks in the same query or another query with additional columns (Task name, Due date, Last Assigned Date, Completion date, Action)?

This will help to calculate SLAs and performances in the dashboard.

0 0

You are not allowed to follow up in this post.

Question

Question

How to Extract Laserfiche Forms Monitor (Instances & Tasks) Data via SQL for Custom Dashboards?

Replies

Sign in to reply to this post.