1 2 Previous Next 26 Replies Latest reply on Sep 4, 2009 1:54 PM by TSGal

    Combining data into records and image container from path

    jla_1

      Title

      Combining data into records and image container from path

      Post

      I have an Excel file (or csv) that has data setup like this:

       

      doe,jane,100100,12,data1

      doe,jane,100100,12,data2

      doe,jane,100100,12,data3

      doe,john,100101,11,data1

      doe,john,100101,11,data2

      doe,john,100101,11,data3

      vandelay,art,200201,10,data1

      vandelay,art,200201,10,data2

      vandelay,art,200201,10,data3

       

      I need to get that into a filemaker db, which I have setup as Table1: last,first,id,rm#,data

       

      Now, I know the obvious, I can't just import that csv into my db as it creates 3 records for jane, 3 for john, etc...Can anyone give me a concise yet thorough (oxymoron?) way to create the tables I need to get that data into a db so that when I browse to Jane's record, it shows a field that lists her last name, first name, id, rm#, data1, data2, data 3? 

       

      The id's will always be particular to that person.  The data is basically a data1.jpg, data2.jpg file.

       

      Thanks.. 

       

       

        • 1. Re: Combining data into records and image container from path
          comment_1
            

          You can't do it exactly as you describe - nor would you want to. You should import the data twice, into two tables: People and Data (for lack of better name). Import the last name, first name, id, and rm# fields into the People table, and the id and data fields into Data.

           

          In the People table, set the id field's validation as Unique, Validate always.

           

          Define a relationship between the tables matching on id, and show the related Data records in a portal placed on a layout of People.

          • 2. Re: Combining data into records and image container from path
            jla_1
              

            Is there an easy way for FM to strip the redundant data from the csv?  Since each line in Excel may have the same name with the exception of the data at the end, FM is importing each name 3 times.  Maybe a quick way to search for duplicates in FM and remove the extras?

             

            I was going (based on your suggestion) to create two csv files to import, one with lastn,first,id,rm and another with id,data.  Then import the first csv into Table: person and the second csv into Table:data.  Am I on the right path? 

            • 3. Re: Combining data into records and image container from path
              comment_1
                

              You don't need two files to import; you can import the same file twice. The validation I have suggested will make sure each person is imported only once into People table.

               

              Is this a one-time conversion, or will you be doing this periodically?

              • 4. Re: Combining data into records and image container from path
                jla_1
                  

                I went ahead and created two csv files, and it looks like's it working!  I used the find/delete script from: http://filemakertoday.com/com/showthread.php?t=8950 to get rid of the duplicate records fromt the people import and that worked like a charm (took me awhile figuring out where to put the script first).  

                 

                Am I reaching in thinking there's a way for FM to display a thumb or import an image/reference that displays based on those data.jpg files?

                 

                I will be doing it periodically, but once only with each database. 

                • 5. Re: Combining data into records and image container from path
                  comment_1
                    

                  I believe you are making this much more complicated than it needs to be. If you will be doing this periodically, then you should import the entire .csv (as is) into a temp table (this way you only have to select it once). Then import (a) from the temp table into People, and (b) from the temp table into Data. This can be entirely scripted, so all you need to do is click a button and select the .csv file.

                   

                   


                  jla wrote:
                  Am I reaching in thinking there's a way for FM to display a thumb or import an image/reference that displays based on those data.jpg files?

                  It depends on what the actual data is. For sure, a .csv file has no pictures in it, so I guess those are references to image files? If the reference can be resolved into a path, Filemaker can display the image.

                   


                  • 6. Re: Combining data into records and image container from path
                    jla_1
                       Thank you for the help.  That makes sense the temp table import.  I'll try that next time, less time in Excel cutting and saving.  Yes, they are images, sorry I didn't clarify that.  The original Excel file was exported into a csv.  The data1.jpg, data2.jpg are references to images that reside in a sub folder from the FM database file.  Can you walk me through the steps of making that work? The folder will pretty much be the same for all databases, an /images folder.
                    • 7. Re: Combining data into records and image container from path
                      jla_1
                         I've just about searched everywhere for a solution to the last part about displaying the images.  I've found plenty of posts about batch importing,etc...I know I need to make a calculation that sets the container(s) to path/$value where the value is the image name pulled from the related field.  Am I on the right path?  Any help with the calculations would be appreciated, it's driving me nuts.
                      • 8. Re: Combining data into records and image container from path
                        comment_1
                          

                        If you have a folder named "MyPictures", located in the same directory as the .fp7 file, you can set the container field to:

                         

                        "image:MyPictures/data1.jpg"

                         

                        or make it a calculation field (with Container as the result type), with the formula returning the above text. 

                        • 9. Re: Combining data into records and image container from path
                          jla_1
                            

                          Ok, for the benefit of those in the same predicament:

                           

                          I created a field, container1, made it a type:calculation and for the calculation I used: "filewin:/c:/images/" & IMAGES::IMAGE1

                          and for the result made it container. I put the field on the main form with the images portal.

                           

                          Ok, so now I can see an icon of a jpeg with the name of the image, but no image.  I verified it was indeed seeing the image by removing it from the folder and it gave me a 'image doesn't exist' error.  How can get the actual image to display, even as a reference?  Also, some of the records have two images, maybe three images (remember the import into the images table from above) and the portal shows all the names of the images associated with that id correctly, but how can I get each image to show up on the container field?  I tried duplicating the above steps and creating a 'portal2' but it still only shows the jpeg/name from the first line in the portal, not subsequent ones.

                          • 10. Re: Combining data into records and image container from path
                            comment_1
                              

                            Try "imagewin" instead of "filewin" (actually, it should be either "imagewin" or "imagemac", according to the current system platform). That is, if you want the absolute path - with relative path, just "image" will do - as I already suggested earlier.

                             

                            The container should be in the Images table, so it calculates an image for each portal row, using the "local" data.

                            • 11. Re: Combining data into records and image container from path
                              jla_1
                                

                              Thanks, the imagewin fixed the preview.  I'm not sure I'm following you about the images table.  If I put a portal in the images table, that doesn't allow me to view the lname, fname, etc...info.  I had set it up like this:

                               

                              People:First,Last,HR,ID,container

                              Images:Image,ID

                               

                              I have the relation set between the id's.  I imported the data.jpg and matching id into Images::, then on the People layout put the first,last,hr,ID and a portal showing the image numbers from the matching id's.  That's working, but the container only shows the first image in the portal row and creating two containers only shows two of that first row.  Should I have laid this out differently? 

                              • 12. Re: Combining data into records and image container from path
                                comment_1
                                  

                                I meant:

                                 

                                People:First,Last,HR,ID

                                Images:Image,ID,container

                                 

                                You have a one-to-many relationship between People and Images. There can be ANY number of images related to a person - so it's not practical to have multiple container fields in the People table. That's actually the basic principle of a relational database.

                                • 13. Re: Combining data into records and image container from path
                                  jla_1
                                     So theres no way to have a layout that shows all the images attributed to one person?  In the above model, I have to browse through each id multiple times to see all the images attributed to that id.
                                  • 14. Re: Combining data into records and image container from path
                                    comment_1
                                      

                                    jla wrote:
                                    So theres no way to have a layout that shows all the images attributed to one person?

                                    Yes, there is: place a portal to Images on a layout of People.

                                     

                                    BTW, I already said this in my first post in this thread.

                                     


                                    1 2 Previous Next