advice on portals

[OWMAX]

I am an intermediate user of FM8.5 and am in the process of updating my company's DB. We are tracking Notables in one table and Representatives in another. In each Notable, there is an agent, manager and publicist selectable by field. The relationship back to Representative is a "x" connection. I would like to add a portal to the representative's layout which shows all notables they represent but can't make it work. Either shows all Notables in list or nothing. Any advice would be much appreciated.

[comment]

Don't you have any relationships linking the tables by matching fields, e.g. AgentID to RepresentativeID?

[OWMAX]

the matching fields would be Agent (in Notable) to Representative Name (in Rep). Should I try and add a serial field?

[comment]

Definitely - you should never match on names. You should select a RepresentativeID in each one of the three fields in Notables, and have three relationships from Notables to three occurences of the Representatives table:

1.

Notables::AgentID = Agent::RepresentativeID

2.

Notables::ManagerID = Manager::RepresentativeID

3.

Notables::PublicistID = Publicist::RepresentativeID

where Agent, Manager and Publicist are occurrences of the Representatives table.

Now, if you define a calculation field cRepIDs in Notables (result is Text) =

List ( AgentID ; ManagerID ; PublicistID )

you can define the fourth relationship as:

4.

Representatives::RepresentativeID = Notables::cRepIDs

and use it for your portal.

---

Another way to do this would be by using a third table of Representations as a join table between Notables and Representatives. This way a notable can have any number of representatives, in any role.

[OWMAX]

Thanks for your advice. I'm going to try a join table with the multiple in the am when I have my notes from home. I guess my only question is that the Rep names are two fields - first & last.

With that in mind, to select by name, then have the RepID autofill on the join table, is there a way to look at both fields as one to select?

[comment]

It's best to create a calculation field of full name, then use it as the second field for the value list.

[OWMAX]

Amazing! It seems to work! The join table did what it needed to!

I have one other question for you....in the same file and the same set of tables, there is a value list for invitations that we send to these notables. I have set that up as a check set value list which is fine, but one of our three principals leads the relationship, but changes between invites. Is there a way to note that without creating another join table with our invites or should I just do it that way?

Thank you for all of your amazing advice!!!!

[comment]

I am afraid I didn't understand the question, esp. the part of what needs to be accomplished.

[OWMAX]

Sorry bout that. We use our DB to send invitations to notables to events. I have a value list made of events to invite to. That being said, there are three of us here that reach out to contacts, and we set who is in charge of each notable each time we do an event (so they are not the same). I'm trying to figure out if there is a way to have this info (which person on my side is point) on a value list without creating another join table with the "Invitations".

If I do create a join table, it will be pulling information from the reps, celebs, and our media db and I will put portals on each of the screens I need to. I hope this helps?

[comment]

The relationship between Events and Notables is a many-to-many, so strictly speaking, there should be a join table in-between them.

Filemaker allows you to define a many-to-many without a join table, by having a checkbox of Invitees in the Events table. But one of the disadvantages of this shortcut is that an individual invitation can have no attributes. It either is or isn't. If you need to record who's in charge of each invitation, or any other data that describes a particular Event/Notable combination, you'll need the join table.

[OWMAX]

Thank you! I'm now having a horrible time adding this relationship. Here is the file. I'm failing. Help!

I have added the portal in the Data Entry Layout and the Notable layout but I can't get them to populate from my related Invitations_MediaLists join table.

Help! ack.

Edited July 9, 2009 by Guest

[comment]

I don't really understand your file (and don't expect to in the short time I can afford for this), but two things stand out:

1. A layout belongs to a specific TO. If your graph has two TO's of a table, you will get different results depending on which TO your layout belongs to.

2. You should use ONLY serial IDs for your relationships, not a combination of ID and Name.

[OWMAX]

Thank you again for everything. I will try and figure that out.

M