Clarion and ACT! - An OLE Case Study - Part 2

by Tom Hebenstreit, Review Editor

Published 1998-12-01    Printer-friendly version

Download the code here

This article is the second of two that examine integrating a Clarion program with the popular ACT! contact management package from Symantec Corporation. In the first installment, we covered how to connect to, navigate through, and apply an update to an ACT! database. This time, we will be delving deeper into the internals of the ACT! Database Object, and going over some of the examples in the application which accompanies this article.

Before we begin again, though, I highly recommend that you refresh your memory by re-reading the first article before continuing with this one, as I will be referring in passing to many of the concepts and methods it covered. Go ahead, give it another look. I'll wait…

Great. Now let's dig into the way you can dynamically discover the format of -any- ACT! database.

Tell me about yourself

As I mentioned last time, one of the hurdles in communicating with an ACT! database is finding out what's inside it (the structure, in other words). Remember, we do NOT have a nice neat file definition sitting in our dictionary, so we can't just use field labels. To complicate matters further, we need to refer to each ACT! field individually by its ACT! internal Field ID number when using the 'Data' method to move data to and from the ACT! database buffer (as discussed in Part One).

Side note: I want to make clear that even though all of the examples in this article will refer to the ACT! Contact object, these methods will work the same for the rest of the ACT! objects that correspond to files (Phone numbers, Activities, Notes, Groups, etc.). For example, the sample app has procedures to list fields for both the Contact and Email objects. If you study the code, you will see that those procedures are virtually identical other than the file object they use. Also, I will not be explaining in detail most of the ACT! methods I mention here, as that is the job of those 350 pages of ACT! SDK documentation.

In any case, the challenge is to discover those Field ID numbers, and there are two ways to do that:

Standard (default) Fields

For standard fields, i.e., fields in the default ACT! file format, you can refer to the ACT! SDK documentation where they are listed in the OLE database section. For example, looking there, we can see that the Contact Company field has an ID of 25, while the Name field is 26.

This will work fine as long as the users keep the default file structure, but problems can arise as soon as changes are made. For example, the user can rename and/or resize the default fields, or even delete them altogether -- so what you think is the record status is now the name of the Contact's mother (or is missing altogether). Additionally, the user can create brand new fields on the fly which the ACT! documentation obviously doesn't know anything about. To cap it all off, there is no way within ACT! itself (the program) to find out what the ID for a given field is.

There must be a better way, you say… and you know what? You're right.

Introducing the 'Fields' object

Looking back at the ACT! object hierarchy, we see that each of the objects which corresponds to a data file (logically or physically) has a child object called the 'Fields' object. The relationship looks something like this:

Database -> Contacts -> Fields

The Fields object describes the Contacts object, and can provide everything we want to know such as field types, lengths, attributes, ID numbers, names and more.

Sounds great, huh? The kicker here, though, is that there is no documented way of creating and using the child of another OLE object within Clarion. You can't just access the Contact object and say:

MyVar = ?OLE{'Contact.Fields.Label(26)'}

because the Fields object has not been created yet -- only the Contact object has been created. (To be more exact, no LPDISPATCH interface has been created into Fields.) And how do we create such an interface and use it? Like this:

First, we must create a Cstring variable, 20 characters long. This variable will be the key to the kingdom, so to speak, as it will give us access to the Fields object (the ID for the interface to the OLE object will be placed in it). Let's examine some code taken from the example app:

!-- The Contact object can tell us how many fields it has
Count1 = ?Ole{'Contact.FieldCount'}
!** This Contact method creates a Fields object for Contact
!** and then returns an interface identifier for that new
!** object which we are placing in our Cstring(20) var
FieldsObject = ?Ole{'Contact.Fields'}
!-- Ask the Fields object how many fields Contact has
Count2 = ?Ole{FieldsObject & '.Count'}
If Count2 = 0
   Message('No fields in selected DB, open failed.' & |
           '|File: ' & ActFileName & |
           '|Short:' & shortpath(ActFileName) & |
           '|Error is ' & ?OLE{'Error'} & |
           '|Code is ' & ?OLE{'LastError'},'ERROR!')
Else
   !-- These are always the same if everything worked
   Message('Total Fields in Contact: ' & Count1 & |
           '|Total from Contact.Fields.Count: ' & Count2)
End

The key line is this one:

FieldsObject = ?Ole{'Contact.Fields'}

What we are doing here is asking ACT! to create a new Fields object for the Contact object, and to give us an interface into that new object so that we can reference it. The next most important line is the one that demonstrates how we use that interface:

Count2 = ?Ole{FieldsObject & '.Count'}

Note how we no longer mention 'Contact' when invoking the Fields method. Instead, we specify our 'Contact.Fields' interface by passing the FieldsObject Cstring to the CW OLE layer, then the Fields method we want to use. This is the shortcut that allows to use an object (Fields) created by another object.(Contacts) within the top level object (the Database).

Putting 'Fields' to work

Now that we know how to create and access a Fields object, let's take a moment to examine what it is. In Clarion terms, you could think of it is as an array which contains the unique key values (Field IDs) for a queue of field information, with one entry in each for each field. You can use the index (which is sequential) to find out what a key value (Field ID) is, then the value itself to get the queue information.

Clear as mud, huh? Let's illustrate how it works using some Fields object methods and the following file format (from the ActDemo database):

Index  Field ID  Field Name        Type        Length

  0      1       Unique Id         Unique ID     12

  1      2       Create Timestamp  TimeStamp      6

  2      3       Edit Timestamp    TimeStamp      6

  3      4       Merge Timestamp   TimeStamp      6

  4      5       Public/Private    Numeric        1

  5      6       Record Manager    Unique ID     12

  6     25       Company           String        50

  7     26       Contact           String        50

  8     27       Address1          String        50

...lots more fields

First, we use the 'FieldIDAt' method to ask "What field is at index number 7", to which ACT! responds "Field ID 26".

We then use that ID to obtain the rest of the information, e.g., asking for 'Label(26)' will return 'Contact', 'Type(26)' returns 'String' and 'Length(26)' returns 50. Thus we know that this is the Contact Name field in this ACT! database and it is a 50 character string.

A point to keep in mind is that the index number is not permanently associated with a particular field; that is the job of the Field ID. In other words, if we created a new field between Contact and Address1, the Address1 index would now be 9. Its all-important Field ID, however, would still be 27.

Ok, let's take a look at some code from the example app that demonstrates how to query a Fields object. In this case, we are going to ask for a field count, then loop through the Fields object adding some information to a browse display queue for each field.

Final notes: Remember that the index into the Fields array begins at zero instead of one, so we loop one iteration less than our field count. Also, in order to make things fit here, we will shorten the name of our FieldsObject Cstring variable to 'Fields'. Here's the code:

Count2 = ?Ole{Fields & '.Count'}

Loop idx# = 0 to Count2 - 1
  !-- First get the ID for the field
  ListQ.ID = ?OLE{Fields & '.FieldIdAt(' & idx# & ')'}

  !-- Now use the ID to get the rest of the field info
  ListQ.Label = ?OLE{Fields & '.Label(' & clip(ListQ.ID) & ')'}
  ListQ.Length= ?OLE{Fields & '.Length(' & clip(ListQ.ID) & ')'}
  nFieldType = ?OLE{Fields & '.Type(' & clip(ListQ.ID) & ')'}
  Case nFieldType
    of 0
       ListQ.FieldType = 'None'
    of 1
       ListQ.FieldType = 'String'
    of 2
       ListQ.FieldType = 'String - Uppercase'
    of 3
       ListQ.FieldType = 'String - Lowercase'
    of 4
       ListQ.FieldType = 'String - Initial Caps'
    of 5
       ListQ.FieldType = 'Numeric'
    of 6
       ListQ.FieldType = 'Numeric - Currency'
    of 7
       ListQ.FieldType = 'TimeStamp'
    of 8
       ListQ.FieldType = 'Date'
    of 9
       ListQ.FieldType = 'Time'
    of 10
       ListQ.FieldType = 'DateTime'
    of 11
       ListQ.FieldType = 'Blob'
    of 12
       ListQ.FieldType = 'Binary'
    of 13
       ListQ.FieldType = 'Unique ID'
    of 14
       ListQ.FieldType = 'Phone'
    of 15
       ListQ.FieldType = 'URL'
  Else
       ListQ.FieldType = '*Not* Documented: ' & nFieldType
  END
  !-- Add the record to the browse Queue
  Add(ListQ)
END

Now, that's not so bad, right? Here's a sample of the list generated by the sample program for an ACT! database:

Contacts Field list for an ACT! database

By the way, one other handy Fields method is 'IsSortable'. This tells us whether we can instruct ACT! to sort the database on that particular field. All told, there are over twenty Fields methods that tell you virtually everything you could want to know about a field.

Deeper and deeper into the ID…

So, now we know how to create a Fields Object and how to query it. Our final exploration of the Fields Object will cover what it tells you. Examine this following field list generated by the loop in the previous section for a file with 115 fields:

Index  Field ID  Field Name        Type        Length

  0      1       Unique Id         Unique ID     12

  1      2       Create Timestamp  TimeStamp      6

  2      3       Edit Timestamp    TimeStamp      6

  3      4       Merge Timestamp   TimeStamp      6

  4      5       Public/Private    Numeric        1

  5      6       Record Manager    Unique ID     12

  6     25       Company           String        50

  7     26       Contact           String        50

  8     27       Address1          String        50

...lots more fields

106   1032       Online Source     String         3

107   1033       Fix Flag          String         1

109    500       Create Date       Date           8

110    501       Edit Date         Date           8

111    502       Merge Date        Date           8

112    200       E-mail Address    String      -256

113    201       Note              String      -256

114    202       E-mail Login      String      -256

115    203       E-mail System     String       128

We can see that the index went up sequentially as expected, but what about these jumps in Field ID's? And those field lengths of minus 256?

The answer lies in the realization that the Contact object is exactly that – a dynamic object, not just a database record. What we see here are:

• User defined fields (brand new fields) - These are assigned numbers beginning with 1000 and are then numbered sequentially on up from there. If you examined the physical format of the DBF file for this database, you would find that 1033 ('Fix Flag') is the last field in it.
• Virtual fields for Replication - These are the 500 series IDs, and provide information about Contact replication between systems (as far as I can tell). They do not exist in the Contact DBF itself.
• Virtual Email fields - ACT! lets you have multiple email addresses for a contact, and each address can have a different transport, e.g., CompuServe, Internet, cc:Mail and so forth. One address, though, is marked as the 'primary' address, and the Contacts object automatically adds the information for that one to the end of the contact 'record' it creates for you (pretty handy, actually). These fields (and the non-primary addresses) are actually stored in another data file that has an EBF extension, and are related back to the contact record through its Unique_ID field.

By the way, the virtual fields are NOT updateable via the Contact object (they are read only). To update them, you would need to access the Email fields through, for example, the Email object. Also, within the Email object they have their own actual Field ID numbers which are not the same as the virtual field IDs.

Documenting an ACT! Database layout

Having all these Field IDs brings up a point which can be irksome in dealing with ACT!, namely, having to use those obscure numbers for everything when moving data back and forth. To ease the pain of this, and to help make your ACT! programs more readable and updateable, the sample app can create two files for any ACT! Contact or Email database. The first is a text file listing the indexes, IDs, field labels, etc., as shown in the above examples. This is extremely handy as a reference, especially when there are well over one hundred fields in a file.

The second contains the same database field labels and IDs converted into the form of Clarion Equates. For example, the format shown above comes out like this:

! Contact equates for E:\ACT\NEWALIST.DBF as of 11/29/98 at 6:58 PM
eUnique_Id           equate(1)   ! Unique ID             12
eCreate_Timestamp    equate(2)   ! TimeStamp              6
eEdit_Timestamp      equate(3)   ! TimeStamp              6
eMerge_Timestamp     equate(4)   ! TimeStamp              6
ePublic/Private      equate(5)   ! Numeric                1
eRecord_Manager      equate(6)   ! Unique ID             12
eCompany             equate(25)  ! String                50
eContact             equate(26)  ! String                50
eAddress_1           equate(27)  ! String                50

Note how spaces within the field labels are converted to underscores, and all equates are prefaced with a lower case 'e'. Using the equates makes your code much more readable, as well as insulating you from having to make changes all over your application in the case of a Field ID change. To illustrate, code like this:

COM1:CompanyName = ?OLE{'Contact.Data(25)'}
COM1:Address1 = ?OLE{'Contact.Data(27)'}

now looks like this:

COM1:CompanyName = ?OLE{'Contact.Data(eCompany)'}
COM1:Address1 = ?OLE{'Contact.Data(eAddress_1)'}

Much more readable, in my opinion.

To use the equates, all you have to do is include the generated equates file. This technique is also handy when using a 'map' file – a file which relates fields in your Clarion databases to Field IDs in an ACT! file. For ultimate flexibility, you could write the IDs out to a database or INI file, and then change your code to reference variables loaded from that file, like this:

COM1:CompanyName = ?OLE{'Contact.Data(AMA:Company)'}
COM1:Address1 = ?OLE{'Contact.Data(AMA:Address_1)'}

That way, your code doesn't need to change, even if the ACT! Field IDs for the same data fields (e.g., Company) are completely different from location to location. All you would need to change is the contents of the map file.

In any case, since you have the app file the sky is the limit. You can also easily adapt these procedures to document any of the other ACT! objects simply by changing five or six lines of code.

About the Sample Application

The application and dictionary that accompany this article provide working examples of all of the concepts discussed in both parts of the series, plus many additional ACT! methods and examples. Highlights include:

• VCR style navigation via a Form - This similar to the example screen shown in Part One. It has been enhanced to also query the Email object and load a list box with all of the email records for each contact. Note the warnings about what happens when you hit an ACT! ID with those characters that trip up the CW OLE layer (as described in Part 1). In that case, the method that sets the scope (filter) for the email records will fail and result in ALL email addresses being shown.
• Database Update - The VCR Forms allow you to edit common fields and save the changes.
• Detection and Handling of Multi-User databases - The examples all allow you to select (via FileDialog) the database you wish to investigate. If the selected database has been flagged as multi-user in ACT!, the application demonstrates how to detect that and log in as a valid user.
• Listing Fields for Contact and Email objects - You can select any ACT! database file and view the field layouts for either the Contacts or Email objects. As shown above, you can also save those layouts for both reference and to use as an equates include file.
• Simple Querying of the Contacts Object - One procedure demonstrates how to enter a query, apply it to the database, and then view the resulting data set.
• Calling a Form from a Contacts Query - The above procedure also shows how to call a VCR Form which can only scroll through the results of the query, and which interacts with the ACT! OLE object on the calling procedure's window. For example, if your query is for contacts where the state is equal to "CA", the Form will only be able to browse through those same records.

The application is provided as a Clarion4 ABC application, though there are only a few lines of ABC specific code in any of the example embeds (primarily doing a Return(Level:Fatal) rather than a ProcedureReturn, etc.). Applying the code to the Clarion templates or in CW 2.x shouldn't pose any problem, as all of the examples are based on simple Windows procedure templates (no ABC Browses, etc.). C5 users can simply load the app and automatically upgrade it. And remember, you must compile it 32-bit.

To actually run the application, you'll need to have ACT! 3.07 or above installed (the current version is 4.02). If you haven't purchased ACT!, you can download the evaluation version of the program from the Symantec web site. It will work just fine with the sample app (it is the full product, but limited to 25 records max).

One other note: For demonstration purposes, the sample app uses a TPS file buffer to hold data that is being moved back and forth from ACT!. The physical file is created in the sample directory, but never actually used.

Well, that wraps it up. Hope you found this information useful, and… happy interfacing!

Article Resources *UPDATED*

To get the ACT! SDK:

Rather than having to sign and fax back a document to get the SDK as mentioned in Part 1, Symantec has now made it available online at their web site. To download it, go to: http://www.symantec.com/act/sdk/index.html

Last Minute C5 Update

C5EE now mentions (just barely) the technique described in this article for creating and accessing child objects (see 'Clarion OLE/OCX library and object hierarchies', LRM page 742). Additionally, there seem to be three new 'Interface Properties' listed (but not explained) in the same location which might have a bearing this as well. If, after testing, I find that these new properties are useful when using OLE in situations such as ACT!, I'll post an update to these articles.

Printer-friendly version

Copyright © 1999-2008 by CoveComm Inc. All Rights Reserved. Reproduction in any form without the express written consent of CoveComm Inc., except as described in the subscription agreement, is prohibited.

Clarion Magazine ISSN 1718-9942