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

Question

Question

Cloud Forms to Microsoft SQL Lookup

Forms Laserfiche Cloud
Updated July 1, 2022
asked on June 30, 2022

In cloud you can lookup data from SQL but only from workflow or after a forms submission has already been made.

If you want to do a real time lookup on a form, you would need a copy of the data in a simple table right?

However if you wanted up-to-date data, then you would need to get a copy of the current data overnight.

But with workflow limiting activities to 10k per day, how would you do this?

Just updating the data for about 4k rows exceeds the 10k limit because the For Each Activity counts as one of the activities as well as the INSERT rule.

0 0

Replies

replied on June 30, 2022

There's no 10,000 activities per day limit. You may be referring to the 10,000 activities per workflow instance limit.

Running For Each Row and Insert on large tables is inefficient. The better way to do this is to replace the table contents as a whole using Replace Lookup Table Data. Upload the new table contents to the repository as a CSV or Excel spreadsheet, download the CSV in Workflow and replace the table contents.

1 0
replied on June 30, 2022

Do this everyday manually? What is the point of the Agent then?

0 0
replied on June 30, 2022

You have a nightly workflow that has your Remote Agent query the SQL Server instance for the full table as a CSV, upload that CSV to a temp working folder in your repository, and then use it with the Replace Lookup Table Data activity to refresh your lookup table. Fully automated. 

There are other automated methods for the initial steps of getting the CSV of the table data and then getting the CSV into your repository. For example, using an SSIS Package to export the daily CSV file and dropping it off somewhere Import Agent or a Remote Agent script can pick it up to send to the repo to use with Replace Lookup Table Data.

0 0
replied on July 1, 2022

Sam,

We will have to agree to disagree on the definition of 'fully automated'...  ;-)

replied on July 1, 2022

Hi Miruna,

Unrelated to Chad's question, but can the Replace Table Lookup Data activity use the data that it gets from a lookup rule on a SQL data source?

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

Sign in to reply to this post.

Details
Related Posts
Loading ...