Community
Showing results for 
Search instead for 
Do you mean 
Reply

Overflow Table Naming convention

Bronze Super Contributor
Posts: 1,231
Country: USA

Overflow Table Naming convention

Dear Support; 

What are the overflow table naming conventions for all entities?  Custom, Contacts, Groups, Companies, Opportunities, others? 

 

Has the naming convention changed from ACT 2005 to present? 

 

What are the plans on opps table? 

 

thanks

 

-- jim durkin

 

Copper Super Contributor
Posts: 138
Country: United States

Re: Overflow Table Naming convention

Yes, in 10.02 a couple of changes were made to naming and key conventions of Spillover tables.

 

For naming, the "s" to pluralize the Entityname was removed.  So in 10.02 and beyond the convention is:

"CUST_" + EntityName + Table + "_" + Timestamp

Before 10.02 it was:

"CUST_" + EntityName + "s" + Table + "_" + Timestamp"

Prior to 10.02, the Spillover table's Primary Key (PK) column was a surrogate GUID value which was generated new each time.  Then the PK from the Entity base table (such as CONTACTID for the Contact Entity's base table TBL_CONTACT) was created as a Foreign Key (FK) with a Unique Constriaint applied to it.

 

In 10.02 and beyond, the Spillover table's PK column IS the CONTACTID using the example above.  That CONTACTID is also assigned as a cascade-delete FK back to the base table (such as TBL_CONTACT) so that when the base record is deleted, any/all Spillover table records are also deleted.

 

Opportunities:

The thinking is that Opportunity joins the "Entity model" which was (re)established in 10.02.  Users are asking for extensibility and customization, similar to Contacts.  Challenge here is adopting legacy fixed and overloaded behavioral dialog, business and security rules into the new paradigm - all while still retaining the identity and essence of what an Opportunity is and somewhat how it behaves. 

 

Bottom line is that the ACT! thinking and model is becoming one of more generic and definable structure, while still retaining some recognizable baseline of what makes the ACT! Contact Management product what it always has been and is today.

 

Hope that helps.

 

Bill Blakey
ACT! Development Team
Sage Software

Bronze Super Contributor
Posts: 1,231
Country: USA

Re: Overflow Table Naming convention

Thanks Bill that was very helpful.  I have some more questions regarding OLE DB. In this example I have added a custom table named EVENTS with a field name Location.

 

Dim sql As New System.Data.OleDb.OleDbCommand("SELECT TOP 1 Location FROM VRP_CONTACT_EVENTS")

 

Using OLE

Question - Does the 'Location" field need to be the alias or displayname. ( I tested columnName which failed)

 

Question – Since its named VRP_CONTACT_EVENTS I would guess that its because the entity is attached to contacts.          
         Should I also expect to see VRP_COMPANY_EVENTS and VRP_GROUP_EVENTS when the entity is attached to Companies and/or Groups?

          If the entity is attached to all three does it also have a view for all three?

 

Question - What is the View name VRP_ when an entity has spaces like "My photos" 

 

thanks in advance.

 

-- jim durkin

Copper Super Contributor
Posts: 138
Country: United States

Re: Overflow Table Naming convention

The OLE/DB Views are constructed using the Table Displayname and the Column Displayname …generally speaking.  The “Database Structure Detail Report” from ActDiag will provide the field naming conventions include for OLE/DB Provider.  I notice, however, that it does not show the View (VRP) that it resides in, just the table name.  That example is at the end below....

 

So the EVENTS sub-entity is treated as our stock sub-entities of Notes and History.  You're correct that you will not see a "VRP_EVENTS" View, rather, you will see a View for each Entity-Subentity relationship that you have created.  You will see, as you stated, one VRP view for each such as VRP_CONTACT_EVENTS, VRP_GROUP_EVENTS and so on.

 

For field names with spaces in them, we leave the spaces.  So using the Policies example:

----------------------------------------------------------------------------------------------------
     ACT! Field: Policy Amount                                     ACT! Fieldtype: Currency(18,2)
Physical Column: CUST_PolicyAmount_083805161                    Physical Datatype: DECIMAL(18,2)
  OLE/DB Column: POLICY Policy Amount
          Alias: POLICY_AMOUNT


 

NOTE that there is actually an inaccuracy in the "OLE/DB Column" name/value above - for custom sub-entities we stopped the practice of prefixing each column name with the tablename (POLICY for example).  So in the case above, the OLE/DB column would just be "Policy Amount".  I'll correct the report source in ActDiag Smiley Happy

 

For Entity Names containing spaces, we replace spaces with an underscore (_) character.  So in your example the View would be "VRP_MY_PHOTOS".

 

Report example (which equates to the VRP_CONTACT View):

 

    ACT! Table: Contact
Physical Table: TBL_CONTACT
  Record Count: 1740
 Created On/By: Jan 10 2006  8:35AM / ACT! System
   Field Count: 83 (51 Physical, 31 Virtual, 1 Calculated)
(in approx. bytes) Supported Pagesize: 7584, Current Pagesize: 4699, Available Pagesize: 2885
 
----------------------------------------------------------------------------------------------------
     ACT! Field: Address 1                                         ACT! Fieldtype: Character(256)
Physical Column: BUSINESS_LINE1                                 Physical Datatype: NVARCHAR(256)
  OLE/DB Column: CONTACT Address 1

Bill Blakey
ACT! Development Team
Sage Software

Bronze Super Contributor
Posts: 1,231
Country: USA

Re: Overflow Table Naming convention

[ Edited ]

Thanks again Bill,

Just one thing - wouldn't "...the View would be "VRP_MY_PHOTOS"." actually be "VRP_ENTITYNAME_MY_PHOTOS".

 

So for each ACT! entity the view would look like this...

 VRP_CONTACT_MY_PHOTOS

 VRP_COMPANY_MY_PHOTOS

 VRP_GROUP_MY_PHOTOS

 

or does ACT! also create a 'base' view VRP_MY_PHOTOS ?

 

thanks

-- jim durkin

Message Edited by jimdurkin on 11-01-2008 04:13 PM
Copper Super Contributor
Posts: 138
Country: United States

Re: Overflow Table Naming convention

Sorry - you're correct.  There would be a VRP_CONTACT_MY_PHOTOS, etc.  I composed the example too quickly Smiley Happy

 

There is not a base View for MY_PHOTOS, just the Entity-specific Views that follow the Notes and History paradigm.

Bill Blakey
ACT! Development Team
Sage Software

Bronze Super Contributor
Posts: 1,231
Country: USA

Re: Overflow Table Naming convention

Bill,

"You're correct that you will not see a "VRP_EVENTS" View, rather, you will see a View for each Entity-Subentity relationship that you have created.  You will see, as you stated, one VRP view for each such as VRP_CONTACT_EVENTS, VRP_GROUP_EVENTS and so on."

 

When I create a custom table attached to only one entity this seems to work. If I attached a custom table to multiple entities I do not see the VRP_ for each entity. In fact I don't see any VRP_s created.

 

Is there a fix for this?  I am using version 11.0 but I fix/workaround for 10.2 would be very helpfull Smiley Happy

 

thanks

-- jim durkin