Question

Referencing a value range from Forms data source

Forms

Updated April 23, 2015

asked on April 17, 2015 • Show version history

What would be the best way to handle the following in Forms?

Customer has a data source which they use to find a fee. The fee is determined by finding the range of the valuation and using the fee for that range. We capture the valuation in the form but I'm not sure how to assign the appropriate fee.

Example:

Valuation From	Valuation To	Fee
$100.00	$1,000.00	$25.00
$1,001.00	$1,500.00	$50.00

Using the table above, any Valuation between $100 and $1000, the fee would be $25.

How is the best handled? A stored procedure? If so, can you provide an example?

Thanks,

0 0

Answer

APPROVED ANSWER

replied on April 17, 2015 • Show version history

To help visualize what Ken is saying, here's a table called RangeValues

Here's a stored procedure that gets the fee using a parameter called Valuation:

USE [MyDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [GetFee]
	@valuation decimal(10,2)
AS
BEGIN
	SET NOCOUNT ON;
	select fee from rangevalues
	where @valuation between ValuationFrom and ValuationTo
END

Using a form that has two currency fields (one for the valuation and one for the fee), you can then use a lookup rule like

1 0

Replies

replied on April 17, 2015

I am confused. Is the data source providing the range and the associated fee? And you want to have inside your form where if it matches to a defined range, that the highest fee it overlaps to is used, correct?

If you are doing this form for just 1 evaluation, then this is easy, but if you are looking to do it inside a table so multiple items can be evaluated, we may need to do a little bit of JavaScript for this.

You will just want to take the high value and have the stored procedure evaluate if that value is within a range with your two columns in the database being the low and high. It'll return you the fee to use. I believe the example in the help pages should be able to get you most of the way there for the stored procedure. What you will need is to use the BETWEEN statement in your SQL Stored Procedure. w3schools will be very helpful for that.

1 0

replied on April 17, 2015 • Show version history

Thanks, Alex and Kenneth for your replies. Using Alex's example, I have this working.

Thanks again!
Jen

0 0

You are not allowed to follow up in this post.

Question

Question

Referencing a value range from Forms data source

Answer

Replies

Sign in to reply to this post.