Jump to content

Count records in related table and display in portal

This topic is 2047 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 post
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 post
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 post
Share on other sites

This topic is 2047 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
  • Similar Content

    • By Tony Diaz
      The tables.
      The Genre data is already arranged like this:
      Genre::ID (Unique Key)

      The Platform data is 'simple'. ID and Name. But each item could be multiple platforms.  (Example #1 vs. Example #2)
      An item can have multiple Genre Categories related to it, and those usually have a single choice from within their Category, but might have multiples.

      It's supporting the possible multiples that I'm trying to work out. At this point there's 15 possible Genre Categories that each Item could have a selection from. Most have 4-6 of them.

      Example 1: Items::Table on the left, Genre::Table on the right, with some Genre Categories (Genre, Perspective, Pacing, Gameplay, Interface, Setting) and their sub-options.
      This one has just one sub-option per category.

      Example 2:  The Gameplay Genre Category has two sub-options related to it.

      Example Genre Table content:

      Just cracking the surface on One to One and One to Many relationships, I don't think this scenario is quite covered this way.

      Would each of those Genre Categories be portals showing only their related category ID?

      I presume that I would add fields to the Items::Table so I can pull related records:
      But those only support one relationship.
      Would I make value lists from those Genre Categories and Platforms and set them as tick box fields?
    • By stan111
      I use self portal on my Clients layout to serve two things:
      1. display all the records and
      2. quick navigate among them. 
      This portal is not displayed in Webdirect. 
      Is it possible to make it work?
    • By Guy_Smith
      This should be easy, but my brain just won't cooperate!
      I'm moving a bunch of stuff and want to inventory what is in each moving container.  I have a very simple parent-child relationship with the parent record having the container number and category of parts/equipment/supplies and the child records describe each piece of equipment in the container.  I have a portal with the child records showing for each container, but want to put anatto-entered item number for each piece that has the container number followed by a dash and then followed by a serialized number for each item in that particular container.  For example, I would have container 1 labeled "Glassware" and Container 2 labeled "Chemicals".  In Container 1's portal I'd like to see:
      Item    Description          Qty
      1-1      Beakers, 500ml    4
      1-2      Beakers, 250 ml   2
      and in Container 2's portal I'd like to see:
      Item    Description          Qty
      2-1      Potassium           4 g
      2-2      Sodium                26 g
      I'd like to auto-fill the item numbers, but can't figure out how to reset the serialization for each parent record.
      Any help is greatly appreciated.
      Thanks and keep yourselves safe!
    • By Randy Bennett
      Yes, I did say iPhone 5 -
      I have a small database i created that has a few portals on different layouts - Everything works fine on my Mac, fine on my iPhone X, even fine on my iPad mini
      However, I need it to also work on an old iPhone 5 and the portal data is just blank - nothing. In fact, I really ONLY need it to work on this old iPhone 5 (This particular database will go to a music festival to be used by several other people and I can't give them my iPhone) 
      I have another layout with different portals that are working on the iPhone 5 - but not this main screen. 
      I have made sure that all of my fields are small and inside the portal. - So, I am stuck as to why? BTW - It is Filemaker Go (The latest version that I can install on an iPhone 5)

    • By 1FilemakerMan
      I have an inventory database with several different types of inventory in it.  I would love to have at the top of the header a count of each type of device I have.
      The Column name is "Device Type", and right now I only have four different types of devices, "Printer, CiscoPhone, DockStation, and Lablemaker".   I would love to have a counter in the header for each device so I can simply see how many of each device I have.  I can't seem to find a way to do this, and I am sure there must be.
      Could you please help me out?? Thanks!!!!!!!!!!!

  • Create New...

Important Information

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