I asked this years ago, and someone named Henry gave me a nice answer,
but I can't get his solution to work. Does anyone have any
suggestions for me?


Here is the scenario: I have a tree, a car, and a building asset. I
have 10 shots. I would like to create a relational link between them.
When I am on my shot layout, I would like to see a list of all the
assets that are to be included in the shot (the tree, car, building,
or any cmbination therein). Now when I switch to my asset layout I
want to see a list of all shots that use each individual asset.

I have been able to make layouts that will only show one shot or one
asset at a time. Its driving me bonkers. Can anyone help? I from my
last google searching extravaganza, I think I might need to use
portals, but I'm not sure. Any leads appreciated, thanks!

"dc" <damienvfx (AT) gmail (DOT) com> wrote

Yes. A Portal will show data from each related record, so that sounds like
exactly what you need.

Youve already got the Relationship set-up so that each shot can be linked to
various assets, and vice-versa, so all you need to do is put a Portal on
each Layout set to use the appropriate Relationship, and then put Fields
into the first row of the Portal (making sure the Fields are via that
Relationship using the pop-up menu at the top of the "pick a field" window)
.... just make sure the fields are within that first row's boundaries.

Back in Browse mode, the Portal should show all the appropriate related
records. You may need to set the Portal to show a vertical scroll bar so you
can see them all.

If the Relationship is set to "allow creation of related records", then you
can also use the bottom row of the Portal to enter new Related data.

Helpful Harry )

On Apr 8, 7:05*am, "Your Name" <your.n... (AT) isp (DOT) com> wrote:
Thanks Harry. You were the one who helped me before.

I have built the following .fp7 file using FMP 11. It is the closest
I have come to getting this to work. I can see the list of shots to
select from the drop down box when I am in assets, and vice versa.
However, because it does not store a value, I can not seem to store
any viable data in here. The file I have made is:

http://files.me.com/damiencarr/lg1yts

Harry, could you take a look at my relationships and tell me what you
think I might be doing wrong? I've been trying to wrap my head around
many-to-many relationships. I just don't think I have it nailed yet.

Thanks for your help!

dc

Thanks Harry. You were the one who helped me before.

I have built the following .fp7 file using FMP 11. It is the closest
I have come to getting this to work. I can see the list of shots to
select from the drop down box when I am in assets, and vice versa.
However, because it does not store a value, I can not seem to store
any viable data in here. The file I have made is:

http://files.me.com/damiencarr/lg1yts

Harry, could you take a look at my relationships and tell me what you
think I might be doing wrong? I've been trying to wrap my head around
many-to-many relationships. I just don't think I have it nailed yet.

Thanks for your help!

dc
----------------------------

Damien,
I also had a look. You are getting there, but quite yet.

Firstly, at this time there is no need to repeat the shot and asset fields
within the join table. A portal will look through all levels of valid
releationships at once. On the layout of Shots you should change the portal
and field from link to asset.

Secondly your naming-conventions need te be changed. You have to name your
join fienlds in such a way that it is obvious if they are primary keys
(automatically created) or secondary (entred from an existing primary keys).
A common way to do this is naming them pk_MyId and sk_MyId. But anything
that works for you is ok.

If your solution grows a tablename like Link will be confusing. What does it
link? I use a system where I call the table ShotsAssets.

Then you need a script to create the join. I have done all this, and some
more, and made an example for you.

Download it at:
https://www.wetransfer.com/dl.php?co...6b3b5b34e 780
Send this also to your gmail adress.

PLEASE ALL OTHER READERS DO NOT DOWNLOAD THIS. It has a download once-only
status.

Be aware: there is much more to discover on many2many. There is no error
checking in my script. Is has a nasty layout.

--
Keep well / Hou je goed

Ursus

In article
<aa0e117f-7e98-4c6d-99dc-31341fb45f17 (AT) q23g2000yqd (DOT) googlegroups.com>, dc
<damienvfx (AT) gmail (DOT) com> wrote:
Sorry, I don't have FileMaker 11. The newest I can use is FileMaker 9
(which might open the files), but that's only at one client's offices.

It looks like Ursus has already had a look though.

Helpful Harry )

I just isolate one question out of the contest : the one on 'how to set up
a many-to-many relationships'.
My way of doing this applies very well on FMP6, but at least, it is easy to
understand.
The key answer to many-to-many relationship between two files is to create a
third file, with one record per each couple that could exist between file A
and file B.
Say, for instance you have a 'persons' file and an 'organization' file, and
there could be many persons belonging to several organizations and the
reverse also. Then you create a 'detail' file C where every case (couple of
person-organization) is recorded in a separate record. Then the links will
be between A and C for one and B and C for two. Every editing or printing
should be made on that C file. Such a C file can be hidden and not visible
to users.
Of course for FM7 and up you should replace my term of file by table, and
'gurus' there would most probably give you more elegant answers than mine.
BTW, Harry is very knowledgeable on FMP6, so he could also amend what I said
above.
Just my 2 cents.
Remi-Noel


"Your Name" <your.name (AT) isp (DOT) com> a écrit dans le message de
news:your.name-1304101319530001 (AT) 203-109-171-51 (DOT) dial.dyn.ihug.co.nz...

On Apr 10, 6:28*pm, "Ursus" <ursus.k... (AT) ziggo (DOT) nl> wrote:
Ursus,

Your solution has helped me tremendously. I am sorry that I haven't
taken the time to thank you earlier, I have been implementing your
table and script into my database for 2 days straight. It works
extremely well.

Thank you so much for setting that up! You've saved me hours of
toiling, i really appreciate it!