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

Question

Question

Cloud lookup

asked on May 22

Hi,

Does Forms, in Cloud, allow for lookup rules to come from Data Sources or Queries which are based on On Prem SQL server data using LF Agents?  I have setup my Data Sources and Queries but they do not show up in Lookup Rules in Forms Designer.

Thanks,

Anthony

0 0

Replies

replied on May 22

Not yet. Forms in cloud can run direct lookups on cloud-based business rules including lookup tables, but cannot hook up to an external data query.  One current workaround is to use a workflow after the message start event to run the external data query, then send the result back to the business process. You can also automatically load a user task directly after that assigned back to the initiator if the user needs to continue filling out a form after that result is retrieved. 

We are currently enhancing agents with a better queuing/priority system so we can better implement external data lookups directly from forms. 

1 0
replied on May 22

Your two options would be

1. Have the message start event form ask whether they are a new employee or existing employee first. If they are a new employee, show all the form fields and have the new employee fill out that form. If they are an existing employee, have them enter their employee ID or name (whatever you use to look them up) and submit the form.

Then in the process: On the message start event, select the option to "load next user task if it is assigned to the same user". After the message start event, run the WF to use the information gathered on the form to run an external data query and pass back all the employee information. After the WF service task, have a user task assigned back to the /_initiator with all the variables now populated. Because the task is assigned back to the initiator, it should load up automatically for that user. 

The problem with this solution might be the wait time for the user. After filling out their employee ID and submitting the initial form, it may take 10-20 seconds for the next form to load up with the data. Depending on how busy the remote agents are, it may sometimes take longer. This is the reason we need to enhance queuing/prioritization for remote agent requests before implementing this feature directly in forms. Give this a try and see if the performance is acceptable. 

2. Store the employee table as a lookup table in the cloud. Since employee's information doesn't change that often, you could do a nightly sync of your on-prem SQL server to a lookup table. Then you can look up data from that table using a lookup table query and those will be fast since the data is all in the cloud. I recommend this option if possible. Even after implementing on-prem SQL lookups in forms, pushing the data to a cloud table and looking it up from there will be much better performance. 

1 0
replied on May 27

Hi Jared,

Is there any chance you could share any links or guides I can look over with regard to syncing on-prem data to a cloud lookup table to accomplish the second scenario you suggested?

Many thanks,

Anthony 

0 0
replied on May 22

Thank you for your reply.

Thinking out loud.  My form is a replication of a current on-prem form (Employee Contract Approval).  The first part of the form is selecting either A)New Employee or B)Existing Employee.  The existing employees are pulled from a SQL database and populate a drop-down.  Would the workaround you mention work while the user is still filling in the form or is is kicked off on submit?

Anthony  

0 0
replied on May 22

Thank you for the detailed response.  I agree, I think the second option makes sense.  Do you have any links or guides I can look over with regard to syncing on-prem data to a cloud lookup table?

Anthony

0 0
replied on May 27

Hi Jared,

Is there any chance you could share any links or guides I can look over with regard to syncing on-prem data to a cloud lookup table to accomplish the second scenario you suggested?

Many thanks,

Anthony 

replied on May 27

Hi Anthony,

 

We don't have native support for UPSERT or generic sync between on-prem and cloud data at this time.

The easiest way to do this is to mark dirty rows in your on-prem database. If you can do this then you can have a workflow that;

  1. SELECT all dirty rows from on-prem database
  2. Checks by some key if the row exists in your cloud lookup table
  3. If the key exists, UPDATE, if it doesn't INSERT, your data to the cloud lookup table
  4. UPDATE the rows in on-prem table to no longer be marked dirty
0 0
You are not allowed to follow up in this post.

Sign in to reply to this post.