6 Replies Latest reply on May 18, 2014 9:31 PM by lozzieshields

    Confusion - Many to Many relationship - how do I break it?

    lozzieshields

      Title

      Confusion - Many to Many relationship - how do I break it?

      Post

           Hi - hoping someone can assist me with breaking a many-to-many relationship. Can't seem to nut it out!

           I have jobs that hold many images and images that can belong to many jobs - how do I set the relationship

            

           Jobs has a unique ID

           Each image has a unique ID

           A job can belong to only one tone (core, premium, partner) and have only one descriptor (texture, experience, portrait - for photos only but we also use pictograms)

           Whereas some images can belong to only one tone and others can belong to two or three tones. The images only have one descriptor that belongs to them

           The images can be a mix of pictogram and photos - should I split these out into different tables?

           Keep getting confused when I think about it. I want to be able to view what jobs have used what images and what combination of images and if we have used the same combination of images on jobs. I also want to view where images have been used - ie a list of jobs each image was used on.

           Please if someone could help me get my head straight on this one I would be much appreciative!

           Thank you

            

           L

        • 1. Re: Confusion - Many to Many relationship - how do I break it?
          philmodjunk

               I have no idea what a "tone" is and what it has to do with your many to many relationship between jobs and Images.

               I have jobs that hold many images and images that can belong to many jobs - how do I set the relationship

               That much is clear:

               Start with these relationships:

               Jobs-----<Job_Image>-----Images

               Jobs::__pkJobID = Job_Image::_fkJobID
               Images::__pkImageID = Job_Image::_fkImageID

               You can place a portal to Job_Image on the Jobs layout to list and select  Images records for each given Jobs record. Fields from Images can be included in the Portal to show additional info about each selected Images record and the _fkImageID field can be set up with a value list for selecting Images records by their ID field.

               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

          • 2. Re: Confusion - Many to Many relationship - how do I break it?
            lozzieshields

                 Thank you for your reply but I'm sorry I'm still a bit confused.

                 As per your suggestion I have set the database up using a joining table but I am not sure how to get the images to view and filter.

                 We have Images that belong to certain categories (texture, portrait, experience, discovery, worklife etc)

                 A job could hold 1 or more of these images (usually on the cover of brochures etc)

                 I want to achieve a number of things:

                 • be able to go to the 'jobs' table and see which images were used on the job

                 • be able to go to the 'images' table and find an image and be provided with a list of jobs that have used that image so we don't over use some images

                 • be able to do a search on a combination of 4 images to see if they are have already been used on another brochure as a set so we don't have brochure covers looking the same

                 • be able to filter when entering the images that will be used on a job so that when I select a category such as 'discovery' it only shows me the images that are in the 'discovery' group (rather than a list of 400 plus images)

                 Also are you able to see the images (which I have put into a container fields) in the drop down list as well as the image id?

                 I'm just confused as to how to link images so I can use a filtered drop down list in the portal on the jobs page.

                 Your help is very much appreciated : )

                 I hope this is much clearer now

                 thank you!

                  

                 L : )

            • 3. Re: Confusion - Many to Many relationship - how do I break it?
              philmodjunk
                   

                        • be able to go to the 'jobs' table and see which images were used on the job

                   Put a portal to either Images or Job_image on your Jobs layout. In either case, put a container field from Images in the portal row to show the image. (Use the join table if you want add/remove images from the project. Use the Image table if you just want to display the images.)

                   

                        • be able to go to the 'images' table and find an image and be provided with a list of jobs that have used that image so we don't over use some images

                   The same method applies. Put a portal to either jobs or job_image on your images layout. Put fields from Jobs into the portal row to list the data on each job that you want to see in this list.

                   

                        • be able to do a search on a combination of 4 images to see if they are have already been used on another brochure as a set so we don't have brochure covers looking the same

                   That's bit trickier. It would be pretty easy to find job records that are linked to image 1 or image 2 or Image 3..., but finding records linked to image 1 AND image 2 AND image 3 will probably require a script. I suggest getting things to work for your first two bullet items working first and then posting back here to this thread or a new thread in the forum as anything I suggest here will require that you have the basic set up working first.

                   

                        • be able to filter when entering the images that will be used on a job so that when I select a category such as 'discovery' it only shows me the images that are in the 'discovery' group (rather than a list of 400 plus images)

                   This describes a conditional value list. I've compiled a fairly large list of links on the subject. The total set of links may seem overwhelming, but you probably only need to look at the first or second link in combination with the demo file from the third.

                   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

                   

                        Also are you able to see the images (which I have put into a container fields) in the drop down list as well as the image id?

                   A drop down list (or pop up menu ) cannot display images. But there are other selection methods (that can also work like a conditional value list) that use a portal to list the images for selection that can be setup. I will suggest again that you get the basics working first and then this visual based selection method can be added as an enhancement of the initial design.

              • 4. Re: Confusion - Many to Many relationship - how do I break it?
                lozzieshields

                     Hello again - thanks again for your help - the information you provided me with above is extremely helpful.

                     I have created a join table between jobs and images (Job_Image) and I have put a portal there but I am unable to create new records in the portal? I want to be able to click in the portal on a field (thats in the portal) called Image number and be provided with a drop down list and be able to select an image to include but I cannot seems to create a new record. And before I can proceed I need to work this one out? Not sure what I am doing wrong

                     I have selected create a new record in the relationship line but it has been a while since I have worked in filemaker so not sure if I have done it right

                     I have taken a screen grab and attached below.

                     Would you mind looking at this for me?

                     Many thanks once again!

                     L

                • 5. Re: Confusion - Many to Many relationship - how do I break it?
                  philmodjunk
                       

                            I have created a join table between jobs and images (Job_Image) and I have put a portal there

                       And which layout is "there"?

                       You've enabled "allow creation.." for the join table in the images to job_Image relationship. That should allow you to put a portal to Job_Image on your images layout and add new records to the portal to link the current image to an existing jobs record.

                       But if you are trying to put a portal on the Jobs layout in order to add new Job_Image records to link the current job to an existing image record, you need to enable the same "allow creation..." option for Job_Image in the Jobs to Job_Image relationship.

                       From either the jobs or the images layouts, this allows you to link to existing records on the "far side" of the join table, by creating new join table records if you enter data into the bottom, blank "add row" of the portal by entering data into a field from the join table. The "beginner first step" method is to set up the _fk field for the other table as a drop down of ID's and Names from the third table. This does not allow you to create new records in the third table.

                       Example:

                       If you set this up on your Jobs layout with a portal to Job_Image, you can format the _fkImageID field in Job_Image with a value list that specifies the "Use values from a field" option. In that value list, you select __pkImageID from Images as the "first field", select the "also display values from" option and then select a text field from Images that uniquely names/describes that image as "field 2". The user uses the text from that second field to select an image, but the value list enters the corresponding ID number into the _fkImageID field when they select a value. This would require that you add a name or description field to your Images table with a "unique values" validation option specified. Keep in mind that this is the "beginner level" method of getting this to work. Setting it up and seeing it work is good confirmation that you have all the other needed details of the basic design working. (Note that the preceding paragraph would describe linking the current image record to an existing job record on an Images layout if you just swap the field and table names around.)

                       Adding a new image record will require some additional set up that we have not yet discussed. That typically requires a simple script that creates a new record in the image table, copies the ID of the new record to a variable and then creates the needed join table record, linking it to both the current Job and the newly created image record. (and there are several variations of how this script works depending on the level of detail needed to enter the needed data into your new image record.)

                  • 6. Re: Confusion - Many to Many relationship - how do I break it?
                    lozzieshields

                         Hi - It's a bit hard to show you what I have done so I have attached a few screen grabs with the two options

                         Hopefully you can see from here where I have gone wrong! I just can't seem to get the records to create in the portals.

                         Thank you for your patience!

                         : )