many to many to many

[macavity]

is that too many? Here's my problem (simplified)

Three databases:

Exhibitions

Artists

Works

Works is the join between exhibitions and artists.

Works are entered in a portal on the main Exhibitions layout

Works include name (serial no.) of artist (from a value list based on artists database)

Thus: works records include id information on exhibition and artist.

What I want to do is to be able to display, in Artists database, some kind of list of the exhibitions in which the artist ist represented.

Problem:

one exhibition may include one or more works by one or more artists.

An artist may have one or more works in one or more exhibitions.

It can be done via a portal in artists - but: if an artist has three works in one exhibition, that exhibition is listed three times.

The thing is: I only want it listed once! (perhaps with a summary count of how many works are represented in that exhibition - but that part is easier).

Can that be done? So far I haven't found a way, not even with a sub-summary.

[cjaeger]

create a value"artists" list in exhibitions,related only, works::artist

then a calc= valuelistitems(StatusCurrentDatabase;"artists")

- gives you a list of artists without dups.

use this as the key artists<->exhibitions

[Ugo DI LUCA]

Hi,

Create a Concanated field in Work.db

c_concanate = Artist_Id & " " & Exhibition_Id

Create a relationship in the Work.db :

SelfjoinOnSerialNumAndExhibition using c_concanate at left side and right side of your self relationship.

Then add a new calculation

c_returnOnce = Case(line_Id = Max(Seljoin::line_Id), "Artist_Id", " ")

This calc would only return the record once if it is the last occurence.

Then use a portal with relationship "ArtistsinExhibition"

Exhibitions:Exhibition_Id::Work:Exhibition_Id

Use the c_returnOnce as the sort key and place this field in the portal.

You should just have the record once, the empty ones would appear at the end.

Another method (lots of settings, but works fine) would be to :

1. create in Work file :

a num field "n_constantTrigger"

a ''c_indexconstant'' (populate with 1, indexed)

a global "g_constant" (populate with 1)

2. create in Exhibition file :

- a global field "g_constant"(num populated with a 1)

- a calc "c_constant" (num populated with 1, indexed)

- a global field g_Exhibition_Id in the exhibition file (text or num depending on the format of your Exhibition_Id )

- a relationship showonlyOne with "g_constant" at left (Exhibition) and "n_constantTrigger" at right side (Work).

- a relationship "OneToOne" from the Work file to the Exhibition using the g_constant at left side (Work file) and c_constant for the right side (Exhibition)

3. Back in the Work file, create

- another calc c_gConcanate = (OneToOne::g_Exhibition_Id) & Artist_Id where OneToOne is the relationship.

- a self relationship called "SelfjoinOnGlobal" c_gConcanate ::c_Concanate (from the first part of this post)

- a num calculation field at right side (Work file)

c_showOneinCalc = Case(line_Id = Max(SelfjoinOnGlobal::line_Id), 1, 0)

4. Create a script "Step 1" in the Exhibition File :

Allow user abort (no)

Set Error Capture (yes)

Freeze window

Set Field (g_Exhibition_Id, Exhibition_Id)

5. Create a script "Step 2" in the Work File

Find Mode

Set Field (Exhibition, OneToOne::g_Exhibition)

New Request

Set Field (c_showOneinCalc, 1)

Perform Find

6. Create a script "Step 3" in the Work File

Go to First record

Go to Layout where n_constantTrigger is

Set Field (n_consant Trigger, 1)

Replace (no dialog)

7. Create a script "Step 4" in the Exhibition File

Freeze window

Go to Layout with the portal for the list

8. Create a portal with your already created relationship showonlyOne with "g_constant" at left (Exhibition) and "n_constantTrigger" at right side (Work).

9. Finally, set your script in Exhibition :

Step1

External script Step 2

External script Step 3

Step 4

10. I don't know ....I don't like this number Ouch. Big settings yeah !

But according to the size of your list, may need the efforts.

[cjaeger]

i am to tired for that . i would just compile a list of artists for each exhibition and use that to display the artist->exi portal. It's so easy to get used to the Tr-Text -SumText() calc.

[Ugo DI LUCA]

Hi Christian,

Tired ?!

Please before you leave....

It's so easy to get used to the Tr-Text -SumText() calc.

[Ugo DI LUCA]

Hi again,

Part of the 10 steps should already be created for all files in a db structure

constant relationships and constant keys, global to key realtionship and global fields for the keys...

Even the concanate and selfjoins as they would be used for plenty other matters...

It results only 2 or 3 fields + a script at the end. Certainly more work to write and read that to do the job.

[CobaltSky]

Tr-Text -SumText

Christian is referring to one of the functions which is available on workstations which have the 'Text' plug-in from Troi Automatisering installed.

[Ugo DI LUCA]

Hi Ray,

Thanks for clarifying....

So this function could allow to "filter" all records of a line item and display them as a value list item (no duplicate) in a portal ...

[macavity]

Thanks very much for the fast answers. Now to try them out.

As Ugo said, some of the steps in his long solution are already implemented. So maybe it won't be as daunting as it looks. And if it's guaranteed to work - I'm willing to suffer. After all the trouble you went to writing it out, that would only be fair.

macavity

[macavity]

Ugo, Just wanted to thank you again. I followed your first, easier suggestion and it works perfectly. I was worried the empty lines might occur between two lines with text, but oddly (I don't understand why) they are eliminated. They only occur after the bottom entry. And since the portal is purely for information - no line items can be entered there - they aren't visible.

Macavity

[Ugo DI LUCA]

Hi,

glad it worked. The easier is always the better

It works because ...

[color:"red"] Use the c_returnOnce as the sort key and place this field in the portal.

where c_returnOnce is text or nothing. Go to the relationship defs and change the sortkey or just the sortkey prefs (down-up) and you'll see the blanks.

[macavity]

Hi Ugo,

sure, I understand how it works (could have kicked myself for not thinking of it myself, but the "max" function was one I'd never needed before, so it didn't occur to me).

The thing about the blank lines that still has me puzzled, though:

Say there are two exhibitions. In the first the artist has 5 works, in the second he has 7 works.

What I get (whether sorted up or down) is six empty lines after (or before) the second exhibition - but none after the first.

It seems the portal uses the blank lines from the first exhibition to display information for the second.

Which is just what I would want it to do - but since when does FileMaker do what you want it to :

[Ugo DI LUCA]

Hi,

Not sure I'm the best qualified to answer this kind of questions, but here is my explanation and a new tip.

The Max function returns the max number in an existing relationship. You could also have used the "Last" function.

Used with the Case statement and this selfjoin, this formula therefore tags any last occurence in an exhibition for an artist_Id with "artist_Id", all other occurences would be tagged with a " ".

As you sorted by this key, it is logical that the empty records are disappearing at the end of the portal...because the process of relationship is different from which you would get with a classic Sub-summary report based on the Exhibition_ID.

On the other hand, from what you're saying, you doesn't seem to use this feature with the classic relationship :

"Exhibitions:Exhibition_Id::Work:Exhibition_Id".

Therefore, you should have duplicate in this list...

You may like to do the following instead, which would make your "FM life" easier.

1. create a calc field in the Work File

c_showAllAndExhib_ID = "All" & "PP" & "Exhibition_ID" where "PP" stands for a carriage return.

2. create a global field in the Exhibition file called g_Exhibition_ID

3. create a value list in the Exhibition file called "Exhibition_ID List" and define this value list to use values from the field "Exhibition_ID" in the Exhibition file

4. Drag the global field on the layout

5. Format the global field to use the "Exhibition_ID List"

6. Place a button just next to this new field and attach a single script (Set Fied (g_Exhibition_ID, "All")

7. Change your portal relationship (and the related fields in the portal) to a new "filtered relationship"

Exhibitions:g_Exhibition_Id::Work:c_showAllAndExhib_ID.

8. Create a new selfjoin in the Work file with relationship Artist_ID::Artist_ID, that you will call "SelfjoinOnArtist"

8. Change the c_returnOnce calc to :

c_NewreturnOnce= Case(OneToOne::g_Exhibition_ID = "All",

Case(line_id = Max(SelfjoinOnArtist::line_Id), "Artist_Id", " "), Case(line_Id = Max(OldSelfjoin::line_Id), "Artist_Id", " "))

where one to one is your contant relationship (from my previous long post - solutionN

[macavity]

Ugo - I'm sure your latest suggestion is great and I've saved it to study later. However: the original one works fine, no duplications at all.

The empty portal rows all collect at the bottom (or top if you sort is ascending order, which of course I don't). I suppose that might become a problem at some point, when they became very numerous.

On rereading your post I see there's a misunderstanding. I noticed it when I was following your original suggestion and I had to make some changes to it for my purposes:

I don't want a list of artists in an exhibition, but rather a list of exhibitions displayed on the record of each individual artist. In other words Artist No. 1 had 3 works in exhibition A, 5 works in exhibition B etc. etc.

Using (an adaptation of) your original suggestion I get just the result I wanted (I added a summary count field for the number of works exhibited). The only problem I see for the future is that with time there will be an awful lot of empty lines in the portal. I suppose that could be a problem. ?

macavity

[Ugo DI LUCA]

Hohahi,

Right, I made it reverse.

Exhibition-------< Artist

Artist-------------<Exhibitions

So there is only one exhibition attached to a work while there are several works attached to an exhibition....

So when sorted, there was only one occurence of the exhibition, which is logic.

You won't have any problems with this as long as an artist would not exhibit his work in several exhbitions, therefore having a many to many relationship to be solved with the latest suggestion.

As you see, it's not a mater of "number" but rather of "relationship". Just think if this may never happen !

Anyway, glad it worked...