Alpha Video Training
Results 1 to 4 of 4

Thread: Alpha Five tip: Assigning Images to Existing Records

  1. #1
    Alpha Software Employee Richard Rabins's Avatar
    Real Name
    Richard Rabins
    Join Date
    Sep 2000

    Default Alpha Five tip: Assigning Images to Existing Records

    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 ="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
    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) +\


    '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

    '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

    end if
    end while
    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.
    Richard Rabins
    Co Chairman
    Alpha Software

  2. #2
    Real Name
    chris bell
    Join Date
    Oct 2005

    Default Re: Alpha Five tip: Assigning Images to Existing Records

    TNX Richard
    Have just updated all 20,000+ records with the above script adapted to insert a File pathname derived from global variables initiated through an apps settings table run by the autoexec script. This gives the path pointer as a string, to a scanned pdf copy (where valid) of "paper" received at the factory office and stored on a shared drive.
    “There is nothing like a dream to create the future.”
    But then again:
    Basic research is what I am doing when I don't know what I am doing and to steal ideas from one person is plagiarism; to steal from many is research!

  3. #3
    Alpha Software Employee Richard Rabins's Avatar
    Real Name
    Richard Rabins
    Join Date
    Sep 2000

    Default Re: Alpha Five tip: Assigning Images to Existing Records

    Glad this was helpful Chris. I guess it beats do this 20,000 times manually:)

    As an observation, at Alpha, we strive to find menu driven ways (such as genies or components or action scripting etc) to simplify tasks and make them as easy as possible.

    However, this example above, illustrates the benefits and advantages of also having a powerful langauge like xBasic, that empowers one to do exactly what you want when the need arises.
    Richard Rabins
    Co Chairman
    Alpha Software

  4. #4
    "Certified" Alphaholic glenschild's Avatar
    Real Name
    Glen Schild
    Join Date
    Apr 2000
    Frome, Somerset, UK

    Default Re: Alpha Five tip: Assigning Images to Existing Records

    AND with the beauty of action scripting I achieved the same for a membership system with:


    Where the field UID (unique identifier is an 8 character field containing a "number" in the form "00001234") The images are named "00001234.jpg" and stored in a sub folder called "images".

    The only thing this does not do is check to see if there is an image for a particular record but I guess if needed that could be added in a filter.



Similar Threads

  1. Assigning Events to Images
    By rfha in forum Alpha Five Version 7
    Replies: 3
    Last Post: 11-25-2005, 10:00 AM
  2. Report existing and non-existing records
    By Kevin Gillan in forum Alpha Five Version 6
    Replies: 3
    Last Post: 02-14-2005, 09:15 AM
  3. copy records with out overwriting existing table
    By Angie Boutin in forum Alpha Five Version 5
    Replies: 6
    Last Post: 07-12-2004, 09:25 PM
  4. Autoincrementing Existing Records
    By mark buonagurio in forum Alpha Five Version 5
    Replies: 2
    Last Post: 04-27-2004, 07:08 AM
  5. Re-using existing records w/o replacing them
    By Bill Harriff in forum Alpha Five Version 4
    Replies: 3
    Last Post: 03-22-2002, 09:20 AM


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts