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.

Thanks in advance.
--
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
>my business.

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  
 
 [ 2 post ] 

 Relevant Pages 

1. CW4: Formula Editor lacking move formula

2. It crushes when the formula in a Formula Node is somewhat complicated.

3. Formula manipulation

4. Equate Formula

5. Equate Formula

6. Formula One with Windows 2000

7. Linear regression: how to translate APL formula

8. APL+Win and Formula One

9. Math Formula in String

10. User defined formulas

11. Error in formulas with MSSQL

 

 
Powered by phpBB® Forum Software