I have a similar process that after the form is submitted, it triggers a workflow that does two things:
- It saves the values from the form submission to a custom SQL database and table.
- 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