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

Question

Question

Custom Field in Report

asked on June 15, 2021

We have a field on a form that records the Business Unit for a user.  Due to restructuring, users are now in different business units.  Is there a way to add a custom field to reports that correlates the old BU to the new BU and keeps the reports accurate?

As far as I know the form data is locked (completed) so it can't be altered, but if there's an option from this perspective, please let me know that as well.

0 0

Replies

replied on June 15, 2021

Your best bet might be to build a custom database view that takes information from the LFForms submissions, and joins in whatever information you have regarding your employees.

Basically building a "report" directly from the database.

I don't think there is any way (short of database edits) to add the data in to Forms itself on process instances that were completed at some prior date.

0 0
replied on June 15, 2021

What database / table do I query for Forms data?  Is there a diagram showing fields, indexes, etc.?

0 0
replied on June 15, 2021 Show version history

This should get you started...  

SELECT
  s.[bp_instance_id],
  s.[submission_id],
  i.[bp_name],
  d.[attribute_id],
  d.[value]
FROM [LFForms].[dbo].[cf_submissions] AS s
LEFT JOIN [LFForms].[dbo].[cf_bp_data] AS d ON d.[submission_id] = s.[submission_id]
LEFT JOIN [LFForms].[dbo].[cf_bp_main_instances] AS i ON i.[bp_instance_id] = s.[bp_instance_id]
WHERE i.[bp_instance_id] = 123456

Instead of 123456, list the instance ID of one of your forms.  Run this to see all of the field data submitted across all of the submissions in that process.  Once there, you can determine what attribute_id is used for the desired field by reviewing the data values submitted to the value field.

Once you have that, replace the last line of the query with one that searches that attribute ID and the name of the business process, like this: 

WHERE d.[attribute_id] = 1234 AND i.[bp_name] = 'Process Name'

You should now have a query that lists every submission of that process with what was listed in the desired field.

Note that forms that have multiple tasks that include multiple submissions that could modify that particular field - if that is the case, you'll likely want to tweak your query in order to retrieve the value populated by the last submission_id within each bp_instance_id.

It'll take a few more leaps to make this in to a usable report, but hopefully this is a good jumping-off point.

0 0
replied on June 15, 2021

This is a great start...thank you!

1 0
replied on June 16, 2021

Should I be able to see data in the cf_submissions table?  It appears to be empty.

0 0
replied on June 16, 2021

Yeah, every user or workflow activity that populates form data should generate a record in that table.

I'm still on 10.2, so if you are on a later version, I guess it is possible that it was phased out at some point after 10.2.

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

Sign in to reply to this post.