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

Discussion

Discussion

How to insert repository documents into Form upload fields (solution inside!)

posted on June 23, 2016 Show version history

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.wink

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. wink

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!

15 0
replied on August 6 Show version history

Great job! I too would love to see this implemented in Forms out-of-box

0 0
replied on June 20 Show version history

I'm trying to use this workflow and I keep getting this error.

Any help would be really appreciated.

0 0
replied on June 20

Writing directly to the Forms database is not supported.

0 0
replied on June 20

Is this not supported now? It seem to work on some versions of forms.

0 0
replied on June 20

Modifying the database directly for any of the Laserfiche products was never supported.

Your error specifically is an ODBC error indicating the data you're trying to insert is in some way not matching the table specs (too long, in violation of the integrity constraints, etc).

0 0
replied on June 20

Gotcha. Thanks for the quick responses!

0 0
replied on May 5

Hi,

I have tried this and the process runs fine. But when i download the file it has an error. I tried with different types of files. For example if the file is a word document, when we open it we get a blank document. If the file is of PDF format it shows an error saying "failed to load PDF document". Why does this happen.

0 0
replied on March 1

This is great! We're trying this out. We were working on building out the workflow piece and noticed that during the step for:

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:

that inserted_identity is not a token that's available from the insert the document byte array into the Forms database activity. Did you just enter the language for that token manually? It's only suggestions are Results Found and Result Count--- ideas?

0 0
replied on February 9

Has there been update to native functionality for this? Tried it on the latest Workflow version and it doesn't look the solutions viable anymore. 

0 0
replied on February 9

Nothing changed in Workflow. Are you getting an error?

0 0
replied on February 9 Show version history

Nope.

Upon searching it looks like "Extension" isn't enabled by default, you need to go into additional properties of the find entry and click Extension there to enable this in the Custom Query section. Might be obvious to more experienced VARs but I presumed extension would be a default property!

Credit to the answer on this post : https://answers.laserfiche.com/questions/130804/Question-about-Workflow-and-using-file-extensions

1 0
replied on July 13, 2017

We are interested in this and if this feature is in 10.2 Forms, would be great. Thanks!

 

Priya

2 0
replied on March 2, 2017

This looks amazing! I'm currently working on getting this to work but have hit a snag.... I'm assuming I need an SDK licence to be able to use the following:

    using Laserfiche.RepositoryAccess;

Is this correct?

Is there maybe another way to do this (maybe with LF 10.2 hopefully?) without usage of the SDK?

Thanks heaps in advance.

0 0
replied on February 9

You don't need an SDK license to use SDK scripts in Workflow.

0 0
replied on December 19, 2016 Show version history

I just came across this and wish I had a month or so ago.  I'm doing something similar and got stuck on the INSERT part for Binary Attachments (error below).  I noticed that there seemed to be no way to CAST a string to a binary string ("0x00" as a test).  Your workflow is helping me in that I know that I need to use a Custom Query.  I suppose I could do all the queries in SDK code, but I want to stay away from that if I can.

It still seems ridiculous to have to use such a workaround -- manipulating the Forms DB.  
We're still on version 9.2.x here, so it's also helpful to know that upgrading to v10 won't help in this regard.

_______________________________________

A user named 'FormsRouting' initiated an instance of '*************' which terminated.

Reason: Implicit conversion from data type nvarchar to varbinary(max) is not allowed. Use the CONVERT function to run this query. Implicit conversion from data type nvarchar to varbinary(max) is not allowed. Use the CONVERT function to run this query.

0 0

This is just amazing

Sign in to reply to this post.