At 01:07 PM 1/3/2008, Matthew Burke wrote:
How do I get data from two different tables into one RichText
control in the reports designer. We have one table `table1`
with customer account information and another table `table2`
with employee contact information. They are related by
branchnumber. We're using or wanting to use the reporting
functionality of R:Base to generate letters to our customers
which should contain different contact info depending on the
branch the customer is associated with.
Here's one approach to achieve you goal:
01. Define a View (Permanent or Temporary) based on Table1
and Table2 linked by BranchID.
Example:
-- Start
CLS
SET ERROR MESSAGE 677 OFF
DROP VIEW ContactsByBranch
SET ERROR MESSAGE 677 ON
CREATE VIEW ContactsByBranch +
(BranchID,BrColumn2,BrColumn3,EmpCol1,EmpCol2,EmplCol3)
AS SELECT +
T1.BranchID,T1.BrColumn2,T1.BrColumn3, +
T2.EmpCol1,T2.EmpCol2,T2.EmplCol3 +
FROM Branches T1 +
LEFT OUTER JOIN BranchEmployees T2 ON T1.BranchID = T2.BranchID
COMMENT ON VIEW ContactsByBranch IS 'Employee Contacts By Branch'
RETURN
-- End
02. Create a report based on ContactsByBranch view.
03. Uncheck the following bands:
. Report Header
. Report Footer
. Page Header
. Page Footer
Report Designer | Main Menu | Report
The report should only contain [Detail] band.
04. Place a "Rich Text" object on the [Detail] band.
05. Check the "MailMerge" option of Rich Text control.
Right-click on Rich Text control and select "MailMerge"
06. Double-click on Rich Text control to open Rich Text Editor.
07. Draft the letter and place all Columns, Pre-Defined Variables,
or System Variables using the right-click.
While in Rich Text Editor with "MailMerge" enabled, you can
Insert Field... or Insert Variables.... using the right-click.
You can also apply Format Mask for each column/variable, if
you wish.
08. Close the Rich Text Editor and save all changes.
09. Define a Break[0] based on BranchID.
On Break Change: [x] Start New Page
10. Click on [Report Preview] tab to preview the entire report.
Make necessary adjustments, if necessary.
Save the report and close report designer.
That's all there is to it!
Additional References:
01. From The Edge: http://www.razzak.com/fte/
Topic: Using the Rich Text Control for Mail Merge in R:BASE
7.x and Turbo V-8 for Windows
02. Sample Applications: http://www.razzak.com/sampleapplications/
Application: Using the Rich Text Control for Mail Merge
03. Running R:BASE Your Way! (Part 14)
Database: RRBYW14
Report: CustomFormLetter
Have fun!
Very Best R:egards,
Razzak.
