USE [Users_mdb]
GO


UPDATE [dbo].[tblDtsFields]
	SET tCaption='Location',
	tLabelCaption='Location'
	WHERE nID=259
GO

UPDATE [dbo].[tblDtsFields]
	SET tCaption='Location (Employment Popup)',
	tLabelCaption='Location' 
	WHERE nID=29
GO



/******** FOR WORKS WITH *************/
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tblRelWrksWith')
BEGIN
	CREATE TABLE [dbo].[tblRelWrksWith](
		[nID] [int] NOT NULL,
		[nRelID] [int] NOT NULL,
		CONSTRAINT [PK_tblRelWrksWith] PRIMARY KEY NONCLUSTERED 
		(
		[nID] ASC,
		[nRelID] ASC
		)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
		) ON [PRIMARY]
END

IF NOT EXISTS (SELECT * FROM [dbo].[tblDtsFields] WHERE nID=67)
		INSERT [Users_mdb].[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 (67, N'tblRelWrksWith', 32, N'nRelID', 1, 106, 0, 
		 N'Works With', 0, 0, NULL, 0, 1, 1, 1, NULL, 0, 1, 0, N'Works With', 
		 NULL, N'67', 2, NULL, 0, 2, 5, 1, NULL, NULL, NULL, NULL);

IF NOT EXISTS (SELECT * FROM [dbo].[tblDtsFields] WHERE nID=68)
	INSERT [Users_mdb].[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'tblRelWrksWith', 32, N'nID', 1, 103, 3, 
		 N'Services Provided By', 0, 0, 'tblUser', 16, 0, 0, 1, NULL, 1, 1, 0, N'Services Provided By', 
		 N';nRelID', N'68', 2, NULL, 0, 1, 7, 1, NULL, NULL, NULL, NULL);

GO

/*** FOR SCHOOLS ****/
IF NOT EXISTS (SELECT * 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'tblUser', 32, N'Grade_Level', 1, 10, 50, N'Grade_Level', 0, 0, NULL, NULL, 1, 1, 1, NULL, 1, 1, NULL, N'Grade_Level', NULL, N'69', 
	0, NULL, 0, -1, 1, NULL, NULL, NULL, 2, 'Grade_Level');
	
	DELETE FROM [dbo].[tblDtsFields] WHERE tName='Grade_Level' AND nID!=69;
GO

IF NOT EXISTS (SELECT * 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'tblUser', 32, N'Homeroom_Teacher', 1, 103, 50, N'Homeroom Teacher', 0, 0, N'tblUser', 16, 1, 1, 1, NULL, 1, 1, NULL, N'Homeroom Teacher', 
	NULL, N'70', 0, N'WHERE nCategoryID=16', 0, -1, 1, NULL, NULL, NULL, 4, 'Homeroom_Teacher');
	
	DELETE FROM [dbo].[tblDtsFields] WHERE tName='Homeroom_Teacher' AND nID!=70;
	
GO

IF NOT EXISTS (SELECT * 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'tblUser', 32, N'Parent_Name', 1, 10, 50, N'Parent Name', 0, 0, NULL, NULL, 1, 1, 1, NULL, 1, 1, NULL, N'Parent Name', NULL, N'71', 
	1, NULL, 0, -1, 1, NULL, NULL, NULL, 2, 'Parent_Name');
	
	DELETE FROM [dbo].[tblDtsFields] WHERE tName='Parent_Name' AND nID!=71;
GO
	
IF NOT EXISTS (SELECT * 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'tblUser', 32, N'Parent_Email', 1, 10, 50, N'Parent Email', 0, 0, NULL, NULL, 1, 1, 1, NULL, 1, 1, NULL, N'Parent Email', NULL, N'72',
	1, NULL, 0, -1, 1, NULL, NULL, NULL, 2, 'Parent_Email');
	
	DELETE FROM [dbo].[tblDtsFields] WHERE tName='Parent_Email' AND nID!=72;
GO

/*** Consolidate the Category fields into one: 21 nCategoryID  ***/
IF EXISTS (SELECT * FROM [dbo].[tblDtsFields] WHERE nID=120)	
	UPDATE [dbo].[tblDtsFields]
	SET tCaption='Category - Multiple Jobs',
	tLabelCaption='Category - Multiple Jobs'
	WHERE nID=120;

IF EXISTS (SELECT * FROM [dbo].[tblDtsFields] WHERE nID=250)	
	UPDATE [dbo].[tblDtsFields]
	SET tName='nCategoryID'
	WHERE nID=250;

IF EXISTS (SELECT * FROM [dbo].[tblDtsFields] WHERE nID=21)	
	UPDATE [dbo].[tblDtsFields]
	SET tCaption='Category'
	WHERE nID=21;

/*** Fix Job Title ***/
IF EXISTS(SELECT * FROM [tblDtsFields] WHERE nID=53)
	UPDATE [tblDtsFields]
	SET tCaption='Job Title - Multiple Jobs'
	WHERE nID=53;

IF EXISTS(SELECT * FROM [tblDtsFields] WHERE nID=253)
	UPDATE [tblDtsFields]
	SET fCanQueryBy=1,
	tCaption='Job Title',
	tOrigCaption='Job Title'
	WHERE nID=253;

	
	
