Question

Assign metadata from column A to an uploaded file in column B in a Forms Table

Forms Workflow

Updated July 10, 2021

asked on September 23, 2016

I am looking for a way to enter metadata on an uploaded file in Forms, using a 2 column table, where column A is entered as a drop down for "status" and column B is a file upload. When I configure the field from within the Forms service task, it enters all status on every uploaded file... i.e. "draft; draft; publish" I am assuming that this can be accomplished through Workflow, but I have not been able to figure out how to associate column A with the corresponding upload.

1 0

Answer

SELECTED ANSWER

replied on September 23, 2016 • Show version history

We had heard of a similar request before and this was a proposed solution.

Get the attribute_id value of the "status" column in your table. You can find the value by querying the cf_fields table in the Forms database.
In your Forms business process, just configure it normally where the file uploads are saved to the repository.
Create a workflow that has a starting rule where it watches for newly created entries. Set the conditions of the rule where it only gets triggered by the file uploads from the form submission getting created in the repository. This workflow will assign the corresponding "status" value to a metadata field associated with the file.

The workflow definition is pretty simple. The first activity is a delay. This is to give the Forms database the time it needs to get the entry id of the attachment in the repository. Essentially to avoid any possible race conditions. Next is a custom query activity that will basically find the "status" associated with that particular form file upload. Then the last activity just assigns that value to a field associated with that document.

As for the custom query, you'll need to know that attribute_id value mentioned above and the query parameters will just be the %(Entry ID) token.

select value from cf_bp_data where submission_id=(
select submission_id from cf_bp_data where bp_data_id=(
select bp_data_id from cf_bp_data_attachment_mapping where attachment_id=(
select attachment_id from cf_bp_attachment_data where lfentry_id=?)))
and substring(member_path, 0, charindex('.',member_path,0)) = (
select substring(member_path, 0, charindex('.',member_path,0)) from cf_bp_data where bp_data_id=(
select bp_data_id from cf_bp_data_attachment_mapping where attachment_id=(
select attachment_id from cf_bp_attachment_data where lfentry_id=?)))
and attribute_id=[your attribute_id value]
and value<>''
and value is not null

Here's a link to a video showing the POC - http://www.screencast.com/t/8cy2ma9mR

Note that the custom query has been confirmed to work in Forms 9 and 10, but keep in mind that the schema can always change in future versions so that's something to watch out for.

1 0

Replies

replied on July 10, 2021 • Show version history

Starting with version Forms v10.2.1, you now have the ability to apply an index directly on the variable in the table and it will automatically associate metadata from the corresponding row for each attachment.

The syntax is {/dataset/Table/Column_1[Row()]}. In this example, the Table is the variable for my table and Column_1 is the variable for my field that I want to assign as metadata to my attachment. You can do this mapping entirely within forms (save to repository task) and won't need to use workflow.

As of Forms 10.2.1 you can reference specific rows of the table while saving to the repository.

The Save to Repository Service Task now supports process variables that can retrieve values from a specific row in a table or a set in a collection for use in document name or template fields. For example, design a table with a "single line" field and a "file upload" field. If the table then has 2 rows of values, setting the file upload document name in the Save to Repository Service Task to {/dataset/Table_variable_name/Single_Line_variable_name[Row()]} will set the first file with with the first row's value and the second file with the second row's value.

Just add [Row()] after the variable name but before the }

Hope this is helpful to others!

2 0

replied on September 23, 2016

One way i could think of distinguishing, is adding status to the attachment name during save to repository.

I am trying to thing if we could get current value of index or Row {n} to be saved in filename or field.

0 0

replied on September 24, 2016

Thank you for your reply Alexander. I am running into a slight problem and I am sure it is something simple I am missing. When i test the custom query activity, I am receiving an error "Incorrect Syntax Near '?'. When I run the query in Management Studio, using the value of the instance ID rather than the variable, it returns the value from column A. Here is a copy of the altered query a screen shot of the task. What am i missing?

select value from cf_bp_data where submission_id=(
select submission_id from cf_bp_data where bp_data_id=(
select bp_data_id from cf_bp_data_attachment_mapping where attachment_id=(
select attachment_id from cf_bp_attachment_data where lfentry_id=?)))
and substring(member_path, 0, charindex('.',member_path,0)) = (
select substring(member_path, 0, charindex('.',member_path,0)) from cf_bp_data where bp_data_id=(
select bp_data_id from cf_bp_data_attachment_mapping where attachment_id=(
select attachment_id from cf_bp_attachment_data where lfentry_id=?)))
and attribute_id='1354'
and value<>''
and value is not null

0 0

View 2 previous replies

replied on September 25, 2016

If you're not using an ODBC connection, can you try using named parameters instead of the "?" query parameter? See this page for more information.

1 0

replied on September 26, 2016

Thank you very much Sir, works beautifully!

0 0

replied on October 18, 2016

Hello Alexander, Have you seen any issues with this solution when multiple forms are being used in the business process? In my process I have 3 forms, a starting form, a working form, and a publish form where the table containing the upload is being passed to the two subsequent forms and the publish is the only one being saved to the repository. It seems that the status from the first uploaded file in the table is being applied to all uploads. I have determined that the attribute_id_value is the same across all three forms.... Any suggestions other than when using this process, only one form can be used? Thank you for your help.

0 0

replied on February 15, 2017

Pleasure meeting you at conference and wanted to drop a note that we are looking into making this a lot easier to configure. One idea is adding formula support for all locations tokens are accepted including Row(). (But no promises yet)

0 0

replied on February 16, 2017

Robert, thank you very much for your time at the conference and addressing the issues I have found and/or experiencing. I would also like to thank you for the information in the "Scripting and Customization in Laserfiche Forms" on Tuesday afternoon. I have already taken parts of your example to expand the functionality of our Accident / Incident report I created for our Safety Coordinator early last year. I realize the logistics and that this is not probably the correct place to inquire, but I would like to ask if you would possibly have an eta on the patch to address the issue I am experiencing with the Topaz signature pads? If you like, feel free to reach out to me at gwilliams@mcrecc.com.

0 0

You are not allowed to follow up in this post.

Question

Question

Assign metadata from column A to an uploaded file in column B in a Forms Table

Answer

Replies

Sign in to reply to this post.