Article written by Selwyn Rabins in response to a message from a customer.
Assigning Images to Existing Records
Say you have a database of employees, or customers, or students. You have a large number of existing records in your database, but currently you don't have a field in the record that shows an image of each person.
Let's assume that you have a digital picture for all (or some) of the people. What's the best way of getting your existing images into your database (without doing a lot of manual work!).
There are many ways in which this could be done. This article discusses one of the techniques.
The first decision you have to make is what type of image field do you want to use. Alpha Five offers Jpeg, Bitmap and Image File Reference. The first two, Jpeg and Bitmap actually store the image in the table. The third type, Image File Reference, just stores the filename of the image in the table. The benefit of this is that your table file does not get bloated. Images files can be large, and you could quickly end up with a very large table file if you have many images.
Note that the Jpeg field type is not limited to Jpeg images. It can store .jpg, .jpeg, .bmp and .png images. The field type refers to how Alpha Five stores the data internally, not to what types of images can be stored in the fields. Similarly for the Bitmap type. Image File Reference fields can also be used to reference any of the supported image types: Jpeg, Bmp and Png.
For this article, we will use Image File Reference fields.
In this article we will show you how to get your images 'into' your Alpha Five table.
We will make the following assumptions:
You have an existing table called 'Employees'.
The table has (among others), a field called 'firstname' and a field called 'lastname'
You have images for each employee and the image filenames are of the form 'image_firstname_lastname.jpg'. For example, the image filename for John Smith is called 'Image_John_Smith.jpg'.
You have placed the images in a folder called 'Images' relative to the folder where your 'Employees' table is stored.
The first step is to add a new field to your table. To do this:
Right click on the table in the Tables/Sets tab of the Control Panel.
Select the Edit Structure command
Add a new field called 'Image' to the table and select the Image File Reference field type.
Now, we have to populate this new 'Image' field with the filename of the image for each record.
Obviously doing this by hand, one record at a time, would be hugely tedious. So, we are going to create an Xbasic script that will automate the process.
Next, go to the Code tab in the Control Panel, click the 'New' button and create a new script (using the Xbasic editor). This will open up the Xbasic editor. Paste the code shown below in blue into the script editor. Save the script and give it any name you want.
dim t as p
t = table.open("employees")
dim fn as c
dim pathName as c
dim image_filename_template as c
dim imageFolderName as c
imageFolderName = "Images"
'This is the template that describes the format of your image filenames.
image_filename_template = "Image_{alltrim(t.firstname)}_{alltrim(t.lastname)}.jpg"
'The entries enclosed in { } are placeholders that refer to
'fieldnames in your table.
'Notice that the field placeholders in the template are prefixed with 't.'.
'That's because the the table pointer that we opened is called 't'.
'It is important that the template use the same name as the table pointer
'at the start of the script!
'So, for a record where firstname was 'John' and lastname was 'Smith'
'the image filename would be Image_John_Smith.jpg.
'You can define any format that you want.
'get the folder where the table is stored.
pathName = file.filename_parse(t.filename_get(),"dp")
dim imageName as c
t.fetch_first()
dim totalcount as n
totalcount = t.records_get()
dim count as n = 0
while .not. t.fetch_eof()
count = count +1
statusbar.Set_Text("Updating " + count + " of " + totalcount)
imageName = imageFolderName + chr(92) +\
evaluate_string(image_filename_template)
'Only update the record if the imageName file actually exists.
'Note that we need to add in the pathName of the table to
'get a fully qualified filename before we check to see if it exists.
if file.exists(pathName + imageName) then
t.change_begin()
'Notice that we only store the relative filename of the image.
'This means that it will work if we move the location of the
'table and the images sub-folder.
t.Image = imageName
t.change_end(.t.)
end if
t.fetch_next()
end while
t.close()
ui_msg_box("Done","Updated " + count + " records.")
The code is commented, but let's take a look at some of the key parts of it so that you can modify it for your own use.
Firstly, it is likely that in your own case, your naming convention for the image filenames will be different than simply Image_Firstname_Lastname. Your image filenames might reference an employee id, for example.
You will see that the script contains this line:
image_filename_template = "Image_{alltrim(t.firstname)}_{alltrim(t.lastname)}.jpg"
This is the format that describes how the image filenames are constructed. Our script uses a powerful Xbasic function, evaluate_string() which evaluates expressions that are enclosed in curly brackets in a string.
You will notice that the template includes "t." in front of the fieldnames. That's because when the script opens the employees table, the table pointer name we use is 't'. So, the expression 'alltrim(t.firstname)' might evaluate to 'John' for the first record, and 'Fred' for the second record, and so on.
Notice also that the template does an alltrim() around each of the fields. That's because otherwise the data would contain trailing spaces in it, which would mess up our filenames.
If your table had a field called 'employeeid' (assumed to be a character field) and your image filename format was Image_employeeid.jpg (e.g. Image_032343.jpg), then you would change the image_filename_template to be:
image_filename_template = "Image_{alltrim(t.employeeid)}.jpg"
If employeeid was a numeric field, then your template would be (without the alltim() function):
image_filename_template = "Image_{t.employeeid}.jpg"
After the script has computed the image filename for the current record, it checks to see if this file actually does exists. If so, then it puts the record into change mode and updates the image field.
Notice that we store a relative filename in the field (e.g. images\image_john_smith.jpg). This makes the table portable. If you move the table (and the subfolder of images) to another folder on your machine, the Linked Images will still display correctly.
Assigning Images to Existing Records
Say you have a database of employees, or customers, or students. You have a large number of existing records in your database, but currently you don't have a field in the record that shows an image of each person.
Let's assume that you have a digital picture for all (or some) of the people. What's the best way of getting your existing images into your database (without doing a lot of manual work!).
There are many ways in which this could be done. This article discusses one of the techniques.
The first decision you have to make is what type of image field do you want to use. Alpha Five offers Jpeg, Bitmap and Image File Reference. The first two, Jpeg and Bitmap actually store the image in the table. The third type, Image File Reference, just stores the filename of the image in the table. The benefit of this is that your table file does not get bloated. Images files can be large, and you could quickly end up with a very large table file if you have many images.
Note that the Jpeg field type is not limited to Jpeg images. It can store .jpg, .jpeg, .bmp and .png images. The field type refers to how Alpha Five stores the data internally, not to what types of images can be stored in the fields. Similarly for the Bitmap type. Image File Reference fields can also be used to reference any of the supported image types: Jpeg, Bmp and Png.
For this article, we will use Image File Reference fields.
In this article we will show you how to get your images 'into' your Alpha Five table.
We will make the following assumptions:
You have an existing table called 'Employees'.
The table has (among others), a field called 'firstname' and a field called 'lastname'
You have images for each employee and the image filenames are of the form 'image_firstname_lastname.jpg'. For example, the image filename for John Smith is called 'Image_John_Smith.jpg'.
You have placed the images in a folder called 'Images' relative to the folder where your 'Employees' table is stored.
The first step is to add a new field to your table. To do this:
Right click on the table in the Tables/Sets tab of the Control Panel.
Select the Edit Structure command
Add a new field called 'Image' to the table and select the Image File Reference field type.
Now, we have to populate this new 'Image' field with the filename of the image for each record.
Obviously doing this by hand, one record at a time, would be hugely tedious. So, we are going to create an Xbasic script that will automate the process.
Next, go to the Code tab in the Control Panel, click the 'New' button and create a new script (using the Xbasic editor). This will open up the Xbasic editor. Paste the code shown below in blue into the script editor. Save the script and give it any name you want.
dim t as p
t = table.open("employees")
dim fn as c
dim pathName as c
dim image_filename_template as c
dim imageFolderName as c
imageFolderName = "Images"
'This is the template that describes the format of your image filenames.
image_filename_template = "Image_{alltrim(t.firstname)}_{alltrim(t.lastname)}.jpg"
'The entries enclosed in { } are placeholders that refer to
'fieldnames in your table.
'Notice that the field placeholders in the template are prefixed with 't.'.
'That's because the the table pointer that we opened is called 't'.
'It is important that the template use the same name as the table pointer
'at the start of the script!
'So, for a record where firstname was 'John' and lastname was 'Smith'
'the image filename would be Image_John_Smith.jpg.
'You can define any format that you want.
'get the folder where the table is stored.
pathName = file.filename_parse(t.filename_get(),"dp")
dim imageName as c
t.fetch_first()
dim totalcount as n
totalcount = t.records_get()
dim count as n = 0
while .not. t.fetch_eof()
count = count +1
statusbar.Set_Text("Updating " + count + " of " + totalcount)
imageName = imageFolderName + chr(92) +\
evaluate_string(image_filename_template)
'Only update the record if the imageName file actually exists.
'Note that we need to add in the pathName of the table to
'get a fully qualified filename before we check to see if it exists.
if file.exists(pathName + imageName) then
t.change_begin()
'Notice that we only store the relative filename of the image.
'This means that it will work if we move the location of the
'table and the images sub-folder.
t.Image = imageName
t.change_end(.t.)
end if
t.fetch_next()
end while
t.close()
ui_msg_box("Done","Updated " + count + " records.")
The code is commented, but let's take a look at some of the key parts of it so that you can modify it for your own use.
Firstly, it is likely that in your own case, your naming convention for the image filenames will be different than simply Image_Firstname_Lastname. Your image filenames might reference an employee id, for example.
You will see that the script contains this line:
image_filename_template = "Image_{alltrim(t.firstname)}_{alltrim(t.lastname)}.jpg"
This is the format that describes how the image filenames are constructed. Our script uses a powerful Xbasic function, evaluate_string() which evaluates expressions that are enclosed in curly brackets in a string.
You will notice that the template includes "t." in front of the fieldnames. That's because when the script opens the employees table, the table pointer name we use is 't'. So, the expression 'alltrim(t.firstname)' might evaluate to 'John' for the first record, and 'Fred' for the second record, and so on.
Notice also that the template does an alltrim() around each of the fields. That's because otherwise the data would contain trailing spaces in it, which would mess up our filenames.
If your table had a field called 'employeeid' (assumed to be a character field) and your image filename format was Image_employeeid.jpg (e.g. Image_032343.jpg), then you would change the image_filename_template to be:
image_filename_template = "Image_{alltrim(t.employeeid)}.jpg"
If employeeid was a numeric field, then your template would be (without the alltim() function):
image_filename_template = "Image_{t.employeeid}.jpg"
After the script has computed the image filename for the current record, it checks to see if this file actually does exists. If so, then it puts the record into change mode and updates the image field.
Notice that we store a relative filename in the field (e.g. images\image_john_smith.jpg). This makes the table portable. If you move the table (and the subfolder of images) to another folder on your machine, the Linked Images will still display correctly.
Comment