Private property

April 13, 1999
by Frank Di Luzio

I regularly search DOUG for information and I have noticed that articles involving lists didn't necessarily address the issue of how to make a list from an existing file. I saw a need to demonstrate how to get information from a database into a list without having to type in every word. It just so happened that I was currently working on exactly that for myself.

The Assignment

My friend, an artist and designer, asked me if I could program a search function for a CD-ROM catalogue. The user should be able to enter an ID number or word to find a particular product on the CD-ROM. Additionally, the material the product was made of should be a criteria for the search.

General Approach

The ID number, material and description of every product already existed in Microsoft Excel along with the chapter and page where it could be found in a printed catalogue. This last bit of information could be used as a marker in Director to find the frame for that product in the finished movie.

The first step will be to turn the Excel data into something I can use in Director. Then, I will need some way of searching and retrieving the data. Finally, I will need to use the data I got to move to a frame in the movie.

The idea is to find the word or number the user is searching for by finding a property in a property list whose value is a linear list pointing to the line(s) of a seperate database text file. I could then use the property list index to retrieve those particular lines from the database text.

In order for this approach to work, I'll need to turn hundreds of entries in Excel into something I can process in Director and I will want automate the process by creating a utility that makes the property list index for me.

Export a text file from a Database

Let us assume that a database program can export its information to a text file. If the exported text uses tabs (not a valid delimiter for lists in Director) to delimit the data or if the info wasn't in a databse but rather in a formatted text file then you will have to modify the text. Use a word processor to replace the tabs with a colon (or another delimiter which doesn't appear in the data fields).

You will then have a ready to use text document similar to the example below. Each new column is seperated by a colon, or the delimiter you choose and each row is on a new line. In my case the columns in the database are as follows: ID number, Description, Material and Chapter/Page (Chapter/Page is a page and chapter reference to a page in an already existing catalogue).

000001:Adjustment Spindle for Expansion Joint:Aluminum:3/4
000002:Adjustment Spindle for Expansion Joint:Steel:5/10
000003:Bending Plier:Steel:6/1
000004:Cantilever Pole Bracket:Copper:3/4
000005:Carrying case for Hydraulic Compression Tool:Plastic:2/3

All that's left now is to save your text as an unformatted text. I'll refer to this text as the "Master Text". The Master Text is the database in a form we can use in Director. In real life you'll typically have far more than 5 lines of text. In fact, most databases are far too large for even a fast text search. Instead, you'll want to create several searchable indexes of this data to help speed up access to the data. That means I need to create these extensive property list indexes of the data. This is where a property list utility could come in handy.

What does this Property List Utility Do?

The diagram shows the simple structure used to create property lists with the Property List Maker. It is important to note that each property list is a reference to the "Master Text". Therefore, the property lists and the "Master Text" are linked. The implication is that if the data file changes after I've created my indexes then I'm screwed. Be careful in managing your files.

What I am doing is making a reference list that points to a location (line number) in Master Text (my database). The property List Maker looks at every word of a particular line item in the text and adds each new word as a property in a property list. Then the Property List Maker attaches the line number (in linear list format) as the value of that property. The number refers to the line of text currently being searched for words. If a word is already a property the Property List Maker appends the current line number to the list of that property. Therefore, as is required for property lists, every property remains unique.

The following script is an excerpt from the Property List Maker. It makes a property list in which each property is preceded by the # sign. The variable "WhichItem" is an iteger referring to a particular item (database field) in a line. The items in our "Master Text" were delimited by a colon. That is, every block of text separated by a colon is considered an item. We create a property list only for that item (WhichItem) and then go to the next line in the text and continue with the same item of that line. In this manner, we can create a property list index for any of the database's fields: for an ID number (item 1 of each line of the "Master Text") or for a Description (item 2 of each line of the "Master Text") and so on.

gDataFile = the Master Text file
gTempPropList -- the property list we make
gThisLine -- the line we are searching 
gDataList -- the number list we assign to each  property ¬
  to contain the line number
on WritePropListasSymbol WhichItem
global gDataFile, gTempPropList,  gThisLine, gDataList
  -- An item can have more than one word, so you need to 
  -- process all the words for that item
  
  put the number of words in item WhichItem of line ¬
   gThisLine of gDataFile into NrOfWords
  
  repeat with x = 1 to NrOfWords
  
    put word x of Item WhichItem of line gThisLine of ¬
      gDataFile into makesymbol
    
    --This puts a # sign in front of the word 
    put symbol(makesymbol) into thisProp
    
    -- Check to see if property is unique 
    -- and then add it to property list
    if voidP(findPos(gTempPropList, thisProp)) then
    
      -- Add  an integer to the number list 
      append gDataList, gThisLine
      setaProp gTempPropList, thisProp, gDataList
      set gDataList = []
      
    -- If the property already exists, add a new integer
    else
      set gDataList = 0
      put getaProp(gtempproplist, thisProp) into gDataList
      append gDataList, gThisLine
      setProp gTempPropList, thisProp, gDataList
      set gDataList = []
    end if
    
  end repeat
  
end WritePropListasSymbol

Let's get the utility.

In order to make the utility available during authoring, copy the .dir movie into the Director Xtras folder, restart Director and select it from the Xtras menu.

You should get a window like the one below except that the green field will be empty:

As you can see, I've already loaded my "Master Text" into the Property Maker. The text appears in the green preview field above. (The little rectangular boxes are explained later)

With the current settings step 3 would make a property list and display it in the same green field . It also prompts you to save the property list as a text file.

Notice that words appearing once in the "Master Text" have one value per property and words appearing more frequently than once contain more values. These values refer to each line number (product) in my "Master Text".

Using Lists Created with the Property List Maker

As I mentioned in "General Approach", I will use the property lists I create to point me to a particular spot in the "Master Text". Remember, the "Master Text" is the database. It contains all the information for the products being shown in a CD-ROM catalogue. Each new line of the "Master Text" represents one product. Therefore, retrieving a line in the "Master Text" will give me all the information I need about one product (ie. ID number, Description, Material and Chapter/Page). This also means that the "Master Text" is very large.

Without a property list, in order to perform a search for a word in the "Master Text", I would have to first check the text to see if that word exists at all in the text. Then, I could check blocks of that text to see if it contains that word. I would continue narrowing the check until I finally found that word. Then I could retrieve that line of text to get the information about the product which contained that search word. This kind of search would take a considerable amount of time.

With a property list to guide me, I can get the information I need directly. I search a sorted property list instead. Then it tells me where to get the information from the "Master Text". The whole process occurs in a split second.

In this example script; member "Master Text" contains the "Master Text"; member "Description Property List" contains the propety list of item 2 of the "Master Text" I made from it earlier. It also includes a search criteria for the material a product is made of.

on SearchForWholeWord
  --set variables
  put the text of member "Master Text" into Database
  put the text of member "Description Property List" ¬
    into ThisPropList
  
  -- Convert a string variable back to a List
  set ThisPropList = value(ThisPropList)
  sort ThisPropList
  
  -- Get user input and make it property
  put field "user word" into FindWord
  put field "user material" into WhichMaterial
  set FindWord = symbol(FindWord)
  
  -- If property exits then continue
  if voidP(findpos(ThisPropList, FindWord)) then
    alert "This word is not a property in my property list"
    exit
  else
  
    --Get the value attached to the  property
    set myDataList = getaProp (ThisPropList, FindWord)
    
    --how many entries in the list  from the property
    put count (myDataList) into NrOfEntries
    set the itemDelimiter = ":"
    
    repeat with x = 1 to NrOfEntries
      put getAt(myDataList, x) into WhichLine
      put item 3 of line WhichLine  of  DataBase ¬
        into ThisMaterial
      
      if  WhichMaterial = ThisMaterial then
        set MatchFound = #matched
        put line WhichLine of DataBase into field "Result"
      end if
    end repeat  
        
    if MatchFound <> #matched then
      Alert  "No matches to this word with this material."
    end if
    set the itemDelimiter = ","  
  end if
  
end SearchForWholeWord

This is a working sample of the script.

When a user types in an entry like "adjustment" in this search program I can call up lines 1 and 2 in the "Master Text". By comparing item 4 (material) of those lines with the users material, I can determine whether I have a match for that material as well as a match for the user word. Similarly, I could retrieve item 4 (Chapter/Page) to jump to that marker in a movie.

External Files

Another issue when creating movies that use this type of database access is the size of the text files you create. In my example, the "Master Text" fits into a field member. However, the largest file a field can hold is 32 Kb. In many cases, the "Master Text" and the resulting property lists will be larger than that and you will have to work with external text files. In that case, you can do your search with your property list indexes as before, but then just read in the needed lines from the text file using FileIO. My utiliy uses the FileIO xtra (which is free with Director) to open and save text files. You can modify those scripts for use in a movie. Check my references for specific information regarding the fileio.

Strip Line Feed

I've included this feature to delete those "boxes" you see when loading and displaying external text data in fields with Director for Windows. Check out the references for more information. You don't need to erase them to make a property list. The reason they seem to be missing in the property lists you create is that the property list is only one really long line on the first line in the file. The boxes appear only when there is a new line in the text delimited by a carriage return.

Download the utility in ZIP format.

The scripts in the movie are commented to help you understand what I did. I highly recommend you read my references if you are new to lists as they cover most of the background you will need. This utility worked great for my purposes. I hope this helps you create utilities that make your programming faster and less tedious.

References:

Director Technotes:

FileIO
http://www.macromedia.com/support/director/ts/documents/tn3192.html

Line Feed Character
http://www.macromedia.com/support/director/ts/documents/tn3133.html
http://www.macromedia.com/support/director/ts/documents/tn3151.html

Lists
http://www.macromedia.com/support/director/ts/documents/tn3106.html
http://www.macromedia.com/support/director/ts/documents/fmkb0879.html
http://www.macromedia.com/support/director/ts/documents/tn3122.html
http://www.macromedia.com/support/director/how/expert/lists/lists3.html

DOUG

Handyman Articles

Searching Property Lists buildArticle.php?id=10

Lists & FileIO
buildArticle.php?id=230

Searching Text Fields buildArticle.php?id=201

Frank Di Luzio has a BFA in photography from Rochester Institute of Technology. As the special effects photography he did moved from the darkroom to the computer he persued his interests in electronic media. Currently, Frank is the Director progamer for DIA3 in Munich, Germany. Born in Spain and raised in the USA, Frank is now fluent in 3 languages.