USE [Resources02_def]
GO

/*** CONTAINER FIELDS ****/

UPDATE tblDtsFields
SET tName='Container',
	ntabledstype=32,
	tCaption='Container',
	tFieldValuesTable='tblDts',
	nValueTableDsType=32,
	tWhere='WHERE [Track_As]=3 AND [nUsageType]=1',
	tLabelCaption='Container',
	tValueTableNameFld='tBriefDescription',
	tBoundControlName='1095'
WHERE niD=1095

GO

IF NOT EXISTS ( SELECT nID FROM [dbo].[tblDtsFields]  WHERE nID=76)
	INSERT [dbo].[tblDtsFields] 
		([nID], [tTableName], [nTableDSType], [tName], [nDataViewID], [nDataType], [nDataSize], [tCaption], [fChoiceOrder], [fRequired], [tFieldValuesTable], 
		[nValueTableDSType], [fEnabled], [fKeepHistory], [fCanQueryBy], [tDestinationTab], [fEditable], [fVisible], [nRelatedField], [tLabelCaption],
		[tAliasSourceTable], [tBoundControlName], [nDestinationTabID], [tWhere], [fIncludeInDefaultReports], [nRelationshipType], 
		[nContainerType], [nControlType], [tValueTableIDFld], [tValueTableNameFld], [nRevisionNumber], [tOrigCaption]) 
	VALUES (76, N'Dts', 32, N'nUserID', 1, 103, 50, N'Responsible of Container', 0, 0, N'tblUser', 
		16, 0, 0, 1, NULL, 1, 1, NULL, N'Responsible of Container', 
		N'Container;nID', N'76', 30, NULL, 0, 1, 
		NULL, NULL, NULL, NULL, NULL, NULL)

GO


/*** REPAIRS & LEMON ****/
IF NOT EXISTS ( SELECT nID FROM [dbo].[tblDtsFields]  WHERE nID=68)
	INSERT [dbo].[tblDtsFields] 
		([nID], [tTableName], [nTableDSType], [tName], [nDataViewID], [nDataType], [nDataSize], [tCaption], [fChoiceOrder], 
		[fRequired], [tFieldValuesTable], [nValueTableDSType], [fEnabled], [fKeepHistory], [fCanQueryBy], [tDestinationTab], [fEditable], [fVisible], 
		[nRelatedField], [tLabelCaption], [tAliasSourceTable], [tBoundControlName], [nDestinationTabID], [tWhere], [fIncludeInDefaultReports], 
		[nRelationshipType], [nContainerType], [nControlType], [tValueTableIDFld], [tValueTableNameFld], [nRevisionNumber], [tOrigCaption]) 
	VALUES (68, N'tblDts', 32, N'Number_of_Repairs', 1, 102, 50, N'Number of Repairs', 0, 0, NULL, NULL, 1, 1, 1, NULL, 1, 1, NULL, 
		N'Number of Repairs', NULL, N'68', 3, NULL, 0, -1, 1, NULL, NULL, NULL, 2, NULL)

GO

IF NOT EXISTS ( SELECT nID FROM [dbo].[tblDtsFields]  WHERE nID=69)
	INSERT [dbo].[tblDtsFields] 
		([nID], [tTableName], [nTableDSType], [tName], [nDataViewID], [nDataType], [nDataSize], [tCaption], [fChoiceOrder], 
		[fRequired], [tFieldValuesTable], [nValueTableDSType], [fEnabled], [fKeepHistory], [fCanQueryBy], [tDestinationTab], [fEditable], [fVisible], 
		[nRelatedField], [tLabelCaption], [tAliasSourceTable], [tBoundControlName], [nDestinationTabID], [tWhere], [fIncludeInDefaultReports], 
		[nRelationshipType], [nContainerType], [nControlType], [tValueTableIDFld], [tValueTableNameFld], [nRevisionNumber], [tOrigCaption]) 
	VALUES (69, N'tblDts', 32, N'Is_Lemon', 1, 103, 50, N'Is Lemon', 0, 0, N'tblYesNo', 16, 1, 1, 1, NULL, 1, 1, NULL, 
		N'Is Lemon', NULL, N'69', 3, NULL, 0, -1, 1, NULL, NULL, NULL, 2, NULL)

GO

/*** FOR SCHOOLS - RELATED STUDENT INFO ****/
IF NOT EXISTS ( SELECT nID FROM [dbo].[tblDtsFields]  WHERE nID=70)
	INSERT [dbo].[tblDtsFields] 
		([nID], [tTableName], [nTableDSType], [tName], [nDataViewID], [nDataType], [nDataSize], [tCaption], [fChoiceOrder], 
		[fRequired], [tFieldValuesTable], [nValueTableDSType], [fEnabled], [fKeepHistory], [fCanQueryBy], [tDestinationTab], [fEditable], [fVisible], 
		[nRelatedField], [tLabelCaption], [tAliasSourceTable], [tBoundControlName], [nDestinationTabID], [tWhere], [fIncludeInDefaultReports], 
		[nRelationshipType], [nContainerType], [nControlType], [tValueTableIDFld], [tValueTableNameFld], [nRevisionNumber], [tOrigCaption]) 
	VALUES (70, N'tblDts', 32, N'Assignee_Grade_Level', 1, 10, 50, N'Assignee Grade Level', 0, 0, NULL, NULL, 0, 1, 1, NULL, 1, 1, NULL, 
		N'Grade Level', NULL, N'70', 1, NULL, 0, -1, 1, NULL, NULL, NULL, 6, NULL)

GO

IF NOT EXISTS ( SELECT nID FROM [dbo].[tblDtsFields]  WHERE nID=71)
	INSERT [dbo].[tblDtsFields] 
		([nID], [tTableName], [nTableDSType], [tName], [nDataViewID], [nDataType], [nDataSize], [tCaption], [fChoiceOrder], 
		[fRequired], [tFieldValuesTable], [nValueTableDSType], [fEnabled], [fKeepHistory], [fCanQueryBy], [tDestinationTab], [fEditable], [fVisible], 
		[nRelatedField], [tLabelCaption], [tAliasSourceTable], [tBoundControlName], [nDestinationTabID], [tWhere], [fIncludeInDefaultReports], 
		[nRelationshipType], [nContainerType], [nControlType], [tValueTableIDFld], [tValueTableNameFld], [nRevisionNumber], [tOrigCaption]) 
	VALUES (71, N'tblDts', 32, N'Assignee_ID', 1, 10, 50, N'Assignee ID', 0, 0, NULL, NULL, 0, 1, 1, NULL, 1, 1, NULL, 
		N'Employee ID', NULL, N'71', 1, NULL, 0, -1, 1, NULL, NULL, NULL, 3, 'Assignee ID')

GO

IF NOT EXISTS ( SELECT nID FROM [dbo].[tblDtsFields]  WHERE nID=72)
	INSERT [dbo].[tblDtsFields] 
		([nID], [tTableName], [nTableDSType], [tName], [nDataViewID], [nDataType], [nDataSize], [tCaption], [fChoiceOrder], 
		[fRequired], [tFieldValuesTable], [nValueTableDSType], [fEnabled], [fKeepHistory], [fCanQueryBy], [tDestinationTab], [fEditable], [fVisible], 
		[nRelatedField], [tLabelCaption], [tAliasSourceTable], [tBoundControlName], [nDestinationTabID], [tWhere], [fIncludeInDefaultReports], 
		[nRelationshipType], [nContainerType], [nControlType], [tValueTableIDFld], [tValueTableNameFld], [nRevisionNumber], [tOrigCaption]) 
	VALUES (72, N'tblDts', 32, N'Assignee_Homeroom_Teacher', 1, 103, 50, N'Assignee Homeroom Teacher', 0, 0, N'tblUser', 16, 0, 1, 1, NULL, 1, 1, NULL, 
		N'Homeroom Teacher', NULL, N'72', 1, NULL, 0, -1, 1, NULL, NULL, NULL, 3, NULL)

GO

IF NOT EXISTS ( SELECT nID FROM [dbo].[tblDtsFields]  WHERE nID=73)
	INSERT [dbo].[tblDtsFields] 
		([nID], [tTableName], [nTableDSType], [tName], [nDataViewID], [nDataType], [nDataSize], [tCaption], [fChoiceOrder], 
		[fRequired], [tFieldValuesTable], [nValueTableDSType], [fEnabled], [fKeepHistory], [fCanQueryBy], [tDestinationTab], [fEditable], [fVisible], 
		[nRelatedField], [tLabelCaption], [tAliasSourceTable], [tBoundControlName], [nDestinationTabID], [tWhere], [fIncludeInDefaultReports], 
		[nRelationshipType], [nContainerType], [nControlType], [tValueTableIDFld], [tValueTableNameFld], [nRevisionNumber], [tOrigCaption]) 
	VALUES (73, N'tblDts', 32, N'Assignee_Location', 1, 103, 50, N'Assignee Location', 0, 0, N'Location', 16, 0, 1, 1, NULL, 1, 1, NULL, 
		N'Location', NULL, N'73', 1, NULL, 0, -1, 1, NULL, NULL, NULL, 3, NULL)

GO

IF NOT EXISTS ( SELECT nID FROM [dbo].[tblDtsFields]  WHERE nID=74)
	INSERT [dbo].[tblDtsFields] 
		([nID], [tTableName], [nTableDSType], [tName], [nDataViewID], [nDataType], [nDataSize], [tCaption], [fChoiceOrder], 
		[fRequired], [tFieldValuesTable], [nValueTableDSType], [fEnabled], [fKeepHistory], [fCanQueryBy], [tDestinationTab], [fEditable], [fVisible], 
		[nRelatedField], [tLabelCaption], [tAliasSourceTable], [tBoundControlName], [nDestinationTabID], [tWhere], [fIncludeInDefaultReports], 
		[nRelationshipType], [nContainerType], [nControlType], [tValueTableIDFld], [tValueTableNameFld], [nRevisionNumber], [tOrigCaption]) 
	VALUES (74, N'tblDts', 32, N'Assignee_Photo', 1, 10, 50, N'Assignee Photo', 0, 0, NULL, NULL, 0, 1, 1, NULL, 1, 1, NULL, 
		N'Photo', NULL, N'74', 1, NULL, 0, -1, 1, NULL, NULL, NULL, 3, NULL)

GO

IF NOT EXISTS ( SELECT nID FROM [dbo].[tblDtsFields]  WHERE nID=75)
	INSERT [dbo].[tblDtsFields] 
		([nID], [tTableName], [nTableDSType], [tName], [nDataViewID], [nDataType], [nDataSize], [tCaption], [fChoiceOrder], 
		[fRequired], [tFieldValuesTable], [nValueTableDSType], [fEnabled], [fKeepHistory], [fCanQueryBy], [tDestinationTab], [fEditable], [fVisible], 
		[nRelatedField], [tLabelCaption], [tAliasSourceTable], [tBoundControlName], [nDestinationTabID], [tWhere], [fIncludeInDefaultReports], 
		[nRelationshipType], [nContainerType], [nControlType], [tValueTableIDFld], [tValueTableNameFld], [nRevisionNumber], [tOrigCaption]) 
	VALUES (75, N'tblDts', 32, N'Assignee_Category', 1, 103, 50, N'Assignee Category', 0, 0, N'tblUserCategory', 16, 1, 1, 1, NULL, 1, 1, NULL, 
		N'Assignee Category', NULL, N'75', 1, NULL, 0, -1, 1, NULL, NULL, NULL, 2, NULL)

GO

UPDATE [Resources02_Def].dbo.[tblDtsFields] 
	SET nRelatedField=1082
	WHERE nID=74

GO


/*** MIGRATE LOCATION TO ASSET_LOCATION ****/

UPDATE [Resources02_Def].dbo.[tblDtsFields] 
	SET tName='Asset_Location',
	tCaption='Location (Overview Tab)',
	tLabelCaption='Location'
	WHERE nID=1196

GO


/*** RENAME FIELDS ****/
UPDATE tblDtsFields
	SET tCaption='Return Date',
	tLabelCaption='Return Date',
	tBoundControlName='1054'
	WHERE niD=1054

GO

UPDATE tblDtsFields
	SET tCaption='Return Date (Overview)',
	tLabelCaption='Return Date',
	tBoundControlName='1078'
	WHERE niD=1078
GO

UPDATE tblDtsFields
	SET tCaption='Last Assigned Date',
	tLabelCaption='Last Assigned Date',
	tBoundControlName='7'
	WHERE niD=7

GO


