add fields from 2 child tables to layout

[dbsmkitez]

Hi:)

While I have created many databases over the years, this is my first FileMaker db. I am used to being able to query, so there has been a bit of a learning curve. Essentially, I created a db that is based off a main table (Organizations). Through the OrgId, a one to many relationship exists between the Organizations table and each of the following 2 tables: Events and Contacts. The clients I am creating the db for send letters to the primary contacts for each Org. with information about upcoming events. I have tried to add the primary contact and the event info to a layout that pretty much is a letter. The problem is if I base the layout on the Events table, contacts are added based on the contact id, not whether they are the primary, even though I have constrained the find. If I base the layout on the Contacts table, I cannot constrain the found set to a date range. It is as if FileMaker no longer recognizes them as dates but as text, and so the wrong dates are included in the layout. Any help would be greatly appreicated. It is basically my last stepping stone to a finished product.

Thank you in advanced!

[Fitch]

I'm not clear whether you're storing the contact ID in the Event record or not. I think you should do so, and then make another Table Occurrence (TO) from Event to Contact based on the ID. I.e., your structure as described is:

Contacts --< Organizations >-- Events

... and I am proposing:

Contacts --< Organizations >-- Events -- EventContact

Put the contact name from the new TO on the Event layout and you're all done.

[dbsmkitez]

Hi, thanks for your reply, but I am confused. I have noticed that FileMaker doesn't follow all the rules of a relational database. Having to create additional TOs has been confusing.

The contacts "belong" to one Organization (many-to-one, respectively). Creating another TO would indicate that they belong to an event, and that isn't quite correct. The events also belong to an Organization, and because the database is normalized, it does recognize the relationships. Won't that mess up how the data is updated if changed in one area? It seems redundant to me to create a direct relationship between Events and Contacts since there is already a relationship via Organizations. Is this just one of the quirks of FileMaker, or can it be done as I have it?

Thanks again in advance:)

Lisa :confused:

[Ender]

Does this model fit better with what you're doing? With this, the Contact-Event table is the join, and the mailing would be based out of there. If you wished to remember the mailings that are sent for each event, you'd add a couple more tables (Mailing and Contact-Event-Mailing) to track them.

[David Jondreau]

How do you flag a Contact as being the primary contact for an Organization? You need to create a second relationship between Organizations and Contacts (adding a new Contacts TO). This new relationship should filter out all but the primary contact.

[Ender]

How do you flag a Contact as being the primary contact for an Organization? You need to create a second relationship between Organizations and Contacts (adding a new Contacts TO). This new relationship should filter out all but the primary contact.

Oh yeah.

I'd have a Primary ContactID field in the Organization TO, and relate that to Contact-Event (probably in addition to the underlying structure I showed above). This means a second TO of Contact-Event (by Primary Contact).

[dbsmkitez]

I don't think this will work, because the only way the contact is related to an event is because of the organization it is associated with. The contacts can change for the organizations from season to season. The only reason they track the primary contact is so my clients know who to get a hold of about events and problems. I don't think connecting a contact directly to an event would help this. I use a pull-down field in the contacts table to designate the primary by choosing "Yes". The OrgID is the fk in both the Contacts table and the Events table. I think adding a ContactID would just reverse the relationship. There are many contacts for each Organization and many Events for each organization. The contacts aren't directly related to the events. Contacts are just the current people to call if necessary.

[dbsmkitez]

Maybe I misunderstand your post, but I have flagged the primary contacts via a field. I am able to create a script that recognizes the contacts associated with the Org. that is associated with the event. The problem is, I can't get both the correct event info and the correct contact info on the same layout. If this was a report requiring that information, how would I create it. This is something I would normally be able to do with a simple query, but FileMaker doesn't allow for queries, so I am at a loss. :frown:

[Ender]

No problem. The model does need to fit the goals.

How's this one look?

Although you don't really talk about it, I do think the join table is useful. It allows you to have the event information be stored in the Event table (Title, Date, Venue, etc.), and have information about the Organization's role/registration in the event be in the join. This way multiple organizations could participate in the same event (is this true??) and an organization can participate in multiple events.

The Primary Contact should still be kept as an ID in the Organization table. This is because there should only be one primary contact per organization (this can't be enforced if the primary contact is simply a flag in the Contact table). So a second TO of Contact is still used for the Primary Contact.

In this model, the mailings would be generated out of the Org-Event join (or a linked Org-Event-Mailing TO, if you're keeping track of Mailings sent), showing the related primary contact from the 'Contact by Primary' TO.

[Ender]

With the Primary Contact set up this way, you can then run your query in the Org-Event TO. There will only ever be one primary contact per Organization (that's essentially a one-to-one), so you wouldn't get the false positive you'd get from searching Contact fields from Org-Event.

This means you could run a Find in Org-Event for something like:

Contact by Primary::Position: "Big Wig"

AND Event::Event Date: ">7/1/2007"

and get Org_Event records whose primary contact is a Big Wig and are assigned to participate in any Events after 7/1/2007.

Does this work?

[Fitch]

I have noticed that FileMaker doesn't follow all the rules of a relational database.

Does ANY commercial DBMS follow all the rules? (Let's start a separate thread about that if we must.)

Having to create additional TOs has been confusing... Creating another TO would indicate that they belong to an event, and that isn't quite correct.

You're right, it's not correct to say that the relationship graph indicates anything about database structure or normalization. The graph is not an ERD.

Maybe if you think of the graph more as a way to define queries it will make more sense. A typical FileMaker solution will have many Table Occurrences (TOs) of a given base table, and veteran developers will use unrelated Table Occurrence Groups (TOGs) that enable us to present our data in different contexts and maintain our sanity.

You can solve this particular problem in a number of ways.

Let's say you want to keep your TOs as simple as possible:

Contacts --< Organizations >-- Events

Now, based on the way you're flagging the primary contact with a "Yes":

- you could use your value list of "Yes" to sort the Contacts --< Organizations relationship. This would make the primary contact info show up when you put a contact field on an Organization layout. Unfortunately, sorted relationships only affect data that's one "hop" away, so it wouldn't help you in presenting data on an Events layout.

- you could "pipeline" the contact via the sorted relationship into a calculated field in Organizations, and then show that field on Events layouts.

- that's kind of messy, so you could ditch the sorted relationship and go back to just putting the contact field on the Events layout, but this time put it inside a hidden one-row portal that is sorted on a value list. There's a "stupid FileMaker trick™" for ya.

- still kind of messy. What if you made a calc field "Yes" in Events and then a relationship to a Contact TO by Yes+OrgID?

Now we're back to my original suggestion. That's just one possible way to solve this. I'm not saying it's the "right" way, but it works. Requires no extra value lists, fields, or layout tricks. (FWIW I'd go with a "1" vs. "Yes" just to streamline the data.)

[dbsmkitez]

Tom-

I appreciate all your explanations. They did help me understand more how FileMaker "thinks"; and I did try your suggestion, but it did not work. In fact I didn't get any information to show up on the layout. I'm sure it is me and the fact that I probably misunderstood your explanation. I also think the problem is that although the really important information comes from the events table and contacts table, information is also pulled from a Locations table. Plus, I'm not just pulling one field from the Contacts table, I am pulling 5 fields. When I created the new TO, I still have to combine data from 5 tables into one. Talk about messy. I have attached a basic diagram of the tables involved and the fields I need to place on the one layout. These are the tables that currently exist. If you could explain further or have another suggestion, I would really appreciate it. I thank you for your patience:)

Lisa

FileMakerProbERD.bmp

[Fitch]

See attached file.

Not_an_ERD.fp7.zip