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

Question

Question

Display Values Between 2 dates from a SQL Lookup to a Table in Forms

Forms
Updated October 25, 2018
asked on October 25, 2018

Good Morning All,

 

I may be exceeding the limitations of forms here, but I wanted to bounce this off the community for validation.  I have a form in which I need to have the user specify a starting and ending date.  Those date are then used to perform a SQL lookup.  All rows that are between those 2 dates should be returned and displayed in a table (or collection) in Forms.

1. I can't use lookup rules because I have no option to pass the values of the "search between" dates, only if something matches.

2. I can't use Javascript or HTML because as stated by Brett Hickinbotham in this post (https://answers.laserfiche.com/questions/57966/Use-Custom-HTML-or-JavaScript-to-query-SQL-and-display-results-on-a-form#82540), the JavaScript is executed clientside, so it won't have access to the database, and HTML just doesn't have query capabilities.

3. I can't use a stored procedure because I have no way to pass it the "search between" dates from Forms.

4. I can't use workflow because the form is not submitted, it is used as a lookup tool.

Does anyone have a way to achieve this?

0 0

Answer

SELECTED ANSWER
replied on October 25, 2018

You would be utilizing a lookup rule with stored procedures.  Assuming they are date fields, you would pass your two parameters and utilize the SQL BETWEEN command in the SQL stored procedure.

 

Basically, I would look into the SQL stored procedure portion, get it working where it returns your rows when executing a stored procedure with your start and end parameter, then move on to Forms. This also assumes it is a more recent version of forms, I do not think Stored Procedures were added until a version of 10.

3 0

Replies

replied on October 25, 2018

Why is Stored Procedures out of the question? You say in your post, you have the user pick 2 dates. Both these dates can be passed as variables into a stored procedure.

1 0
replied on October 25, 2018

John,

How would those values be passed from forms without utilizing the lookup rule?  If this is possible, it may be a work-around for me.  

0 0
SELECTED ANSWER
replied on October 25, 2018

You would be utilizing a lookup rule with stored procedures.  Assuming they are date fields, you would pass your two parameters and utilize the SQL BETWEEN command in the SQL stored procedure.

 

Basically, I would look into the SQL stored procedure portion, get it working where it returns your rows when executing a stored procedure with your start and end parameter, then move on to Forms. This also assumes it is a more recent version of forms, I do not think Stored Procedures were added until a version of 10.

3 0
replied on October 25, 2018 • Show version history

Many thanks John.  I had not used stored procedures in forms before.  The part that was confusing was that "stored procedures" doesn't show up as an option in the dropdown until you have already created the stored procedure, unlike the table/view option.

 

As a recommendation for first timers using stored procedures for custom SQL queries in forms, it would have been more intuitive if the option was there and the list of stored procedures was just blank.

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

Sign in to reply to this post.

Details
Related Posts
Loading ...