I have two separate questions regarding portals and how they work. Before I start let me describe what I'm doing. I'm constructing DB for local karate club. I have one table/layout with general info about members, I have second table/layout with records about belts (including color of the belt, when & where the belt was obtained, the mentor of the member & sparing partners of the member etc.) and I have 3rd table/layout that shows various activities of each member (there are also several other tables but not important for this topic). All tables are connected via key field (ID number)
I have no problem creating portals on 3rd layout to show me which belts particular member has with all additional info from the second table that go along with it. BUT I don't know how to create a portal that shows all members of the club that had particular member as sparing partner. Thing is that I have 3 separate fields for sparing partner because each member can have up to 3 sparing partners and I simply don't know how to connect them all so I can see the list of all members that have particular member as one of three possible sparing partners.
I know how to place count of related records in filtered portal but I'm not sure how to get a Total of this counts.
Any help is much appreciated!
In my database of weekly music charts (FMPro14) there are 4 linked tables:
[Hits] - list of the hit singles
[Entries] - list of different entries, remixes, reissues
[Runs] - list of chart placings for every week
[Dates] - list of dates of the weekly charts
<idHits> index field
<Title> text field
<idEntries> index field
<Title> text field
<idHits_fk> number field, a foreign key which links [Hits] table with [Entries] table
[Runs] contains fields as below:
<idRuns> index field
<Place> number field, position on the chart
<LastWeek> number field, last week position
<idDates_fk> number field, a foreign key which links [Dates] table with [Runs] table
<Title> text field, title of a single
<Name> text field, name of the artists
<idDates> index field
<Date> date field
Relations are as follows.
[Hits] & [Entries] are linked by the relation:
Hits.<idHits> = Entries.<idHits_fk>
[Entries] & [Runs] are linked by the relation:
Entries.<idHits> = Runs.<idEntries_fk>
[Dates] & [Runs] are linked by the relation:
Dates.<idDates> = Runs.<idDates_fk>
Two years ago, on this forum, with a Consultant help, I have defined 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.
So this allows counting chart weeks for every record in [Entries].
Now, I’d like to also count TOTAL number of weeks, for [Hits], not [Entries], something like:
Runs.<Place>; Runs.<LastWeek>; Entries.<Title>, TotalWeeksCount for main record in [Hits]
Example. Let’s say there is a hit single titled „Purple Rain”. Looking at its chart history we should get something like:
Purple Rain - original entry - 10.10.1984 - 10 (0) 1 week
Purple Rain - original entry - 17.10.1984 - 8 (10) 2 weeks
Purple Rain - original entry - 14.12.1984 - 43 (33) 10 weeks
— out of chart —
Purple Rain - re-entry - 08.07.2008 - 56 (Null) 11 weeks *** 11 weeks, NOT 1st week of this new entry! ***
Purple Rain - re-entry - 15.07.2008 - 48 (56) 12 weeks
How can I get such results? Help needed, thanks!
While creating a database that has Products, Suppliers and Lines I have come across the need to calculate a count of the suppliers from the context of a Products record. My relationship is like so:
My question if I want to pull a lot of information about Supplier is should my portal in the Products record be from Suppliers or from Lines? If the best answer is Lines, could I still have a count of related Suppliers by using the primary key from that table in the portal? Is it a bad idea to use fields from a related tables portal other than its own? Thanks
View File pdf Pages Count
Someone asked if it is possible to count the pages of a PDF...
The attached file contains a tail recursive custom function that can do the work for the most common cases.
Submitter Raybaudi Submitted 09/27/2015 Category Samples FM Version FM Version: 4