1 2 Previous Next 22 Replies Latest reply on Feb 19, 2017 1:49 AM by arjen.evertse

    import images into container field in database table

    glenncogar

      Hi, I am new to FM and I am trying to set up a new database solution to hold details of products I sell.

       

      I have images of each of the products and I have created a container field in the products table in the database.

       

      I know I can drag and drop the images into the relevant container fields for each product but I have 100's of products and I am looking for a way I can bulk update the relevant products with the product image from a folder on my Mac. I can match the product images to the relevant product records using the value of a text field I already have stored in the database i.e. value of field = xxx image file for that product = xxx.jpg

       

      I was hoping I could do the updates with an import but I cant find a way of importing images to container fields.

       

      Maybe I need a script to do this?

       

      Can anyone point me in the right direction?

       

      Thanks in advance

        • 1. Re: import images into container field in database table
          mikebeargie

          If all the images are in a specific location, then yes, you can script a loop to "Insert Picture" for each file into your records.

           

          Say for instance you had all the files in your documents folder ( inside /products as a subfolder), and you have a products table with a field called "productCode" that corresponds to the image name. Also, your "image" field in your products table, and all of your images in the folder with a .jpg extension.

           

          Your script would look something like this:

           

          Go To Record/Request [first]

          Loop

             Set Variable [ $path ; Get(DocumentPath) & "/products/" & products::productCode & ".jpg" ]

             Insert Picture [ products::image ; $path ]

             Go To Record/Request [next ; exit after last ]

          End Loop

           

          You could even expand on this to get some error checking built in, so you can store a list of missing images to a "log" field in your table:

          Go To Record/Request [first]

          Loop

            Set Variable [ $path ; Get(DocumentPath) & "/products/" & products::productCode & ".jpg" ]

            Insert Picture [ products::image ; $path ]

             if [ get(lasterror) <> 0 ]

                set variable [ $log ; products::productCode & ".jpg is missing from folder" ]

            End If

            Go To Record/Request [next ; exit after last ]

          End Loop

          Set Field [ products::log ; $log ]

          Set Variable [ $logpath ; Get(DesktopPath) & "log.txt" ]

          Export Field Contents [ products::log ; $logpath ]

           

          Not too bad right, and a huge time saver!

          • 2. Re: import images into container field in database table
            erolst

            Hi Mike,

             

            at a guess, you mean …

            Mike Beargie wrote:

            If [ Get ( LastError ) ]

                  Set Variable [ $log ; List ( $log ; products::productCode & ".jpg is missing from folder" ) ]

               End If

            … otherwise you'll only log the last error.

            • 3. Re: import images into container field in database table
              mikebeargie

              nice catch! Yep, I meant to concatenate it to a list, hence the line break as the last character of my string. Using the List() function is a better method though.

              • 4. Re: import images into container field in database table
                glenncogar

                Thanks so much Mike and erolst :)

                 

                I will have to learn FMP scripting, seems like it would be very useful.

                 

                Given you two know about scripting, what would be the best way to learn? I have access to the FMP training series but the module on scripting does not go into a lot of detail.

                 

                I am used to working in scripting languages e.g. Ruby and Python and using scripts via a command line interface.  I know the FMP interface is a lot 'prettier' but some times I wish I could switch to a cli and run SQL queries against my data and scripts that access the data via an API.

                 

                am I dreaming? :)

                • 5. Re: import images into container field in database table
                  mikebeargie

                  Filemaker can be used as a data source if you want to run Java apps (via JDBC) or other apps (via ODBC) and do your scripts/queries/UI in another platform completely. There is a filemaker python interface ( https://code.google.com/p/pyfilemaker/ ) as well as a handy tool called BBox ( http://www.beezwax.net/products/bbox ) that allows you to script and execute Python, Ruby and other scripts from within filemaker.

                   

                  If you just want to query your data in a SQL format, the new ExecuteSQL() function that came out in 12 is certainly the easiest way, as it's inside of filemaker's calculation engine and requires no plugin translations, and supports mostly any variant of a SELECT statement you can throw at it.

                   

                  As for learning to script in filemaker, yes, it's mandatory to learn if you want to create filemaker solutions. Don't compare filemaker scripting to compiled/executed scripting though, you will just frustrate yourself. Filemaker scripting is more WYSIWYG, you select from a preset list of "script steps" that perform different actions. A lot of the script steps are based on things you see in the program menus (IE Copy, Paste, Duplicate Record, Delete Record, Perform Find, etc..), so in essence, scripting allows you to "automate" a process of gathering, displaying or modifying data inside of the filemaker environment.

                   

                  Lynda.com has a ton of filemaker training videos, as does vtc.com. A youtube search for filemaker scripting nets many results as well.

                  • 6. Re: import images into container field in database table
                    glenncogar

                    Thanks again Mike, that's outstanding! Lots for me to try out.  I will post back here to relate how I got on.  Maybe this topic will also be of interest/ value to others

                    • 7. Re: import images into container field in database table
                      ZWW

                      Hi,

                       

                      I had the same question

                      your explanation helped me solve the problem.

                      writing the correct path in the value window was my stumbling block.

                       

                      Many thanks

                      Ernest

                      • 8. Re: import images into container field in database table
                        philmodjunk

                        It's also possible to import all images from a specified folder using Import Records with the Folder option. This can populate a table both with images in container fields and other data such as the file name. If the filename is sufficient to tell which image matches to a given product record, this info can then be used to link the new records in this table to records in your products table. No looping script and no need to calculate file paths needed with that approach.

                        • 9. Re: import images into container field in database table
                          ZWW

                          That is right and I used this approach upon your suggestion earlier. It worked nicely.

                          But.... I wanted to learn also the looping script.

                          Thanks to Mike's help I can do it now.

                           

                          this forum is tremendously helpful

                          Ernest

                          • 10. Re: import images into container field in database table
                            ZWW

                            Dear Mike,

                             

                            Do you still read this ? Or should I contact you via your website. I wil do that too.

                            As I said before, you helped me very well with the script.

                            My question concerns the second half.

                            Since I have 300 records and only 100 pictures to import I get many messages saying that picture xxx can not be found. Is the next part meant to avoid that ? I used the string "is missing from folder¶" just like this. Is that what I should do ?

                             

                            Many thanks

                            Ernest

                             

                            if

                            [ get(lasterror) <> 0 ]

                            set variable [ $log ; products::productCode & ".jpg is missing from folder" ]

                            End If

                            • 11. Re: import images into container field in database table
                              ZWW

                              Dear Mike,

                               

                              This method has worked for me but now it does not anymore.

                               

                              I have a file with Photos: 1.jpg, 2.jpg etc

                              And a fmp file with the fields Number and Photo (container)

                              My script to import the photos from the file into the container is

                               

                              Script.jpg

                               

                              But is does not work. Can you tell me what is wrong ?

                              Many thanks

                              Ernest

                              • 12. Re: import images into container field in database table
                                BruceRobertson

                                You should take a closer look at the result of get( documentsPath ).

                                In particular, note that it ends with a "/" character.

                                And recognize that your folder string BEGINS with "/"

                                 

                                So your result will look something like this, note the double "//":

                                "/Macintosh HD/Users/Ernest/Documents//ImportTest/Photos/"

                                • 13. Re: import images into container field in database table
                                  ZWW

                                  Thank you for your suggestion.

                                  I changed the path as you indicated, but it did not help.

                                  Then I changed insert picture for insert file.

                                  Now I get the warning: the file could not be found, which seems to indicate that the path is still not correct.

                                  See below.

                                  Any other idea ?

                                  Many thanks

                                  Ernest

                                  ScriptNew.jpgwarning.jpg

                                  • 14. Re: import images into container field in database table
                                    BruceRobertson

                                    No, you really did NOT follow the recommendation at all.

                                    Now you have the document folder in there twice, and you have compounded the problem of multiple backslashes.

                                     

                                    The whole point was to get you to look at your original expression.

                                    THINK.

                                    Do ONE THING:

                                    Drop the leading backslash In your folder substring.

                                    NOT

                                    "/ImportTest/Photos/"

                                    Should be:

                                    "ImportTest/Photos/"

                                    1 of 1 people found this helpful
                                    1 2 Previous Next