Skip to main content
Skip table of contents

SQL - Upgrade to 8.0 Contract Links

Description

SQL used to associate contract documents using new document pattern in 8.x

Usage

Adds contract_documents record for each contract document

Updates existing document links to new root_id and root_type structure

Enables document tab for any contract that doesn’t have it but has a document attachment

SQL

CODE
/* Create a contract_documents record from each contract document. */
INSERT INTO contract_documents 
SELECT
	root_id AS contract_id,
	id AS document_id,
	(
		SELECT
			id
		FROM
			document_types
		WHERE
			name = 'Contract') AS document_type_id, NULL AS notes, updated_by_id, created_at, updated_at
	FROM
		documents
	WHERE
		root_type = 'Contract';

/* Update documents to new root_id and root_type structure */
UPDATE
	d
SET
	d.root_id = cd.id,
	d.root_type = 'ContractDocument'
FROM
	documents d
	JOIN contract_documents cd ON cd.document_id = d.id
WHERE
	d.root_type = 'Contract';

/* Enable documents tab for any contract with a document */
UPDATE
	contracts
SET
	contract_documents_enabled = 1
WHERE
	anchor_id in( SELECT DISTINCT
			anchor_id FROM contracts c
			JOIN contract_documents cd ON c.id = cd.contract_id
		WHERE
			ISNULL(c.contract_documents_enabled, 0) <> 1);
JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.