Count for each record with a matching element 2

[griffter]

Hi hope someone can help. I have a report which displays the design of a study by extracting the forms created its fields and their values. For each main form type there are responses, ie fax, mail, html. I am selecting records based on the main form name, I then need to display what responses exist for this record, some may only have fax, some may have all three.

I have created three formula fields and a simple
x field in [typeid]. However I get returned only a true for the first response type displayed even though there are other response types for that form in existance.

To try to see what was happening I placed a cross tab in the heading with the same formulas. This showed two email responses, two fax responses and one html (which is not true) and a total (on distinct count of 4). This does not make sense??? Is this the way the data is being linked that is causing the problem???

 

[Madawc]

Group by main form name, if you're not doing so already.

Create three running totals, each counting selectively on a response type.

In the group footer, check the counts and output suitable text. E.g.

if @fax > 0 then if @mail > 0 then if @html > 0 then "Fax, mail and HTML" else "Fax and mail" ...

You'll have to cover all the combinations.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

It is unclear what you want your final result to be. If you want a comma-separated list per form, then insert a group on form and then create three formulas:

//{@reset} to be placed in the group header:
whileprintingrecords;
stringvar response;
if not inrepeatedgroupheader then
response := "";

//{@accum} to be placed in the detail section:
whileprintingrecords;
stringvar response;

if instr(response, {table.responsetype}) = 0 then
response := response + {table.responsetype} + ", ";

//{@display} to be placed in the group footer:
whileprintingrecords;
stringvar response;
if len(response) > 0 then
left(response,len(response)-2)

Then suppress the detail section. If you were looking for the counts of all fax responses, then you would use a detail-level formula like:

if {table.responsetype} = "fax" then 1

Then you would right click on this formula and insert a sum, not count.

-LB