13 Replies Latest reply on May 20, 2014 9:18 AM by philmodjunk

    Many to Many?

    TodPoirier

      Title

      Many to Many?

      Post

           I'm a Newbie to Filemaker and am overseeing work on a database for tracking inventory and trouble reports for the engineering department of a tv station.  The expectations of the database have grown since the first, simple, inception and now I need to figure out how to associate devices with each other.  I have one table listing inventory and all the things you'd want to know about a piece of electronic or computer equipment.  Now I need to create a tab in each inventory record to associate one piece of equipment with another.  I want to do this by bringing up a location pull down menu to choose the room the associated equipment is in.  This would then bring up a list of equipment in that room I could choose from to associate the items.  This list would have to have the ability to contain several items so I'm thinking a portal would be the best way to accomplish it, but I'm having trouble figuring out the relationships.  I know I need to create a join table, but none of the tests I've tried to put into place don't work the way I'd expect.  Here's a screen shot of my layout and relationships.

           Thanks,

           Tod

      screen_shot.jpg

        • 1. Re: Many to Many?
          philmodjunk

               It appears that you want to link a given equipment record with possibly many other equipment record that are in the same location, but not all equipment at that location. Correct?

               This describes what is not only a many to many relationship but a self join-- a many to many self join relationship to be exact. This can be done with two table occurrences of your equipment table linked by a join table:

               Equipment-----<Join>-----Equipment 2

               The other part of your question, getting a value list of just those equipment from the same location, is called a conditional value list. I keep a rather large set of links on hand for that subject: (You may only need to look at the first or second link.)

               There's a lot of overlap between the first two links so you can read one and skim the other. The demo file gives you several examples of different conditional value lists so you can examine them to see how they are set up. If you are using FileMaker 12 or later, you can open this demo file from your File menu to get a converted copy of the demo file that you can examine in your version of FileMaker.

               The next to last link discusses how to set up a chain of conditional value lists where the value selected in each conditional value list controls the values listed in the next value list and the very last link is a fairly new addition that describes how to use ExecuteSQL to produce conditional value lists that aren't easily possible with the other methods described here.

               Forum Tutorial: Custom Value List?

               Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

               Demo File: https://www.dropbox.com/s/j6qf0z9fnem3uxd/ConditionalValueListDemo.fp7

               Hierarchical Conditional Value lists: Conditional Value List Question

               Using ExecuteSQL to produce a conditional value list: Using ExecuteSQL to Produce a Conditional Value List

               Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.

          • 2. Re: Many to Many?
            TodPoirier

                 Not going to be getting back to this til monday.  I can see the logic behind it, hopefully I can make it work.

            • 3. Re: Many to Many?
              TodPoirier

                   Ok, I'm still pulling my hair out.  These are a couple screen shots of what I've got going on again, obviously unfinished right now.  I assume for my issue I should be creating the AD_Join table shown near the right side of the relationships and linking the Inventory and Associated Device tables.  Associated Device was just a copy of Inventory not used for anything else.  I've tried making a relationship between AD_Location in the AD_Join table and Location in the Inventory table but I only get a partial listing of possible locations and I'm not sure why - I hadn't set it up to filter anything yet.  I'd pulled the fields out of the portal so none of that sorting was involved and it was the same.  I'm sure there's other things messed up with my scenario but I'm waiting for a light bulb to go off to keep proceeding.  It's not helping that someone else put the majority of the project together who's not here anymore and that it's a very very side project for me so gets little of my time.

                   Thanks for any help!
                   Tod

              • 4. Re: Many to Many?
                philmodjunk

                     It appears that you are trying to set up one relationship where you need two.

                     You need a many to many relationship where you use the join table to link equipment records to each other.

                     You need a separate relationship based on the location field that links to all equipment records with the same location to use for your conditional value list.

                     Your many to many relationship should look like this:

                     Inventory----<AD_Join>----Associated Device

                     Inventory::Serial Number = AD_Join::AD_Serial 1
                     Associated Device::Serial Number = AD_Join::AD_Serial 2

                     the two name fields in AD_Join are redundant and should not be used as match fields in the relationship. (You don't need them at all except for certain special uses not inevidence here.)

                     For your conditional value list, you will need to add another table occurrence of Inventory and link it to Inventory by Location. You would use this occurrence as the source of values for your value list and specify Inventory as your "starting from" table in the "include only related values" option for this value list.

                • 5. Re: Many to Many?
                  TodPoirier

                       Ok, it looks like I have most of this working right.  I have two issues left.  Right now the portal brings in the drop down boxes for 1 item to be associated.  When this is filled in it doesn't allow me the option to associate another piece of equipment.

                       The other problem I have is trying to put a button in the portal line that will bring us to the piece of linked equipment in the inventory.  The closest I've gotten to making it work is getting an error message saying the operation couldn't be completed because the layout cannot display the result.  What I'm wondering is if the result is trying to be displayed in the portal instead of in the layout itself?

                  • 6. Re: Many to Many?
                    TodPoirier

                         oops, 2 more pictures coming of the layouts.

                    • 7. Re: Many to Many?
                      TodPoirier

                           Last image - relationships

                      • 8. Re: Many to Many?
                        philmodjunk

                             "Allow creation of records..." needs to be enabled in the relationship for the AD_Join table on your inventory layout. This adds a blank "add row" after the last portal record for linking in more equipment records.

                             And I am referring to the original relationships that I recommended. Your most recent set of relationships does match what I suggested and does not look like it will work for what you need here.

                             A button in the AD_Join portal row can use Go To Related Records to make that associated device record the current record of a layout based on either inventory or associated device. (Any layout based on a table occurrence with the same data source table as Associated Device will work.)

                             Go To Related Record [Show only related records; From table: Associated Device ; Using layout: "Associated Device" (Associated Device) ]

                             or

                             Go To Related Record [Show only related records; From table: Associated Device ; Using layout: "Inventory" (Inventory) ]

                             But you still don't have your relationships set up correctly.

                        • 9. Re: Many to Many?
                          TodPoirier

                               We were trying to set up the relationships the way you suggested and they didn't work.  The way they are set up now is the only way we've been able to get results that have made sense.  The "AD_Location" drop down only shows locations that have equipment in them and the "Model and Serial Number" drop down does show the equipment that is in the location.  

                               If I try turning on the "Allow creation of records...." option I get blank pull-downs in the portal - one for each piece of equipment in the room.

                          • 10. Re: Many to Many?
                            TodPoirier

                                 Not saying we're right and you're wrong..,  just that we're missing something.  Two of us independently tried following your advice and didn't get good results.  I'm wondering if we have another relationship set up that's affecting things - is that possible?

                            • 11. Re: Many to Many?
                              philmodjunk

                                   There are two different issues:

                                   a) getting the correct relationships in order to link equipment records from inventory in a many to many self join relationship. That requires the table occurrences I specified in my original post:

                                   Inventory----<AD_Join>----Associated Device

                                   Inventory::Serial Number = AD_Join::AD_Serial 1
                                   Associated Device::Serial Number = AD_Join::AD_Serial 2

                                   b) getting the correctly relationships in place so that your value lists work. You have to have the above for a many to many relationship, but I didn't go into a lot of detail on what set up to use for the conditional value lists. Let's take a closer look at that part of the project:

                                   Starting with the above relationships add:

                                   Inventory------<Inventory|Location

                                   Inventory::Location = Inventory|Location::Location

                                   where Inventory|Location is another occurrence of the inventory data source table.

                                   Define your value list to list values from Inventory|Location with "Include only related values starting from [Inventory]"

                                   This is what I meant in a previous post by:

                                   

                                        you will need to add another table occurrence of Inventory and link it to Inventory by Location. You would use this occurrence as the source of values for your value list and specify Inventory as your "starting from" table in the "include only related values" option for this value list.

                                   Note that this added table occurrence does not link directly to the Join table.

                                    

                              • 12. Re: Many to Many?
                                TodPoirier

                                     ok, we don't get what you mean by these two lines:

                                     Inventory------<Inventory|Location
                                     Inventory::Location = Inventory|Location::Location

                                     I do understand what you mean by creating another occurrence of the inventory data source table.

                                • 13. Re: Many to Many?
                                  philmodjunk

                                       Inventory------<Inventory|Location

                                       ---< means "one to many"

                                       "Inventory|Location" is the name that I've given to the new table occurrence of inventory.

                                       Inventory::Location = Inventory|Location::Location

                                       describes the match fields that you'd use in this relationship to produce the one to many relationship. (Though filemaker will show this as >---<)

                                       Inventory|Location would then be the table occurrence from which you select fields for your conditional value list of all equipment from the same location.