need help in setting up 3-file database

[soxsoxsox]

Hello -

I am trying to set up a database involving 3 files keeping track of CDs released during the year:

1. Releases (e.g. artist/title/format/label)

2. Artists

3. Labels

What I would like to do is be able to enter the information concerning each CD in the Releases file, and have that create (as necessary) new Artists or new Labels as they arise. However, I haven't found a way to be able to have the existing Artists and Labels show up on the Releases layout without also creating duplicate records for Artists and Labels who appear more than once. I tried formatting the fields for the portal records to have a Value Field equal to the indexed contents in those fields, with an option for an "Other..." entry in case of a new Artist or Label. This works as far as adding new entries, but when I pick an existing Artist or Label when entering a new Release, this creates a second entry for them in their respetive files.

Can anyone shed some light on a direction I should be taking here?

[underhat]

Hi sox,

First, I'm not clear why you're using portals on the Releases layout. If I understand your data structure correctly, you have this:

ONE artist can have MANY releases, but each release only has one artist.

ONE label can have MANY releases, but each release only has one label.

You really shouldn't have portals in Releases, then. A portal is intended to show the MANY records related to the ONE record you're looking at -- so a portal would be appropriate in the artist file, or the label file.

I presume you have serial number fields in each file so that you have ID numbers for artists, labels, and releases. What you want, then, is to put the ID number for the artists you want into a field on the release. You can do this through a value list which shows two fields from the artists file -- the artist ID # for the first field, and the artist name for the second field. You'd do similarly for the label.

For creating artists which don't exist already, I'd recommend simply having a New Artist button by the artist ID field in releases. Then, if you don't see the artist in the value list, you can hit the button to take you to a screen to create a new one.

I can give you some more detail and send you example files if you like.

[stanley]

If you think about it, the assumption:

"ONE artist can have MANY releases, but each release only has one artist."

is incorrect, as there are often guest artists, compilation albums, collaborations, etc.

Regardless, underhat's suggestion for creating artists (and lables) is pretty much what I'd recommend. Using a value list (with "Other..." checked) is a half-step and not good enough. What you want to do is make sure that, when entering a new artist or label, you're actually in the Artist or Label database. Then, when that data is entered, you can take yourself back to the Releases database to complete the data entry there.

I think you'll encounter another problem altogether when you try to input releases with multiple artists.

-Stanley