Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Requesting help in creating a relationship between 2 fields in 2 databases


Joshua B
 Share

Recommended Posts

Hi. Thanks in advance for helping.

 

Filemaker Pro 7

 

I have two music databases.

 

Database #1. Rhythms (see below)

Rhythms is a song database with a field called "album title." In this field, those entries (underlined in orange) with multiple album titles are now separated by commas. (Comma separation was probably not the best way to do this, right?)

 

fm_rhythms.jpg

 

--

 

Database #2. CDs (see below)

This is the database that stores information on all of my reggae CDs. Each record has a field called "CD Title" (underlined in orange). There is only one album title in the CD Title field. Each record also includes a unique ID number in a field called "CD ID" (see orange arrow pointing toward it).

 

fm_cds.jpg

 

The question:

I'd like to create a relationship between "album title" in the Rhythms database and "CD Title" in the CDs database so that "album title" would be dynamically updated when I make a change to the "CD Title" field. I'd also like to be able to click on an album in the "album title" field and have that album's record open up in my CDs database. What type of relationship do I want and how do I set it up? Do I use the "CD ID" field in the CDs database?

 

Do I first need to overhaul the "album title" field in the Rhythms database? If so, any suggestions?

 

Thanks again.

Link to comment
Share on other sites

You can cheat rather than having to rip out your existing way of doing things:

 

Create calc field, TitlesSeparated, text,

 

Substitute(OriginalTitleFieldWithCommas, ", ", "¶")

 

Then use THAT field to form a relationship with CD Title in the other table.

Link to comment
Share on other sites

You can cheat rather than having to rip out your existing way of doing things:

 

Create calc field, TitlesSeparated, text,

 

Substitute(OriginalTitleFieldWithCommas, ", ", "¶")

 

Then use THAT field to form a relationship with CD Title in the other table.

 

Thanks for your help AHunter3. Unfortunately, I have never even created a calculation field before. I am trying to use Help to figure it out, but I'm sorry to report that I'm stuck at your first step, "TitlesSeparated, text,"

 

If you wouldn't mind posting the step-by-step instructions for your cheat, that would be tremendous.

 

Otherwise, I'm willing to put in the work to make my databases more proper. I just need some help with how to start.

Link to comment
Share on other sites

• Open Field Definitions. It's one of three available tabs when you open Define Database.

 

• Define a new field by typing its name at the bottom. I suggest "TitlesSeparated" as a field name but you can use whatever you wish. (If you use something else, substitute your field name for "TitlesSeparated" wherever it appears in the remainder of these instrux).

 

• The type of field you want to define is a Calculation. To the right of the blank for field name is a dropdown; select "Calculation" from the list of options.

 

• When you hit Return key after entering name and "Calculation" for the field type, dialog will come up to enter your formula. Enter this formula:

 

Substitute(OriginalTitleFieldWithCommas, ", ", "¶")

 

 

In case it's not compellingly obvious, use your actual field name, not "OriginalTitleFieldWtihCommas". I don't know your actual field name.

 

• Click "OK" to exit the calc formula definition. You should now be back at Field Definitions.

 

• On the overhead, switch from Field Definitions to Define Relationships.

 

• Define a relationship between TitlesSeparated in this table and CD Title in the other table. This is the relationship you want, the one that you asked for in the original post, one that will let any of the titles in your comma-separated text field match with the CD Title field in the other table.

Link to comment
Share on other sites

Thanks again AHunter3.

 

So I've learned a bit about relationships, calculations, and repetitions.

 

Because of some instances of inconsistent data, I decided to create a relationship with the CD ID field of the CDs database and the newly created CDReference field (circled below) in my Rhythms (individual songs) database.

 

In the CDReference field, I set the maximum number of repetitions at 10 because the same song might be on many different CDs. Then I inputted the CD ID numbers (unique CD identifiers) from the CDs database into the new CDReference field.

 

Then I created a couple calculation fields to show some information from the CDs database...Artist, Song Title, and Label.

 

The relationship seems to be working, because when I input a CD ID number into the Rhythms' CDReference field, the calculation fields are picking it up and outputting the calculations I requested (Artist, Album Title, and Label). But it's not repeating. Only the information from the last record inputted is showing up. See big question marks.

 

fm_rhythms2.jpg

 

Will I be able to display CD information for all items entered into the CDReference field? If so, how?

 

Thank you again! I've worked with these databases since 1996 but I'm just now trying to get them to relate. I'd like to one day share them on the web so I want to do things right.

Link to comment
Share on other sites

 Share



×
×
  • Create New...

Important Information

Terms of Use