Community
Showing results for 
Search instead for 
Do you mean 
Reply

Act 6.0 Compressed Field question

PTJ
New Member
Posts: 6
Country: United States

Act 6.0 Compressed Field question

Hi Folks, I'm hoping somebody can help me with this.

 

Short version: For Act! 6, in the raw database, what is the format or method for decoding a compressed field such as the timestamps and unique_id fields?

 

Longer version:

 

I have inherited a project from another developer (no longer here) where we are building a tool that anaylizes relationships between contacts. The source of the contacts is an Act! 6 database. In addition, I have to access the activity data, as well. The flow here is that we import the data from an Act database into our own database and run our analytics there. We then take the results and pump them into a series of charts and graphs.

 

I have written code to successfully extract all of the data from the native dBase files and import them into our target database in mySql. The problem that I am having is that the date and unique identifier fields are, according to the Act SDK documentation, compressed. Unfortunately, the SDK does not explain how to uncompress the values. Adding to my pain is that using a 3rd party tool (such as Act Exporter) has been rejected by my manager. Use of the OLE object in Act 6 is also out, as we do not use Microsoft tools such as Visual Basic - we're a C and Smalltalk shop.

 

If anybody could offer some guidance on how to convert the compressed data, I would be very appreciative.

 

Thanks in advance,

 

PJ

Platinum Elite Contributor
Posts: 14,384
Country: Australia
PTJ
New Member
Posts: 6
Country: United States

Re: Act 6.0 Compressed Field question

Thanks for the response, Mike. Unfortunately, the field values are not numeric. Here are a few of the timestamp values that I see. Again, keep in mind that this is Act 6.0.

 

^$A77
^$A=1
^$A?&
^$AD:
^$AMF
^$AY6

!!DIUK

 

I guess I need to figure out how to decode these values first. Any suggestions?

 

Thanks again,

 

PJ

 

Platinum Elite Contributor
Posts: 14,384
Country: Australia

Re: Act 6.0 Compressed Field question

Base 256 using the ASCII chart - that's the "compression" :-)


 

You will get a value (on the order of 970,000,000) that is the number of seconds that have elapsed between the time in question and Jan 1, 1970.

PTJ
New Member
Posts: 6
Country: United States

Re: Act 6.0 Compressed Field question

Mike,

 

I want to thank you for taking the time and trying to help. It means a lot to me.

 

I wish that I could say that I've been successful but converting the encoded date as a base256 value keeps resulting in a number that is too small, with a date that is around 1982. It should be 2005.

 

If you're interested in helping, I can post the code snippet here. Likewise, if you have some code that you're willing to share, that works, too.

 

Regardless, thanks again for your help, Mike.

PTJ
New Member
Posts: 6
Country: United States

Re: Act 6.0 Compressed Field question

[ Edited ]

I'm also seeing some timestamps with six characters which results in a value far into the future.

 

 

Here's a breakdown on the conversion of one of the examples listed above, "]E'B3"

Char  ASCII      Value

3      51            51.0
B      66
        16896.0
'      39
      2555904.0
E      69    1.157627904E9
]      93  3.99431958528E11
conv: 400592159283 -> Sat Sep 11 07:35:59 EDT 1982

 

Total = 4,005,921,592,835 seconds

 

Resulting date = Apr 4, 14664  03:48:03

 

If the value is to be treated as milliseconds instead of seconds, the resulting date is Sep 11 1982 07:35:59.

 

The expected date should be Sep 27, 2002 10:26:11 AM

 

 

Also, I'm struggling to decode the recurrance detail field (RECURID in the activity table). I've found information on how to translate the value, I just can't seem to decode it.

 

Lastly, for what it's worth, I tried the conversion that you have recommended for other people. Unfortunately, it does not preserve the relationships between an activity and multiple attendees (as defined in the REL table), as well as the recurrence details. The recurrence info is encoded and I have not been able to get any further information from the vendor.

 

Again, thanks for all of your help,

 

PJ

Message Edited by PTJ on 06-06-2010 12:47 PM
Platinum Elite Contributor
Posts: 14,384
Country: Australia

Re: Act 6.0 Compressed Field question

Why don't you just use the SDK?

 

You'll spend a very long time trying to access the data directly

PTJ
New Member
Posts: 6
Country: United States

Re: Act 6.0 Compressed Field question

[ Edited ]

We're not a .NET shop and, as I understand it, the SDK requires Visual Studio (?). Despite that, I looked into the SDK from home (where I could install VB Express) but could only find support going back to ACT v7. This is an ACT v6 database. I wasn't able to locate an SDK for v6.

 

I guess if I had the v6 SDK I could write an export app. Without it, I have managed to extract all of the data with the exception of the record timestamps (for creation times) and the recurrence details. 

 

PJ

 

p.s.  What are you doing up so early? Isn't it something like 4am in AUS?  Smiley Happy 

Message Edited by PTJ on 06-06-2010 01:20 PM
Platinum Elite Contributor
Posts: 14,384
Country: Australia

Re: Act 6.0 Compressed Field question

No, you do not need .Net for ACT! 6.0... it's was written well before .Net.

 

We still code apps for it.

 

I also use an export app for ACT! 6.0 - http://www.glcomputing.com.au/main/69/2

PTJ
New Member
Posts: 6
Country: United States

Re: Act 6.0 Compressed Field question

Yeah, I looked at ExportIT, as well. It's difficult to see what it's capable of doing as the demo claims to only output 10 sample records. In reality, it converted between 1 and 4 records, and only about 7 or 8 fields - none of which preserved the recurrence information or the fact that an appointment can involve multiple contacts. So, in this situation, I am not able to use it.

 

So, that leaves me with the SDK or decrypting the fields. And you're right about it not being .NET for ACT 6. I remember from my research that it is actually OLE. Still, without having the SDK itself, I'm still stuck.

 

PJ