1. Here's a thread that needs an answer: Rule for Distribution List

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

Discussion in 'BCM (Business Contact Manager)' started by edumas, Apr 21, 2011.

  1. edumas

    edumas

    Member
    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)
     
  2. edumas

    edumas

    Member
    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.
     
  3. Forum Admin

    Forum Admin

    Administrator
    Re: Accessing User Defined Fields in BCM Database part 2

    Thanks for sharing!
     
  4. Squire4Hire

    Squire4Hire

    Member
    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?
     
  5. Squire4Hire

    Squire4Hire

    Member
    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).
     
  6. tlassner

    tlassner

    Senior Member
    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
     
    Last edited: Mar 21, 2014

Share This Page