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

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

 

That's exactly how a one-to-many relationship works: the Hits table contains songs, and the Runs table holds (IIUC) chart positions for these songs – at least one, but possibly many per song. (But I don't understand what “id2” is supposed to do …)

 

In English: One song can appear in many charts (or many times in the same chart) in different weeks – but each position in a given chart denotes exactly one song.

 

That is why hitID is a primary key in Hits (must be unique), but a foreign key in Runs (is supposed to be non-unique).

 

Though it's not required, it would be helpful to name them accordingly, i.e. as pk_hitID, or hitID_PK, and fk_hitID, or hitID_FK, respectively – or whatever naming scheme works for you (as long as you are consistent).

 

I'd like to move content of the <id2> field from one table (Hits) to the corresponding <id2> field in another table (Runs).

 

Maybe you'd like to, but you shouldn't. All that's required in Runs is the hitID that refers back to the Song.

 

Everything that you need to say about a given song should be stored in the Hits table. Everything you need to say about a specific chart position of a song should be stored in Runs.

 

Within a chart you can simply display the related song title field from the Hits table (since each Run only has one parent Hit); for a song, you can display a portal with its chart position, displaying the data from the related Runs record(s) (since there can be many Runs child records for a Hit).

 

All in all; no need to copy data in this scenario.

 

And what's "<id2> extra field, a number"? You shouldn't use needlessly short, and potentially misleading names for fields (or any objects, for that matter).

 

How to do that? I've tried to create some scripts in FMPro to do that task but failed.

Any suggestions, please?

 

To come back to your original question: if you define the relationship between Hits and Runs (not a very fortunate combination …) as "Allow creation of related records", you can use a portal to create Runs for Hits; the Hit's primary key will automatically be set as the foreign key in a new portal row (i.e. related record).

 

As to your scripts, it would be helpful to see what you've already tried.

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

Edited May 18, 201411 yr by AGilek

It seems like you should have a third table, where <id2> would be unique. It's difficult to be specific without understanding exactly what <id2> stands for but if you had, for example:

 

Songs -< Versions -< Ratings

 

then a record in Songs could aggregate the ratings of all of its versions through the two relationships, with no need to replicate the SongID in the Ratings record*.

 

 

--

(*) except maybe as an unstored calculation field for report grouping purposes.