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

Question

Question

Using workflow to listen to sql table for new entry

asked on March 3, 2023

Is it possible to use workflow to listen to an external sql table for a new entry, then when a new entry is entered into that table pass those values as variables to a form?

We have the SQL connection right now working with a rule, but I can't figure out a way to "listen" for a new entry, or how we could run this on a time-frame but then determine if an entry is new or was previously there.

We are using Cloud, and the SQL is here onsite.

Thanks,

Brian

0 0

Answer

SELECTED ANSWER
replied on March 3, 2023 Show version history

You can set up a workflow to repeat on an interval.  I have one that runs every 15 minutes Monday through Friday.  I did have to set up a separate rule for each day, but that was the only problem.

As far as determining a new entry, that same process looks for the unique identifier for the "master" DB entry.  The "master" table triggers a write of the unique identifier to a "transaction" table when the "master" is written or updated.

I run a custom query from workflow that retrieves the unique ids using a "select distinct id" query.  Once I retrieve the id I delete the entry from the "transaction" table (before I execute the update in case the entry is updated while the workflow is processing the Laserfiche update.  This ensures I pick up the second update.).  I then retrieve the "master" entry and do my Laserfiche update.

After the workflow has processed all of the "transaction" entries, it exits and then is kicked off again 15 minutes later.

0 0
replied on March 3, 2023

James - thanks for the info. We will start digging into this more next week.

Out of curiosity, is the transaction table created in SQL or a Laserfiche table? We were thinking it is probably a SQL table.

thanks

Brian

0 0
replied on March 3, 2023

SQL table and here is the script for creating the trigger

1 0

Replies

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

Sign in to reply to this post.