many-to-many ... how to edit a portal field?

[Slater]

Hi there,

I've got a many-to-many relationship, via a join table, for two databases: 'People' and 'Campaigns'. Any individual can have many (marketing) campaigns directed at them (between 1 and 10), and any campaign can be sent to many people (between 5 and 500).

I've got the people showing up in the campaign window by way of the join file using a calculation, within a portal. (The join file has a calculated field called calcName=Name and it's this calculated field that's shown in the portal in the 'campaign' window.) All good - but there are some fields that are in the portal that i would like to edit right there, and not go into the people file to do so.

An example of this would be a person's email_address. I'd much rather be able to edit a person's email address in the portal on the campagin window, and not switch to another file. Because what's showing up in the portal is a calculation (made in the join file) i can't edit it: "This action cannot be performed because this field is not modifiable."

Is there a way to do this?

Thanks in advance,

Slater

ps the relationships are:

Campaign to link file - via campaign's ID number

link file to Person - via person's ID number

[comment]

You don't need the calculation fields. You can place the name and e-mail fields from People directly into the portal.

[Slater]

Oh!! :

That's great, but i can't see past the relationship with the join table to the 'People' file behind it ... how would i do that?

Because it's a portal with lots and lots of people getting the same campaign, i thought you had to link via a join table - when i link directly, i get the same person in every row of the portal

thanks for your help

[comment]

Have a look at the file here:

http://www.fmforums.com/forum/showpost.php?post/246136/

All the fields in italics are coming from the "opposite parent" of the join table. The entry is disabled on layout level only, so don't let that confuse you.

[Slater]

so don't let that confuse you

since you posted i've been going thru your files and also the user's guide, and i'm confused!! The only glimmer of hope i had was in thinking that i could have everything on the join file, but that's a red herring i'm guessing.

Also, i saw that you were using a lookup rather than a calculation, so i had a go with that, but i couldn't get it to work. In the example file, you said that

"The relationship can even tunnel through the join entity"

which is what i guess i'm after because i'm trying to have an editable field that's actually based not on the join file, but on the record on the other side of that ... thanks for any clarification you can give, very much appreciated if you can help me sort this,

Slater

[comment]

Well, now you have managed to confuse ME. Because there are no lookups in the file I have linked to, and I don't recall ever saying that "The relationship can even tunnel through the join entity".

[Slater]

Well, now you have managed to confuse ME.

Gosh, i'm making a right pickle of this!

I followed the link you gave me in your second post, and that had a file to download which came out as two files, one of which had that quote ("Have a look at the file here:

http://www.fmforums.com/forum/showpost.php?post/24...")

Maybe we should rewind ... you mentioned that i might be able to directly place the name and e-mail fields from People directly into the portal - but my problem is that the portal is in the third file (Campaign) not the join file. To recall my objective, i'm trying to have a field from the 1st file, go thru the second (join) file, and into the third file but still be editable there, so that i don't have to go back to the first file to edit it's contents.

Many apologies again for confusing the subject - and thank you for being so kind to help me like this,

cheers,

Slater :

[comment]

This is getting weirder and weirder. I just tested the link and it leads (correctly) to a post of mine of March 29, 2007, which ends with:

---

I am attaching a basic demo of a join table. See if you can adapt it to your needs.

Attachment: JoinDemo.fp7.zip (9.78 KB) 228 View(s)

---

[Fenton]

Slater, add another table occurrence (TO) of the People file (if that's where the email field actually is) to the other side of the Campaigns table occurrence (which is being used for the portal); relationship based on PeopleID. Place the email field from that TO in your portal. In other words, as comment is saying, go THRU campaigns back to people again.

[Slater]

Hi Comment,

Thanks for getting this far - yes that was the download that i thought i got two files from - i guess i must be losing my marbles because both you and i only now get the one file (but in my defence, my download manager has that link originally producing two files ...strange!)

In your file i can see that you've got a field that's come not from the join file but from the file behind that. When i add a field and specify it's contents in the file you gave me, and chose whether it comes from the current table or a related table, i can see the distant table, but when i do this in my database, i can't see that far - only to the join table. Could this be because you've got all your tables in the one file, and all three of mine are separate files?

One thing about this, though, is that i can't edit the field's contents in your file when it's a distant table - which was what i was hoping to do (name field in organisation layout).

Thanks again

[comment]

i can't edit the field's contents in your file when it's a distant table

That's what I said earlier - it's disabled by choice. Just go to Layout mode and enable the entry back (in Format > Field/Control > Behavior).

I am not sure how your files are set up (or why they are separate), but you need to have a TO of People on the graph in Campaigns, connected to the other side of the join table (it's not a join table if it doesn't join between People and Campaigns).

[Slater]

Guys, i really can not thank you enough ... you've made me and, by extension, my lady very happy!!

I had to do a bit of research about what a table occurrence was (my knowledge of filemaker comes soley from the user guide and this forum) but once i'd understood what you were saying (all along) i sorted it within seconds!

I've got all of my databases as separate files - not sure where i got the idea from though it must go back 7 or 8 years because that's how old the heart of this database is ... i'm gathering from your tone Comment, that that's not the best thing to have done!

Thank you again, very very much.

CHEERS,

Slater

[comment]

Let me put it this way: I would keep all the tables in a single file, unless I had a good reason not to.