I'm trying to create a report for a solution, and have hit a bit of a roadblock when creating a report.

 

I have 3 tables:

 

1. Statistic Categories

        -Each record in the table is a particular statistic code

 

2. Incidents

        -Contains individual incidents. Each incident is coded with a statistic (using a value list, containing values from Statistic Categories table)

        -Only 1 statistic code is assigned per incident

 

3. Reports

        -Reports are related to a particular incident, and contain an incident report for the incident.

        -Each incident may contain multiple reports

 

 

I am using a report format already in use by the company, and need to continue using the same format. The report itself is "hard coded" as it needs to be in the same format as the existing form.

 

 

From time to time, searches may be performed to display incident reports for a certain incident date range, or for a certain incident type(s). Based on the reports found, I need to update the header for the report to output a Yes/No value as to whether the found set contains a certain report type. I suspect this can be done somehow by obtaining a count of each statistic category in the found set?

 

I would appreciate some advice on how to achieve this.

 

I have attached an example file.

 

Thanks

Matt

Report.zip

I was able to find a resolution for this.

 

I used the GetFoundSet(Fieldname) function from http://www.briandunning.com/cf/1093

 

Using global variables, I was able to incorporate this into a report generation script. When the report is generated, several global variables are created, example: $$TheftY, $$TheftN. If a "Theft" statistic is coded in the found set, $$TheftY="X", which is output using a merge variable onto the report.

You could also try the following:

 

In the Reports table, create calculated field "booleanTheft"  and set it =  Incidents::Statistic Category = "Theft"

Also create a Summary field "sumTheft" and set it = Total of booleanTheft

 

Put 'sumTheft' in your header  to display the count of Thefts in the current found set, or format the number to display as a boolean (yes/no) if you prefer.  

 

Do the same with your other statistics categories.