posted on June 9 • Show version history

I had a manager ask me for a report of the top 10 Forms submissions in a specific period of time. I couldn't figure out how to do it myself from within the Forms Reports UI so I googled "laserfiche forms report top forms/processes and their numbers".

Gemini AI offered two ways of doing it. One was through the Forms Reports UI but it's possible it was giving me directions for Cloud because it was referencing things that didn't appear in the Self-hosted UI, which we are. Or maybe it just hallucinated the whole thing.

The other way was via a SQL query. I decided to give that a shot. This is what it first suggested:
 

SELECT TOP 10 
    bp.name AS ProcessName, 
    COUNT(pi.process_instance_id) AS TotalSubmissions
FROM 
    dbo.cf_business_processes bp
JOIN 
    dbo.cf_process_instance pi ON bp.process_id = pi.process_id
GROUP BY 
    bp.name
ORDER BY 
    TotalSubmissions DESC;

I am by no means a whiz at SQL or intimately familiar with the database schema for the Laserfiche Forms database but at a glance it appears that at least one of the statements here is malformed. I corrected it and executed the query and got an error because the dbo.cf_process_instance table simply doesn't exist.

Was tempted to give up here but decided to point that out to Gemini just to see what it would do. This was its response:

You are completely right. cf_process_instance does not exist in the Laserfiche Forms database schema. [1]

Laserfiche tracks submissions using the cf_bp_main_instances table. The column name for the process name is also bp_name. [1, 2]

Use this corrected script to pull your top 10 most submitted processes:

 

Here's the corrected script:

SELECT TOP 10 
    bp_name AS ProcessName, 
    COUNT(bp_instance_id) AS TotalSubmissions
FROM 
    dbo.cf_bp_main_instances
GROUP BY 
    bp_name
ORDER BY 
    TotalSubmissions DESC;

This worked! However, it returned the all-time results so I asked Gemini to have the script filter the report for the time period in question. It returned a script that again referenced something that doesn't exist. 

 

SELECT TOP 10 
    bp_name AS ProcessName, 
    COUNT(bp_instance_id) AS TotalSubmissions
FROM 
    dbo.cf_bp_main_instances
WHERE 
    start_time >= '2025-08-01 00:00:00'
GROUP BY 
    bp_name
ORDER BY 
    TotalSubmissions DESC;

The column name in the dbo.cf_bp_main_instances table is start_date not start_time. Here is the final version of the script which worked.
 

SELECT TOP 10 
    bp_name AS ProcessName, 
    COUNT(bp_instance_id) AS TotalSubmissions
FROM 
    dbo.cf_bp_main_instances
WHERE 
    start_date >= '2025-08-01 00:00:00'
GROUP BY 
    bp_name
ORDER BY 
    TotalSubmissions DESC;

Probably nothing super surprising here to any of you who've used AI this way before but it was the first time I'd asked Gemini to write a SQL script and I thought it was an interesting experience. It got me to where I needed to be but I had to jump through some hoops to get there.

And I definitely would not want to try to get Gemini to write a complicated script. I was able to figure out the issues in this one only because it was so simple. At my skill level I would not be able to debug anything really complex if Gemini decides to hallucinate when creating it.

"Your mileage may vary" but this was my experience using AI to write a SQL Query.

EDIT: It occurs to me that this is probably pulling ALL the instances submitted whether they completed or not. I asked Gemini about this and it suggested using 'status = 2' in the WHERE clause which did return slightly different numbers. So the script wound up looking like this:
 

SELECT TOP 10 
    bp_name AS ProcessName, 
    COUNT(bp_instance_id) AS TotalSubmissions
FROM 
    dbo.cf_bp_main_instances
WHERE 
    start_date >= '2025-08-01 00:00:00' AND status = 2
GROUP BY 
    bp_name
ORDER BY 
    TotalSubmissions DESC;


And one more note! The manager specifically asked for numbers from August 1st, 2025 to the present. That's why I only needed to use start_date. But end_date is also available if you need to specify a more specific time frame.

 

2 0