Community
Showing results for 
Search instead for 
Do you mean 
Reply

Custom Oportunity Fields Missing from Oportunity Table - ODBC connect to ACT Database

Accepted Solution Solved
New Member
Posts: 2
Country: Australia
Accepted Solution

Custom Oportunity Fields Missing from Oportunity Table - ODBC connect to ACT Database

Sage ACT! Premium 2011 Version 13.1.111.0, Hot Fix 6

Database Located on Windows 2012 R2 

ODBC Data Source 32 Bit

SQL Server Native Client 10.0

 

Main Issue:

Reading data from ACT database.

 

I been connecting to the act database using ODBC SERVER\ACT7 for few years.. and all worked perfect .

 

One day I added a few more custom fields in ACT for the opportunity using the ACT interface.

Going back to my SQL queries to pull the data out of act database I could not see the new column names in the OPORTUNITY table .

After a lot of searching I found the ActDiag and I manage to generate a database fields detail report.

 

In the report I found all the custom opportunity added fields and their names.

The only difference is the place where they are stored.

 

If you look at the OLE/DB Column / OLEDB2 Column you will see the missing field is in OPPORTUNITY TABLE 1 / [OpportunityTable1_062832] VS the one which I can see, is in the OPPORTUNITY / [OPPORTUNITY] table.

But I can’t find the OPPORTUNITY TABLE 1 or OpportunityTable1_062832 table anywhere so I can query on it .. 

 

I am connecting to the act database using ODBC from php and also I use a visual tool the Database Fishing Tool ( DAFT )

 

Details of the custom filed I can't find in the opportunity table.

ACT! Field: ADS_Date_Sample_Rec                               ACT! Fieldtype: Date

Physical Column: CUST_ADS_Date_Sample_Rec_121944429             Physical Datatype: DATETIME

OLE/DB Column: OPPORTUNITY TABLE 1 ADS_Date_Sample_Rec

OLEDB2) Column: [OpportunityTable1_062832].[ADS_Date_Sample_Rec]

Alias: ADS_Date_Sample_Rec24         

Allow Blanks: 1  Primary: 0            Record History: 0

Protected: 0       Can Resize: 0      Can Delete: 1

Default Access: FULL ACCESS (2)               Valid Access Levels: 7 (NA RO FA)

Access List: All Users(2)

 

Filed I can find in the opportunity table.

ACT! Field: ADS_Date_Art_Deadline                             ACT! Fieldtype: Date

Physical Column: CUST_ADS_Date_Art_Deadline_062713797           Physical Datatype: DATETIME

OLE/DB Column: OPPORTUNITY ADS_Date_Art_Deadline

OLEDB2) Column: [OPPORTUNITY].[ADS_Date_Art_Deadline]

Alias: ADS_Date_Art_Deadline319        

Allow Blanks: 1  Primary: 0            Record History: 0

Protected: 0       Can Resize: 0      Can Delete: 1

Default Access: FULL ACCESS (2)               Valid Access Levels: 7 (NA RO FA)

Access List: All Users(2)

 

 

Any Help will be appreciated.

Regards

Allen


Accepted Solutions
Solution
Accepted by Gary W (Administrator)
‎01-31-2017 06:17 AM
New Member
Posts: 2
Country: Australia

Re: Custom Oportunity Fields Missing from Oportunity Table - ODBC connect to ACT Database

Found missing fields:

 

I used the ActDiag to do a database rebuild schema.

Database Repair and Database Reindex .

Not sure which one fix it but after doing that I manage to see the missing table in the database under the following name: CUST_OpportunityTable1_062832

 

I am assuming there is a limitation on how many columns you can have in a certain table and ACT automatically created the Custom Table to accommodate the extra custom field names.

 

Hope this will help others in the future .

Allen

 

View solution in original post


All Replies
Solution
Accepted by Gary W (Administrator)
‎01-31-2017 06:17 AM
New Member
Posts: 2
Country: Australia

Re: Custom Oportunity Fields Missing from Oportunity Table - ODBC connect to ACT Database

Found missing fields:

 

I used the ActDiag to do a database rebuild schema.

Database Repair and Database Reindex .

Not sure which one fix it but after doing that I manage to see the missing table in the database under the following name: CUST_OpportunityTable1_062832

 

I am assuming there is a limitation on how many columns you can have in a certain table and ACT automatically created the Custom Table to accommodate the extra custom field names.

 

Hope this will help others in the future .

Allen

 

Employee
Posts: 23
Country: United_Kingdom

Re: Custom Oportunity Fields Missing from Oportunity Table - ODBC connect to ACT Database

Thanks for posting the resolution.

 

Hopefully this will help other ACT! users in the future