Question

Stored Procedure doesn't run when I want it to

Forms Version 9

Updated October 25, 2018

asked on October 19, 2018

Hi,

I am trying to execute a stored procedure that uses the data the user enters into 4 fields. Although, when I set up the look up rule using the stored procedure option, it only runs on load and not after all the fields are filled. I have also search all on here for answers and most people say to use the Lookup rule using table/view. Although when I do this, I still do not receive any rows.

When I pass through the test data into my stored procedure, I do receive a row (only supposed to pull 1). I am trying to prevent a table to be filled of rows because the Data Source table I am building will be increasing each day. Over time this will make my form take longer to load.

Is there any way I can run my stored procedure using Javascript or would I have to set my DB Table to all nvarchars for the lookup rule to properly pass though data (like a date) because I cannot convert the date like I would in my stored procedure?

Thanks!

0 0

Answer

SELECTED ANSWER

replied on October 19, 2018

Based on what you're describing, it doesn't sound like the problem is execution of the lookup, it sounds like it isn't getting/receiving the expected data.

You can easily test this by checking the "Show Auto Fill Button" option on the lookup rule; this will give you the ability to manually run the lookup.

If you manually execute it and still don't get results, then there is something else wrong. Another thing to check is your source value names. With Stored Procedures you have to make sure the "column" names match exactly as they are case sensitive.

1 0

replied on October 19, 2018

That option does not exist in my version of Laserfiche Forms. Although the button does appear on my Form when I preview it. Is there a way I can change the location of this button? It seems to place it in a weird location.

0 0

replied on October 19, 2018

Which version are you running? I’m sure you could relocate it with CSS, but I don’t have any sample code for that since I’ve always had it run automatically.

Although I would recommend upgrading Forms, another option is to use JavaScript/JQuery to hide the button and click it automatically any time one of the lookup fields changes.

I used to do that in the older Forms versions, but that was awhile back so I don’t think I have the code anymore.

Does the lookup work when you click the button?

0 0

replied on October 19, 2018

That sounds like a very interesting option that I would actually use for this case. I am running 10.2, apologies I thought it was a version 9.x

0 0

Replies

replied on October 23, 2018

This doesn't directly address your question, but it kinda sounds like your proc really doesn't do much other than filter a table to a single row. If that's the case, it may be simpler to use a view:

CREATE VIEW LookupsShouldAllowSQL AS
SELECT TOP 1
	*
	FROM MyTable
	ORDER BY MyDate DESC

Nothing wrong with using a proc, but sometimes datatypes don't get passed the way you might expect.

0 0

replied on October 24, 2018

Hi, I came across this issue many a time and from my experience I found the only way to guarantee database side execution for multiple inputs was to create a separate field that is a "field calculation" that concatenates the multiple input fields you have and only send that as an input to the stored procedure. If the stored proc itself can be set up to strip out the long string into the components field parts. This way I found the auto-update need not be invoked as only one field is being sent. You can try it and see how it goes but that was my way out of this issue.

0 0

replied on October 25, 2018

I ended up having to use $('.autofill').trigger("click"); and place it in functions where ever I wanted the procedure to run. I found that if you have a textfield in a table, it can have a lot of issues with the lookup rules. I had to create separate text fields that were hidden (similar to Farid's solution) but instead of concatenating all the values, I still use multiple fields. I had 4 inputs to query the values I required from the Database Table. I went through each field separately until I found which field would not work and then create a separate hidden field. Kinda annoying since the Lookup Rule allows me to use the fields but yet won't actually pass them through to the database.

Another issue I ran into was when using Workflow, I would pass in a date field value tell workflow I have a date value, try to insert it into my database table as a date, datetime, or any other type of date variable but Workflow would error saying it can't convert a nvarchar to a date. This made no sense to me because at every point along the way from Forms to DB I was using a date type of value. I had to create a custom query and CONVERT(datetime, @date) and that worked.

0 0

replied on October 25, 2018

I’m sorry to hear it caused so much trouble; I was very happy they added the auto option in the later updates.

As for the data type issue, did you try using the token editor to trim and format the date? The issue sounds familiar and that might be enough to get it working.

0 0

You are not allowed to follow up in this post.

Question

Question

Stored Procedure doesn't run when I want it to

Answer

Replies

Sign in to reply to this post.