Question

How to use stored procedure in the lookup rules?

Forms

Updated July 7, 2021

asked on July 6, 2021

Hello,

I'm have problems understand how the Stored procedure in the Lookup Rules work. I have my all data from SQL, and connect to Laserfiche and I see all data from the Stored procedure all list in the dropdown and when I choose my select I pick and I'm not seeing anything so what are the steps for doing stored procedure in the lookup rules?

0 0

Replies

replied on July 6, 2021

When using table or view queries, you get a drop-down list for columns to match to your fields. With stored procedures it's a little different.

For the Use criteria, you'll see your input parameters "hard coded" and then you select the proper field to fill it.

For the Fill options you'll have to type or copy the column names into Forms. I've also had bad luck with explicit output criteria though it's been a few versions since I last tried it. Basically, it works for me if the stored procedure ends up presenting columns from a select statement. (I am by no means a SP expert.)

Here are a few screenshots to show what I mean

Top: simple SP definition
Middle: SP results in SQL SMS
Bottom: using the SP in Forms

3 0

replied on July 7, 2021

Hey Pieter, I got all of that so my next question is do you to use a dropdown field or any other field???

0 0

replied on July 7, 2021

You can use text or dropdown or a couple other field types, depending on the type of data being sent/received.

I recommend creating a test form so that you can experiment, then set it up on your real form.

0 0

replied on July 6, 2021

If you do not see any inputs or outputs after selecting the procedure, is it possible it has no input or output variables in the definition within the database?

1 0

replied on July 6, 2021

I just remembered that SPs are called on form load and/if fields involved with input variables are changed. If you are trying to control when it is called, than add an optional input with an already set value, so you can create a trigger field to re-set the value.

However you can't stop the initial form load call, so check to see if the value of your optional variable is default and escape to avoid invalid calls.

1 0

You are not allowed to follow up in this post.

Question

Question

How to use stored procedure in the lookup rules?

Replies

Sign in to reply to this post.