Many to Many, join tables, and Menu Lists, help

[otterboy]

My setup:

3 Tables

- Images

- Organisms

- ImagesOrganismLinker

Each of Images and Organisms has a serial number. The linker table contains only two serial numbers, from records in each of the Images and Organisms tables.

On the Images table, I have a drop down that contains a value list of Organism names.

When a record is created in the Images table, and an Organism is selected, I need to have a record created in the linker table, containing the two serial numbers.

What I have so far doesn't exactly do this. Can someone help me make this work?

deleteme.fp7.zip

[transpower]

I think you need just Images and Organisms; I assume that the Organisms table is relatively fixed, but Images keep being added. Have a portal in Organisms for Images; add a new image in the bottom line of the portal. The key from Organisms will transfer to Images. By the way, we usually use singular form for table names (so it should be Image and Organism, by convention).

[otterboy]

I think you need just Images and Organisms; I assume that the Organisms table is relatively fixed, but Images keep being added. Have a portal in Organisms for Images; add a new image in the bottom line of the portal. The key from Organisms will transfer to Images. By the way, we usually use singular form for table names (so it should be Image and Organism, by convention).

Thanks for the naming tip, I've been learning FM7 on my own, so I don't know any of the conventions used, I'm just now to the point where I'm knowing enough to ask questions...:

Without the linking table, how do I build the relationship between the two tables to show only related records? I'm not clear on what the keys would be in the case.

What I want eventually is to have a portal on both layouts (Image and Organism) that shows all the related records. So from an organism layout, there are a bunch of images in a portal with a "Go to Related Record" button next to them, so that I can pick out a specific image, and view that image.

I also may need multiple organism related records on the Image page, since some images may contain more than one organism.

Does that change your advice at all? I'm sorry I didn't explain it more thoroughly the first time. But it's very clunky, because you have to choose a serial number rather than a name in the drop-down list in order to create a record in ImageOrganismLinker which contains the serial numbers, which is not very intuitive.

These tables are a smaller piece of a much larger database, but I've run into this problem several times before, and I'm not sure how to solve it.

Thanks again for your response. I will try playing with it to make it work without the linking table like you suggested.

-Jon

[transpower]

But (if an image relates just to one organism) you would not need a portal in the Image layout for organisms; just a related field would suffice. For the Organism layout, you're correct to use a portal to Image with a GTRR script step.

If, on the other hand, there are multiple organisms per image, then you've thrown a monkey wrench into the database schema. You would then need to have the linking table, just as you had originally proposed; it's the only way to connect a many-to-many relationship. In that case, you should work from the linking table, with two drop-down value lists for choice of organism and image (with options to add). Then have an image portal in Organism and an organsim portal in Image.

[otterboy]

If, on the other hand, there are multiple organisms per image, then you've thrown a monkey wrench into the database schema. You would then need to have the linking table, just as you had originally proposed; it's the only way to connect a many-to-many relationship. In that case, you should work from the linking table, with two drop-down value lists for choice of organism and image (with options to add). Then have an image portal in Organism and an organsim portal in Image.

This is my conundrum, and it does end up being a many to many both ways, which is why I ended up with the linking table.

I see now that I need to work from the linking table layout. I've been working from the Image table layout, and able to pick the organisms in the portal via a menu list. Obviously if I work from the linking table there isn't an issue of trying to pass the right field to the linking table.

Thanks for your comments.

[RalphL]

I am working on your file to show you a way to do this from the either of the main tables. It will be latter today before I can get back to it.

[RalphL]

I got back to your file and finished my modifications. I haave it setup to allow you to add records to the join table from the Image table. You can do the same thing from the Organism table. The only tricky part is the setup of the value list unused. It must be marked to use only related values.

I also turned off the allow creation of related records.

[otterboy]

I got back to your file and finished my modifications. I haave it setup to allow you to add records to the join table from the Image table. You can do the same thing from the Organism table. The only tricky part is the setup of the value list unused. It must be marked to use only related values.

Wow, that's great. It's a different approach to what I've been doing, and it's got me out of my rut!

I did notice that if I delete all the organisms from the image, I don't get the deleted organisms back to select from in the list, but I've only spent about two minutes looking at it. I'm leaving town tomorrow, so don't think me rude if I disappear for a couple days.

I really appreciate your help with this, I will peruse the file more carefully in the meantime.

Thanks again for the suggestions.

[wmugrad28]

I am having trouble with my file. I have three tables: Customer, Equipment, and a join table CustomerEquipment. This is a many to many relationship because I only want to be able to type customers into the Customer table and equipment into the Equipment table. How can I get the two tables to create a record of foriegn ids into the joined table?

My sample file is attached.

Greg

jointable.zip

[-Queue-]

Set the join table to 'Allow creation of related records' in both connecting relationships. Then, from either main table, add a portal to the join table and the other main table's foreign key field to the portal. Create a value list based on the other table's primary key and name, sorted by name, and format the new portal's field to be a list or menu based on this value list. When you select a value for the field, a new record containing both foreign keys will be created in the join table.

[wmugrad28]

I saw this same technique in Using FileMaker 7 Special Edition . Is there anyway to avoid putting a portal on the Customer table or Equipment table? I would prefer a technique to have the software figure out that a user has input a piece of equipment and attached a customer to it from the Customer table. Are there cleaner alternatives out there?

Greg

[-Queue-]

Ideally, you wouldn't include the Customer information in the Equipment table. You would match Customers to Equipment by creating new records in the join table, using value lists to display the available Customer and Equipment IDs. If one customer can have multiple equipment and a piece of equipment can have multiple customers, then this is the way you should do it to avoid cluttering up your entities and making life much more difficult for yourself. After all, this is the purpose of a join table. If you don't require a many-to-many relationship, then you can eliminate the join table, directly relate Customers to Equipment, and use a portal to apply customers in the Equipment table.

[wmugrad28]

Ideally, you wouldn't include the Customer information in the Equipment table. You would match Customers to Equipment by creating new records in the join table, using value lists to display the available Customer and Equipment IDs.

Thanks again for your reply. I guess I never thought of leaving the customer's name off of the Equipment table. I have two thoughts/questions about your post.

1. Over time a piece of equipment could have several owners (in theory). A customer could have a dozen or so pieces of equipment. The problem I have with a value list is that I might have 1000 customers to pick from on the equipment side or 3000-4000 pieces of equipment (which have 3-4 key pieces of information instead of one... like model, serial, and part numbers). Should I try to create a sorting value list in a portal or is there something better?

2. The pieces of equipment will have different owners over time. A piece could be purchased by a company, then sold, and then purchased again by a different company. Will this impact things any?

Thanks again for your help. What you are recommending is beginning to make sense.

Greg

[wmugrad28]

1. Over time a piece of equipment could have several owners (in theory). A customer could have a dozen or so pieces of equipment. The problem I have with a value list is that I might have 1000 customers to pick from on the equipment side or 3000-4000 pieces of equipment (which have 3-4 key pieces of information instead of one... like model, serial, and part numbers). Should I try to create a sorting value list in a portal or is there something better?

I think I have the second issue figured out, but the first one still remains. Actually the first one should be in two parts (a sorting value list because of the number of options to pick from and how to get a value list to show more than two things because I need it to show 3-4 to distinguish exactly which piece it is talking about!!!).

Greg

[RalphL]

For large groups of items I like to use a filtered portal to reduce the number by typing in a few characters of the name. The portal offers advantage over a value list in that it can display many fields and will also should duplicates. See Type Ahead by John Mark Osborne. http://www.databasepros.com/

[wmugrad28]

I've used that technique before when I used to try and match up customer names to a piece of equipment instead of using the power of a join table. It's too bad that I might need to have TWO portals on a layout (one to pick from and the other to show the running list that I've selected) unless someone has a better idea.

Greg

[RalphL]

I have been using a different layout when adding a new item or editing an existing one. You might find my sample file for a Speration Model Many To Many of use. http://www.fmforums.com/threads/showflat...e=1&fpart=3

[wmugrad28]

Is a SIT file Mac? I've got an IBM.

Greg

[RalphL]

Yes, it is Mac, but it can be expanded on a Windows machine with the right expander. Look at www.allume.com for a windows version of Stuffit

[wmugrad28]

Thanks for your help. That appears to work pretty good. Is it too difficult to have both the pick list and picked list on the same TO? I've thought long and hard and can't figure it out yet. I've tried creating additonal TOs, but no luck.

Greg

[wmugrad28]

(Before my last post) I've done some rearranging and am now trying to connect a table to a join table using a join table.

I have a "Customer" table and "Equipment" table related (many to many) with a join table "JOIN_Customer_Equipment" (this table has it's own unique ID).

I have a "PM" table (preventative maintence) that needs to have zero-many customer/equipment combos assigned to it (from JOIN_Customer_Equipment). So, I created ANOTHER join table called "JOIN_JOIN_CE_PM" since the JOIN_Customer_Equipment and PM relationship is many to many as well.

From the PM table occ. I have been successful in allowing a user to select from a popup list a KEY for "JOIN_Customer_Equipment" and then have it auto enter in details that make sense to the user. The problem is that the user doesn't know the five or six important details from the popul list info... only two. I really want to be able to see a list of options from all the JOIN_Customer_Equipment records WITH USEABLE DETAILS.

The only way I could figure out how to partially achieve this was to create extra records on the JOIN_Customer_Equipment that would copy the related data (that's useable) from the related records. Then I could just view all the records from the JOIN_Customer_Equipment portal on the right, select a record, and have it copy the ID to a JOIN_JOIN_CE_PM portal on the left along with the PM ID. I want to find a way to:

1. Not create the extra file weight of copying the extra data.

2. Have live data instead of my version of viewing old copied stuff.

Greg

[RalphL]

A star join or a modification of it may work here. See my sample file at

http://www.fmforums.com/threads/showflat...true#Post139633

Make Committee your Equipment, Office your PM and Member your Customer.

[wmugrad28]

I don't see how that would work. I just tried several different variations with no luck. I always thought that a star join was for three different things at once. I just don't want to break the connection between the Customer table and the Equipment table. There has to be a work around somehow. Let me know if you have anymore ideas. Thanks.

Greg

[wmugrad28]

One Idea: How could I view the records in the JOIN_Customer_Equipment table in a portal along with "related" field records from Customer and Equipment? If I could find a way to do this I might be able to execute a script to copy the IDs to the appropriate table JOIN_JOIN_CE_PM. After this, I could view records from JOIN_JOIN_CE_PM in a portal from the PM table layout... along with details from related field records from Customer and Equipment.

Greg

[RalphL]

Sorry that you couldn't make it work. It seemed to be a natural, Individual = Customer, Committee = Equipment, Office = Maintenance & Membership = JOIN_Customer_Equipment. Individual and Committee have a many to many relationship. Committee has a one to many relationship with Office. Membership is the join table. Individual and Committee also have a many to many relationship. Thus a star join.

I assumed that Equipment would have a one to many relationship with Maintenance. You have already established a many to many relationship between Customer & Equipment.

[wmugrad28]

I spent more time on it and have got it to work except one part.

I created a table called "Blank" and joined it with an x to "JOIN_Customer_Equipment. I then made a script from the PM layout that took me to the Blank table and copied the PM KeyID to a global field. This layout also has a portal from JOIN_Customer_Equipment showing all its records (exactly what I wanted). Now I placed a button "Add" in each portal record that runs a script that says:

Go to Layout ["JOIN_JOIN_CE_PM" (JOIN_JOIN_CE_PM)]

New Record/Request

Set Field [JOIN_JOIN_CE_PM::PM_FOREIGNKEYID; Blank::FOREIGNKEYID_PM]

#

Set Field [JOIN_JOIN_CE_PM::JOIN_CE_FOREIGNKEYID; JOIN_Customer_Equipment::JOIN_CE_PRIMARYKEYID]

Go to Layout ["PM" (PM)]

The first Set Field works, but the second one doesn't. All four fields involved in the scripts are the "text" type. I don't know why it won't copy the data over. It creates the record, copies the only global (the first part) and then won't copy the second field. What am I doing wrong? The error capture doesn't return anything. I've even tried copy/paste in the script with no luck.

Greg

[-Queue-]

Try adding a Commit Records/Requests step after your first Set Field, to establish the relationship before you try to reference it.

[wmugrad28]

Thanks Queue,

I tried that and it didn't work. The field that I'm having trouble copying is a "text" field and it is marked as a Serial Number (JCE1, JCE2, etc.). The other field is text as well. Everything looks like it is working fine, but nothing shows up in that cell. If you type JCE1, JCE2, etc. in the portal it populates the remaining cells properly. Huh... Other things I've tried is to put Commit Records/Requests after both Set Fields. I even tried it (Commit Records/Requests) right after the New Record/Request step.

Greg

[-Queue-]

Rereading your post above, it appears that you're creating a new record, setting PM_FOREIGNKEYID with a global, then trying to set a field with data that isn't currently related to this new record; but only globals can be referenced without a valid relationship. If PM_FOREIGNKEYID is not the key for the JOIN_Customer_Equipment relationship, then there is no way that this will work.

Also, it seems that putting this button in every portal row is overkill. It functions the same way, regardless of which row you're on. I would put it outside the portal and save the screen space.