Nested tables numbering that are created on master table

So i have the following issue. In my database I have a table named bp_minutes_app.

This table stores records of minutes from committee meetings. Each minute can consist of many files. The database design regarding minutes is the following:

The table bp_minutes_attachments is connected via a foreign key to the bp_minutes_app and holds the following information:

description, createdby

The table bp_files  is connected via a foreign key to the  bp_minutes_app and holds the file along other information. We are interested in taking only the name and id of the file (the latter in order to create a hyperlink to the file, but this is out of scope of the present question). 

Final remark. All those tables are linked to the master bp_full  table that is joined only to get the id of each specific report.

In my report I want to print on a table all minutes and the accompanying information (file name, description, createdby, submission date), but make a separate entry for each minute. In order to achieve this, I first created a table that uses the following dataset:

SELECT bp_minutes_app.id
FROM bp_minutes_app
    INNER JOIN bp_full ON
     bp_minutes_app.bp_full_app_id = bp_full.id
    WHERE bp_full.id = $P{id}

With this dataset I retrieve each minutes record. In my example, I get two records with ids 39 and 40.

Inside this table I nested another table to retrieve information for each file in each minutes record. The dataset that I used is the following:

SELECT bp_minutes_app.submission_date,
    bp_minutes_attachments.createdby,
    bp_minutes_attachments.description,
    bp_files.id,
    bp_files.name
FROM bp_minutes_attachments
    LEFT JOIN bp_minutes_app ON
     bp_minutes_attachments.bp_minutes_app_id = bp_minutes_app.id
     AND bp_minutes_app.id = $P{mid}
    LEFT JOIN bp_full ON
     bp_minutes_app.bp_full_app_id = bp_full.id
    LEFT JOIN bp_files ON
     bp_minutes_attachments.bp_file_id = bp_files.id
WHERE
     bp_full.id = $P{id}

The id parameter is used again to take the specific report and the mid parameter to take a specific minute. On parameter specification mid is declared as follows:

mid = $F{id}, where id is the bp_minutes_app.id.

Following this setup I managed to get nested tables for each minute (forgive me for the greek headers):

What I want to do is number each subtable from 1...n, according to the number of minutes/ subtables. How can I achieve this? I tried to create a suitable variable but it was not met with success. I want to somehow "save" in a parameter / variable the count of bp_minutes_app records and manipulate this number for each nested table header.

rpdnick's picture
Joined: Nov 2 2015 - 12:44am
Last seen: 7 years 6 months ago

0 Answers:

No answers yet
Feedback
randomness