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.
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?
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?
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.
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"
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]
Set Field [Image_Booking::FileName ; $FileName]
Set Field [Image_Booking::BookingNumber ; $BookingNumb]
Go to Layout [ImportedData]
Set Variable [$Column ; value: $Column + 1 ]
Exit Loop IF [ $Column > 14 ]
Go to Record/Request/Page [ Next ; exit after last]
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.
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.
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.
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.
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.
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?
I'm west coast US. I'm just north of Los Angeles.
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.
Back atcha PMJ. I just want to make sure my email doesn't end up in your junk email folder. :->