SuperOffice UDEF - ColumnId

lock
push_pin
done
Answered
6

Hi,

Where can I find valid range of ColumnIds used for mapping to all the UDEF fields in for instance UDCONTACTSMALL.
From what I understand from some posts and from the SDK there are sertain ranges in play here.
But I have not been able to find a complete list.

I have Found that for instance: UDcontactSmall.Long03 = 8963

Whats the logic here? Can it some how be computed , whats the columId for UDcontactSmall.string05 ?
Seems to be 9025, if I look in some databases.
And for project UDEFs it seems to be 36353 and up ? 

Did also find this text sdk:
The column id is the dictionary id of the table and field used for storing the value..
36358 = UdProjSmall.Long06 (not indexed --> not fast to search)
36353 = UdProjSmall.Long01 (indexed field)

Where can info about the dictionary be found? Was not the dictionary discontinued when CDD was introduced?

Here is an old but still relevant post I guess
https://community.superoffice.com/en/developer/forum/rooms/topic/superoffice-product-api-group/crm-windows-application/udeffield-columnid/

Best regards

/Anders

 

27 Feb 2019 | 12:00 AM

All Replies (6)

I'll give it a try...
The UDEFFIELD record:

columnId is a physical field id, pointing to a field of some UD record, as in your example:

              kUdcontSmallLong03 = 8963, // long value 3, indexed of a UDCONTACTSMALL record

This is NOT garanteed to remain unique for the entire life of the defined UDef field. When you edit this contact udef field, the chances are it can get moved to another "place" in the udcontactsmall or large record. If so, columnId will change.
These field id are old and remain statically known. CDD does not affect them.

udefIdentity was introduced, in SIX I believe, is generated when a udef field is defined in admin and WILL remain so for the entire live of the udef field. It can be used to uniquely identity a udef field even after it was moved/converted to another value.
It is managed by us.

progId was introduced later and is a string identifier that works as the udefIdentity. Admin suggests a new progId e.g. superoffice:1 when defining a new udef field but you are allowed to change it for your own special needs. This will also remain valid for the entire life of a udef field unless it got changed during a edit! You can use it in our API's to get the udef field definition.

So, as I don't know what you are trying to achieve, I believe the best practice is to use a progId string for identiying a udef field in you scripts a.o.

Reading a udef field is a 2-stage process. Though you could use the columnId to get to a value, you cannot know what the value represents unless you bind it with the relevant udeffield record in order to be able to know how to interpret the value.

 

Conrad

27 Feb 2019 | 12:00 AM

ColumnId = tablenumber * 256 + fieldnumber

So 36353  would be first field (long01) of tablenumber 142 since the primarykey is fieldnumber 0.

27 Feb 2019 | 12:00 AM

Hi

Thanks both to you Conrad and to you Margrethe.

I am not buildning somthing special I was just curious to know how to backtrace a certain field and the only way of nowing this from the UDEFFIELD table seemed to be the columnId. I could by looking to the Ids in some sampledatabase see and understand that it points to some certain place in for example UDCONTACTSMALL. 

For current version (MAX on every field) I guess the columnid then is correct pointning to a certain field in one of the UD SMALL/LARGE for all the entities (Contact, Person, Project , Sale , Appointment/document) just as Margrethe stated by here formula. How else would the system keep track of where to find a certain fields value in UDCONTACTSMALL for instance?

I understand that columnid will change if for instance you make a field index eg data will be moved by admin-client from string05 to string04. The columid for the field will be changed from 8965 to 8961 (if no fiedd was previous indexed)

So formula for first non indexed string field (String05 ) in udcontactsmall = position 66
256*35(tablenumer for contact according to SDK for SO 7.5) + 66-1 = 9025 (we need to remove 1 from since UDefField_Id is first field in the UD-SMALL/LARGE tables)

By SQL-code:

select 256*35 + (
SELECT ORDINAL_POSITION
FROM SuperOffice7.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'UDCONTACTSMALL' and COLUMN_NAME ='string05') -1 --Need to take away the first id-value column

 

By the way:
Where to find the Offline SDK for 8.X ? Does it exist? 

 

Best regards

Anders

27 Feb 2019 | 12:00 AM

Hi Anders,

An easy assumption to do, is to use the version with the highest number. I suppose this might give to the correct answer in most cases, but it is actually not the correct way, if I have understood everything correctly.

Actually the correct version to use, is saved as a setting in the userpreference-table.

I have a meeega long SQL-query that I have used in some cases, to get the correct udef-columnname in the database, based on the progid. It is a query that initially was built by a colleague of me, but verified, completed and a bit changed by me. I have also added some explanations of different parts, based on how I have interpreted the information found about these parts.

It should be correct, but I don't give any guarantees. So use it on your own risk! Maybe Margrethe can do a quick verification of the query and my comments in the script? :)

I have seen more elegant versions that uses more dynamic ways of creating the same result in SQL, but I haven't had the time to verify the output, so I have sticked to this one in some cases.

Why would you want this? Well, it has been used in onsite solutions when doing Business Intelligence-extracts that fetches the wanted data only using SQL.

I have used it in .Net batch scripts (not netserver scripts) that uses SQL for some more advanced joins, but where I still want to be using the ProgId to fetch the actual coulumnname to use in the query. Actually in those cases, I have used it as a way to verify that a certain ProgId actually uses the expected columnname, and if not, the script fails. This to avoid executions with udef-fields that might have gotten moved. Then I have saved the progid with the expected columnname as a setting in the app.config, so it easily could be changed if needed. That kind of change of udef-fields in SO would imply a quite extensive change, that might not be that common, but it could happen.

There might be some nice SO-class that can give you the same result in .NET nowadays, but if you are using pure SQL or SearchEngines in CRMScript, the understanding of this could be useful at least.

Using the ProgId is always the preferred way to go but if you implement a solution that uses udef-columns directly, you should always take measures to have some kind of ProgId<->current columnname-verification method that is run first to verify that you don't do executions with a field that might have moved.

 

/* ######################################################################################################################################
Return a list of the active UDF fields in SuperOffice
-----------------------------------------------------
Created By: Marcus Svenningsson
Company: SuperOffice Sweden AB
Date: 2018-04-06
###################################################################################################################################### */

--Choose correct DB first
--USE superoffice

SELECT fieldlabel, progId, columnid, 'UdefDbField' = CASE columnid
	WHEN 8961 THEN 'udcontactsmall.long01'
	WHEN 8962 THEN 'udcontactsmall.long02'
	WHEN 8963 THEN 'udcontactsmall.long03'
	WHEN 8964 THEN 'udcontactsmall.long04'
	WHEN 8965 THEN 'udcontactsmall.long05'
	WHEN 8966 THEN 'udcontactsmall.long06'
	WHEN 8967 THEN 'udcontactsmall.long07'
	WHEN 8968 THEN 'udcontactsmall.long08'
	WHEN 8969 THEN 'udcontactsmall.long09'
	WHEN 8970 THEN 'udcontactsmall.long10'
	WHEN 8971 THEN 'udcontactsmall.long11'
	WHEN 8972 THEN 'udcontactsmall.long12'
	WHEN 8973 THEN 'udcontactsmall.long13'
	WHEN 8974 THEN 'udcontactsmall.long14'
	WHEN 8975 THEN 'udcontactsmall.long15'
	WHEN 8976 THEN 'udcontactsmall.long16'
	WHEN 8977 THEN 'udcontactsmall.long17'
	WHEN 8978 THEN 'udcontactsmall.long18'
	WHEN 8979 THEN 'udcontactsmall.long19'
	WHEN 8980 THEN 'udcontactsmall.long20'
	WHEN 8981 THEN 'udcontactsmall.long21'
	WHEN 8982 THEN 'udcontactsmall.long22'
	WHEN 8983 THEN 'udcontactsmall.long23'
	WHEN 8984 THEN 'udcontactsmall.long24'
	WHEN 8985 THEN 'udcontactsmall.long25'
	WHEN 8986 THEN 'udcontactsmall.long26'
	WHEN 8987 THEN 'udcontactsmall.long27'
	WHEN 8988 THEN 'udcontactsmall.long28'
	WHEN 8989 THEN 'udcontactsmall.long29'
	WHEN 8990 THEN 'udcontactsmall.long30'
	WHEN 8991 THEN 'udcontactsmall.long31'
	WHEN 8992 THEN 'udcontactsmall.long32'
	WHEN 8993 THEN 'udcontactsmall.long33'
	WHEN 8994 THEN 'udcontactsmall.long34'
	WHEN 8995 THEN 'udcontactsmall.long35'
	WHEN 8996 THEN 'udcontactsmall.long36'
	WHEN 8997 THEN 'udcontactsmall.long37'
	WHEN 8998 THEN 'udcontactsmall.long38'
	WHEN 8999 THEN 'udcontactsmall.long39'
	WHEN 9000 THEN 'udcontactsmall.long40'
	WHEN 9001 THEN 'udcontactsmall.long41'
	WHEN 9002 THEN 'udcontactsmall.long42'
	WHEN 9003 THEN 'udcontactsmall.long43'
	WHEN 9004 THEN 'udcontactsmall.long44'
	WHEN 9005 THEN 'udcontactsmall.long45'
	WHEN 9006 THEN 'udcontactsmall.long46'
	WHEN 9007 THEN 'udcontactsmall.long47'
	WHEN 9008 THEN 'udcontactsmall.long48'
	WHEN 9009 THEN 'udcontactsmall.long49'
	WHEN 9010 THEN 'udcontactsmall.long50'
	WHEN 9011 THEN 'udcontactsmall.long51'
	WHEN 9012 THEN 'udcontactsmall.long52'
	WHEN 9013 THEN 'udcontactsmall.long53'
	WHEN 9014 THEN 'udcontactsmall.long54'
	WHEN 9015 THEN 'udcontactsmall.long55'
	WHEN 9016 THEN 'udcontactsmall.long56'
	WHEN 9017 THEN 'udcontactsmall.long57'
	WHEN 9018 THEN 'udcontactsmall.long58'
	WHEN 9019 THEN 'udcontactsmall.long59'
	WHEN 9020 THEN 'udcontactsmall.long60'
	WHEN 9021 THEN 'udcontactsmall.string01'
	WHEN 9022 THEN 'udcontactsmall.string02'
	WHEN 9023 THEN 'udcontactsmall.string03'
	WHEN 9024 THEN 'udcontactsmall.string04'
	WHEN 9025 THEN 'udcontactsmall.string05'
	WHEN 9026 THEN 'udcontactsmall.string06'
	WHEN 9027 THEN 'udcontactsmall.string07'
	WHEN 9028 THEN 'udcontactsmall.string08'
	WHEN 9029 THEN 'udcontactsmall.string09'
	WHEN 9030 THEN 'udcontactsmall.string10'
	WHEN 9031 THEN 'udcontactsmall.string11'
	WHEN 9032 THEN 'udcontactsmall.string12'
	WHEN 9033 THEN 'udcontactsmall.string13'
	WHEN 9034 THEN 'udcontactsmall.string14'
	WHEN 9035 THEN 'udcontactsmall.string15'
	WHEN 9036 THEN 'udcontactsmall.string16'
	WHEN 9037 THEN 'udcontactsmall.string17'
	WHEN 9038 THEN 'udcontactsmall.string18'
	WHEN 9039 THEN 'udcontactsmall.string19'
	WHEN 9040 THEN 'udcontactsmall.string20'
	WHEN 9041 THEN 'udcontactsmall.string21'
	WHEN 9042 THEN 'udcontactsmall.string22'
	WHEN 9043 THEN 'udcontactsmall.string23'
	WHEN 9044 THEN 'udcontactsmall.string24'
	WHEN 9045 THEN 'udcontactsmall.string25'
	WHEN 9046 THEN 'udcontactsmall.string26'
	WHEN 9047 THEN 'udcontactsmall.string27'
	WHEN 9048 THEN 'udcontactsmall.string28'
	WHEN 9049 THEN 'udcontactsmall.string29'
	WHEN 9050 THEN 'udcontactsmall.string30'
	WHEN 9051 THEN 'udcontactsmall.string31'
	WHEN 9052 THEN 'udcontactsmall.string32'
	WHEN 9053 THEN 'udcontactsmall.string33'
	WHEN 9054 THEN 'udcontactsmall.string34'
	WHEN 9055 THEN 'udcontactsmall.string35'
	WHEN 9056 THEN 'udcontactsmall.string36'
	WHEN 9057 THEN 'udcontactsmall.string37'
	WHEN 9058 THEN 'udcontactsmall.string38'
	WHEN 9059 THEN 'udcontactsmall.string39'
	WHEN 9060 THEN 'udcontactsmall.string40'
	WHEN 9061 THEN 'udcontactsmall.double01'
	WHEN 9062 THEN 'udcontactsmall.double02'
	WHEN 9063 THEN 'udcontactsmall.double03'
	WHEN 9064 THEN 'udcontactsmall.double04'
	WHEN 9065 THEN 'udcontactsmall.double05'
	WHEN 9066 THEN 'udcontactsmall.double06'
	WHEN 9067 THEN 'udcontactsmall.double07'
	WHEN 9068 THEN 'udcontactsmall.double08'
	WHEN 9069 THEN 'udcontactsmall.double09'
	WHEN 9070 THEN 'udcontactsmall.double10'

	WHEN 9217 THEN 'udcontactlarge.string41'
	WHEN 9218 THEN 'udcontactlarge.string42'
	WHEN 9219 THEN 'udcontactlarge.string43'
	WHEN 9220 THEN 'udcontactlarge.string44'
	WHEN 9221 THEN 'udcontactlarge.string45'
	WHEN 9222 THEN 'udcontactlarge.string46'
	WHEN 9223 THEN 'udcontactlarge.string47'
	WHEN 9224 THEN 'udcontactlarge.string48'
	WHEN 9225 THEN 'udcontactlarge.string49'

	WHEN 35841 THEN 'udpersonsmall.long01'
	WHEN 35842 THEN 'udpersonsmall.long02'
	WHEN 35843 THEN 'udpersonsmall.long03'
	WHEN 35844 THEN 'udpersonsmall.long04'
	WHEN 35845 THEN 'udpersonsmall.long05'
	WHEN 35846 THEN 'udpersonsmall.long06'
	WHEN 35847 THEN 'udpersonsmall.long07'
	WHEN 35848 THEN 'udpersonsmall.long08'
	WHEN 35849 THEN 'udpersonsmall.long09'
	WHEN 35850 THEN 'udpersonsmall.long10'
	WHEN 35851 THEN 'udpersonsmall.long11'
	WHEN 35852 THEN 'udpersonsmall.long12'
	WHEN 35853 THEN 'udpersonsmall.long13'
	WHEN 35854 THEN 'udpersonsmall.long14'
	WHEN 35855 THEN 'udpersonsmall.long15'
	WHEN 35856 THEN 'udpersonsmall.long16'
	WHEN 35857 THEN 'udpersonsmall.long17'
	WHEN 35858 THEN 'udpersonsmall.long18'
	WHEN 35859 THEN 'udpersonsmall.long19'
	WHEN 35860 THEN 'udpersonsmall.long20'
	WHEN 35861 THEN 'udpersonsmall.long21'
	WHEN 35862 THEN 'udpersonsmall.long22'
	WHEN 35863 THEN 'udpersonsmall.long23'
	WHEN 35864 THEN 'udpersonsmall.long24'
	WHEN 35865 THEN 'udpersonsmall.long25'
	WHEN 35866 THEN 'udpersonsmall.long26'
	WHEN 35867 THEN 'udpersonsmall.long27'
	WHEN 35868 THEN 'udpersonsmall.long28'
	WHEN 35869 THEN 'udpersonsmall.long29'
	WHEN 35870 THEN 'udpersonsmall.long30'
	WHEN 35871 THEN 'udpersonsmall.long31'
	WHEN 35872 THEN 'udpersonsmall.long32'
	WHEN 35873 THEN 'udpersonsmall.long33'
	WHEN 35874 THEN 'udpersonsmall.long34'
	WHEN 35875 THEN 'udpersonsmall.long35'
	WHEN 35876 THEN 'udpersonsmall.long36'
	WHEN 35877 THEN 'udpersonsmall.long37'
	WHEN 35878 THEN 'udpersonsmall.long38'
	WHEN 35879 THEN 'udpersonsmall.long39'
	WHEN 35880 THEN 'udpersonsmall.long40'
	WHEN 35881 THEN 'udpersonsmall.long41'
	WHEN 35882 THEN 'udpersonsmall.long42'
	WHEN 35883 THEN 'udpersonsmall.long43'
	WHEN 35884 THEN 'udpersonsmall.long44'
	WHEN 35885 THEN 'udpersonsmall.long45'
	WHEN 35886 THEN 'udpersonsmall.long46'
	WHEN 35887 THEN 'udpersonsmall.long47'
	WHEN 35888 THEN 'udpersonsmall.long48'
	WHEN 35889 THEN 'udpersonsmall.long49'
	WHEN 35890 THEN 'udpersonsmall.long50'
	WHEN 35891 THEN 'udpersonsmall.long51'
	WHEN 35892 THEN 'udpersonsmall.long52'
	WHEN 35893 THEN 'udpersonsmall.long53'
	WHEN 35894 THEN 'udpersonsmall.long54'
	WHEN 35895 THEN 'udpersonsmall.long55'
	WHEN 35896 THEN 'udpersonsmall.long56'
	WHEN 35897 THEN 'udpersonsmall.long57'
	WHEN 35898 THEN 'udpersonsmall.long58'
	WHEN 35899 THEN 'udpersonsmall.long59'
	WHEN 35900 THEN 'udpersonsmall.long60'
	WHEN 35901 THEN 'udpersonsmall.string01'
	WHEN 35902 THEN 'udpersonsmall.string02'
	WHEN 35903 THEN 'udpersonsmall.string03'
	WHEN 35904 THEN 'udpersonsmall.string04'
	WHEN 35905 THEN 'udpersonsmall.string05'
	WHEN 35906 THEN 'udpersonsmall.string06'
	WHEN 35907 THEN 'udpersonsmall.string07'
	WHEN 35908 THEN 'udpersonsmall.string08'
	WHEN 35909 THEN 'udpersonsmall.string09'
	WHEN 35910 THEN 'udpersonsmall.string10'
	WHEN 35911 THEN 'udpersonsmall.string11'
	WHEN 35912 THEN 'udpersonsmall.string12'
	WHEN 35913 THEN 'udpersonsmall.string13'
	WHEN 35914 THEN 'udpersonsmall.string14'
	WHEN 35915 THEN 'udpersonsmall.string15'
	WHEN 35916 THEN 'udpersonsmall.string16'
	WHEN 35917 THEN 'udpersonsmall.string17'
	WHEN 35918 THEN 'udpersonsmall.string18'
	WHEN 35919 THEN 'udpersonsmall.string19'
	WHEN 35920 THEN 'udpersonsmall.string20'
	WHEN 35921 THEN 'udpersonsmall.string21'
	WHEN 35922 THEN 'udpersonsmall.string22'
	WHEN 35923 THEN 'udpersonsmall.string23'
	WHEN 35924 THEN 'udpersonsmall.string24'
	WHEN 35925 THEN 'udpersonsmall.string25'
	WHEN 35926 THEN 'udpersonsmall.string26'
	WHEN 35927 THEN 'udpersonsmall.string27'
	WHEN 35928 THEN 'udpersonsmall.string28'
	WHEN 35929 THEN 'udpersonsmall.string29'
	WHEN 35930 THEN 'udpersonsmall.string30'
	WHEN 35931 THEN 'udpersonsmall.string31'
	WHEN 35932 THEN 'udpersonsmall.string32'
	WHEN 35933 THEN 'udpersonsmall.string33'
	WHEN 35934 THEN 'udpersonsmall.string34'
	WHEN 35935 THEN 'udpersonsmall.string35'
	WHEN 35936 THEN 'udpersonsmall.string36'
	WHEN 35937 THEN 'udpersonsmall.string37'
	WHEN 35938 THEN 'udpersonsmall.string38'
	WHEN 35939 THEN 'udpersonsmall.string39'
	WHEN 35940 THEN 'udpersonsmall.string40'
	WHEN 35941 THEN 'udpersonsmall.double01'
	WHEN 35942 THEN 'udpersonsmall.double02'
	WHEN 35943 THEN 'udpersonsmall.double03'
	WHEN 35944 THEN 'udpersonsmall.double04'
	WHEN 35945 THEN 'udpersonsmall.double05'
	WHEN 35946 THEN 'udpersonsmall.double06'
	WHEN 35947 THEN 'udpersonsmall.double07'
	WHEN 35948 THEN 'udpersonsmall.double08'
	WHEN 35949 THEN 'udpersonsmall.double09'
	WHEN 35950 THEN 'udpersonsmall.double10'

	WHEN 36097 THEN 'udpersonlarge.string41'
	WHEN 36098 THEN 'udpersonlarge.string42'
	WHEN 36099 THEN 'udpersonlarge.string43'
	WHEN 36100 THEN 'udpersonlarge.string44'
	WHEN 36101 THEN 'udpersonlarge.string45'
	WHEN 36102 THEN 'udpersonlarge.string46'
	WHEN 36103 THEN 'udpersonlarge.string47'
	WHEN 36104 THEN 'udpersonlarge.string48'
	WHEN 36105 THEN 'udpersonlarge.string49'

	WHEN 36353 THEN 'udprojectsmall.long01'
	WHEN 36354 THEN 'udprojectsmall.long02'
	WHEN 36355 THEN 'udprojectsmall.long03'
	WHEN 36356 THEN 'udprojectsmall.long04'
	WHEN 36357 THEN 'udprojectsmall.long05'
	WHEN 36358 THEN 'udprojectsmall.long06'
	WHEN 36359 THEN 'udprojectsmall.long07'
	WHEN 36360 THEN 'udprojectsmall.long08'
	WHEN 36361 THEN 'udprojectsmall.long09'
	WHEN 36362 THEN 'udprojectsmall.long10'
	WHEN 36363 THEN 'udprojectsmall.long11'
	WHEN 36364 THEN 'udprojectsmall.long12'
	WHEN 36365 THEN 'udprojectsmall.long13'
	WHEN 36366 THEN 'udprojectsmall.long14'
	WHEN 36367 THEN 'udprojectsmall.long15'
	WHEN 36368 THEN 'udprojectsmall.long16'
	WHEN 36369 THEN 'udprojectsmall.long17'
	WHEN 36370 THEN 'udprojectsmall.long18'
	WHEN 36371 THEN 'udprojectsmall.long19'
	WHEN 36372 THEN 'udprojectsmall.long20'
	WHEN 36373 THEN 'udprojectsmall.long21'
	WHEN 36374 THEN 'udprojectsmall.long22'
	WHEN 36375 THEN 'udprojectsmall.long23'
	WHEN 36376 THEN 'udprojectsmall.long24'
	WHEN 36377 THEN 'udprojectsmall.long25'
	WHEN 36378 THEN 'udprojectsmall.long26'
	WHEN 36379 THEN 'udprojectsmall.long27'
	WHEN 36380 THEN 'udprojectsmall.long28'
	WHEN 36381 THEN 'udprojectsmall.long29'
	WHEN 36382 THEN 'udprojectsmall.long30'
	WHEN 36383 THEN 'udprojectsmall.long31'
	WHEN 36384 THEN 'udprojectsmall.long32'
	WHEN 36385 THEN 'udprojectsmall.long33'
	WHEN 36386 THEN 'udprojectsmall.long34'
	WHEN 36387 THEN 'udprojectsmall.long35'
	WHEN 36388 THEN 'udprojectsmall.long36'
	WHEN 36389 THEN 'udprojectsmall.long37'
	WHEN 36390 THEN 'udprojectsmall.long38'
	WHEN 36391 THEN 'udprojectsmall.long39'
	WHEN 36392 THEN 'udprojectsmall.long40'
	WHEN 36393 THEN 'udprojectsmall.long41'
	WHEN 36394 THEN 'udprojectsmall.long42'
	WHEN 36395 THEN 'udprojectsmall.long43'
	WHEN 36396 THEN 'udprojectsmall.long44'
	WHEN 36397 THEN 'udprojectsmall.long45'
	WHEN 36398 THEN 'udprojectsmall.long46'
	WHEN 36399 THEN 'udprojectsmall.long47'
	WHEN 36400 THEN 'udprojectsmall.long48'
	WHEN 36401 THEN 'udprojectsmall.long49'
	WHEN 36402 THEN 'udprojectsmall.long50'
	WHEN 36403 THEN 'udprojectsmall.long51'
	WHEN 36404 THEN 'udprojectsmall.long52'
	WHEN 36405 THEN 'udprojectsmall.long53'
	WHEN 36406 THEN 'udprojectsmall.long54'
	WHEN 36407 THEN 'udprojectsmall.long55'
	WHEN 36408 THEN 'udprojectsmall.long56'
	WHEN 36409 THEN 'udprojectsmall.long57'
	WHEN 36410 THEN 'udprojectsmall.long58'
	WHEN 36411 THEN 'udprojectsmall.long59'
	WHEN 36412 THEN 'udprojectsmall.long60'
	WHEN 36413 THEN 'udprojectsmall.string01'
	WHEN 36414 THEN 'udprojectsmall.string02'
	WHEN 36415 THEN 'udprojectsmall.string03'
	WHEN 36416 THEN 'udprojectsmall.string04'
	WHEN 36417 THEN 'udprojectsmall.string05'
	WHEN 36418 THEN 'udprojectsmall.string06'
	WHEN 36419 THEN 'udprojectsmall.string07'
	WHEN 36420 THEN 'udprojectsmall.string08'
	WHEN 36421 THEN 'udprojectsmall.string09'
	WHEN 36422 THEN 'udprojectsmall.string10'
	WHEN 36423 THEN 'udprojectsmall.string11'
	WHEN 36424 THEN 'udprojectsmall.string12'
	WHEN 36425 THEN 'udprojectsmall.string13'
	WHEN 36426 THEN 'udprojectsmall.string14'
	WHEN 36427 THEN 'udprojectsmall.string15'
	WHEN 36428 THEN 'udprojectsmall.string16'
	WHEN 36429 THEN 'udprojectsmall.string17'
	WHEN 36430 THEN 'udprojectsmall.string18'
	WHEN 36431 THEN 'udprojectsmall.string19'
	WHEN 36432 THEN 'udprojectsmall.string20'
	WHEN 36433 THEN 'udprojectsmall.string21'
	WHEN 36434 THEN 'udprojectsmall.string22'
	WHEN 36435 THEN 'udprojectsmall.string23'
	WHEN 36436 THEN 'udprojectsmall.string24'
	WHEN 36437 THEN 'udprojectsmall.string25'
	WHEN 36438 THEN 'udprojectsmall.string26'
	WHEN 36439 THEN 'udprojectsmall.string27'
	WHEN 36440 THEN 'udprojectsmall.string28'
	WHEN 36441 THEN 'udprojectsmall.string29'
	WHEN 36442 THEN 'udprojectsmall.string30'
	WHEN 36443 THEN 'udprojectsmall.string31'
	WHEN 36444 THEN 'udprojectsmall.string32'
	WHEN 36445 THEN 'udprojectsmall.string33'
	WHEN 36446 THEN 'udprojectsmall.string34'
	WHEN 36447 THEN 'udprojectsmall.string35'
	WHEN 36448 THEN 'udprojectsmall.string36'
	WHEN 36449 THEN 'udprojectsmall.string37'
	WHEN 36450 THEN 'udprojectsmall.string38'
	WHEN 36451 THEN 'udprojectsmall.string39'
	WHEN 36452 THEN 'udprojectsmall.string40'
	WHEN 36453 THEN 'udprojectsmall.double01'
	WHEN 36454 THEN 'udprojectsmall.double02'
	WHEN 36455 THEN 'udprojectsmall.double03'
	WHEN 36456 THEN 'udprojectsmall.double04'
	WHEN 36457 THEN 'udprojectsmall.double05'
	WHEN 36458 THEN 'udprojectsmall.double06'
	WHEN 36459 THEN 'udprojectsmall.double07'
	WHEN 36460 THEN 'udprojectsmall.double08'
	WHEN 36461 THEN 'udprojectsmall.double09'
	WHEN 36462 THEN 'udprojectsmall.double10'

	WHEN 36609 THEN 'udprojectlarge.string41'
	WHEN 36610 THEN 'udprojectlarge.string42'
	WHEN 36611 THEN 'udprojectlarge.string43'
	WHEN 36612 THEN 'udprojectlarge.string44'
	WHEN 36613 THEN 'udprojectlarge.string45'
	WHEN 36614 THEN 'udprojectlarge.string46'
	WHEN 36615 THEN 'udprojectlarge.string47'
	WHEN 36616 THEN 'udprojectlarge.string48'
	WHEN 36617 THEN 'udprojectlarge.string49'

	WHEN 40449 THEN 'udsalesmall.long01'
	WHEN 40450 THEN 'udsalesmall.long02'
	WHEN 40451 THEN 'udsalesmall.long03'
	WHEN 40452 THEN 'udsalesmall.long04'
	WHEN 40453 THEN 'udsalesmall.long05'
	WHEN 40454 THEN 'udsalesmall.long06'
	WHEN 40455 THEN 'udsalesmall.long07'
	WHEN 40456 THEN 'udsalesmall.long08'
	WHEN 40457 THEN 'udsalesmall.long09'
	WHEN 40458 THEN 'udsalesmall.long10'
	WHEN 40459 THEN 'udsalesmall.long11'
	WHEN 40460 THEN 'udsalesmall.long12'
	WHEN 40461 THEN 'udsalesmall.long13'
	WHEN 40462 THEN 'udsalesmall.long14'
	WHEN 40463 THEN 'udsalesmall.long15'
	WHEN 40464 THEN 'udsalesmall.long16'
	WHEN 40465 THEN 'udsalesmall.long17'
	WHEN 40466 THEN 'udsalesmall.long18'
	WHEN 40467 THEN 'udsalesmall.long19'
	WHEN 40468 THEN 'udsalesmall.long20'
	WHEN 40469 THEN 'udsalesmall.long21'
	WHEN 40470 THEN 'udsalesmall.long22'
	WHEN 40471 THEN 'udsalesmall.long23'
	WHEN 40472 THEN 'udsalesmall.long24'
	WHEN 40473 THEN 'udsalesmall.long25'
	WHEN 40474 THEN 'udsalesmall.long26'
	WHEN 40475 THEN 'udsalesmall.long27'
	WHEN 40476 THEN 'udsalesmall.long28'
	WHEN 40477 THEN 'udsalesmall.long29'
	WHEN 40478 THEN 'udsalesmall.long30'
	WHEN 40479 THEN 'udsalesmall.long31'
	WHEN 40480 THEN 'udsalesmall.long32'
	WHEN 40481 THEN 'udsalesmall.long33'
	WHEN 40482 THEN 'udsalesmall.long34'
	WHEN 40483 THEN 'udsalesmall.long35'
	WHEN 40484 THEN 'udsalesmall.long36'
	WHEN 40485 THEN 'udsalesmall.long37'
	WHEN 40486 THEN 'udsalesmall.long38'
	WHEN 40487 THEN 'udsalesmall.long39'
	WHEN 40488 THEN 'udsalesmall.long40'
	WHEN 40489 THEN 'udsalesmall.long41'
	WHEN 40490 THEN 'udsalesmall.long42'
	WHEN 40491 THEN 'udsalesmall.long43'
	WHEN 40492 THEN 'udsalesmall.long44'
	WHEN 40493 THEN 'udsalesmall.long45'
	WHEN 40494 THEN 'udsalesmall.long46'
	WHEN 40495 THEN 'udsalesmall.long47'
	WHEN 40496 THEN 'udsalesmall.long48'
	WHEN 40497 THEN 'udsalesmall.long49'
	WHEN 40498 THEN 'udsalesmall.long50'
	WHEN 40499 THEN 'udsalesmall.long51'
	WHEN 40500 THEN 'udsalesmall.long52'
	WHEN 40501 THEN 'udsalesmall.long53'
	WHEN 40502 THEN 'udsalesmall.long54'
	WHEN 40503 THEN 'udsalesmall.long55'
	WHEN 40504 THEN 'udsalesmall.long56'
	WHEN 40505 THEN 'udsalesmall.long57'
	WHEN 40506 THEN 'udsalesmall.long58'
	WHEN 40507 THEN 'udsalesmall.long59'
	WHEN 40508 THEN 'udsalesmall.long60'
	WHEN 40509 THEN 'udsalesmall.string01'
	WHEN 40510 THEN 'udsalesmall.string02'
	WHEN 40511 THEN 'udsalesmall.string03'
	WHEN 40512 THEN 'udsalesmall.string04'
	WHEN 40513 THEN 'udsalesmall.string05'
	WHEN 40514 THEN 'udsalesmall.string06'
	WHEN 40515 THEN 'udsalesmall.string07'
	WHEN 40516 THEN 'udsalesmall.string08'
	WHEN 40517 THEN 'udsalesmall.string09'
	WHEN 40518 THEN 'udsalesmall.string10'
	WHEN 40519 THEN 'udsalesmall.string11'
	WHEN 40520 THEN 'udsalesmall.string12'
	WHEN 40521 THEN 'udsalesmall.string13'
	WHEN 40522 THEN 'udsalesmall.string14'
	WHEN 40523 THEN 'udsalesmall.string15'
	WHEN 40524 THEN 'udsalesmall.string16'
	WHEN 40525 THEN 'udsalesmall.string17'
	WHEN 40526 THEN 'udsalesmall.string18'
	WHEN 40527 THEN 'udsalesmall.string19'
	WHEN 40528 THEN 'udsalesmall.string20'
	WHEN 40529 THEN 'udsalesmall.string21'
	WHEN 40530 THEN 'udsalesmall.string22'
	WHEN 40531 THEN 'udsalesmall.string23'
	WHEN 40532 THEN 'udsalesmall.string24'
	WHEN 40533 THEN 'udsalesmall.string25'
	WHEN 40534 THEN 'udsalesmall.string26'
	WHEN 40535 THEN 'udsalesmall.string27'
	WHEN 40536 THEN 'udsalesmall.string28'
	WHEN 40537 THEN 'udsalesmall.string29'
	WHEN 40538 THEN 'udsalesmall.string30'
	WHEN 40539 THEN 'udsalesmall.string31'
	WHEN 40540 THEN 'udsalesmall.string32'
	WHEN 40541 THEN 'udsalesmall.string33'
	WHEN 40542 THEN 'udsalesmall.string34'
	WHEN 40543 THEN 'udsalesmall.string35'
	WHEN 40544 THEN 'udsalesmall.string36'
	WHEN 40545 THEN 'udsalesmall.string37'
	WHEN 40546 THEN 'udsalesmall.string38'
	WHEN 40547 THEN 'udsalesmall.string39'
	WHEN 40548 THEN 'udsalesmall.string40'
	WHEN 40549 THEN 'udsalesmall.double01'
	WHEN 40550 THEN 'udsalesmall.double02'
	WHEN 40551 THEN 'udsalesmall.double03'
	WHEN 40552 THEN 'udsalesmall.double04'
	WHEN 40553 THEN 'udsalesmall.double05'
	WHEN 40554 THEN 'udsalesmall.double06'
	WHEN 40555 THEN 'udsalesmall.double07'
	WHEN 40556 THEN 'udsalesmall.double08'
	WHEN 40557 THEN 'udsalesmall.double09'
	WHEN 40558 THEN 'udsalesmall.double10'

	WHEN 40705 THEN 'udsalelarge.string41'
	WHEN 40706 THEN 'udsalelarge.string42'
	WHEN 40707 THEN 'udsalelarge.string43'
	WHEN 40708 THEN 'udsalelarge.string44'
	WHEN 40709 THEN 'udsalelarge.string45'
	WHEN 40710 THEN 'udsalelarge.string46'
	WHEN 40711 THEN 'udsalelarge.string47'
	WHEN 40712 THEN 'udsalelarge.string48'
	WHEN 40713 THEN 'udsalelarge.string49'

  WHEN 58369 THEN 'udappntsmall.long01'
  WHEN 58370 THEN 'udappntsmall.long02'
  WHEN 58371 THEN 'udappntsmall.long03'
  WHEN 58372 THEN 'udappntsmall.long04'
  WHEN 58373 THEN 'udappntsmall.long05'
  WHEN 58374 THEN 'udappntsmall.long06'
  WHEN 58375 THEN 'udappntsmall.long07'
  WHEN 58376 THEN 'udappntsmall.long08'
  WHEN 58377 THEN 'udappntsmall.long09'
  WHEN 58378 THEN 'udappntsmall.long10'
  WHEN 58379 THEN 'udappntsmall.long11'
  WHEN 58380 THEN 'udappntsmall.long12'
  WHEN 58381 THEN 'udappntsmall.long13'
  WHEN 58382 THEN 'udappntsmall.long14'
  WHEN 58383 THEN 'udappntsmall.long15'
  WHEN 58384 THEN 'udappntsmall.long16'
  WHEN 58385 THEN 'udappntsmall.long17'
  WHEN 58386 THEN 'udappntsmall.long18'
  WHEN 58387 THEN 'udappntsmall.long19'
  WHEN 58388 THEN 'udappntsmall.long20'
  WHEN 58389 THEN 'udappntsmall.long21'
  WHEN 58390 THEN 'udappntsmall.long22'
  WHEN 58391 THEN 'udappntsmall.long23'
  WHEN 58392 THEN 'udappntsmall.long24'
  WHEN 58393 THEN 'udappntsmall.long25'
  WHEN 58394 THEN 'udappntsmall.long26'
  WHEN 58395 THEN 'udappntsmall.long27'
  WHEN 58396 THEN 'udappntsmall.long28'
  WHEN 58397 THEN 'udappntsmall.long29'
  WHEN 58398 THEN 'udappntsmall.long30'
  WHEN 58399 THEN 'udappntsmall.long31'
  WHEN 58400 THEN 'udappntsmall.long32'
  WHEN 58401 THEN 'udappntsmall.long33'
  WHEN 58402 THEN 'udappntsmall.long34'
  WHEN 58403 THEN 'udappntsmall.long35'
  WHEN 58404 THEN 'udappntsmall.long36'
  WHEN 58405 THEN 'udappntsmall.long37'
  WHEN 58406 THEN 'udappntsmall.long38'
  WHEN 58407 THEN 'udappntsmall.long39'
  WHEN 58408 THEN 'udappntsmall.long40'
  WHEN 58409 THEN 'udappntsmall.long41'
  WHEN 58410 THEN 'udappntsmall.long42'
  WHEN 58411 THEN 'udappntsmall.long43'
  WHEN 58412 THEN 'udappntsmall.long44'
  WHEN 58413 THEN 'udappntsmall.long45'
  WHEN 58414 THEN 'udappntsmall.long46'
  WHEN 58415 THEN 'udappntsmall.long47'
  WHEN 58416 THEN 'udappntsmall.long48'
  WHEN 58417 THEN 'udappntsmall.long49'
  WHEN 58418 THEN 'udappntsmall.long50'
  WHEN 58419 THEN 'udappntsmall.long51'
  WHEN 58420 THEN 'udappntsmall.long52'
  WHEN 58421 THEN 'udappntsmall.long53'
  WHEN 58422 THEN 'udappntsmall.long54'
  WHEN 58423 THEN 'udappntsmall.long55'
  WHEN 58424 THEN 'udappntsmall.long56'
  WHEN 58425 THEN 'udappntsmall.long57'
  WHEN 58426 THEN 'udappntsmall.long58'
  WHEN 58427 THEN 'udappntsmall.long59'
  WHEN 58428 THEN 'udappntsmall.long60'
  WHEN 58429 THEN 'udappntsmall.string01'
  WHEN 58430 THEN 'udappntsmall.string02'
  WHEN 58431 THEN 'udappntsmall.string03'
  WHEN 58432 THEN 'udappntsmall.string04'
  WHEN 58433 THEN 'udappntsmall.string05'
  WHEN 58434 THEN 'udappntsmall.string06'
  WHEN 58435 THEN 'udappntsmall.string07'
  WHEN 58436 THEN 'udappntsmall.string08'
  WHEN 58437 THEN 'udappntsmall.string09'
  WHEN 58438 THEN 'udappntsmall.string10'
  WHEN 58439 THEN 'udappntsmall.string11'
  WHEN 58440 THEN 'udappntsmall.string12'
  WHEN 58441 THEN 'udappntsmall.string13'
  WHEN 58442 THEN 'udappntsmall.string14'
  WHEN 58443 THEN 'udappntsmall.string15'
  WHEN 58444 THEN 'udappntsmall.string16'
  WHEN 58445 THEN 'udappntsmall.string17'
  WHEN 58446 THEN 'udappntsmall.string18'
  WHEN 58447 THEN 'udappntsmall.string19'
  WHEN 58448 THEN 'udappntsmall.string20'
  WHEN 58449 THEN 'udappntsmall.string21'
  WHEN 58450 THEN 'udappntsmall.string22'
  WHEN 58451 THEN 'udappntsmall.string23'
  WHEN 58452 THEN 'udappntsmall.string24'
  WHEN 58453 THEN 'udappntsmall.string25'
  WHEN 58454 THEN 'udappntsmall.string26'
  WHEN 58455 THEN 'udappntsmall.string27'
  WHEN 58456 THEN 'udappntsmall.string28'
  WHEN 58457 THEN 'udappntsmall.string29'
  WHEN 58458 THEN 'udappntsmall.string30'
  WHEN 58459 THEN 'udappntsmall.string31'
  WHEN 58460 THEN 'udappntsmall.string32'
  WHEN 58461 THEN 'udappntsmall.string33'
  WHEN 58462 THEN 'udappntsmall.string34'
  WHEN 58463 THEN 'udappntsmall.string35'
  WHEN 58464 THEN 'udappntsmall.string36'
  WHEN 58465 THEN 'udappntsmall.string37'
  WHEN 58466 THEN 'udappntsmall.string38'
  WHEN 58467 THEN 'udappntsmall.string39'
  WHEN 58468 THEN 'udappntsmall.string40'
  WHEN 58469 THEN 'udappntsmall.double01'
  WHEN 58470 THEN 'udappntsmall.double02'
  WHEN 58471 THEN 'udappntsmall.double03'
  WHEN 58472 THEN 'udappntsmall.double04'
  WHEN 58473 THEN 'udappntsmall.double05'
  WHEN 58474 THEN 'udappntsmall.double06'
  WHEN 58475 THEN 'udappntsmall.double07'
  WHEN 58476 THEN 'udappntsmall.double08'
  WHEN 58477 THEN 'udappntsmall.double09'
  WHEN 58478 THEN 'udappntsmall.double10'

  WHEN 58625 THEN 'udappntlarge.string41'
  WHEN 58626 THEN 'udappntlarge.string42'
  WHEN 58627 THEN 'udappntlarge.string43'
  WHEN 58628 THEN 'udappntlarge.string44'
  WHEN 58629 THEN 'udappntlarge.string45'
  WHEN 58630 THEN 'udappntlarge.string46'
  WHEN 58631 THEN 'udappntlarge.string47'
  WHEN 58632 THEN 'udappntlarge.string48'
  WHEN 58633 THEN 'udappntlarge.string49'

  WHEN 58881 THEN 'uddocsmall.long01'
  WHEN 58882 THEN 'uddocsmall.long02'
  WHEN 58883 THEN 'uddocsmall.long03'
  WHEN 58884 THEN 'uddocsmall.long04'
  WHEN 58885 THEN 'uddocsmall.long05'
  WHEN 58886 THEN 'uddocsmall.long06'
  WHEN 58887 THEN 'uddocsmall.long07'
  WHEN 58888 THEN 'uddocsmall.long08'
  WHEN 58889 THEN 'uddocsmall.long09'
  WHEN 58890 THEN 'uddocsmall.long10'
  WHEN 58891 THEN 'uddocsmall.long11'
  WHEN 58892 THEN 'uddocsmall.long12'
  WHEN 58893 THEN 'uddocsmall.long13'
  WHEN 58894 THEN 'uddocsmall.long14'
  WHEN 58895 THEN 'uddocsmall.long15'
  WHEN 58896 THEN 'uddocsmall.long16'
  WHEN 58897 THEN 'uddocsmall.long17'
  WHEN 58898 THEN 'uddocsmall.long18'
  WHEN 58899 THEN 'uddocsmall.long19'
  WHEN 58900 THEN 'uddocsmall.long20'
  WHEN 58901 THEN 'uddocsmall.long21'
  WHEN 58902 THEN 'uddocsmall.long22'
  WHEN 58903 THEN 'uddocsmall.long23'
  WHEN 58904 THEN 'uddocsmall.long24'
  WHEN 58905 THEN 'uddocsmall.long25'
  WHEN 58906 THEN 'uddocsmall.long26'
  WHEN 58907 THEN 'uddocsmall.long27'
  WHEN 58908 THEN 'uddocsmall.long28'
  WHEN 58909 THEN 'uddocsmall.long29'
  WHEN 58910 THEN 'uddocsmall.long30'
  WHEN 58911 THEN 'uddocsmall.long31'
  WHEN 58912 THEN 'uddocsmall.long32'
  WHEN 58913 THEN 'uddocsmall.long33'
  WHEN 58914 THEN 'uddocsmall.long34'
  WHEN 58915 THEN 'uddocsmall.long35'
  WHEN 58916 THEN 'uddocsmall.long36'
  WHEN 58917 THEN 'uddocsmall.long37'
  WHEN 58918 THEN 'uddocsmall.long38'
  WHEN 58919 THEN 'uddocsmall.long39'
  WHEN 58920 THEN 'uddocsmall.long40'
  WHEN 58921 THEN 'uddocsmall.long41'
  WHEN 58922 THEN 'uddocsmall.long42'
  WHEN 58923 THEN 'uddocsmall.long43'
  WHEN 58924 THEN 'uddocsmall.long44'
  WHEN 58925 THEN 'uddocsmall.long45'
  WHEN 58926 THEN 'uddocsmall.long46'
  WHEN 58927 THEN 'uddocsmall.long47'
  WHEN 58928 THEN 'uddocsmall.long48'
  WHEN 58929 THEN 'uddocsmall.long49'
  WHEN 58930 THEN 'uddocsmall.long50'
  WHEN 58931 THEN 'uddocsmall.long51'
  WHEN 58932 THEN 'uddocsmall.long52'
  WHEN 58933 THEN 'uddocsmall.long53'
  WHEN 58934 THEN 'uddocsmall.long54'
  WHEN 58935 THEN 'uddocsmall.long55'
  WHEN 58936 THEN 'uddocsmall.long56'
  WHEN 58937 THEN 'uddocsmall.long57'
  WHEN 58938 THEN 'uddocsmall.long58'
  WHEN 58939 THEN 'uddocsmall.long59'
  WHEN 58940 THEN 'uddocsmall.long60'
  WHEN 58941 THEN 'uddocsmall.string01'
  WHEN 58942 THEN 'uddocsmall.string02'
  WHEN 58943 THEN 'uddocsmall.string03'
  WHEN 58944 THEN 'uddocsmall.string04'
  WHEN 58945 THEN 'uddocsmall.string05'
  WHEN 58946 THEN 'uddocsmall.string06'
  WHEN 58947 THEN 'uddocsmall.string07'
  WHEN 58948 THEN 'uddocsmall.string08'
  WHEN 58949 THEN 'uddocsmall.string09'
  WHEN 58950 THEN 'uddocsmall.string10'
  WHEN 58951 THEN 'uddocsmall.string11'
  WHEN 58952 THEN 'uddocsmall.string12'
  WHEN 58953 THEN 'uddocsmall.string13'
  WHEN 58954 THEN 'uddocsmall.string14'
  WHEN 58955 THEN 'uddocsmall.string15'
  WHEN 58956 THEN 'uddocsmall.string16'
  WHEN 58957 THEN 'uddocsmall.string17'
  WHEN 58958 THEN 'uddocsmall.string18'
  WHEN 58959 THEN 'uddocsmall.string19'
  WHEN 58960 THEN 'uddocsmall.string20'
  WHEN 58961 THEN 'uddocsmall.string21'
  WHEN 58962 THEN 'uddocsmall.string22'
  WHEN 58963 THEN 'uddocsmall.string23'
  WHEN 58964 THEN 'uddocsmall.string24'
  WHEN 58965 THEN 'uddocsmall.string25'
  WHEN 58966 THEN 'uddocsmall.string26'
  WHEN 58967 THEN 'uddocsmall.string27'
  WHEN 58968 THEN 'uddocsmall.string28'
  WHEN 58969 THEN 'uddocsmall.string29'
  WHEN 58970 THEN 'uddocsmall.string30'
  WHEN 58971 THEN 'uddocsmall.string31'
  WHEN 58972 THEN 'uddocsmall.string32'
  WHEN 58973 THEN 'uddocsmall.string33'
  WHEN 58974 THEN 'uddocsmall.string34'
  WHEN 58975 THEN 'uddocsmall.string35'
  WHEN 58976 THEN 'uddocsmall.string36'
  WHEN 58977 THEN 'uddocsmall.string37'
  WHEN 58978 THEN 'uddocsmall.string38'
  WHEN 58979 THEN 'uddocsmall.string39'
  WHEN 58980 THEN 'uddocsmall.string40'
  WHEN 58981 THEN 'uddocsmall.double01'
  WHEN 58982 THEN 'uddocsmall.double02'
  WHEN 58983 THEN 'uddocsmall.double03'
  WHEN 58984 THEN 'uddocsmall.double04'
  WHEN 58985 THEN 'uddocsmall.double05'
  WHEN 58986 THEN 'uddocsmall.double06'
  WHEN 58987 THEN 'uddocsmall.double07'
  WHEN 58988 THEN 'uddocsmall.double08'
  WHEN 58989 THEN 'uddocsmall.double09'
  WHEN 58990 THEN 'uddocsmall.double10'

  WHEN 59137 THEN 'uddoclarge.string41'
  WHEN 59138 THEN 'uddoclarge.string42'
  WHEN 59139 THEN 'uddoclarge.string43'
  WHEN 59140 THEN 'uddoclarge.string44'
  WHEN 59141 THEN 'uddoclarge.string45'
  WHEN 59142 THEN 'uddoclarge.string46'
  WHEN 59143 THEN 'uddoclarge.string47'
  WHEN 59144 THEN 'uddoclarge.string48'
  WHEN 59145 THEN 'uddoclarge.string49'

  WHEN 36865 THEN 'udtempsmall.long01'
  WHEN 36866 THEN 'udtempsmall.long02'
  WHEN 36867 THEN 'udtempsmall.long03'
  WHEN 36868 THEN 'udtempsmall.long04'
  WHEN 36869 THEN 'udtempsmall.long05'
  WHEN 36870 THEN 'udtempsmall.long06'
  WHEN 36871 THEN 'udtempsmall.long07'
  WHEN 36872 THEN 'udtempsmall.long08'
  WHEN 36873 THEN 'udtempsmall.long09'
  WHEN 36874 THEN 'udtempsmall.long10'
  WHEN 36875 THEN 'udtempsmall.long11'
  WHEN 36876 THEN 'udtempsmall.long12'
  WHEN 36877 THEN 'udtempsmall.long13'
  WHEN 36878 THEN 'udtempsmall.long14'
  WHEN 36879 THEN 'udtempsmall.long15'
  WHEN 36880 THEN 'udtempsmall.long16'
  WHEN 36881 THEN 'udtempsmall.long17'
  WHEN 36882 THEN 'udtempsmall.long18'
  WHEN 36883 THEN 'udtempsmall.long19'
  WHEN 36884 THEN 'udtempsmall.long20'
  WHEN 36885 THEN 'udtempsmall.long21'
  WHEN 36886 THEN 'udtempsmall.long22'
  WHEN 36887 THEN 'udtempsmall.long23'
  WHEN 36888 THEN 'udtempsmall.long24'
  WHEN 36889 THEN 'udtempsmall.long25'
  WHEN 36890 THEN 'udtempsmall.long26'
  WHEN 36891 THEN 'udtempsmall.long27'
  WHEN 36892 THEN 'udtempsmall.long28'
  WHEN 36893 THEN 'udtempsmall.long29'
  WHEN 36894 THEN 'udtempsmall.long30'
  WHEN 36895 THEN 'udtempsmall.long31'
  WHEN 36896 THEN 'udtempsmall.long32'
  WHEN 36897 THEN 'udtempsmall.long33'
  WHEN 36898 THEN 'udtempsmall.long34'
  WHEN 36899 THEN 'udtempsmall.long35'
  WHEN 36900 THEN 'udtempsmall.long36'
  WHEN 36901 THEN 'udtempsmall.long37'
  WHEN 36902 THEN 'udtempsmall.long38'
  WHEN 36903 THEN 'udtempsmall.long39'
  WHEN 36904 THEN 'udtempsmall.long40'
  WHEN 36905 THEN 'udtempsmall.long41'
  WHEN 36906 THEN 'udtempsmall.long42'
  WHEN 36907 THEN 'udtempsmall.long43'
  WHEN 36908 THEN 'udtempsmall.long44'
  WHEN 36909 THEN 'udtempsmall.long45'
  WHEN 36910 THEN 'udtempsmall.long46'
  WHEN 36911 THEN 'udtempsmall.long47'
  WHEN 36912 THEN 'udtempsmall.long48'
  WHEN 36913 THEN 'udtempsmall.long49'
  WHEN 36914 THEN 'udtempsmall.long50'
  WHEN 36915 THEN 'udtempsmall.long51'
  WHEN 36916 THEN 'udtempsmall.long52'
  WHEN 36917 THEN 'udtempsmall.long53'
  WHEN 36918 THEN 'udtempsmall.long54'
  WHEN 36919 THEN 'udtempsmall.long55'
  WHEN 36920 THEN 'udtempsmall.long56'
  WHEN 36921 THEN 'udtempsmall.long57'
  WHEN 36922 THEN 'udtempsmall.long58'
  WHEN 36923 THEN 'udtempsmall.long59'
  WHEN 36924 THEN 'udtempsmall.long60'
  WHEN 36925 THEN 'udtempsmall.string01'
  WHEN 36926 THEN 'udtempsmall.string02'
  WHEN 36927 THEN 'udtempsmall.string03'
  WHEN 36928 THEN 'udtempsmall.string04'
  WHEN 36929 THEN 'udtempsmall.string05'
  WHEN 36930 THEN 'udtempsmall.string06'
  WHEN 36931 THEN 'udtempsmall.string07'
  WHEN 36932 THEN 'udtempsmall.string08'
  WHEN 36933 THEN 'udtempsmall.string09'
  WHEN 36934 THEN 'udtempsmall.string10'
  WHEN 36935 THEN 'udtempsmall.string11'
  WHEN 36936 THEN 'udtempsmall.string12'
  WHEN 36937 THEN 'udtempsmall.string13'
  WHEN 36938 THEN 'udtempsmall.string14'
  WHEN 36939 THEN 'udtempsmall.string15'
  WHEN 36940 THEN 'udtempsmall.string16'
  WHEN 36941 THEN 'udtempsmall.string17'
  WHEN 36942 THEN 'udtempsmall.string18'
  WHEN 36943 THEN 'udtempsmall.string19'
  WHEN 36944 THEN 'udtempsmall.string20'
  WHEN 36945 THEN 'udtempsmall.string21'
  WHEN 36946 THEN 'udtempsmall.string22'
  WHEN 36947 THEN 'udtempsmall.string23'
  WHEN 36948 THEN 'udtempsmall.string24'
  WHEN 36949 THEN 'udtempsmall.string25'
  WHEN 36950 THEN 'udtempsmall.string26'
  WHEN 36951 THEN 'udtempsmall.string27'
  WHEN 36952 THEN 'udtempsmall.string28'
  WHEN 36953 THEN 'udtempsmall.string29'
  WHEN 36954 THEN 'udtempsmall.string30'
  WHEN 36955 THEN 'udtempsmall.string31'
  WHEN 36956 THEN 'udtempsmall.string32'
  WHEN 36957 THEN 'udtempsmall.string33'
  WHEN 36958 THEN 'udtempsmall.string34'
  WHEN 36959 THEN 'udtempsmall.string35'
  WHEN 36960 THEN 'udtempsmall.string36'
  WHEN 36961 THEN 'udtempsmall.string37'
  WHEN 36962 THEN 'udtempsmall.string38'
  WHEN 36963 THEN 'udtempsmall.string39'
  WHEN 36964 THEN 'udtempsmall.string40'
  WHEN 36965 THEN 'udtempsmall.double01'
  WHEN 36966 THEN 'udtempsmall.double02'
  WHEN 36967 THEN 'udtempsmall.double03'
  WHEN 36968 THEN 'udtempsmall.double04'
  WHEN 36969 THEN 'udtempsmall.double05'
  WHEN 36970 THEN 'udtempsmall.double06'
  WHEN 36971 THEN 'udtempsmall.double07'
  WHEN 36972 THEN 'udtempsmall.double08'
  WHEN 36973 THEN 'udtempsmall.double09'
  WHEN 36974 THEN 'udtempsmall.double10'

  WHEN 37121 THEN 'udtemplarge.string41'
  WHEN 37122 THEN 'udtemplarge.string42'
  WHEN 37123 THEN 'udtemplarge.string43'
  WHEN 37124 THEN 'udtemplarge.string44'
  WHEN 37125 THEN 'udtemplarge.string45'
  WHEN 37126 THEN 'udtemplarge.string46'
  WHEN 37127 THEN 'udtemplarge.string47'
  WHEN 37128 THEN 'udtemplarge.string48'
  WHEN 37129 THEN 'udtemplarge.string49'

END
FROM crm7.udeffield
WHERE
  -- currentudefversion = The latest published version of the udef-fields
     (ownertable_id=7 AND version IN  (SELECT prefvalue FROM crm7.userpreference WHERE prefkey='currentudefversioncontact'))
  OR (ownertable_id=8 AND version IN  (SELECT prefvalue FROM crm7.userpreference WHERE prefkey='currentudefversionperson'))
  OR (ownertable_id=9 AND version IN  (SELECT prefvalue FROM crm7.userpreference WHERE prefkey='currentudefversionproject'))
  OR (ownertable_id=10 AND version IN (SELECT prefvalue FROM crm7.userpreference WHERE prefkey='currentudefversionsale'))
  OR (ownertable_id=12 AND version IN (SELECT prefvalue FROM crm7.userpreference WHERE prefkey='currentudefversionappointment'))
  OR (ownertable_id=13 AND version IN (SELECT prefvalue FROM crm7.userpreference WHERE prefkey='currentudefversiondocument'))

-- adminudefversion = The version of the udef-fields while editing in admin and before a publish
--   (ownertable_id=7 AND version IN  (SELECT prefvalue FROM crm7.userpreference WHERE prefkey='adminudefversioncontact'))
--OR (ownertable_id=8 AND version IN  (SELECT prefvalue FROM crm7.userpreference WHERE prefkey='adminudefversionperson'))
--OR (ownertable_id=9 AND version IN  (SELECT prefvalue FROM crm7.userpreference WHERE prefkey='adminudefversionproject'))
--OR (ownertable_id=10 AND version IN (SELECT prefvalue FROM crm7.userpreference WHERE prefkey='adminudefversionsale'))
--OR (ownertable_id=12 AND version IN (SELECT prefvalue FROM crm7.userpreference WHERE prefkey='adminudefversionappointment'))
--OR (ownertable_id=13 AND version IN (SELECT prefvalue FROM crm7.userpreference WHERE prefkey='adminudefversiondocument'))

ORDER BY columnid
--FOR XML AUTO


/* ###################################################################################################################################
Table - udeffield
=================

Field - version
---------------
* The [crm7.udeffield] table contains many versions of the same field.
* For each publish, a complete set of all udef fields for the corresponding entity is saved in the table with a new version set
* The version that is currently used by the socrm client is stored in a user preference.
* The table [crm7.userpreference] contains the version number for the active published version as well as the active non-published version
  * Each Udef-Entity has its own version

Field - ownertable_id
---------------------
* udeffield contains an ownertable id, which is the table the defined field belongs to.
* This field is an enum, unlike all other owner table ids in the database it does not use the table id listed in the dictionary.

udefcontact = 7
udefperson = 8
udefproject = 9
udefsale = 10
udeftemp = 11
udefappointment = 12
udefdocument = 13

Field - fieldtype
-----------------
1 = number
2 = short text (up to 39 chars long)
3 = long text  ( 40 to 200 chars long)
4 = date (for dates 1970 - 2036, stored as long value)
5 = unlimited date (for dates 0001 - 9999, stored as string)
6 = check box (stored as 0/1 in long field)
7 = drop-down (listtableid determines which list. if listtableid = 136 then this is a userdefined list and all items are stored in the udlist table with udlist.udlistdefinition_id = udeffield.udlistdefinition_id)
8 = decimal  - acutally double - that is 80 bits ieee floating point.

Field - progid
--------------
* The prog-id can be used to tag fields that are used by your application with a known value, so that you can easily find the udef fields you need.
* This may also be set from the admin client.
* Must be unique per version+ownertable
* Is per default named "SuperOffice:n" where n is a counter (integer)
* Can be given a more descriptive custom name if wanted, which is my personal best practice if the UDEF-fields are to be used in scripts
* It's recommended to include the entityname the ProgId is related to in the ProgId name, eg "contactCustomField2:1", "saleCustomField3:1".
  * This makes it easier to distinguish between ProgIds connected to different entities in the source code
* An earlier release of SO 8.0 had a bug which invalidated custom names that didn't contain the ":n" part in the ProgId
  * The bug was only in the validation form of Admin, so that part wasn't actually a requirement of SuperOffice
  * The bug should be fixed in SO 8.1+
  * To be sure I still made a best practice of including that part in the form of ":1", eg "Entity1CustomField1:1", "Entity3CustomField2:1", "Entity2CustomField3:1"
################################################################################################################################### */
28 Feb 2019 | 12:00 AM

Hi Marcus! 

Thanks for that convient script.

I am not building anything special , but i rather wanted to understand what/how the columnId is determind for a field in the database. That Maping list in your script is was I was after from the beging, so thanks for that.
I could see that the columnId must somehow poitn to correct field in the UDConatctSMALL table for instance but could at the time not understand how. Margrethets formula gave the answer and your list in the PS-script is usefull as well.

If I understand correctly the current version of  a field is as you say determined by the the userpref value.
One value per Entity (contact, person,project,sale,appointment, document) 
The AdminUdefVersions of a field is the "next generation" eg current edited put not published seems to always have a +1 value compared to the UdefVersion userpref value. (regardles if the button publish is activated or not)

When everything is published current version and adminversion should be the same.

Sometimes that can be korrupt (publishbutton always active) . See here:
https://community.superoffice.com/en/blog/fixing-the-publish-button-for-userdefined-fields-in-soadmin/

I am also a PowerShell user and tend to use it more and more for different tasks.
Maybe we should share knowledge sometime!

best regards

Anders

1 Mar 2019 | 12:00 AM

Sorry to hijack this thread, but thank you Marcus for posting that long query - it has really helped in a situation where I need to query an SO database directly with SQL as matching udef field names with values was causing me a real headache.

Could this snippet make it into the docs at some point? It's just very useful (and also the search engine on this site turned up so many results for my search terms that it was pure luck that I found this!)

Thanks again

21 Aug 2019 | 12:00 AM

Add reply