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

Question

Question

Forms Database cf_bp_attachment_data lfentry_id field - Could this determine if a process is complete?

Forms
Updated November 5, 2018
asked on November 2, 2018 • Show version history

There is a field in this table called lfentry_id. If this field is populated, does that mean the attachment referenced has been saved to the repository in the process?

I am wondering if this field could determine when a process no longer needs that attachment. Assuming we know our process ends with an archive of the attachment.

I normally don't like to work directly in someone elses database, but I can't find a feature to manage our attachments anywhere in the product. You have to delete the entire instance which drops all the metadata.

0 0

Replies

replied on November 2, 2018

I tested an attachment with lfentry_id = NULL until the entry was submitted to the repository. 

Checking again the lfentry_id = Value and searching that same ID in the repo returned the document with the same image attached within.

I believe from what I just tested this is true.  I'm sure LF will chime in though.

0 0
View 2 previous replies
replied on November 2, 2018

Thanks Chase! I think this could be the key, but need to be super careful as I wouldn't want to delete documents waiting for review.

0 0
replied on November 2, 2018

Is this an effort to clean up the Forms DB size by clearing any attachments from this table after verifying it is archived in the Repo?  Even if the Approval process is continuing?

I didn't test how the form behaves after removing that specific row from the table after archived and making sure the form and any other attachments are retained.  

Like you said, working in another DB can be rough, if that attachment ID links to an indexed table referencing the attachment position in an upload field, it may break it.

I would setup a separate dev Forms DB to verify that one.

0 0
replied on November 2, 2018 • Show version history

Also note, in Forms 10.4 we are releasing a feature to automate this cleanup for you. 

Automatic removal of file attachments after a set time
This feature allows admins or process designers to remove files uploaded to the Forms database after a set period of time. For most organizations, if a file is collected via a form submission, it is saved into the repository for further use. After saving the file to the repository, there is very little need to keep it stored in the Forms database also. In an effort to reduce the overall size of the Forms database, this feature will allow admin to delete the file from Forms once the process has completed and the time period has passed.

This won't delete file uploads from active processes, but will go through daily and remove any uploaded files from completed processes that have lasted past the set retention time. Is deleting files IMMEDIATELY after they are saved critical to your use case? Or does deleting them after the process has completed suffice? 

2 0
replied on November 3, 2018

Hi Jared, this is great and much needed. On a bigger picture though, it would be nice if the uploaded button was converted to a link(s) once the file was saved to the repository (as you store the entry ID in your Forms DB). This would then remove the BP user(s) ability to download the file locally as it is secured by user rights. It would also be helpful if Forms Authenticated users has read only rights to the repository like a subscription user so they could view that file from the link. A lot of projects we are working on display documents in an iframe in forms, and this could provide other options.

1 0
replied on November 4, 2018

Hi Jared

I am trying to improve the performance of downloading files from forms for this specific project.

However in general I think files should be archived in the repository, not the database. To answer your question, I think it is very important that the file should be removed from the database when the process is Complete. NOT immediately after saving to the repository. This is because there may be multiple saves to the repository throughout the process.

It is worth noting the feedback we get from customers on this though. They do LIKE the ability to archive documents in forms rather than the repository. This is because they can find and view them as referenced through completed tasks and participants can always access what they have submitted. They just DO NOT LIKE waiting up to 30 seconds before the download starts, after clicking the file. That is what I am trying to solve.

Right now I am just trying to reduce the total BLOB data to a size small enough to fit all of it in RAM. It is more of an experiment to see if it makes any difference in file prep time, based on some articles I read about problems reading BLOB data from the disk when it is stored through a SQL table instead of referenced from a text file.

The recommended way to store BLOB data on disk is to write directly to disk and only reference the disk location in the DB. Then have the web service reference the disk directly when retrieving BLOB data. It has something to do with SQL's lack of a shared-disk model, only leaving one thread for all disk transactions. I don't know how to even begin doing this in Laserfiche, but I am trying to move everything into RAM to see if I can prove the problem is a bottleneck with SQL disk access.

I can pull hundreds of documents in 1 second through a query, but it is nearly impossible to tell which records are in RAM and which are on disk. I do know that when you query a record, it moves into RAM and replaces something else, also if you wait 30 seconds to download a document, you can then download it for hours instantaneously after that. So my assumption is that disk is the problem.

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

Sign in to reply to this post.

Details
Related Posts
Loading ...