11 Replies Latest reply on Apr 23, 2009 2:59 PM by philmodjunk

    Linking data with the same database

    donok

      Title

      Linking data with the same database

      Post

      Hello!  I am going nuts here... I have a fairly simple photo database.  Some of the photos need to store a reference to other photos (like a spouse or sibling - some photos are backgraound images of compsited photos) -- anyway, I want a field that links to these other records in the same database.

       

      1 - I created a new table called linked_images

      2 - I created a field in that table called linked_image_field

      3 - I made a relationship in the Manage Database window where linked_image_field = image_field (off the main database)

      4 - I created a value list called linked_images_values and told it to Use values from field "image_field" 

      5 - In my layout I created a field - chose linked_image_field and made it display as a Drop-Down List

      6 - I told it to display values from linked_image_values and display data from linked_image_field in the linked_images table 

       

      When I browse - the field is completely empty - if I make it a checkbox field, I can see the values, but I can't choose any of them.

       

      What the heck am I doing wrong!?

       

      Thanks - Don 

        • 1. Re: Linking data with the same database
          philmodjunk
            

          Are linked_image_field and image_field both number fields? Going by the name, Image_Field might be a container field, If so that won't work.

           

          If they are both number or both text fields...

           

          In your specify field dialog box for your value list, there's an "Include only related values starting from..." option. Did you use it? What table occurrence did you select for this option?

          • 2. Re: Linking data with the same database
            donok
              

            Thanks for helping on this --

             

            Are linked_image_field and image_field both number fields?

             

            Nope - they are both Type = text,  Options = Indexed

             

            In your specify field dialog box for your value list, there's an "Include only related values starting from..." option. Did you use it?

             

            No, I used "Include all values"

             

            What table occurrence did you select for this option?

             

            I tried choosing my main database and the linked_images table here, but nothing changed...

             

             

             

             

            • 3. Re: Linking data with the same database
              philmodjunk
                

              Text base keys can work. I just needed to be sure you weren't using a container field.

               

              What do you want to do with your drop down list once it is working?

               

              What values do you want to see, just the names of related images or the names of all images?

               

              You need to use the related values option in your value list if you just want the related imaage names.

              • 4. Re: Linking data with the same database
                donok
                  

                What do you want to do with your drop down list once it is working?

                 
                This is the field where I _set_ the related image - so, I need all the images in the database listed here and I choose from the list (which one is the original foreground image used to create the composite image that is this record).
                 
                I was thinking this might be the same as a "spouse" field in an address database - where you choose another member of the database to cross-reference them as spouses.
                 
                Am I just going about this all wrong?
                 
                Thanks again, Don 
                • 5. Re: Linking data with the same database
                  philmodjunk
                    

                  I went back and carefully read your original post. Do you have two tables? the Main Table and the new table: Linked_Images?

                   

                  What records do you have in the Linked_Images table?

                   

                  Does your layout refer to your main table or Linked_Images? as its underlying table?

                   

                  It sounds like there are no records in the new table and you haven't enabled the "create related record option for the relationship" but I could be guessing wrong.

                  • 6. Re: Linking data with the same database
                    donok
                      

                    Yeah - sorry - I started out more messy than I needed to, and my problem has morphed.

                     

                    I created a second table, when I could have just created a second field and used a value list that draws from the list of all photos (choose one to be the foreground image).  But, now they want to see that linked photo - like in a portal? But the images are in the same table - so, I thought I would create a new table that looked to the main database... anyway, I'm now lost.

                     

                    Can I have a little cross-reference set of two fields

                     

                    1- an image from the same database and

                    2- the name of that image

                    ?

                     

                    thanks. 

                    • 7. Re: Linking data with the same database
                      philmodjunk
                          

                      Ok, that clears things up.

                       

                      You could set this up as a self-join but if there might be more than a small number of related pictures for any given record in your table, a separate table is likely the better approach and you've already started in that direction.

                       

                      You'll need Three fields in linked_images

                      linked_image_field (text)

                      Image_ID  (text)

                       

                      Now click relationship tab and make a second occurrence for your main table:

                      (Click the Main Table and then click the button with two green overlapping plus signs.) Name this new Table Occurrence (TO) "child_images"

                      set up the relationship child_images::Image_Field--=--Linked_images::Image_ID

                       

                      Now return to the field definitions tab for Linked_Images and add a calculation field, "Picture" with the follow expression: child_images::YourPictureField (replace "YourPictureField" with the name of the field where you store a picture in your original table.)

                       

                      If you haven't already, make sure that the "create related record option" is enabled for the Linked_images in the relationship linking it to your original Main table occurrence.

                       

                      Now go to your original layout and use the portal tool, specify Linked_images for the portal's table and add the following fields to the portal:

                      Linked_images::Image_ID

                      Linked_images:: Picture

                       

                      Format the image_Id field in the portal as a value list. Your value list should be set up to specify the Image_field in your Main Table.

                       

                      Now enter browse mode and try it out. You should be able to select a value in your drop down and see the matching picture appear in the picture field.

                      • 8. Re: Linking data with the same database
                        donok
                          

                        Hello!  Thanks again for helping me with this - but I really am not getting it...

                         

                        You ask me to "create related record option" -- but I can't find this command/option anywhere -- where do I set that? I am using FM9, if that matters.

                         

                        When I try to create a portal, it doesn't allow me to choose linked_images.  I assume this is because the layout I am using is set to "show records from" the main/original table and I have not set-up a relationship between the main table in either the child_table or the linked_images table...

                         

                        -- Don

                        • 9. Re: Linking data with the same database
                          philmodjunk
                            

                          donok wrote:

                          You ask me to "create related record option" -- but I can't find this command/option anywhere -- where do I set that? I am using FM9, if that matters.

                          I always end up guessing as to what level of expertise another forum member has.

                           

                          Select Manage | Database | Relationships

                          Find the line linking the two tables in this graph. Double click it to open a dialog box for the relationship's settings. This option is a check box at the bottom of this dialog. You'll see this option for both tables in the relationship. Click it for the new table I had you create.

                           

                          When I try to create a portal, it doesn't allow me to choose linked_images.  I assume this is because the layout I am using is set to "show records from" the main/original table and I have not set-up a relationship between the main table in either the child_table or the linked_images table...

                          You are correct. You must first create the relationship before you can create the portal.

                           

                          If this is all as new to you as it seems to me, you may want to acquire a book on Filemaker Pro or purchase some tutorials or such. Spending some time acquiring a good basic understanding of databases in general and Filemaker Pro in particular can help you make better sense of the advice you get in this forum.

                          • 10. Re: Linking data with the same database
                            donok
                              

                            Maybe I am crazy... but, this is the window I get when I double click on the link between the two tables - I am not seeing anything about "create related record option" right?:

                             

                             

                             

                            You must first create the relationship before you can create the portal.

                             So, I should create a relationship between the child_images table and the main/original table?

                             

                            And - yes - I have a book and am plowing through it.  Sorry to be sending you on this wild goose chase!

                             

                            -- Don 

                            • 11. Re: Linking data with the same database
                              philmodjunk
                                

                              You've got the right dialog box. You see 6 check boxes in two groups of three. The check box that should be selected is the uppermost check box on the right in your screen shot. I was describing the option from memory and didn't use exactly the same words. "Allow creation of records in this table via this relationship".

                               

                              "And - yes - I have a book and am plowing through it.

                              Glad to hear that.