Relationships the other way

[Eike Formella]

Hi,

I’d wish that there was a way to do the following things:

1.) I have built a database of collected song-scores I sing with my choirs. There are fields for the Composer, the Lyric-Writer and the Arranger – sometimes same sometimes different persons. Now I’d like to have a way to set a relationship to a second database, where I could save further informations about this persons (Birthday, Place of birth and so on).

Okay – I could set a normal relationship to a second database. But then I always get a table for each person – that doesn’t look very nice ;)

2.) It would be another way to add a additional table column in the relationship-table wich only exists in the first database. On this way I could build a relationship to the person-database and add the information of the song (Composer, Arranger …) in the first. I do remember that it was possible in Bento to expand such tables.
That would be also handy for this example – I have a database of workshops. There I want to have a relationship-table of the members of the workshop. Up here no problem. (Contact database as source) But when I want to add the information if the single members have paid their contribution, I don’t see a way to do this.

Any ideas to do this on a other way?

Greetings from Germany, Eike

PS: Sorry for mistakes! I give my best to write english without the help of google ;)

[Brendan]

Hi Elke,

One thing you could do is add your relationship the other way. From Contact to Songs using the One-to-Many link. That way when you look at a Song record, you can see just the one Contact that relates to it. If you use a One-to-Many relationship, you’ll get only a single record and not the table of records when you view the child record.

But when you look at a contact, you’ll see a list of songs they’re related to.

[Mike Schwartz]

Eike,

Just to clarify a little on Brendan’s suggestion: Since there are many roles associated with a song, you will need to create multiple linked fields in your Choir Persons form, each one linking to your Choir Songs form.

Here’s an example for Composer: Create a new field in your Choir Persons form with the name “Songs as Composer”. It will list all the songs where that person acted as the composer. Assign these field properties: Type = Link to Form, Link Type = One to Many, Link to Form = Choir Songs, Show Inverse Relationship = (checked). When you use the pop-up menu and select Choir Songs as your Link to Form, Tap Forms will automatically change the field name to “Choir Songs”. You will need to re-edit the field name back to “Songs as Composer”. Next, switch to your Choir Songs form and look at your field definitions. You’ll see an inverse link field with the name Choir Persons. Change the field name to just “Composer” (not Songs as Composer).

Repeat the above steps in your Choir Persons form to create fields for “Songs as Arranger”, “Songs as Lyricist”, etc.

Using the forms: When you use your database, you will be able to add information in either direction. When using your Choir Songs form, you will be able to click the check-mark controls and select a single name for Composer, Arranger, and Lyricist. And from your Choir Persons form, you can link a person to one or many songs in the roles of Composer, Arranger, or Lyricist.

WORKSHOPS DATABASE

This one will be a little different. I’m assuming that each workshop has multiple members, and that persons can join more than one workshop. That is a many-to-many relationship. And each workshop has its own fee (contribution). So if Stefan is a member of two different workshops, then you need to track that he paid for each one. I have an approach to suggest, but it gets messy unless you only have a fairly small number of different workshops.

Let’s say you have four different workshops, and each one is a record in your Workshops form. You also have a persons form, that we’ll call Workshop Members (this could be the same as the Choir Persons form from above). In your Workshop Members form, create a Link to Form field named “Workshops”, set the Link Type as Many to Many, link it to your Workshops form, and check the box to Show Inverse Relationship. Now let’s say that your four workshops have the names “Singing”, “Melody”, “Harmony”, and “Composing”. Then add to your Workshop Members form four new checkbox fields: Singing Paid, Melody Paid, Harmony Paid, and Composing Paid.

Using the forms: For each person in the Workshop Members form, you can now select one or more workshops in the Workshops field. So if Ute is attending the Melody and Harmony workshops, you will select both of them in the Checkmark Pop-up that appears under the Workshops field. Then you can click the individual Paid checkboxes as needed. Switching to the Workshop form, if you go to the Melody record or the Harmony record, you should see Ute’s name listed in the Workshop Members field along with any other members of that workshop. Tip #1: By default, the table will show every field from the Workshop Members form. You can click the tool icon in the upper right-hand corner and select only the Name field and the four Paid fields. Tip #2: Instead of a simple checkbox field for Paid, you can use a number field formatted for Currency Style and enter the amount paid in Euros. Then your Workshops form can show the total paid for each workshop.

Hope that helps,
Mike

[Eike Formella]

Wow – you are great!

Thank you so much for taking time to answer. My database of songs is ready now – yeah! That’s what I wanted to do.

I haven’t had enough time yet to try out your solutions for the workshop-database. I will do it on Monday – but I’m sure it will work :)

So again – thanks!

Best wishes, Eike (happy now :) )

And thanks to Brendan – TapForms is great!

[Brendan]

You’re very welcome Eike. And thanks very much Mike for such a comprehensive answer!

[Author]

Posts