Re:How can I view Act Database Schema

[JaimeK]

Dear All,

I am currently using the Durkin Impact Suite to assist me in making some major design changes to our Act Pro2012 Database, but am having trouble envisioning the links between different Database Tables so that I can try and correctly link all fields that I want to when renaming or creating new fields and tabs etc.

Is there any way I can get an overall view of the tables and their links to each other without purchasing a full version of some SQL Editor etc?

Thanks
Jaime

[benhamilton]

If you have Visio, this method works: http://blog.glcomputing.com.au/2009/04/getting-table-structure-and-links-from.html

Or you could use Flyspeed Query, not the same full table view, but it does let you see all the tables.

[JaimeK]

Hi Ben,

Yes I have Visio that sounds great.

Thank you!

Jaime

[JaimeK]

Hi Ben or anyone else,

Just an update to this It seems that you can only use this method if you have the premium version of Act.

Does anyone know of a method to view the database Schema that will work with Act 2012 Pro?

Thanks

Jaime

[jimdurkin]

What are you looking for?

You could install our Impact Report Designer which has a robust SQL builder. In this builder are some auto rules on the custom tables bridge/joins based on both the ACT! internal schema and the Impact custom table schema.

There is also a 'Data Dictionary' icon on the QUERY BUILDER ribbon. This data dictionary is dynamic for each database. There is an option to save and reload the dictionary. You can use the save/load options manually.  

About custom tables 

The act! SDK does not support/allow third party developers to create GUID fields. Impact Suite uses character field to store the partner/child relations.( we requested this feature a few years ago and I have no idea what the status of this request is.)

So this trick is to use the CONVERT() SQL function to convert the character back to a GUID

CONVERT('" & keyValue.ToString & "',System.Guid)

There is a downside to using CONVERT which is;  if and rows in the select return an empty field the CONVERT throws an error. ( I wish MS would change that behavior!) Always include a WHERE clause to remove the empty rows

We have a 10 minute video on the report designer here: http://durkincomputing.com/video.aspx

If you send me more specific question I could give you better examples.

-- Jim Durkin