Count records in related table and display in portal

[AGilek]

Hello!

Please help a newbie!

 

I’ve got a FMPro13 database which covers UK music charts.

There are 2 related tables: [Dates] and [Runs]. They look as follows:

 

[Dates] contains field as below:

<idDates>     index field, autonumber

<Date1>        date field, a date of publishing the chart

 

[Runs] contains fields as below:

<idRuns>       index field, autonumber

<Place>         number field, position on the chart

<idDates_fk> number field, a foreign key which links both tables

<Title>           text field, title of a single

<Name>        text field, name of the artists

 

I've created a layout based on [Dates] table - titled [Weekly]. At the top placed combo field linked to <idDates> field. Then placed a portal inside [Weekly] layout based on [Runs] table. The idea was to show chart runs for the particular date assigned to particular record from [Date] table. Changing record in <id Dates> changes titles and names on the chart, week by week. Nothing spectacular.

 

The next idea was to count weeks the particular track was on the chart. Created <Weeks1> field in a portal, next to <Place> field. Simple Count (Runs::idDates_fk) function always returns total number of weeks, up to latest chart.

 

But how to create a way to "dynamically" count weeks-on-chart number on every weekly chart?

Something like this:

 

idDates_fk = 1000

Place = 1

Weeks = 1

 

idDates_fk = 1001

Place = 4

Weeks = 2

 

idDates_fk = 1002

Place = 5

Weeks = 3

 

Tried to create a global variable and copying <idDates> to it every time record changes. Then counting records in [Runs] where idDates_fk < "idDates selected". Failed.

 

Could you help, please? thank you!

Andrzej

[comment]

You will need to define a self-join relationship of the Runs table. Before getting to the details: why don't you have a table for the Titles, where each title would have a unique record?

[AGilek]

You will need to define a self-join relationship of the Runs table. Before getting to the details: why don't you have a table for the Titles, where each title would have a unique record?

In fact I do have separate tables for the Titles and Names: [Entries] and [Artists]. Both tables have foreign key fields in [Runs] table: <idEntries_fk> and <idArtists_fk>. So no problem here.

Just tried to make my question simpler.

 

Now, how do I define a self-join relationship of the [Runs] table?

[comment]

Define the self-join as:
 
Runs::idEntries_fk = Runs 2::idEntries_fk
AND
Runs::idDates_fk ≥ Runs 2::idDates_fk
 
where Runs 2 is a new occurrence of the Runs table. Then define a calculation field (result is Number) =
 

Count ( Runs 2::idRuns )

This will return the number of times the entry has appeared in the Runs table before (and including) the current week.

 

Note that the count is discontinuous: if an entry appeared for 3 weeks, then missed 2 weeks and then reappeared for 4 weeks, the result will be the total 7 weeks.
 

[AGilek]

Thank you, sir! Works like charm!

Now it seems so simple and obvious.

 

Regards, Andrzej