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

Question

Question

Laserfiche Forms update SQL table record

Forms Workflow Laserfiche
Updated July 31, 2023
asked on July 28, 2023

After a form submission, we want a user to be able to update the form submitted, and reflect that update in the SQL record that was written in the first submission.

This the form, I am thinking to be able to update record, we would have a lookup that would retrieve the form if it already exists in the SQL table.

0 0

Replies

replied on July 28, 2023

I have a similar process that after the form is submitted, it triggers a workflow that does two things:

  1. It saves the values from the form submission to a custom SQL database and table.
  2. It sends a confirmation email to the initiator.

 

The "Form ID" number on your form ("Record Number" on mine) is a hidden field, but triggers all the lookups.  If it is blank, the field values are blank and can be filled in manually as a first-time submission, but if it is populated it triggers the lookups from the prior form submission, and will be treated as an update of the prior form.

The workflow checks to see if the "Record Number" on the form is blank. 

  • If it is blank, it will insert the record to the database (new submission).  I use the form instance ID to create the record number for the new item in this case, but there are other options too, including having the table auto-increment. 
  • If the "Record Number" on the form is not blank, the Workflow will do an update instead of an insert, to modify the previously submitted record.

 

The confirmation email that the workflow sends is how the user would edit that prior submission, it includes a link to the form, with the "Record Number" populated.  Since it's hidden, this is the only straightforward way to populate that value and trigger the lookup.  It's fairly easy to set-up in Workflow, and the structure of the URL would be like this (assuming the variable for the "Record Number" field is record_number):   

https://LFFormsServerURL/Forms/FormName?record_number=123456

 

0 0
replied on July 28, 2023 • Show version history

I've done similar things as well, however, I would highly recommend using a guid rather than a numeric value for the identifier, especially if this is a public form.

If you use a uniqueidentifier column in SQL you can configure the db to automatically generate a new guid for each request/row.

The main reason I usually go this route is that it prevents people from "searching" for valid ids using sequential number checks.

Since guids are unique and nonsequential that makes it substantially harder for people to find a valid identifier without you sending them a link.

This still wouldn't be considered a security feature since all it does is obscure the values, but it's still a good way to prevent easy data retrieval.

 

UPDATE: I notice the form has an "account number" field which suggests it contains sensitive information, so you want to be especially cautious about setting up a form that can look up and display data if those values are included.

You definitely don't want a public-facing form that could be used to retrieve any sensitive personally identifiable information.

3 0
replied on July 28, 2023

That's a very good idea!  The one I'm using isn't public, so it wasnt a concern, but I would definitely do that with a public form.

0 0
replied on July 31, 2023

I have a bunch of input/update workflows that are driven by Forms. It is super useful if you have the back end to manage it, and Jason I am going to try out your guid instead of a simple int value for an identifier. 

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

Sign in to reply to this post.

Details
Related Posts
Loading ...