3 Replies Latest reply on Apr 24, 2011 6:20 PM by LaRetta_1

    Help producing a report with images

    JamesBarnes

      Title

      Help producing a report with images

      Post

      I'm fairly new at this & am struggling to figure out how to produce a customer report with multiple products each with product name, description & image 

      I have these tables – Customers, Workout Program, Exercises

      The exercises table has: ID, Name, Description & Photo

      I’m currently using my ‘Workout Program’ layout with 15 drop down list fields that allow me to select their values from the ‘Exercises’ table. The idea is that each customer has a workout program with 6 or 8 or 11 etc different exercises (up to 15).

      This is what it looks like

      Exercise List

      I need to produce a report for each customer that shows all of the exercises selected in the above layout, plus the Exercise name, description and most importantly Exercise Photo from the exercises table

      In summary:
      Each customer can have up to 15 Exercises
      I need a report for each customer that shows all of the exercises selected + the exercise description & exercise photo

      The report to look something like this

      Exercise 1 Ecercise 2 Exercise 3

      Exercixe Name

      Description

      Photo 

      Exercixe Name

      Description

      Photo 

      Exercixe Name

      Description

      Photo 

      Exercise 4 Ecercise 5 Exercise 6

      Exercixe Name

      Description

      Photo 

      Exercixe Name

      Description

      Photo 

      Exercixe Name

      Description

      Photo 

        • 1. Re: Help producing a report with images
          LaRetta_1

          The inability to produce reports or summarize on these multiple fields is one major reason that your Exercises should be related records and not fields. 

          Any time you find yourself creating multiple 'like' fields, such as Exercise1, Exercise2, etc, it is red flag that you are mis-structuring.  Each exercise should be a record in the Workout table instead which would look like this:

          WorkoutID (unique auto-enter FM-generated meaningless serial)
          ExerciseID (foreign key which is the primary ID from your Exercises table)
          CustomerID (foreign key which is the primary ID from your Customers table)

          You may want additional fields in this workout schedule such as duration, repetitions ... anything else specific to the workout.

          When you have this structured properly, you then isolate your customer records (either by performing a search in the Workout table or by issuing a Go To Related 'from customer to Workout).    You would then generate your report easily because the exercise is a record.  You would place ExerciseName, Description and Photo fields from Exercises directly on the Workouts layout.  Since they are related by ExerciseID, the information will display in Workouts.

          If you wish to see an example, I would be happy to provide one.

          • 2. Re: Help producing a report with images
            JamesBarnes

            Thanks LaRetta,

            I have it figured out now.

            Always seem to get ahead of myself creating pretty layouts and fancy reports without thinking about the foundations first!

            • 3. Re: Help producing a report with images
              LaRetta_1

              Boy, I hear ya there.  I'm always having to rein myself in from same thing and put off the 'fun' part of UI until the structure is completely right.  I'm glad that you are moving forward again. Laughing