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

Question

Question

Field value updates

asked on November 20, 2014

I'm trying to figure out the best way to update field values from an external db?  Here's what we're trying to do:

Customer has a Case Mgmt System that they use, and in that system, there is a unique ID set for a person.  However, there may be times that the person doesn't yet have their SSN in the system or their name is spelled wrong, etc.  They'd like to be able to have Laserfiche field values updated as this information is changed in their Case Mgmt System.  I know I can create a WF, but it seems like it would be resource intensive to do a query daily and update ALL docs and Folders.  I know I can schedule it to run on a Saturday so it doesn't impact workers...but my question is there a better way to do this or a better way to write my WF that will Query ONLY and UPDATE ONLY those records that have changed in the Case Mgmt System...so we're not updating field values for documents in Laserfiche that have not changed??

 

0 0

Replies

replied on November 20, 2014

Daryl,

A couple of quick thoughts; if you have access to the Case Management System database tables check to see if there is something like a 'last updated' date/time field in the 'People' table.  If there is then perhaps you can query that table for records with a 'last updated' value within the past N hours/days to get a recordset to work with.

The other thought is that if the Case Management System was developed in-house then you could add an INSERT or UPDATE trigger to the 'People' table.  That trigger can write an intermediate record to a table for you to query to get your recordset to work with.  Then perhaps deleting the intermediate record once LF has been updated.  If the Case Management System is not an in-house app then I would not add the triggers.  Software vendors tend to get irritated when you muck around in their database!  ;-)

 

 

 

1 0
replied on November 20, 2014

Hi Daryl, 

I agree with Cliff in that the most efficient way to do this is to get a list from the database of what was changed. Another idea is to add a "Recently Updated" field to the table you are working with. Mark this field as 1 upon update and then use that value as part of your query in Workflow. Then, after the Workflow has run, set the value of that field back to 0. Essentially, use is as a true/false to narrow down what rows and documents you need to target. 

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

Sign in to reply to this post.