AGilek

Hmm, this is probably my fault. I'll try to explain. My chain of one-to-many relationships would be: Hits -< Entries -< Runs In the Runs table you can find all the weekly chart information: DateOfChart, PositionThisWeek, PositionLastWeek, idEntries_fk (foreign key to Entries). And for a particular chart I get something like that: DateOfChart, PositionThisWeek, PositionLastWeek, idEntries_fk (title taken from Entries table), WeeksCount calculated 11.10.1997, 1, 0 , HITSINGLE, 1 All my WEEKS COUNT calculations are for Entries. If there was another re-entry for HITSIGNLE on the way, weeks count starts from 1 on. I wrote before how the current weeks count is calculated: 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 ) There is no direct info on HITS in Runs table. But I want my chart to display the weeks count from the very first chart to certain chart in history (not total up-to-date weeks count). So, as in the example above, if this was a re-mix and there was original entry in 1995 for, say, 6 weeks, the number of total weeks on the chart week ending 11.10.1997 would be 7, not 1. On 18.10.1997 would be 8, not 2, etc.

Hi, everyone! 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 —————————————————————————————— [Hits] contains: <idHits> index field <Title> text field [Entries] contains: <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 [Dates] contains: <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 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. 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!

Thank you, sir! Works like charm! Now it seems so simple and obvious. Regards, Andrzej

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?

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

You are right, I should explain the purpose of <id2> field. I've tried to solve one particular problem. The same song could have been a hit many times, especially in our age of digital downloads. Lots of re-entries. Very old songs suddenly re-entering etc. So, taking example above: Table [Hits]: <idHits> <Title> <id2> 11 My Song (original CD single) 357 44 My Song (digital re-entry) 357 You see, two different chart entries but <id2> field combines them somehow. What I try to do is to COUNT WEEKS of "My song" throughout the whole chart spectrum. So I should add weeks count both for <idHits>=11 and <idHits>=44. On the other hand I want separate both entries and list peak/weeks statistic only for particular re-entry. Table [Runs] would look say like this: <idRuns> <idHits> <Place> <Date> <id2> 11 11 2 2001-01-01 357 12 11 8 2001-01-08 357 13 11 19 2001-01-15 357 71 44 4 2014-01-04 357 72 44 22 2014-01-11 357 73 44 33 2014-01-18 357 With <id2> field within [Hits] table I cannot get such diversity. I get something like I want with a report but not quite. But maybe there is simpler way of doing such task and my whole <id2> field makes no sense anyway? Regards, Andrzej

Hello! Need some basic advice if you don't mind. In my database (FMPro13) there are 2 linked tables, [Hits] and [Runs]. The [Hits] table includes: <idHits> index field, autonumber <Title> text field, title of the song <id2> extra field, a number Table [Runs] also contains a number of fields including: <idRuns> index field, autonumber <idHits> number field --> this one creates a relationship between both tables, created a link <Place> position in the charts, number field <Date> date of the weekly chart compiled <id2> extra field, a number I'd like to move content of the <id2> field from one table (Hits) to the corresponding <id2> field in another table (Runs). But there is one problem. In [Hits] for every <idHits> record, there is only one particular <id2> value. When placed in [Runs], <idHits> could occur many times. Same with corresponding <id2> value. Example: --------------- Table [Hits]: <idHits> <Title> <id2> 4 My Song 357 Table [Runs]: <idRuns> <idHits> <Place> <Date> <id2> 1 4 2 2014-01-01 357 2 4 8 2014-01-08 357 3 4 19 2014-01-15 357 How to do that? I've tried to create some scripts in FMPro to do that task but failed. Any suggestions, please? Thanks! Andrzej

Oh, thank you very much for suggestion! I will start from moving MainArtistID field into the HitRecords table. Then I will rebuild layouts. I'll get back to report! A.

I’ve got a FMPro12 database and there are 3 tables inside: [HitRecords], [Artists], [RE_AR]. Yep, it covers UK chart singles & albums. One disclaimer. I’ve switched from MSAccess so my understanding of some concepts may not be correct. The basic concept is this. ———————————————————— Table [HitRecords]: <ID_RE> index field, autonumber <Title> text field, title of an album/single Table [Artists]: <ID_AR> index field, autonumber <Name> text field, artist name Table [REAR] creates many-to-many relation between [HitRecords] & [Artists]: <ID_RE> number field, related record from [HitRecords] <ID_AR> number field, related record from [Artists] <Main> 0/1 field* * for every record in [HitRecords] there is only ONE record in [Artists] for which [REAR]<Main>=1 EXAMPLE ———————————————————————- Table [HitRecords]: <ID_RE> <Title> 12 Drunk In Love Table [Artists]: <ID_AR> <Name> 345 Jay-Z 456 Beyonce 567 Beyonce featuring Jay-Z Table [REAR]: <ID_RE> <ID_AR> <Main> 12 345 0 12 456 0 12 567 1 Idea is being that when presented in the charts, this hit single would be credited to “Beyonce featuring Jay-Z” but when searching for, say, all Jay-Z hit singles, we would get “Drunk In Love” as well. I created a layout based on [HitRecords] table. Then inserted a PORTAL based on [Artists] table. Linking table is [REAR] of course. Two portal fields are: Artists::Name, REAR::Main All is fine up to that point. I can sort artists within portal and make that record with <Main>=1 is listed at the top. Perfect. PROBLEM ——————————————————————— I created new layout (all HitRecords with corresponding Artists) in Table View: HitRecords::ID_RE HitRecords::Title Artists::Name REAR::Main No matter what I do I always get FIRST Artists::Name as entered in [Artists] table. Can’t make my DB to get Artist:Name for whom REAR::Main=1. In the example above it will always be “Jay-Z” instead of “Beyonce featuring Jay-Z”. So I get HitRecords::ID_RE HitRecords::Title Artists::Name REAR::Main 12 Drunk In Love Jay-Z 0 instead of HitRecords::ID_RE HitRecords::Title Artists::Name REAR::Main 12 Drunk In Love Beyonce featuring Jay-Z 1 In MSAccess I always created separate query first filtering [Artists] table only for records where REAR:Main=1. And that “ArtistsFiltered” query was then linked to HitRecords table. No idea how to do something similar in FMPro. Thanks fore help! Andrzej, Poland