UDEF-Tables: from ProgId to column name in DB

lock
push_pin
done
Besvart
5

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!

9. okt. 2024 | 09:27 a.m.

Alle Svar (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

9. okt. 2024 | 11:02 a.m.

Hi Anders,

 

thank you so much!

I don't know how or why I wasn't able to find that thread myself.

9. okt. 2024 | 12:06 p.m.
No problem, kudos goes to Marcus S, for providing the secrect mapping.
It is possible to use the sys-tables as well but you have to build up some CTEs or helper views. I found out that just using the script in the other thread works ok, put it in a SQL-view and you are done.

9. okt. 2024 | 12:26 p.m.

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!

10. okt. 2024 | 10:28 a.m.
Hi Tony,

sorry, just noticed there was another reply - that also comes in very handy, thank you very much!
22. okt. 2024 | 09:50 a.m.

Legg til svar