Jump to content

Count records in related table and display in portal

This topic is 2900 days old. Please don't post here. Open a new topic instead.

Recommended Posts


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!
Link to comment
Share on other sites

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? :)

Link to comment
Share on other sites

Define the self-join as:
Runs::idEntries_fk = Runs 2::idEntries_fk
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.

  • Like 3
Link to comment
Share on other sites

This topic is 2900 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...

Important Information

By using this site, you agree to our Terms of Use.