Jump to content

Transferring field's content from one table to another


AGilek
 Share

This topic is 2631 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Regards, Andrzej

Edited by AGilek
Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

This topic is 2631 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
 Share

  • Who Viewed the Topic

    1 member has viewed this topic:
    comment 
×
×
  • Create New...

Important Information

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