Question

Cleaning up historical Forms data for reporting

Forms Version 11

Updated March 23, 2023

asked on April 5, 2022

We have a business process that has been running for many years. As our business has evolved over time, our Segment, Business Unit, Division names have changed as well. This has caused the summary reports generated from this process to lose their effectiveness because they are incomplete. It would be great if there was a way to clean up historical Forms data and make these reports accurate and meaningful to the process participants without having to purchase, integrate and maintain an additional 3rd party reporting tool. The recent addition of the "Edit Variables" feature in Forms 11 has been extremely useful for in-process instances and this added functionality would allow this feature to be extended to completed processes as well.

If there is no plan to implement this feature in the short-term, would it be possible to provide a SQL query that would allow the correct database fields to be updated in the interim? Thanks!

9 0

Replies

replied on April 5, 2022 • Show version history

There are certainly a few people interested in this feature request. A quick search of answers reveals that. As an alternative suggestion, maybe Laserfiche could add a way to mask data in reports with new values. When column equals X display Y. If we want to be offering a reporting dashboard, there needs to be a better way to help our clients "clean up" their data post instance completion.

The prime use case for this type of solution is when a department name changes or when two designers create a process and the department names do not match. They need to be able to go back into the data and synchronize the department names for reporting.

3 0

replied on April 5, 2022

There is definitely a need for this. I have tried using a couple of different scripts in Workflow to get to this based off a list of InstanceIDs and values I want to write for a field I am targeting.

I can:

a) get the latest SubmissionID for a certain InstanceID in a particular forms process.

b) use that SubmitID and InstanceID to have workflow pull the value for a particular FieldID I want to alter.

c) if the value is blank, use the value from the lookup spreadsheet. Otherwise move on to the next.

This seems like it would work, but I don't think I am not getting the latest form SubmissionID that was submitted/saved. There has to be a flag I am missing for determining the last saved form in the DB.

When I run the workflow, it says it is successful, but nothing changed when I ran the report from LF Forms. So I may be just changing the field value from the previous submission.

To get the SubmissionIDs, I am using:

SELECT MAX(submission_id) AS SID
FROM [LF-FORMS].[dbo].[cf_submissions]
WHERE bp_instance_id=@InstanceID <--- this is coming from the flat file containing the instances, fieldID I am targeting and new values

That's where I left off since it wasn't being reflected in the DB when I pulled the report. Hopefully something like this can be used to update old, erroneous data.

Thank you!

2 0

replied on April 18, 2022 • Show version history

Extend the update variable functionality to completed instances should not be too hard and the update action can be audited from the instance history. But if you have a large amount of instances, you will need to update them one by one since there is no bulk update.

Data mask in custom report may be a better solution and it will be useful for protecting sensitive data as well, but the development effort is bigger so it will take longer for us to evaluate the solution.

1 0

replied on March 23, 2023

I would like this feature as well.

Having a way to edit/clean responses with the correct data (eg. Spelling errors) after the form has been completed would save us a lot of extra time correcting reports after they have been generated.

The problem compounds over time as more and more responses are collected (each with a creative way of spelling the names of various employees for example). Each time we run the report, we have to clean them before they are consumed by the user.

Herb

1 0

replied on April 18, 2022

Thank you! We would like to look forward to both updates. Is that the plan?

0 0

replied on April 18, 2022

We may take extend the update variable value for completed instances as the start point.

3 0

You are not allowed to follow up in this post.

Question

Question

Cleaning up historical Forms data for reporting

Replies

Sign in to reply to this post.