Jump to content
Sign in to follow this  
AGilek

Count records in related table and display in portal

Recommended Posts

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

Share this post


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?

Share this post


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

Share this post


Link to post
Share on other sites

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.
 

  • Like 3

Share this post


Link to post
Share on other sites

Thank you, sir! :) Works like charm!

Now it seems so simple and obvious.

 

Regards, Andrzej

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

  • Similar Content

    • 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 16.0.4.456 (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!!!!!!!!!!!
       
       
       
       
      pic1.docx
    • By cat traveller
      Hi everyone,
      on a file accessed via filemaker Go 17, I am using a scripted navigation to move records up and down in a portal that is inside a popover.
      This has worked in filemaker 12 like a charm and also works on a filemaker client. The records smoothly move up and down the list. 
      However in Filemaker Go, the portal follows every move of the script as if it is not aware of the freeze portal.
      EG the down button is to move the record one record further down inside the filtered and sorted portal (like so)
      This is the script.

      - freeze window and go to portal
      - go to first line of portal and loop through the portal until I hit the record to move
      - go to next line and give the position field the value of my record to be moved
      - go to line above and give the position field the value of my record +1
      - commit record and refresh portal via an x join 
      - go to last line of the portal 
      - go to the line of position of my record to be moved. 
       
      Would anyone be aware of this issue or know a workaround to stop the portal from flickering?
       
      Thanks so much
       
    • By Gismo12
      Hi,
       
      I have a drop down in a portal, how do i filter the drop down box to only display the record relating the record in focus on the active layout where the portal is inserted?
      example: I have a customer selected in my customer layout, the customer has several products related to that customer. when I click on the drop down list in the portal, I only want to see the products for that specific customer. The drop down is in the warranty portal, products are saved in the product table with a reference to the customer.
       
      Please could you assist?
    • By H
      Hi
      i have 3 Tables Property , Units , Tenant.
      Each property has many units , Each unit has one tenant active but may have many inactive tenants from previous years.
      All working fine BUT.....
      i would need to show a table in the property layout of the units and each one only with its active tenant.
      The portal is based on the units table. 
      However the tenant just show up the tenant that was first input to the unit.
      My question is , how could i get that only the active tenant should show to the unit?
      i would really appreciate it someone could guide me on this.
×
×
  • Create New...

Important Information

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