13 Replies Latest reply on Oct 11, 2012 10:18 AM by GaryGriggs

    Creating a Relational from a Flat



      Creating a Relational from a Flat


           Forgive me if this has been covered elsewhere. I'm not even sure how to search on what it is I'm asking.

           I have imported an Excel file as a .csv file into FMPro 11. I did this simply out of exasperation with Excel, and the hope that FM  would help me achieve what I'm after. Excel experts at my company tell me Excel can't easily perform the task I'm asking.

           With some other fancy (facial recognition) software, I have associated specific people with photographs they are in. My Excel spreadsheet showed the photograph (Image Number), and all the people (Booking #) that are in that particular image.

           Now, I have over 17,000 Images, and about 2,500 unique Booking #'s (people). Any given Booking # can be in multiple Images, and any given Image can have 1-14 (in this case) Booking #'s in it.

           My desire is "simply" to arrange the data so that instead of having a list of Images with associated Booking #'s, I need a list of unique Booking #'s, and all the images that are association with each.

           In the attached file (1 record), you'll see how the information imported to FM. You should be able to imagine a new list with the Booking # as the primary field, and all the Image #'s pertaining to that Booking # listing out below.

           I was hoping there might be a way to separate the data into two distinct databases (Images and Booking #'s), but somehow maintain the link they already possess. I think that's wishful thinking.

           Can anybody suggest a solution to my dilemma?

           Thanks in advance!


        • 1. Re: Creating a Relational from a Flat

               Am I correct that what you are showing is a single record with 14 booking number fields and one file name for the image file?

               What do you get if there are two or three image files of the same person?

               You can definitely split this into a table of bookings that link to a Person record, which in turn links to all the images. I'd do it that way, with three tables, not two.

               Is this the only data you are importing? no Name or other ID fields about the individual booked and their images?

               Working with just the data shown, I'd set a script to loop through the 14 booking fields, creating one new Booking record for each. I'd use an auto-entered serial number field to uniquely identify each booking record and to link it to other tables. I'd include the externally generated booking number as a text field in this table so that I can perform searches on it. It may be tempting to use the booking number field for your ID field used in relationships, but it's a few percentage points safer to use an internal serial number instead.

               The same script can create a Record in the Person table before looping through the booking fields so that a serial number field from Persons can be entered into a field in the Bookings table in order to link the person to their bookings records. If there was just one image fiel as shown in this example, it could be entered in a container field defined in the Person table, but you mentioned multiple images. If that is the case, then a third table related to the Person table can be used to store all the images--one image to each related record.

          • 2. Re: Creating a Relational from a Flat

                 PMJ, thanks so much for your response.

                 Your first assumption is correct.

                 Separate images will list their corresponding people.

                 The Booking # is the person. Each person has a unique Booking #. For this example I should have listed them as people, but for my purposes the Booking # works.

                 Since the Booking # and the Image # are all unique, I wasn't sure if I needed to create another field with a unique reference of some type. Then again, I wasn't sure how all this data was going to get extracted to its own database. So, no, there wasn't any other data, other than Profile, which really isn't important to this exercise.

                 Now, before we get to the script writing, how do I actually create the separate tables to which you refer?

                 Your script suggestion sounds logical, and I believe you're on the right track. Since I know nothing about scripting, do you know of an economical service/person who could do this for me?

                 Thanks again.


            • 3. Re: Creating a Relational from a Flat

                   Tables are created in Manage | database | tables, but before you do that, there's a part that is not clear to me:


                        The Booking # is the person. Each person has a unique Booking #.

                   If that is the case, why do I see 14 different booking numbers and only one picture file in your screen shot?

              • 4. Re: Creating a Relational from a Flat

                     The Booking # pertains to only one person. Here's the deal, I work for a cruise line, and each passenger is issued a unique Booking # upon booking the cruise. For my purposes, the Booking # identifies the passenger to me. I don't require more information about them for this exercise.

                     In the example I provided, that particular image actually has 14 different people in it. It's a group shot. In my database, 14 people is the most any given image has in it. Most of the photos have two people. But, anyone can be in many different photos.

                     Attached is another screen shot from a different Layout in FM. You can see the individual images list the people that are in them, and the people are in multiple images.


                • 5. Re: Creating a Relational from a Flat

                       And here I thought people were being "booked" into a jail. surprise

                       This has the potential to complicate your relationships as a person can appear in many pictures and a picture can contain many people. This is called a "many to many" relationship--something not obvious in your original example.

                       First, you need these tables as a minimum:


                       Images::FileName = Image_Booking::FileName
                       Bookings::BookingNumber = Image_booking::BookingNumber

                       First, set up the bookings table.

                       define a table with at least one field, BookingNumber. Define a unique values, validate always validation rule in Field Options for this field.

                       Now import records from your spread sheet 14 times. Each time, map a different column in the spread sheet to the BookingNumber field. The validation rules will filter out duplicate so that you do not have more than one record in this table with a given booking number. (IF you have a different file available that lists each booking number once--even if the person does not appear in any photo, it will be easier to import from that table as you may not need to do so many different imports.)

                       Now you can define your Images table I'm going to assume that you want to see the images in your database. Define at least two fields in Images:FileName, and Image, make FileName a text field and Image a container field. Use Import Records | Folder to import the images and their file names from a folder into your database. You can import actual copies of your image files or just references to them.

                       Now all you need do is use your script to build the join table Image_booking, to link an image to all related booking numbers and vice versa. You'll need the table you already created and for which you posted a screen shot at the beginning of this thread. Run this script once through the imported records and you'll get your join table. (But use your table name in place of mine.):

                       Go to layout [ImportedData]  ---> I'll call your original table "importedData"
                       Freeze Window
                       Show All Records
                       Go to Record/request/page [First]
                          Set Variable [$Column ; value: 1 ]
                          Set Variable [$FileName ; value: ImportedData::Image Number]
                             Set Variable [$BookingNumb ; value: GetField ( "ImportedData::Booking #" & $Column]
                             If [ Not IsEmpty ( $BookingNumb ) //make sure that there is really a value in the field]
                                Go To Layout [Image_booking]
                                New Record/Request
                                Set Field [Image_Booking::FileName ; $FileName]
                                Set Field [Image_Booking::BookingNumber ; $BookingNumb]
                                Go to Layout [ImportedData]
                             End IF
                             Set Variable [$Column ; value: $Column + 1 ]
                             Exit Loop IF [ $Column > 14 ]
                          End Loop
                          Go to Record/Request/Page [ Next ; exit after last]
                       End Loop

                       If you plant to import data like this repeatedly, remove the show all records step and instead, run this script immediately after importing new data into the ImportedData table.

                       Notes about layout design:

                       With this setup, a portal to Image_Booking on the booking layout can display all pictures in which that person appears. Just include the Image field from Images inside the portal row. A portal to Image_Booking on the Image layout, can be used to list all booking numbers of people that appear in the picture. 

                  • 6. Re: Creating a Relational from a Flat

                         Wow! PMJ, you're the bomb! I will definitely pursue this. I'm outta time today, but first thing tomorrow, I'll follow your steps. Thank you so much. I will let you know how it goes. I hope someday I can return the favor.


                    • 7. Re: Creating a Relational from a Flat

                           PMJ, I don't know why I got so excited yesterday. Rarely does something work correctly for me the first time.

                           I'm sure I have something wrong in the script, but I have no idea where, not being a "scriptor". One place in particular I ran into a problem...on the "Set Variable" line after the 2nd Loop you indicated: GetField ("ImportedData::Booking #" & $Column]

                           I trust there is to be a ")" after "$Column". Also, I'm forced to choose a field listed in this layout, and I have the choise of fields "Booking #1"... through 14. So, I chose #1, but your script doesn't indicate a number. Finally, I'm presuming that is an "&" between "Booking #" and "$Column". If it's another character, I can't identify it.

                           I'm confused as to how the script will build the correct number of Booking #'s in "Image_Booking".

                           I'm including a screenshot of both the Relationships of the tables, and my current script. I hope from there you can see where I've gone astray. Thanks again for your help. I feel like we're REALLY CLOSE.

                      • 8. Re: Creating a Relational from a Flat

                             You are correct about the parenthesis. The step should be:

                             Set Variable [$BookingNumb ; value: GetField ( "ImportedData::Booking #" & $Column ) ]

                             Get Field expects text that evaluates to the name of a field. This expression calculates field names of ImportedData::Booking #1, ImportedData::Booking #2, ImportedData::Booking #3 and so forth...

                             You don't select any field by name here, everything inside the parenthesis is typed in.

                             I also don't see a container field in Images, but do see an additional fields named "Event" and "Profile?". If you have other data besides a folder of images to import into your database, you'll need two tables where we currently have one. One will store the images and filenames, the other would store this additional data in these other fields. This is ONLY if you are importing that data, not hand entering it after the fact.

                        • 9. Re: Creating a Relational from a Flat

                               PMJ, thanks again for your response. You're my only lifeline at this point. I made minor tweeks to the script, saved it, and I've run it. It processes for about 2-1/2 minutes, but doesn't appear to give me any results. "Image_Booking" is empty.

                               I didn't actually need a container field for the photo itself. At this juncture, I'm only interested in the file interaction between the photos and the people in them. The image files I've stored elsewhere. They constitute about 60 GB of data! The other fields you noted are minor details I wanted to keep track of on the images. But, again, not pertinent to the efforts we're making now.

                               Would you be amenable to contacting me offline? I want to discuss a proposition.


                               Thanks, Gary

                          • 10. Re: Creating a Relational from a Flat

                                 Best guess is that this calculation: "Imported Data::Booking #" & $Column

                                 Is not producing the exact name of the table and field. If it differs by even one character, you'll get the results that you are reporting


                                      They constitute about 60 GB of data!

                                 But of course, and this is why you would use Import Records | File to import references to the files, not the actual image files.

                                 What time zone are you in?

                            • 11. Re: Creating a Relational from a Flat

                                   I'm west coast US. I'm just north of Los Angeles.

                              • 12. Re: Creating a Relational from a Flat

                                     Sorry about that. My brain though phone number when you actually posted an email address. Check your in box.

                                     I'm PST too so if we need to talk by phone we won't have any time zone difficulties.

                                • 13. Re: Creating a Relational from a Flat

                                       Back atcha PMJ. I just want to make sure my email doesn't end up in your junk email folder. :->