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

Question

Question

Limit Lookup from Lookup Rules

Forms
Updated February 14, 2022
asked on February 14, 2022

I am looking for a way to limit the number of results from a Lookup Rule. Currently the Lookup Rule is bring in all transactions and I am only wanting transactions that are 70 days old and newer and then display them into a table. 

Example todays date is 02/14/2021, 70 days ago would be 12/06/2021, I want to pull in all transaction into the table from 12/06/2021 to present, but I can't figure out a way to do this. 

Is a Java Script the way to go? If someone could help me with that that would be awesome

Is there a way on the field that I can set a formula to allow dates 70 days old and newer?

Any help is much appreciated. 

 

 

0 0

Replies

replied on February 14, 2022

Hi Matthew-

You should use a stored procedure for this. Basically, the SP will query all the rows that where the date column is greater than or equal to the date 90 days ago:

WHERE [CreateDate] >= DATEADD(day, -90, GETDATE())

Then for your lookup, use the stored procedure instead of the table/view you have been.

If you're new to stored procedures, an easy way to think about them is pre-configured queries. They can include various input parameters, for example, if you need to limit to a specific customer. I was a little intimidated by them when getting started, but they're quite useful with Forms. w3Schools has some good info on them.

SQL Stored Procedures (w3schools.com)

 

3 0
replied on February 14, 2022

You could also create a view with the same WHERE clause. The most important difference with a Stored Procedure is that it won't pull in the column names so you would have to manually type them all whereas a view acts more like a table in Forms.

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

Sign in to reply to this post.

Details
Related Posts
Loading ...