(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:
CAST(OpportunityIMAPIView.UserField7 AS nvarchar(500)) AS Test_UDF
OpportunityFullView ON OpportunityIMAPIView.ContactServiceID = OpportunityFullView.ContactServiceID
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.