That is a good idea.
You could also use formulas, variables and grouping to display the information in the group footer.
Group on customer
In the group header add a formula
WhilePrintingRecords;
Global StringVar PRERDate;
Global StringVar PROPDate;
Global StringVar POLDDate;
PRERDate := "";
PROPDate := "";
POLDDate := "";
In the detail section add a formula
WhilePrintingRecords;
Global StringVar PRERDate;
Global StringVar PROPDate;
Global StringVar POLDDate;
If {ActTypeField} = 'PRER' then
PRERDate := CStr({ActDateField},"m/d/y
Else If {ActTypeField} = 'PROP' then
PROPDate := CStr({ActDateField},"m/d/y
Else If {ActTypeField} = 'POLD' then
POLDDate := CStr({ActDateField},"m/d/y
"
In the group footer use 3 formulas to display the values
WhilePrintingRecords;
Global StringVar PRERDate;
PRERDate
Another way to do this is to add the Activity Table 3 times using a COMMAND
Join on the Customer and filter in the JOIN on the activity type
SELECT Cust, ExpDate, Type, ID, Act1.ActType, ACT1.ActDate, Act2.ActType, ACT2.ActDate, Act3.ActType, ACT3.ActDate
FROM
Table1 LEFT OUTER JOIN ActivityTable Act1 ON Cust = Act1.Cust and Act1.ActType = 'PRER'
LEFT OUTER JOIN ActivityTable Act2 ON Cust = Act2.Cust and Act2.ActType = 'PROP'
LEFT OUTER JOIN ActivityTable Act3 ON Cust = Act3.Cust and Act3.ActType = 'POLD'
You can then simply use the fields to display the records in the detail section
mlmcc
Main Topics
Browse All Topics
by: peter57rPosted on 2009-10-30 at 08:57:40ID: 25704228
I think you could create a single formula field by concatenating the Cust & ExpDate & Type & ID#. The exact formatting would need some care to produce a com=nsistent appearance. You could then use a crosstab report to produce the output you need, using the formula field as the row label.