LMS - Bulk User Defined Field creation

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.

Create udf table

-- 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.

Append UDF columns

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.

Adding UDF to definition table

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

0 Comments