@XLookup with Report Derived Columns


You can use an @XLOOKUP function in a derived column of a report to bring in 
information from a different database.  For example, if you had two databases,  

EMPLOYEE and CONTRIB, that have a unique key field in common (employee number 
for this example) you could use information from both databases in one report.

The EMPLOYEE database is the same as the sample database provided with Q&A  
with the addition of a field called Emp# for the unique employee number.  The 
second database, CONTRIB, only has two fields, Emp# and Contribution amount.  
This database is for recording charitable contribution from employees.  A 
report from the contributions database would be very stark, only an employee 
number and a dollar figure.  To bring in the employee's first and last name 
use the @XLOOKUP function.

The following is the Derived Column spec in the CONTRIB database for the 
example report below.  On the Column Sort Spec the Contribution field is #3.  
On the CONTRIB database the Emp# field has been labeled #1 on the program
form.  @XLOOKUP here has the same parameters as programming with @XLOOKUP. Put
a 1 in the EMP# field in the Column/sort spec.


                             DERIVED COLUMNS

              Heading:  Last Name
              Formula:  @XLOOKUP("Employee", #1, "Emp#", "Last Name")
              Column Spec:  2,AS


              Heading:  First Name
              Formula:  @XLOOKUP("Employee", #1, "Emp#", "First Name")
              Column Spec:  3,AS,CS


              Heading:
              Formula:
              Column Spec:


              Heading:
              Formula:
              Column Spec:













Q & A Application Note       Rev. 6/92           #2208     Page 1 of 2


                     @XLookup with Report Derived Columns


    The report looks like the following:

         Last Name           First Name          Contribution

         Jeffers             David                     $10.00
                                                 ÄÄÄÄÄÄÄÄÄÄÄÄ

         Total:                                        $10.00

         Gallaway            James                      $5.00
                                                       $15.00
                                                 ÄÄÄÄÄÄÄÄÄÄÄÄ

         Total:                                        $20.00

         Abrams              Judy                      $15.00
                                                        $5.00
                                                       $15.00
                                                 ÄÄÄÄÄÄÄÄÄÄÄÄ

         Total:                                        $35.00

         ÍÍÍÍÍÍÍÍ            ÍÍÍÍÍÍÍÍÍ           ÍÍÍÍÍÍÍÍÍÍÍÍ
         Total:                                        $65.00


























 



Q & A Application Note       Rev. 6/92           #2208     Page 2 of 2