UDEF-Tables: from ProgId to column name in DB
Hi there,
I was wondering where exactly in the database I can find the link between a custom field (ProgId) and the corresponding column name in the database.
For example if I have a custom field "SuperOffice:45" for the person entity, how do find out the column name in the UDPERSONSMALL table (e.g. "long06")?
Unfortunately, none of the system tables (sys.columns, sys.tables etc) seem to map directly to the info found in the UDEFFIELD table and I was unable to locate any dedicated mapping table.
We do regular bulk updates of custom fields and for now the only way to find out the right column name is by changing the value in the UI and checking for differences in the UD-tables.
Any hint would be greatly appreciated!
All Replies (5)
Hi, take look here, if you are working towards the DB:
It is a kind of formula you need to be able to do the mapping.
SuperOffice UDEF - ColumnId
Hi Anders,
thank you so much!
I don't know how or why I wasn't able to find that thread myself.
Adding one more alternative to get the column name from a progID and udef table name (Base on tableId and field-offset):
DECLARE @PROGID NVARCHAR(128);
DECLARE @COLNM NVARCHAR(128);
DECLARE @TABID INTEGER;
DECLARE @COLID INTEGER;
DECLARE @OFFSET INTEGER;
DECLARE @TABNM NVARCHAR(128);
-- Specify the table name
SET @TABNM = 'UDCONTACTSMALL'
-- Specify the progID
SET @PROGID = 'SuperOffice:2'
SELECT @COLID = [columnId]
FROM [CRM7].[UDEFFIELD]
WHERE progId = @PROGID;
--SELECT @COLID AS ColumnId;
SET @TABID = @COLID >> 8;
--SELECT @TABID AS TableId
SET @OFFSET = @COLID % (@TABID * 256 - 1)
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TABNM AND ORDINAL_POSITION = @OFFSET
Hope this helps!