Method Community

Aloha Everyone,

I have had a problem using report writer when trying to access data.  let me give you an example.  I have a Lesson Plan that has mutliple activities.  I want to run a report that shows all the activies for a lesson plan.   This I can do without much problem.   The difficulty comes when I then want to also show all of the the materials used for an activity.  The materials are linked to the activity record and not to the lesson plan.  

So my structure is  Lesson Plan Table,  Lesson Plan Activity Table (multiple activities records per Lessson Plan), Activity Table with information about the activity, Material Table with multiple material records per Activity)  There are an undetermined number of activities per Lesson and an undertermined number of materials per activity.  Activities can be used in many Lesson Plans.

So in trying to resolve this without having to build a seperate table at the time of printing I have been searching for results.   It is easy to do on the screen but seems impossible in the report writer.

One idea I have is to use a script in the report writer to get the Material information I need.   Unfortunately I am not skilled in VB Scripting.  Can anyone send me some examples of a script that would retrieve data from a Table so it could be printed in a label in the report writer?

Or if you have any ideas about how report writer can do what I need directly please let me know that as well.

Stuart Katz

Hello Stuart,

Instead of using scripts which shows a particular workflow, why don't you use the table with most information as your master table and then link all the fields required on the report to that table. Once you do that, it will be supereasy to show those fields on report and it will show all the data from all the tables. the benefit of this workflow is you can design the report in whichever way you want, but with scripting it will be a limited workflow.

-Inder

Inder,

I would love to talk with you about this on the phone.  I don't think there is anyway to do what you are suggesting.

I have 4 tables.   1 - A Lesson Plan Table that has the descriptive information about the Lesson Plan.  2 - A Lesson Plan Activities file that has multiple records for each Lesson Plan that includes the Lesson Plan ID, a sequence number and an Activity ID.  (There are multiple Activities in each Lesson Plan.)  3 - There is then an Actrivity Table that has descriptive information about the Activity. (Activities can be used in any Lesson Plan)  4 - Material Table that has description about a specific Material that is used in the Activity (There are Multiple Materials in each Activity)

The report we want would have a header for the Lesson Plan descriptive information.  It would then have the first Activity (based on the sequence number) for the Lesson Plan with the descriptive information from the Activity File and then lastly it should show all the Material records for the Activity.   Then it would show the second Activity with the Material for that Activity.   This would continue thru all the Activites for the lesson plan.

If you would like me to show you what I mean give me a time when we can schedule a phone call.

Stuart Katz