[SOLVED] Accessing User Defined Fields in BCM Database part 1

E

edumas

I had a heck of time solving this so I thought i'd share. Thanks to k99ja04 for pointing me in the right direction. Hopefully there aren't too many errors in this write up.

Accessing User-Defined Fields in a Business Contact Manager 2010 SQL Database

In BCM, custom fields are called user-defined fields (UDF). When a UDF is created, BCM creates entries in several tables to keep track of the new UDF, stores the actual data in binary format and labels the columns UserField# - where # is a number between 1 and 300. In order to access the data stored in a UDF (e.g. for reporting purposes), a user must find the following information:

1. FieldGUID associated with user-specified FieldName

2. DataType associated with user-specified FieldName

3. DataTypeName for the DataType (DataType is same as DataTypeID)

4. UserFieldIndex associated with FieldGUID

5. EntityType associated with UserFieldIndex

6. EntityTypeName for the EntityType (EntityType is same as EntityTypeID)

7. SQL Database View of the EntityType which also contains the UDFs

8. SQL Database Views containing information related to the EntityType View

9. Create SQL Query

a. Including Joins of related views

b. Using CAST function to view UDF Binary Data

The best way to explain this process is by example. Let’s assume a UDF has been created for the Opportunity form called Test UDF and is of type Date/Time. Now let’s assume we’d like to query the database for a list of Opportunities including the Opportunity Name, Business Contact Name and Test UDF data. We’ll start by collecting the information specified above.

Step 1: Find the FieldGUID associated with Test UDF (user-specified FieldName)

This is located in the SQL Database Table called UserFieldDefinitions

Step 2: Find the DataType associated with user-specified FieldName

This is also located in the SQL Table called UserFieldDefinitions

Step 3: Find the DataTypeName for the DataType

This is located in the SQL Table called UserFieldDataTypes

Step 4: Find the UserFieldIndex associated with the FieldGUID

This is located in the SQL Database Table called EntityUserFields

Step 5: Find the EntityType associated with UserFieldIndex

This is also located in the SQL Database Table called EntityUserFields

(continued in part 2 post)
 
E

edumas

Accessing User Defined Fields in BCM Database part 2

(continued from part 1)

Step 6: Find the EntityTypeName for the EntityType (EntityType is same as EntityTypeID)*

This is located in the SQL Database Table called EntityTypesTable

* Some fields may exist in multiple EntityTypes so you’ll need to refer back to step 4 to find the Field associated with the EntityType of interest.

Step 7: Find the SQL Database View of the EntityType which also contains the UDFs

The SQL Database Views that contain “IMAPIView” in the title are the views that contain all the UDFs. In this example, we want to use the OpportunityIMAPIView.

Step 8: Find the SQL Database Views containing information related to the EntityType View

Since the OpportunityIMAPIView only contains the UDFs, we’ll need to also reference the Opportunity View that contains the core fields as well as the Contact View that contains the core fields. In this example, we’ll need the OpportunityFullView and the ContactFullView.

Step 9: Create the SQL Query (including Joins of related Views)

Using Query Designer in SQL Server Management Studio, the final Query looks like this:

SELECT

OpportunityFullView.OpportunityName,

ContactFullView.FullName,

CAST(OpportunityIMAPIView.UserField7 AS nvarchar(500)) AS Test_UDF

FROM

OpportunityIMAPIView

INNER JOIN

OpportunityFullView ON OpportunityIMAPIView.ContactServiceID = OpportunityFullView.ContactServiceID

INNER JOIN

ContactFullView ON OpportunityFullView.ParentEntryID = ContactFullView.EntryGUID

A few final key points:

1. Step 3 was how the DataType was determined for the CAST function. In this example, UserField7 was CAST as nvarchar since the DataType was “Text”. If it had been of DataType “Date Time”, we would have used CAST as datetime.

2. Finally, from Step 4, we determined from the UserFieldIndex that we wanted UserField7 from the OpportunityIMAPIView View.
 

Squire4Hire

Member
Outlook version
Outlook 2010 64 bit
Email Account
POP3
Excellent post! This helped a heap in getting myself connected between the SQL Server and MS Access 2010.

I did however run into a problem with the CAST statement in the Access query when it came to a currency datatype. A CStr() worked fine in converting the OLE Object to plain text but it's throwing an error when attempting to use a CCur() or CDbl() for the IndexField applicable to a BCM currency datatype.

Do you have any suggestions for this?
 

Squire4Hire

Member
Outlook version
Outlook 2010 64 bit
Email Account
POP3
I have discovered the pass-through query! This allows a user, after setting up a DNS connection to the SQL Server, to connect directly to the BCM tables and pass more robust SQL Commands without having the JET Engine try to process it for you. Commands like CAST() and CONVERT are then able to change the OLE Objects into readable data - such as CAST(dbo.ContactIMAPIView.UserField as smallmoney).
 

tlassner

Senior Member
Outlook version
Outlook 2013 32 bit
Email Account
Exchange Server
Does anyone know how to cast the YesNo "Datatype"? Would be heplful for Access. I have to use access because i just prepare standard queries and our back-office has the task to cutomize it/ set the conditions/criteria.

Additional: think its 16-bit boolean
 

run404

New Member
Outlook version
Outlook 2010 32 bit
Email Account
POP3
actually i had the same trouble, i working on timeshare attorney business and the database doesnt connect i dont know why , tnx for share anyway
 

Tobias Koch

New Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server
hello everyone, luckily I was able to get most things regarding BCM Userfields even acquire. For integration to our ERP system (A View that proviedes date for our ERP). Use this integration is to automatically generate offers. Now, however, we come to the limit of 300 Userfields. Does anyone know if you can put up this border?
 

ISARALF

New Member
Outlook version
Outlook 2010 64 bit
Email Account
POP3
I had a heck of time solving this so I thought i'd share. Thanks to k99ja04 for pointing me in the right direction. Hopefully there aren't too many errors in this write up.

Accessing User-Defined Fields in a Business Contact Manager 2010 SQL Database

In BCM, custom fields are called user-defined fields (UDF). When a UDF is created, BCM creates entries in several tables to keep track of the new UDF, stores the actual data in binary format and labels the columns UserField# - where # is a number between 1 and 300. In order to access the data stored in a UDF (e.g. for reporting purposes), a user must find the following information:

1. FieldGUID associated with user-specified FieldName

2. DataType associated with user-specified FieldName

3. DataTypeName for the DataType (DataType is same as DataTypeID)

4. UserFieldIndex associated with FieldGUID

5. EntityType associated with UserFieldIndex

6. EntityTypeName for the EntityType (EntityType is same as EntityTypeID)

7. SQL Database View of the EntityType which also contains the UDFs

8. SQL Database Views containing information related to the EntityType View

9. Create SQL Query

a. Including Joins of related views

b. Using CAST function to view UDF Binary Data

The best way to explain this process is by example. Let’s assume a UDF has been created for the Opportunity form called Test UDF and is of type Date/Time. Now let’s assume we’d like to query the database for a list of Opportunities including the Opportunity Name, Business Contact Name and Test UDF data. We’ll start by collecting the information specified above.

Step 1: Find the FieldGUID associated with Test UDF (user-specified FieldName)

This is located in the SQL Database Table called UserFieldDefinitions

Step 2: Find the DataType associated with user-specified FieldName

This is also located in the SQL Table called UserFieldDefinitions

Step 3: Find the DataTypeName for the DataType

This is located in the SQL Table called UserFieldDataTypes

Step 4: Find the UserFieldIndex associated with the FieldGUID

This is located in the SQL Database Table called EntityUserFields

Step 5: Find the EntityType associated with UserFieldIndex

This is also located in the SQL Database Table called EntityUserFields

(continued in part 2 post)
[SOLVED] - Accessing User Defined Fields in BCM Database part 1
 
Similar threads
Thread starter Title Forum Replies Date
E Accessing User Defined Fields in BCM Database part 2 BCM (Business Contact Manager) 0
RBLampert Accessing Outlook accounts from multiple computers Using Outlook 8
E Accessing shared outlook folder doesn't work since switch to new outlook/excel Outlook VBA and Custom Forms 11
J Accessing calendar items for Resource Mailboxes Outlook VBA and Custom Forms 2
S Outlook [Online - Office365] perfomance is getting affected when accessing the mails using Redemptio Using Outlook 1
J Accessing Hotmail accounts with Outlook 2016 Using Outlook 3
n2b Accessing Plain Text Draft Without Outlook Using Outlook 3
G Issues with accessing Outlook Contact Picture with VBA Outlook VBA and Custom Forms 4
M Accessing ExpiryTime property Outlook VBA and Custom Forms 1
M Accessing BCM with Excel / Running Reports BCM (Business Contact Manager) 1
C Problem accessing events for folder "username" Server Threw Exception Using Outlook 1
P accessing custom task pane with Outlook ActiveInspector Using Outlook 1
mikecox Accessing Outlook on my Win7 desktop from Win8 laptop Using Outlook 1
A "Unable to display the folder" error when accessing Group mailbox Using Outlook 0
Commodore Accessing mail from multiple devices Using Outlook 1
S Programmatically accessing Outlook Calendar data Using Outlook 1
R Accessing folder 'Other Contacts' Outlook VBA and Custom Forms 1
E Accessing a Form Region from an Inspector Outlook VBA and Custom Forms 3
E Accessing a Form Region from an Inspector Outlook VBA and Custom Forms 3
D Accessing Global Address List details with VBA Outlook VBA and Custom Forms 1
H Accessing a control on a frame in Outlook with VBScript Outlook VBA and Custom Forms 1
K Programmatically accessing Outlook properties Outlook VBA and Custom Forms 3
K assistant accessing from her home computer BCM (Business Contact Manager) 2
A Accessing .pst folder in outlook using VBA Code Outlook VBA and Custom Forms 4
J Outlook closes when accessing History in BCM BCM (Business Contact Manager) 4
B Accessing Outlook 2003 Signature Programmatically using C# Outlook VBA and Custom Forms 6
L Outlook Forms-Accessing Outlook VBA and Custom Forms 1
C Accessing _RecipientControl1 (Assigned To) Outlook VBA and Custom Forms 2
K Accessing contact's custom properties in a Outlook 2007 form region Outlook VBA and Custom Forms 1
V Outlook custom form - installing and accessing Outlook VBA and Custom Forms 1
A Daily hours of recurring/ multi-day appts, & accessing mult calend Outlook VBA and Custom Forms 2
R New users creating get page cannot be displayed when accessing OWA Exchange Server Administration 16
N Item cannot be saved because it was modified by another user or window, and, Item could not be moved... Using Outlook 0
A How to stop user form from disapearing once mail window is closed? Outlook VBA and Custom Forms 0
H Information from user defined field into Excel Outlook VBA and Custom Forms 7
D Outlook 2010 Outlook in Windows 10 keeps asking for user name and password repeatedly Using Outlook 14
E Asking user to select multiple options in a list in an email Outlook VBA and Custom Forms 0
icacream Enter your user name and password for the following server. Using Outlook 4
B User defined field for messages with 'me' in the [To], [Cc] line Using Outlook 0
S User Defined Fields adding new value (2) Using Outlook 0
R Creating a user defined function Outlook VBA and Custom Forms 3
M Compile error: User-defined type not defined Outlook VBA and Custom Forms 0
M How to export Voting Results with user names and their responses Outlook VBA and Custom Forms 13
M vCard does not have user-defined fields from my custom contact form (365) Using Outlook 1
C Outlook 2016 Conditional Format for User Defined Field Using Outlook 1
G <mailto:user@domain.com<mailto:user@domain.com<mailto:user@domain.com>>> Using Outlook 5
F MAPI, User Defined Fields and perspective after 20 years Outlook VBA and Custom Forms 0
B Outlook Business Contact Manager with SQL to Excel, User Defined Fields in BCM don't sync in SQL. Can I use VBA code to copy 1 field to another? BCM (Business Contact Manager) 0
A Created a new user account and the local account cannot see email Using Outlook 3
N How to set automatically the default or user defined Quickstyle Templates by Answer in Outlook Using Outlook 1

Similar threads

Top