One to Many Report 1

[BettyM]

Trying to write a CR report in version 8 which has two databases linked by file number. One record in the firts database will match 10 records in the second db for each file number.

Records have two fields in the second db. Need to see the answer to those two fields if a certain criteria is met.

My problem . . . it only wants to display record one of the second db. CR isn't including the other 9 matches in the results.

Any ideas how to get it to loop through all the results?

 

[lbass]

You would get results like this if you were displaying your fields in the group header or footer, instead of the details section, or if you had specifically selected to limit records to one per group, such as a group select limiting records to the maximum date for a particular group. Maybe you should provide your select statements (record and group).

If this a report you are editing, not creating, you also might check for conditonal suppressions on fields or sections.

-LB

 

[BettyM]

You are absolutely right. I had consolidated into the group header and suppressed the details. I see the details with the information I need now but it takes up too many rows to list all the "many" rows.

The rows in the "many" have two fields . . . a line number equal to 1, 2, 3, 4, 5, 6, etc and a Y/N field. I only need line number 1's Y/N answer and line number 5's Y/N answer.

I assume I neeed to capture the answer in some formula as it passes through the details and display the consolodiated answer in a group footer.

How do you save Y/N values based on a condition to display in a group footer and reset them for the next pass of one to many?

 

[lbass]

If you don't need the other details, the simplest approach would be to select only those details you need:

{table.lineno} in [1,5]

Then you could just create the following formulas:
{@Y}:
if {table.yn} = "Y" then 1 else 0

{@N}:
if {table.yn} = "N" then 1 else 0

You could then insert summaries on these by right clicking on the formulas to get group and/or report totals.

If you need to keep the other details, you could amend these formulas to:

{@Y}:
if {table.lineno} in [1,5] and
{table.yn} = "Y" then 1 else 0

{@N}:
if {table.lineno} in [1,5] and
{table.yn} = "N" then 1 else 0

If you have duplicate records, then you would need to use running totals. Using the running total editor, choose a recurring field to do a distinctcount, evaluate based on a formula, e.g., for {#Y}:

{table.lineno} in [1,5] and
{table.yn} = "Y"

Reset on change of group. At the report level, create the same running total, but reset never.

-LB