Lookup Formula
Author Message
Lookup Formula

- - - - - - - - - -
Hi,

Can anyone please help? I got a problem in a form
procedure which I developed using the AppGen in   CW2003.

I have one file(AmtDetailFile) that look up
the table of another file (DiscountTableFile) as
shown below:

AMTDetailFile          DIScountTableFile
--------------         ---------------------------
AMT:TotalAmount         DIS:AmtFrom  DIS:AmtTO  DIS:Rate
AMT:DiscountRate        -----------  ---------  --------
\$0.00           \$19.99  0 %
\$20.00          \$49.99  5 %
\$50.00          \$74.99  7 %

In the formula editor:

Class : After Lookup
Result: AMT:DiscountRate

Under the conditional structure:

IF DIS:AmtFrom <= AMT:TotalAmount AND DIS:AmtTO >= AMT:TotalAmount
= DIS:Rate
ELSE
=0

But the above did not generate result at all. Did I miss something?
Appreciate some help very much.

--
B Chen

Wed, 22 Dec 1999 03:00:00 GMT
Lookup Formula

- - - - - - - - - -

Quote:
>Hello,

>Thank you for your reply and suggestion. If it is not too much to
>ask, could you correct me if this is the right FUNCTION to do a
>SET and NEXT?

>[...]

>I don't have any programming knowledge and CW is new to me
>and I am still learning and trying to develop a program for

You could do something like this:

Make the DIS-table as follows:

DIS
Below  Rate
-----  ----
20.00     0
50.00     5
75.00     7
75.01     9

Make Below+Rate the fields of the primary-key (I would name it DIS:B_R_).

Because the NEXT will put it on the first higher value, do it like this:

CLEAR( DIS:RECORD )
DIS:Below = AMT:Total + 0.01
SET( DIS:B_R_, DIS:B_R_ )
NEXT( DIS )
IF EOF( DIS ) THEN PREVIOUS( DIS ).

Results:
If AMT:Total = 50.00, then DIS:Below = 50.01, and the NEXT goes to 75.00,
corresponding to 7%.
If AMT:Total = 49.99, then DIS:Below = 50.00, and the NEXT goes to 50.00,
corresponding to 5%.
If AMT:Total = 75.00, then DIS:Below = 75.01, and the NEXT goes to 75.01,
corresponding to 9%.
If AMT:TOTAL > 75.00, then DIS:Below > 75.01, and the NEXT goes to EOF, and
the PREVIOUS brings you back to 9%.

I assumed that your numbers are stored as DECIMAL with 2 positions. When
AMT:Total is of type REAL, use DIS:Below = ROUND(AMT:Total, 0.01).

I once used a similar technique to cope with another problem: how to store
consecutive chairmen. I used a table like this

DATE       PERSON
Until      PRS_AUTO
------     ------
19941231      0       Nobody (from whenever  -1994/12/31)
19950630     23       Mr.X   (from 1995/01/01-1995/06/30)
19951231     11       Mr.Y   (from 1995/07/01-1995/12/31)
19960501     17       Mr.Z   (from 1996/01/01-1996/05/01)
99999999     27       Mr.A   (from 1996/05/02-now)

etc., so that you could easily ask who was the chairman at a given date.

You can use this technique to store historical values of any field in your
database, to make it possible to even answer questions such as: what were
our discount rates in July 1995. But I will leave that upto the reader.

Best regards, and good luck,

Fri, 24 Dec 1999 03:00:00 GMT

 Page 1 of 1 [ 2 post ]

Relevant Pages