Blog ala Vidar

In AppFrame we’ve got something called value lists. These are meant to make filtering even easier. When creating your filter in for example a “Documents” CR3 form, and you choose the field “Responsible”, this of course have a foreign key to some kind of Persons-table. When choosing Responsible, why not produce a list of Persons so that you can click on the person instead of having to type in the PersonID or what ever is making it unique? Well, that’s the point of value lists. I just created a script to auto populate these, based on the foreign keys. This will only take the FKs that are bound on one column though! I’ll probably fix that tomorrow, but till then, here’s the script:

DECLARE
  @ForeignKeyBaseTable AS NVARCHAR(128),
  @PrimaryKeyTable AS NVARCHAR(128),
  @ForeignKeyColumn AS NVARCHAR(128),
  @PrimaryKeyColumn AS NVARCHAR(128),
  @ColumnName AS NVARCHAR(128),
  @ColumnLength AS SMALLINT,
  @IsNullable NVARCHAR(5),
  @ColumnType NVARCHAR(15),
  @SQL AS NVARCHAR(MAX)

DECLARE @SelectColumns AS TABLE (
	ColumnName NVARCHAR(128),
	ColumnLength INT,
	IsNullable NVARCHAR(5)
)

SET NOCOUNT ON

SELECT
  O.name AS ForeignKeyBaseTable,
  OBJECT_NAME(FK.referenced_object_id) AS PrimaryKeyTable,
  FKC.constraint_column_id AS ColumnNumber,
  FKColumn.name AS ForeignKeyColumn,
  PKColumn.name AS PrimaryKeyColumn,
  CASE WHEN T.name IN ('tinyint', 'smallint', 'int') THEN 'Int32'
		WHEN T.name IN ('smalldatetime', 'datetime', 'date', 'time') THEN 'DateTime'
		WHEN T.name IN ('float', 'decimal', 'numeric') THEN 'Long'
		WHEN T.name IN ('bigint') THEN 'Int64'
		ELSE NULL
  END AS ColumnType,
  FKColumn.max_length AS ColumnLength,
  CASE WHEN FKColumn.is_nullable = 0 THEN 'false' ELSE 'true' END AS IsNullable
	INTO #FKColumns
	FROM sys.foreign_keys AS FK
	INNER JOIN sys.objects AS O ON FK.parent_object_id = O.object_id
	INNER JOIN sys.foreign_key_columns AS FKC ON FKC.constraint_object_id = FK.object_id
	INNER JOIN sys.columns AS FKColumn
		ON FKColumn.object_id = FKC.parent_object_id
		  AND FKColumn.column_id = FKC.parent_column_id
	INNER JOIN sys.columns AS PKColumn
		ON PKColumn.object_id = FKC.referenced_object_id
		  AND PKColumn.column_id = FKC.referenced_column_id
	INNER JOIN sys.types AS T
		ON FKColumn.system_type_id = T.system_type_id
		  AND FKColumn.user_type_id = T.user_type_id
	WHERE T.name IN ('tinyint', 'smallint', 'int', 'smalldatetime',
					'datetime', 'date', 'time', 'float', 'decimal',
					'numeric', 'bigint', 'varchar', 'nvarchar')
	  AND O.type = 'U'
	  AND O.name LIKE 'atbl_%'
	  AND NOT EXISTS (SELECT *
						FROM sys.foreign_key_columns
						WHERE constraint_object_id = FK.object_id
						  AND constraint_column_id > 1)
	ORDER BY O.name, OBJECT_NAME(FK.referenced_object_id), FKC.constraint_column_id

WHILE EXISTS (SELECT * FROM #FKColumns)
BEGIN
	SELECT TOP 1
	  @ForeignKeyBaseTable = ForeignKeyBaseTable,
	  @PrimaryKeyTable = PrimaryKeyTable,
	  @ForeignKeyColumn = ForeignKeyColumn,
	  @PrimaryKeyColumn = PrimaryKeyColumn,
	  @ColumnType = ColumnType,
	  @ColumnLength = ColumnLength,
	  @IsNullable = IsNullable
		FROM #FKColumns

	SET @SQL = '<?xml version="1.0" encoding="utf-16"?>
<afRecordSource xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <IsFilterChanging>false</IsFilterChanging>
  <UniqueTable>' + @PrimaryKeyTable + '</UniqueTable>
  <ViewName>' + REPLACE(@PrimaryKeyTable, 'atbl_', 'atbv_') + '</ViewName>
  <SelectColumns>
	<afSelectColumn>
	  <Name>' + @PrimaryKeyColumn + '</Name>
	  <DisplayFormatType>0</DisplayFormatType>
	  <DisplayWidth>0</DisplayWidth>
	  <DisplayAlignment>0</DisplayAlignment>
	  <FieldAlias />'

	IF @ColumnType IS NOT NULL
	BEGIN
		SET @SQL = @SQL + '	  <DataType>' + @ColumnType + '</DataType>'
	END

	SET @SQL = @SQL + '
	  <Size>' + CAST(@ColumnLength AS NVARCHAR(128)) + '</Size>
	  <Nullable>' + @IsNullable + '</Nullable>
	</afSelectColumn>'


	INSERT INTO @SelectColumns
	  (ColumnName, ColumnLength, IsNullable)
	SELECT
	  name, max_length, CASE WHEN is_nullable = 1 THEN 'true' ELSE 'false' END
		FROM sys.columns
		WHERE object_id = OBJECT_ID(@PrimaryKeyTable)
		  AND (name LIKE '%Description%'
			OR REPLACE(name, 'ID', '') LIKE REPLACE(@PrimaryKeyColumn, 'ID', ''))
		  AND name <> @PrimaryKeyColumn

	WHILE EXISTS (SELECT * FROM @SelectColumns)
	BEGIN
		SELECT TOP 1
		  @ColumnName = ColumnName,
		  @ColumnLength = ColumnLength,
		  @IsNullable = IsNullable
			FROM @SelectColumns

		SET @SQL = @SQL + '
	<afSelectColumn>
	  <Name>' + @ColumnName + '</Name>
	  <DisplayFormatType>0</DisplayFormatType>
	  <DisplayWidth>0</DisplayWidth>
	  <DisplayAlignment>0</DisplayAlignment>
	  <FieldAlias />
	  <Size>' + CAST(@ColumnLength AS NVARCHAR(128)) + '</Size>
	  <Nullable>' + @IsNullable + '</Nullable>
	</afSelectColumn>'

		DELETE
			FROM @SelectColumns
			WHERE ColumnName = @ColumnName
	END


	SET @SQL = @SQL + '
  </SelectColumns>
  <OrderByColumns>
	<afOrderByColumn>
	  <Name>' + @PrimaryKeyColumn + '</Name>
	</afOrderByColumn>
  </OrderByColumns>
  <GroupByColumns />
  <WhereClause />
  <FilterString />
  <DistinctRows>false</DistinctRows>
  <MaxRecords>0</MaxRecords>
  <IgnoreDesignTimeMaxRecords>false</IgnoreDesignTimeMaxRecords>
  <MasterChildCriteria />
  <DisableAutoLoad>false</DisableAutoLoad>
  <InitMode>false</InitMode>
</afRecordSource>'

	UPDATE stbl_Database_Fields
		SET
		  ValueListRecordSource = @SQL,
		  ValueListValueMember = @PrimaryKeyColumn
		WHERE DBObjectID IN (@ForeignKeyBaseTable,
							REPLACE(@ForeignKeyBaseTable, 'atbl_', 'atbv_'),
							REPLACE(@ForeignKeyBaseTable, 'atbl_', 'atbx_'),
							REPLACE(@ForeignKeyBaseTable, 'atbl_', 'aviw_'))
		  AND FieldName = @ForeignKeyColumn

	DELETE
		FROM #FKColumns
		WHERE ForeignKeyBaseTable = @ForeignKeyBaseTable
		  AND PrimaryKeyTable = @PrimaryKeyTable
		  AND ForeignKeyColumn = @ForeignKeyColumn
		  AND PrimaryKeyColumn = @PrimaryKeyColumn
END

DROP TABLE #FKColumns