3 Replies Latest reply on Jan 27, 2017 11:20 AM by Charlesdh

    one to many relationship




      I started back to a file that I've been working on. From past help I can relate a "Product" to a "Image".

      As I mentioned in my previous post I have a "Product" that will have more than one image. The "Product" can only have up to 4 images.

      The image it self will be a combination of the "Product" name and a numeric value. Ie: Shelf 001, Shelf 002 ect.

      I can relate the "Product" to the image by manually inputing its Id for image.

      I have 2 tables one for the "Product" and one for the Images.

      I'm attaching the file I was working with.

      What I hoping to accomplish at this point is too be able is

      1. Import the "Images" (which I can do)

      2. Import the "File"

      3. Once importing is done the file should show show the Image's for each product .


      Any help would be appreciated.



        • 1. Re: one to many relationship

          What do you mean by "Import the file"?


          To get from step 1, which you can do, to step 3, add the product ID to the appropriate images. You can do this manually.


          If you have the Product layout open you can manually insert images into the "Picture" field via the portal. This automatically links the pictures to the product.



          • 2. Re: one to many relationship
            I can relate the "Product" to the image by manually inputing its Id for image.

            Don't need to do that.


            You want to link a product to it's images by Product ID so that one Product ID can be used to link to multiple Image Records. If the product names in the file names of your images are exactly correct for a product name in your products table and always match to only one record in Products, then you can temporarily use that match by name to pull over the Product ID so that you can use Product ID as your link once you are finished importing the files.

            1. Import the "Images" (which I can do)

            2. Import the "File"

            But that need not be more than a single Import Records operation that does both 1 and 2 in the same import. You can use the Import Records | Folder option to import all image files into container fields of a table of images in your database. When setting that up, you can designate a text field in the images table to get the file name of the image as part of the import.


            Then, to get a link by product name, another field, a calculation field of type text can strip off the number part of the file name and just return the name of the product. You can use this field to link to products by product name.

            If all file names end with a space and a 3 digit number, this calculation would work for this purpose:

            Left ( FileNameField ; Length ( FileNameField ) - 4 )


            And you can then, from a layout based on your images table, use Replace Field Contents with the calculation option to copy over the ProductID from Products into a Product ID field in Images.


            Then be sure to use a relationship that matches by Product ID for all other purposes in your database. Just use the relationship that matches by product name for this one purpose of getting the correct Product ID.

            1 of 1 people found this helpful
            • 3. Re: one to many relationship

              Thanks. My problem was thinking I needed each image associated with it's name as well as the product. Your example was great. However, for the formula you provided. The example that I used had only 3 digit for the image name. In reality it could be more than 3. So I need to be able to remove all data after the product name.


              Malcolm thanks for your reply.