Selecting records from tables A & C based on value in table B (many-to-many relation)

[AGilek]

I’ve got a FMPro12 database and there are 3 tables inside: [HitRecords], [Artists], [RE_AR].

Yep, it covers UK chart singles & albums.

 

One disclaimer. I’ve switched from MSAccess so my understanding of some concepts may not be correct.

 

The basic concept is this.

————————————————————

Table [HitRecords]:

<ID_RE>     index field, autonumber

<Title>          text field, title of an album/single

 

Table [Artists]:

<ID_AR>       index field, autonumber

<Name>        text field, artist name

 

Table [REAR] creates many-to-many relation between [HitRecords] & [Artists]:

 

<ID_RE>       number field, related record from [HitRecords]

<ID_AR>       number field, related record from [Artists]

<Main>          0/1 field*

* for every record in [HitRecords] there is only ONE record in [Artists] for which [REAR]<Main>=1

 

EXAMPLE

———————————————————————-

Table [HitRecords]:     <ID_RE>              <Title>

                                    12                    Drunk In Love

 

Table [Artists]:            <ID_AR>            <Name>

                                    345                    Jay-Z

                                    456                    Beyonce

                                    567                    Beyonce featuring Jay-Z

 

Table [REAR]:      <ID_RE>      <ID_AR>     <Main>

                                    12             345              0

                                    12             456              0

                                    12             567              1

 

Idea is being that when presented in the charts, this hit single would be credited to “Beyonce featuring Jay-Z” but when searching for, say, all Jay-Z hit singles, we would get “Drunk In Love” as well.

 

I created a layout based on [HitRecords] table. Then inserted a PORTAL based on [Artists] table. Linking table is [REAR] of course. 

Two portal fields are:

Artists::Name,       REAR::Main

 

All is fine up to that point.

I can sort artists within portal and make that record with <Main>=1 is listed at the top. Perfect.

 

PROBLEM 

———————————————————————

I created new layout (all HitRecords with corresponding Artists) in Table View:

HitRecords::ID_RE         HitRecords::Title              Artists::Name               REAR::Main

 

No matter what I do I always get FIRST Artists::Name as entered in [Artists] table.

Can’t make my DB to get Artist:Name for whom REAR::Main=1.

In the example above it will always be “Jay-Z” instead of “Beyonce featuring Jay-Z”.

 

So I get

HitRecords::ID_RE     HitRecords::Title          Artists::Name              REAR::Main

12                                     Drunk In Love              Jay-Z                          0

 

instead of

HitRecords::ID_RE       HitRecords::Title          Artists::Name               REAR::Main

12                                     Drunk In Love     Beyonce featuring Jay-Z     1

 

In MSAccess I always created separate query first filtering [Artists] table only for records where REAR:Main=1.

And that “ArtistsFiltered” query was then linked to HitRecords table. 

No idea how to do something similar in FMPro.

 

Thanks fore help!

Andrzej, Poland

 

[comment]

First, if each hit record can have only one "main" artist, then this is an attribute of a hit record and should be recorded in a MainArtistID field of the HitRecords table.

 

Now, to solve your display issue with your current structure, you will need to use either a filtered portal or a dedicated relationship. If you just place a related field on the layout (outside of any portal), it will always show data from the first related record.

[AGilek]

Oh, thank you very much for suggestion!

I will start from moving MainArtistID field into the HitRecords table. Then I will rebuild layouts. I'll get back to report!

 

A.