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

 

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.

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.