Page History

User defined fields can be added by using the LMS user interface (settings).

However, when a large volume of UDF's need to be created this can be done on the database. Below are scripts to assist with adding bulk UDF's

First prepare the UDF table containing the UDF's to append to the target table. Below script creates that table based on an existing table, using the correct UDF datatypes.

Code Block

language	sql
title	Create udf table
linenumbers	true
collapse	true

-- table with udf fields to ad to existing table
declare @sourceTable varchar(100) = 'joined_202004';

with udfs as (
select 
fieldname = replace(name,' ','_') 
,prefixed = case when system_type_id = 231 or system_type_id = 167 then 'uc' +  replace(name,' ','_') 
				when system_type_id = 56 then 'ui' +  replace(name,' ','_') 
				when system_type_id = 104 then 'ub' +  replace(name,' ','_') 
				when system_type_id = 106 or system_type_id = 62 then 'ud' +  replace(name,' ','_') 
				when system_type_id = 61 then 'ut' +  replace(name,' ','_') 				
		  end
, udfDataType = case when system_type_id = 231 or system_type_id = 167 then 0 
				when system_type_id = 56 then 1
				when system_type_id = 104 then 4 
				when system_type_id = 106 or system_type_id = 62 then 2
				when  system_type_id = 231 or system_type_id = 61 then 3
		  end
, sqlDataType = case when system_type_id = 231 or system_type_id = 167 then 'varchar(' + cast(max_length as varchar) + ')'
					when system_type_id = 56 then 'int'
					when system_type_id = 104 then 'bit'
					when system_type_id = 106 or system_type_id = 62 then 'decimal(18,' + cast([scale] as varchar) + ')' 
					when system_type_id = 61 then 'datetime'
				end
,system_type_id
,max_length
,scale
from sys.columns
where object_id = (select object_id from sys.tables where name = @sourceTable)
)

-- table holding udf data to append to table
select *
into udf_columns
from udfs

Next, append the UDF fields to the loan/, party or deal using the udf_columns table.

Code Block

language	sql
title	Append UDF columns
linenumbers	true
collapse	true

declare @targetTable varchar(100) = 'loans2'
declare @alter table (idx int, alterText nvarchar(1000));
with udfs as (select * from udf_columns)

INSERT INTO @alter (idx,alterText)
select
idx = ROW_NUMBER() over (order by prefixed)
,alterText = 'alter table ' + @targetTable + ' add ' + prefixed + ' ' + sqlDataType 
from udfs;

declare @counter int = 1
declare @maxRow int = (select count(*) from @alter)
declare @alterText nvarchar(1000) = ''
while @counter <= @maxRow
begin
	select @alterText=alterText from @alter where idx=@counter
	-- print @alterText
	exec sp_executesql @alterText 
	set @counter = @counter + 1
end

Finally, add the UDF columns to the _as_UserDefinedFields definition table.

Code Block

language	sql
title	Adding UDF to definition table
linenumbers	true
collapse	true

INSERT INTO [dbo].[_as_UserDefinedFields]
           ([CreationDate]
           ,[LastUpdateDate]
           ,[CreatedOnDate]
           ,[LastModificationDate]
           ,[FieldName]
           ,[Description]
           ,[PageIndex]
           ,[DataType]
           ,[SortOrder]
           ,[FieldSize]
           ,[ForceValue]
           ,[DecimalPlaces]
           ,[DataBaseTableName]
           ,[IsVisible])
select
           getdate()
           ,getdate()
           ,getdate()
           ,getdate()
           ,fieldname
           ,fieldname
           ,0
           ,udfDataType
           ,0
           ,max_length
           ,0
           ,scale
           ,'loans2'
           ,1
from udf_columns

Page tree

Versions Compared

Old Version 4

New Version Current

Key