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!

30 0
replied on August 23, 2022

Hi everyone,

Many years ago, we developed a custom activity that performs the upload to Forms field automatically. I came across this post recently and thought I'd share with everyone in the same position as us.

This activity will insert an entry into a forms upload field on an existing forms instance. This is an advanced activity and requires some SQL knowledge to get the SQL Attribute ID of the field in question. Take note that the compatibility of this activity on future versions of Forms (Works on 10.4+ and 11+) is not guaranteed and custom data insertion into Forms database is not supported by Laserfiche. You can download a trial version here and ask your reseller to assist you with a license if you like it. We've also developed 16 other custom activities in this bundle download that might also be of use to you.

 

3 0
replied on April 1, 2020

Does someone from Laserfiche know if this feature is on the list for future implementation?

2 0
replied on June 8, 2020

I'm also interested in this feature; I have a use-case it would be perfect for! Hoping for a response regarding if this is on the roadmap.

1 0
replied on June 12, 2020

Also agree that this feature is quite important. Of specific value is the Upload Field provides the ability to preview files. So, if invoices, purchase orders, contracts etc are being introduced into a forms-based business process via Workflow it could then use the Upload Field's "thumbnail" control to display the attachments within the form for review and approval.

+1 for getting this feature 

1 0
replied on June 12, 2020

We'd also like to use it to develop a Learning Management System to track training of procedures that are stored in LF. Thanks!

1 0
replied on September 24, 2020

We also have had a similar client request. It would be advantageous to upload a file to a forms process through Workflow.  Reason being is that some users only have access to Forms, not the Repository (otherwise we'd use Web Access iFrame displays)

1 0
replied on September 28, 2021

We had a client request this feature and ask if it has been implemented in a newer release.  I'm not aware that it has been added as a feature yet.

1 0
replied on February 17, 2022

Please add our name to this list too! Would be fantastic to be able to attach a repository document to a Form process - both in Self-Hosted and Cloud

1 0
replied on April 12

+1 for this feature! 11u5 and 8 years after the initial post. My current usage would be to combine the Form and uploaded PDF files in Workflow, then reattach the combined file into the Process so it can be emailed to a Team/Role as a task. Looks like I'll have to query the dB to get the Team/Role users and email them from Workflow.

I know I can email the Form and backup within a task, but I want the files to be combined.

0 0
replied on April 15

@████████, using the Workflow Activity bundle, you could achieve all of your requirements, including combining the PDFs. Go check it out and ask your reseller for a trial license.

https://marketplace.laserfiche.com/details/210806/Workflow-Activity-Bundle

0 0
replied on April 16

@████████, that's a great option, thank you!

0 0
replied on November 27, 2019 Show version history

I really appreciate this article and it seems like it is a viable solution for users of Laserfiche 10.3.x.

I've successfully implemented a version of this process in our environment after tinkering around with the Custom Query for a bit. It kept silently failing on me so I tested it externally using SQLPro Studio and was finally able to convert the byte array to upload the invoice!

There is one thing I would like to add that is helpful when you're trying to figure out attribute ids, member ids and member paths.

On the live form, you're able to see these attributes by inspecting the code and they show up as data attributes on the field's root list item.

For Example:

<li data-aid="3403" data-mid="3480" data-mpath="3480">
    ......
</li>

I removed the excess code to show the pertinent data but it's a lot easier to inspect the form to find out this information so you can move on to more important matters.

Our use case is not quite the same but this article has been very helpful and has allowed me to figure out 95% of the workflow so, again, thank you very much!

Screen Shot 2019-11-28 at 12.25.33 AM.png
1 0
replied on June 14, 2019

Hi Similar to Rosa Yeh I am not seeing the Inserted_identity token available from the "Insert the document byte array into the Forms db" activity. We only get results found and result count. I am afraid this workflow won't work without this. 

0 0
replied on November 27, 2019

I was having this exact issue and was able to get around the problem another way.

In the step to create the cf_bp_attachment_data (which I'm still working on), I set the "file_name" field with the GUID.extension. I stripped out "OUTPUT INSERTED.attachment_id AS inserted_identity" because it was only returning results found and result count (like you said).

Immediately following that step (within the loop), I query the same table for the GUID.extension from the file_name field and use that to get the attachment_id.

I'm confident I will be able to figure this out the rest of the way after extensive testing and fumbling around. Laserfiche's Workflow Designer is not as intuitive as I'd like it to be with many glaring omissions in functionality. Be that as it may, it is a very powerful tool and will likely get better with time.

1 0
replied on August 6, 2018 Show version history

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

4 0
replied on June 20, 2018 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, 2018

Writing directly to the Forms database is not supported.

0 0
replied on June 20, 2018

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

0 0
replied on June 20, 2018

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, 2018

Gotcha. Thanks for the quick responses!

0 0
replied on May 5, 2018

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, 2018

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?

1 0
replied on June 14, 2019

Rosa....Did you ever find out the deal with the inserted_identity token?

0 0
replied on February 9, 2018

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, 2018

Nothing changed in Workflow. Are you getting an error?

0 0
replied on February 9, 2018 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, 2018

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.