Jump to content
Sign in to follow this  

Relationship from 2 fields to 1 field problem

Recommended Posts

I've got two tables: one of plants and one of butterflies.

The plant table:

Each plant record includes two drop down lists with repetitions.

1. for butterflies that use the plant as a host plant.

2. for butterflies that use the plant for nectar.

The butterfly table:

I want to display the plants that the butterfly uses as a host plant and nectar plant in separate fields because these are not the same plants in many instances.

I've made a relationship between the two tables and am able to display the host plants in a portal.

I did this by connecting the field "butterflies that use the plant as a host plant" in the plant table to the "butterfly name" in the butterfly table.

This works. On the butterfly record are displayed the host plants for that butterfly.

However, I can't figure out how to display the nectar plant names in the butterfly record. What I get is a duplication of the host plant names.

What it boils down to is this. I've got two fields using the same value list (butterfly name) in the plant table but I've only got one butterfly name in the butterfly table to which to reference.

Can anyone suggest an elegant solution that even this newbie can grasp? I only got this far through dumb luck, trial and error and dogged persistence. At this point I'm just plumb wore out.

Share this post

Link to post
Share on other sites

I've attached an example of how I'd probably do it. At first glance it does not look simple. But it is correct from a relational point of view, and in practice it's easy to use. It will do whatever you want, both now and in the future (extended functionality, reports, etc.).

There are 3 tables, Butterflies, Plants and a "join" table. The join table has 1 record for each unique combo of Butterfly-Plant-Role (role is either "host" or "nectar"). I made the portals small, but you could have virtually unlimited connections.

The role is automatically entered by the portal's relationship, using a "fixed" unstored calculation field, which just holds the word "host" or "nectar." The portals' relationships have "Allow creation of related records" turned on; that's the mechanism. They also have "Delete records" in the join table turned on; it almost always makes sense to delete the join record when a parent is deleted. I do NOT have it turned on to delete the parent on either side (be careful with that, always look at the direction).

The Relationship Graph is "verbose", in that each of the tables has its own Table Occurrence Group (TOG). This "anchor-buoy" method is the best structure, especially if you extend the solution later. I also added table occurrences* at the far right to reference the names thru the join table. This is not strictly necessary, as they could alternatively be calculated in the join table itself. As I said, it's verbose; also more extendable.

*A table occurrence (TO) is one of those "boxes" on the Relationship Graph. We used to call them "relationships," but that's not really an adequate name anymore; since any given table occurrence can be the end of several "relationships", some of which could pass thru several TOs.

I name my table occurrences according to what TOG they're in, what they pass thru, where they end, and what field is either at the end, or is critical to recognition of the TO (the latter being sometimes more important to me than the former). Some purists may object to the tilde~, but I use it to signify a field; since the darn names of everything are so similar. Whether it's safe to use depends on what other systems you have to interact with.


Share this post

Link to post
Share on other sites

I am stunned. This is incredible.

And you whipped this up in just a few hours? I'm more than impressed.

Thank you so much for sharing your time and obvious skill.

I'll be studying this for the next few days (weeks?) to see how I may implement it.

BTW if you wish, you're invited to visit the actual site where my humble efforts are available for all to view.


Share this post

Link to post
Share on other sites

Thanks. It only took about an hour, and much of that was arranging things on layouts so they looked OK (always tedious). Yours was a (very) common relational problem. It seemed easier to create the database than try and explain why repeating fields were a bad idea for this, and how to do it relationally.

Nice web site, especially the butterflies. There was another butterfly website powered by FileMaker, which won the 1st prize for a contest of web sites using FM Studio (which is a visual interface for FileMaker custom web publishing). The site also features Florida butterflies. Look at:


You can probably tell by the plant names in the example I posted that I also was into plants at one time, but more as landscape construction than gardening. It was in fact my first FileMaker database, over 10 years ago. This was in California, where we've got a drier climate, though down here in San Diego there's quite a bit of overlap with Florida, semi-tropical.

Share this post

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

  • Create New...

Important Information

By using this site, you agree to our Terms of Use.