Method Community

Hello Everyone,

I have a number of highly customized screens which have worked exceptionally well for us.  The biggest problem we have is with the limitations of the report writer.  Let me provide an example.  We have a Class Curriculum system that has three tables.   A list of activities, a list of materials used in activities and a list of lessons that are made up of mutiple materials.   Mutilple activities in each lesson with mutiple materials per activitiy.  We need a report that shows the lessons, with each of the activities listed used in that lesson, with each of the materials used in each activity.

We are able to get the detail report showing the lessons and the associated activities, but can not figure out how to show the materials assoicate with each activity.  The activites are listed in the lesson plan as a dropdown, and the materials are listed as a dropdown in the activitiy.  Just can not figure out how to setup the report designer to show all three files.

Thanks for any help you can offer.

Hi Stuart,

While you have 3 levels of related data here, it’s not impossible to accomplish this. If would be nice if you could simply nest a detail report within another detail report, but Report Designer does not allow us to do so. Instead, you need to “flatten” your data to use 2 tables instead of 3.

You can accomplish this by making use of Method linked fields. Just link all the data you need on the report from the top level table (Lessons) into the middle level table (Activity). You can now use the Activity table as the base table for the report. Your only detail table will be the bottom level table (Materials).

In order to build the report, you’ll need to add 2 Group Headers and 1 Detail Report. These will be configured as follows:

First Group Header:

Group Field: Activity.Lesson_RecordID

Fields to add below: The lesson fields via the linked fields in the Activity table

Second Group Header:

Group Field: Activity.RecordID

Fields to add below: Fields for the activities

Detail Report:

Data Member: Activity.Materials.Activity (the Materials table nested within the Activity table)

Fields to add below: Fields for the materials

Your result will be a report displaying the lesson in the first group header, all activities related to the lesson in the second group header and all materials related to the activity in the detail report. I hope this helps.

--Audisho