Importing Individual records into a repeating field

[Blaze]

Here's my situation.

I've got two databases dealing with audios.

#1 has a list of every recording in the catalog with each recording in an individual record.

#2 is a list of songs and authors.

What I'm needing to do is import the list of available recordings of a specific song (found records in the #1 database) into a single record's repeating fields in database #2.

The resulting record will be a unique song with the various recordings imported into repeating fields.

Also another fly in the ointment is that each song title in #1 isn't going to be identical since there may be other info appended to the song title, such as track or album info. So doing any kind of match isn't feasibile.

Creating a "found set" and importing that will be the needed approach.

Anybody have any ideas how to handle this "many to one" import?

[Søren Dyhr]

The approach you're attempting is not fully embraced, the rule is - ditch repeaters until don't need help with them. Instead are you to take the plunge and learn about relational principles ...and don't put your take on excel as a skill level here!

--sd

[Blaze]

Huh? Not much help there, more of a scolding.

If not repeating fields then how would you handle the task?

[Fitch]

What Mr. D is getting at is that you'll generally be better off using a relational structure rather than repeating fields. E.g. if you want to do a report on recordings.

What I suggest is that you create a unique ID field in your Songs table if you don't already have one. I'll call it Song ID. You can then find all records and do a Replace with serial numbers.

In your Recordings table, create a Song ID field. Now create a relationship between the two tables (Songs and Recordings) based on the Song ID field.

Find the Recordings of a given song. Rather than import them into a repeating field in Songs, replace the Song ID with the Song ID of that song.

On a Songs layout, you can now display the related recordings in a portal.

Hope that helped.

[Blaze]

Cool, That helps. My brain was locked into a certain way of thinking and I needed some jolt of different thoughts to get me out of the vortex.

Thanks

[Blaze]

The only problem with setting it up that way is the #1 (recordings) database has over 200,000 records.

Going back in and mapping the Song ID would be quite a task.

That is part of the reason I wanted to find some way to import based on found sets.

[bruceR]

The only problem with setting it up that way is the #1 (recordings) database has over 200,000 records.

Going back in and mapping the Song ID would be quite a task.

That is part of the reason I wanted to find some way to import based on found sets.

No; splitting repeats is in fact quite a simple process.

[Blaze]

Give me an example of how you would split the repeats.

Thanks

[comment]

You cannot split repeating fields without creating them first; if I understand correctly, you haven't done this yet - and there's no reason why you should.

The problem of matching up the songs with their recordings is the same whichever way you decide to proceed. If you think finding the song's title in the recordings table is an efficient method, you could simply loop among the songs, performing a find and associating the found recordings with the current song (this would be scripted, of course).