Everyone knows that workflow can send data to Forms using the Invoke Business Process and Set Business Process Variables activities. However, one important thing it cannot do is "attach" files to File Upload fields. We were hoping that version 10.1 would have this ability and were disappointed when it didn't. So we decided to figure out how to do it ourselves.

The idea behind this approach is to provide a unified interface for all document approvals. A lot of users, especially those who are not tech-savvy, get really confused by having to navigate between different Laserfiche modules. One minute they are filling out a form or approving a submission, and the next they have to log into the repository, find the document they want and then change a metadata field. This introduces a lot of friction to the user experience!

Here's the approach:

First, create a new business process in forms with a simple approval step.

Then, modify the starting form to look like this:

There's a custom HTML field (not important) as well as two single line fields (GUID and Name) and a file upload field. We will create a workflow that, when run, will push two Word documents inside a repository folder (a resume and a cover letter) into this file upload field. The end result will look like this:

Pretty nice, right?

Let's take a look at the workflow now. It's not a simple workflow, so I'll try to explain each step.

First, we'll create a bunch of tokens using the Assign Token Values activity.

Then, we use the Find Entries activity to find the documents we want to attach to the file upload field. In my case, it simply points to a folder inside the repository, and the folder contains Resume.docx and Cover Letter.docx. Nothing special.

Next, we need to create a GUID, which stands for 'Globally Unique Identifier'. GUIDs are very useful when you need to keep track of items inside systems you develop. You'll see how this GUID comes in handy soon.

Now let's invoke the Forms Business Process we created. We will only assign two variables: the GUID we just created will be assigned to the GUID dataset, and we'll use a hardcoded value to the Name field.

My business process is called "Test Document Review - Copy". Yours can be called something else.

Okay, now things get exciting. Let's take a look at the Find Submission ID activity. The idea behind this activity is that, when we invoked the business process in the previous step, a new submission got created in the Forms database. However, because the Invoke Business Process activity does not return a token for the submission ID, we need to find it manually. The way we do this is by searching the cf_bp_data table for a specific value that belongs to that submission: the GUID.

As you can see, the value column gets the GUID. But what about the attribute_id column? Well, in this case I'm using it to make the query a bit faster. If you have a very large database, searching the value column in the cf_bp_data table for a GUID can take several seconds (since the column is not indexed). Using attribute_id essentially narrows the scope of the search (think Within Folder searches in LF - same concept).

Okay, before I talk about the next activity, I'd like to explain the attribute_id a bit further. Basically, we need to find the attribute_id of the file upload field. To do this, we need to do a bit of digging in the Forms database.

First, we'll need to find the bp_id (business process ID) of the business process we created in forms. This can be found by looking inside the cf_business_processes table. Find the name of your process and you'll find its bp_id.

Then, open the cf_processes table and find the bp_id, and grab the process_id.

Finally, run a query against the bp_datasets table using the process_id you found. In my case, it is 54.

You'll note that it returned three fields, since our business process has three fields (name, GUID and the file upload field). The one with attribute_type of doc is the one we need. Make a note of its attribute_id and member_path. In my case, they are:

attribute_id: 1518

member_path: 1398

Okay, let's move on to the next activity in the workflow. In this activity, we will enter another row into the cf_bp_data table for the submission that workflow made earlier in the instance.

The important stuff here is that you need to check the "create identity token" checkbox. The repeat_id also has to be 0 and the value has to get the blank value from the Blank token we generated at the very beginning. Otherwise, Forms totally freaks out, I have no idea why.

The reason we have gone through all this trouble so far is that, in order for Forms to realize that the submission has files uploaded to it, there needs to be a bp_data_id value for that attribute_id inside the cf_bp_data table. The bp_data_id automatically gets created by the database engine when workflow inserts the row in the activity we talked about above.

We have set the stage for inserting the documents into the file upload field now. Let's now loop through the documents we found using the Find Entries activity. For each document, we do three things.

First, we need to use an SDK activity to get the electronic document portion of the entry and convert it into a byte array.The reason we have to do this is because Forms actually keeps the documents as binary data inside the database:

We also find out the extension of the document because we will need it in the next activity.

Next, we need to insert that byte array into the cf_bp_attachment_data table using a Custom Activity:

I'm actually include this script below since it can be a bit hard to see in the screenshot:

DECLARE @temp varbinary(max)
SET @temp = CONVERT(VARBINARY(MAX), CAST(? AS NVARCHAR(MAX)), 1)
INSERT INTO dbo.cf_bp_attachment_data (attachment, [length], [file_name])
OUTPUT INSERTED.attachment_id AS inserted_identity
VALUES (@temp, ?, ?)

Essentially, we convert the byte array of the document (prepended with 0x) into VARBINARY(MAX) and then insert it into the cf_bp_attachment_data table, along with the length of the document (in bytes - we calculated this in the SDK script) and the document name.

And then for the last step, we map the cf_bp_data row we entered earlier with the binary of the document we inserted with the custom activity:

Once you run this workflow, there should be a new task created for the user inside the User Task you configured in the Forms process, and when they open the task, they should find the files that workflow found using the Find Entries activity. Currently this works only with electronic documents, but it should be easy enough to modify for scanned images too.

I'm also using a bit of custom HTML and JavaScript to change the look-and-feel of the document review form. My custom HTML field contains a table:

<h2>Documents to Review</h2>
<h4 id="name"></h4>
<table class="table"> <caption>The documents you need to review are listed below. You can click each document to download it to your computer and open it in its associated application. Once done, come back to this form, type your comments and select Approve or Reject.</caption>
<thead>
<tr>
<th>Document Name</th>
<th>Size</th></tr>
</thead>
<tbody></tbody> </table>

My JavaScript:

$(document).on("ready", function() {
  $("#name").text("Applicant: " + $("#Field5").val());
  $(".delCell").remove();
  $(".files tr").each(function() {
    $(this).appendTo('#q4 table > tbody');
  });
  $("#q3").remove();
});

And voila!

Couple of things to note...

First, this works very well in our development environment, but we probably would not use it in production. Not only is it brittle (if the Forms database structure changes between versions, it can break), but it also manually inserts data into the forms table, which is not a good idea. If your Forms database breaks because of this workflow, I seriously doubt LF Support would help you. 

Second, it is totally possible to use this and accidentally violate the LF licensing agreement. The approach allows pushing repository documents to Forms Authenticated Participants, which means they no longer need a Named License to view documents in the repository. Unless Laserfiche says otherwise, or comes out with an out-of-the-box method, make sure the forms users these documents are sent to are named users.

Anyway, that's it. I really hope Laserfiche implements this functionality soon. The possibilities it opens up are crazy!