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

Question

Question

Searchinig for Distinct Field Values

Workflow Ideas
Updated October 16, 2018
asked on February 6, 2014

I am writing a 9.1 scheduled workflow to update all employee files if their information has changed in the HR database.  The HR database does not have any fields or dates that signal a change has been made to a record.  Instead of searching for all documents with that template and then checking each one individually against the database, I was hoping to query Laserfiche for distinct employee id's (so that I only get 1 row per distinct employee id) and then batch check the documents by employee (thus reducing my database look ups).  

I realize I can query the Laserfiche SQL tables directly to achieve this. I also realize I can assign the Employee ID at the parent folder level and then just search on folders. What I am hoping for something in Workflow that allows almost SQL like queries directly against the template fields.  Is this type of query possible (without looping through all documents that match the criteria)?  If not, can this kind of functionality be added to Workflow in the future and has anyone found a better way to regularly check/update 10000's of documents?

0 0

Replies

replied on February 6, 2014

What we've done for this sort of situation is create a data warehouse. There's a bulk process that runs every night that populates the warehouse with data from various systems. If it's something we need to update, we timestamp the updated rows. We still examine the data for changes, but it all happens in SSIS. Workflow can then just do a simple query and only get recent rows.

2 0
replied on February 6, 2014

Thanks for you response. I like that idea for large organizations but we have small-mid sized clients who don't want to maintain another solution to assist with these updates.  At this point adding the employee id field to the folder is the quickest/easiest method and works fairly efficiently.  I am hoping in the future LF will allow us to query the template data directly to create a kind of temp table of metadata. That way we don't have to loop through each document to get the data to then check/update.

0 0
replied on February 6, 2014 • Show version history

Beau,

 

Perhaps I am misunderstanding but you can directly query the template fields in Laserfiche with SQL (You just have to understand the table relationships).

 

I've done this by having a master HR personnel template that only gets assigned to one document per employee.  This document is usually stored in a 'master' HR folder and only gets modified when HR information changes.  All other HR documents have a different template and can share HR fields, but the master template is only assigned to the master record.  In this case you could do a SQL query for the specific template for the specific Employee ID and get a single record (or do a search in WF that would return the appropriate document).  You could then reconcile the two HR datasets at that point.


Again, not sure if that is what you were looking for.

0 0
replied on February 6, 2014

I appreciate your suggestions.  I understand how to query the tables directly with SQL but it seems a bit lacking that I have to run SQL queries from Laserfiche Workflow to get data that is stored in Laserfiche. Additionally, this can cause issues as upgrades/migrations/ect can break the query.  I've ended up setting the template at the employee folder level which gives me one place to search to get the employee id of all employees who have documents in Laserfiche.  

 

What I would like is a Workflow activity that allows search capabilities of template fields directly in a manner similar to a SQL query.  Laserfiche is a data goldmine but Workflow is designed to work with the documents associated with the data and not directly with the data itself.  I want to be able to query directly into the metadata and return values (distinct, max, min, top, ...) that can be used in the logic of Workflow. This can be handy to find:

  • a distinct list of employee id's
  • the max value in an ID field that is incremented automatically by Workflow
  • the top 5 case files according to hours billed (if hours billed is a template field)
  • the oldest and/or newest document for a specific project
  • and a ton of other scenarios

 

I understand there are workarounds.  I remember in Workflow 8.0 when there wasn't the Custom Query activity and I had to write my own scripts in the Script activity to query SQL databases.  But Laserfiche is consistently providing better activities to both speed development and minimize customizations.  I am hoping they provide an activity that allows SQL type queries of the template data directly, without the need to loop through their associated entries.

0 0
replied on February 6, 2014

Beau - Great feedback!  (Yes, I misunderstood the question wink) 

 

I am actually a Laserfiche PDP  (Professional Developer Partner) and I am always on the lookout for potentially new custom workflow activities and LF integrations.  Would you be interested in collaborating on a custom workflow activity to provide some of the features you are looking for?  If so, please email me at cprimmer@qfiche.com.

 

Also, (shameless plug) if you have a moment, I have some unique custom workflow activities available at http://www.qfiche.com/products.html.  All are available as free 30 day evals.

0 0
replied on February 13, 2014 • Show version history

I have another customer I am having to write a Workflow for that could use a built-in Workflow activity that treats the metadata like SQL tables/views.  

  • I am first having to find all documents
    • of a specific document type
    • that have a specific template field filled in
    • and have been created/modified in the past week
  • I am then having to find documents
    • of any doc type
    • that have the same value in the specific template field
    • but where other template fields that do not match

To accomplish this I am

  • running an LF search to find the docs that match the first criteria
  • then I am running a For Each Entry loop on the search results to get the template fields I need to compare
  • then I am running an LF search for related docs that one template field matches but the other template fields don't
  • Finally, I am running a For Each Entry loop on the second search to update the metadata that doesn't match

 

While this process works fine, it takes awhile to cycle through 1000's of documents during the For Each Entry loops.  It would be far easier if I could do essentially a JOIN on the two searches so that I only query  LF once for the actual documents I need to update.

 

While I know I can query this directly through SQL on the LF tables, we are trying to keep this using Out-Of-The-Box activities.  For Laserfiche, is there a activity coming that allows us to query the metadata directly (similar to SQL queries) or using LFQL, instead of having to cycle through documents individually to retrieve their metadata.

0 0
replied on June 25, 2014

Anyone at LF know if there is a chance of an out-of-the-box LFQL workflow activity on the road map?  It could piggy back of the custom query or have its own activity but I would like to know before I go through the task of writing my own script and/or custom activity.

0 0
replied on October 15, 2018

Dear Beau,

Grateful if you can share the sample SQL query you used to directly query the Laserfiche database.

0 0
replied on October 16, 2018

No problem.  Below is the code I use to pull field information from a document where the document is in records management.  Replace the "Text Field" with the actual field name, "Date Field" with the actual field name, and adjust as necessary depending on how many fields and what types of fields you wish to query.

SELECT DISTINCT EntryID, [Text Field], [Date Field]
FROM (
	SELECT DISTINCT tocid AS EntryID, rfid,
		(SELECT  a.str_val
		FROM dbo.propval AS a INNER JOIN  dbo.propdef AS b ON a.prop_id = b.prop_id
		WHERE (a.tocid = t.tocid) AND (b.prop_name = 'Text Field')) AS Text Field,
                (SELECT a.date_val
		FROM dbo.propval AS a INNER JOIN dbo.propdef AS b ON a.prop_id = b.prop_id
                WHERE (a.tocid = t.tocid) AND (b.prop_name = 'Date Field')) AS Date Field
	FROM dbo.toc AS t
	WHERE (etype = '-2') AND (toc_flags & 0x800 = 0)) AS z
WHERE (TextField IS NOT NULL) AND (rfid IS NOT NULL)

 

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

Sign in to reply to this post.

Details
Related Posts
Loading ...