Back to Home

Copy Intake Fields and Options

Overview

Business requests updates to copy Dynamic Intake Fields and Dynamic Options from existing Dynamic Intake Forms.

Script 1

• Ensure business has added the new DynamicIntakeFormNo in Intake Form Setup page
• Ensure no data has already been entered into smart.p11_Dynamic_IntakeField & smart.p11_Dynamic_IntakeOption
• Add appropiate values and run Script 1

Note: If business requests multiple intake fields and options to be copied, ensure the temp table name is unique per intake. Example: Copy current script and rename the second script with all places with #temp to #temp1.

• See [Script 1] for example.

DECLARE @OriginalDynamicIntakeFormNo INT = 1,
@NewDynamicIntakeFormNo INT = 49
SELECT a.DynamicINtakeFieldNo, a.DynamicIntakeFormNo, a.IntakeFieldNo, a.IsEditable, a.Alias, a.IsRequired,
a.MinimumValue, a.IsPrePopulated, a.IsDefault, a.Preselected, a.IsHidden, a.Deleted,
b.IntakeOptionNo, b.IsDefault as IsDefaultOption, NULL AS NewDynamicIntakeFieldNo INTO #temp
FROM smart.p11_Dynamic_IntakeField a
LEFT JOIN smart.p11_Dynamic_IntakeOption b ON a.DynamicIntakeFieldNo = b.DynamicIntakeFieldNo AND b.Deleted = 0
WHERE a.DynamicIntakeFormNo = @OriginalDynamicIntakeFormNo AND a.Deleted = 0

INSERT INTO smart.p11_Dynamic_IntakeField (DynamicIntakeFormNo, IntakeFieldNo, IsEditable, Alias, IsRequired, 
MinimumValue, IsPrepopulated, IsDefault, PreSelected, IsHidden)
SELECT DISTINCT @NewDynamicIntakeFormNo, IntakeFieldNo, IsEditable, Alias, IsRequired, 
MinimumValue, IsPrepopulated, IsDefault, PreSelected, IsHidden FROM #temp

UPDATE #temp SET NewDynamicIntakeFieldNo = b.DynamicIntakeFieldNo FROM #temp a
INNER JOIN smart.p11_Dynamic_IntakeField b ON a.IntakeFieldNo = b.IntakeFIeldNo AND b.Deleted = 0
WHERE b.DynamicIntakeFormNo = @NewDynamicIntakeFormNo

INSERT INTO smart.p11_Dynamic_IntakeOption (DynamicIntakeFieldNo, IntakeOptionNo, IsDefault)
SELECT NewDynamicIntakeFieldNo, IntakeOptionNo, IsDefaultOption FROM #temp
WHERE IntakeOptionNo IS NOT NULL