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

Discussion

Discussion

Feature Request: Ability to use Lookup Rules in Forms to query metadata in repository

posted on April 11, 2023 Show version history

Use Case:  I have a form for Invoices.  The user can fill out the PO number, Invoice number, etc.  I would like to query the repository by PO number, vendor number, invoice number, etc., to ensure that the submission will not be duplicated. 

I know there is a way to add a Workflow to my Forms process that can set business process variables from metadata;  however, this is cumbersome and would be much easier to implement if LF Forms could simply do a Lookup Rule on the metadata instead. 

0 0
replied on April 11, 2023

To go along with this, discussion have been had in the past about wanting to have the different Laserfiche databases setup as data sources that could be connected between the different products. This would make it easy to do what you are asking by having Forms be able to query a repository, Workflow, LFDS, etc.

1 0
replied on April 11, 2023

I agree.  The example I provided is very specific and niche, and a little complicated to set-up.  Plus it would require preparing it for exactly the field(s) that you wanted to search, so it's not flexible.  But if the developers were to build in a dedicated interface, they could make it much more flexible and customizable for the various use cases that someone might encounter.

As an alternative...  I've always thought it would be so helpful if they had a way for us to call a workflow from within an active form (as opposed to in-between tasks), and handle sending values from the form to the workflow and populating values back into the form that are returned from the workflow.  Because Workflow can already search the repository, complete a variety of database tasks, run scripts, and interface with APIs - it would be a huge boon for increased low-code and no-code functionality within an active form.  I keep telling anyone at Laserfiche that will listen to me about this idea.

0 0
replied on April 11, 2023

This is a great idea, and I would love to see it added to functionality.

But in the meantime, here's a workaround...

If you have your own SQL database set-up in the same environment that your Laserfiche Repository database is in (on the same server for example) and you are already set-up to do lookups from your database, then you can add a view to your database that can look up this information from the repository's database (there are some assumptions here - such as the access permissions to the repository database for the user that Forms is using for lookups).

Here's an example: 

SELECT
  toc.[tocid] AS entry_id,
  toc.[name] AS entry_name,
  toc.created,
  toc.modified,
  template.[pset_name] AS template,

  --Dispay the value of the String field:
  string_field.[str_val] AS string_field,

  --Display the value of the Number field that is formatted as an Integer:
  CAST(ROUND(number_field1.[num_val], 0) AS INT) AS number_field1,

  --Display the value of the Number field that displays 3 decimal places:
  ROUND(number_field2.[num_val], 3) AS number_field2,

  --Display the value of the Date field - CAST AS DATE removes the time portion:
  CAST(date_field.[date_val] AS DATE) AS date_field

FROM [LaserficheRepositoryDatabaseName].[dbo].[toc] AS toc
LEFT JOIN [LaserficheRepositoryDatabaseName].[dbo].[propset] AS template ON template.[pset_id] = toc.[pset_id]

--Example of field that displays as a string (str_val)
LEFT JOIN (
   SELECT field_val.[tocid], field_val.[str_val] 
   FROM [LaserficheRepositoryDatabaseName].[dbo].[propval] AS field_val 
   LEFT JOIN [LaserficheRepositoryDatabaseName].[dbo].[propdef] AS field_def ON field_def.[prop_id] = field_val.[prop_id]
   WHERE field_def.[prop_name] = 'NAME OF THE STRING FIELD GOES HERE'
) AS string_field ON string_field.[tocid] = toc.[tocid]

--Example of field that displays as a number (num_val)
LEFT JOIN (
   SELECT field_val.[tocid], field_val.[num_val] 
   FROM [LaserficheRepositoryDatabaseName].[dbo].[propval] AS field_val 
   LEFT JOIN [LaserficheRepositoryDatabaseName].[dbo].[propdef] AS field_def ON field_def.[prop_id] = field_val.[prop_id]
   WHERE field_def.[prop_name] = 'NAME OF THE FIRST NUMBER FIELD FIELD GOES HERE'
) AS number_field1 ON number_field1.[tocid] = toc.[tocid]

--Example of field that displays as a number (num_val)
LEFT JOIN (
   SELECT field_val.[tocid], field_val.[num_val] 
   FROM [LaserficheRepositoryDatabaseName].[dbo].[propval] AS field_val 
   LEFT JOIN [LaserficheRepositoryDatabaseName].[dbo].[propdef] AS field_def ON field_def.[prop_id] = field_val.[prop_id]
   WHERE field_def.[prop_name] = 'NAME OF THE SECOND NUMBER FIELD FIELD GOES HERE'
) AS number_field2 ON number_field2.[tocid] = toc.[tocid]

--Example of field that displays as a date (date_val)
LEFT JOIN (
   SELECT field_val.[tocid], field_val.[date_val] 
   FROM [LaserficheRepositoryDatabaseName].[dbo].[propval] AS field_val 
   LEFT JOIN [LaserficheRepositoryDatabaseName].[dbo].[propdef] AS field_def ON field_def.[prop_id] = field_val.[prop_id]
   WHERE field_def.[prop_name] = 'NAME OF THE DATE FIELD FIELD GOES HERE'
) AS date_field ON date_field.[tocid] = toc.[tocid]

--Only show situations where there is a value in the field.
--Strings can be blank or NULL, we only want values that are neither:
WHERE (string_field.[str_val] IS NOT NULL AND string_field.[str_val] <> '')
--Exclude the numbers that are NULL:
  OR number_field1.[num_val] IS NOT NULL
  OR number_field2.[num_val] IS NOT NULL
--Exclude the dates that are NULL:
  OR date_field.[date_val] IS NOT NULL

 

2 0
replied on April 11, 2023

Matthew,

Thanks for the reply.  I've done something similar before, using information you provided in another forum post.  I'll take a look at this and see how it works.

Thanks,

Mike

 

1 0
replied on April 11, 2023

This is awesome! Something that I didn't realise that I needed :)

Thanks

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

Sign in to reply to this post.