[Beta 19.1.7] Can't correct a workflow attribute that contains invalid SQL as a qualifier value

[ElimAdmin]

Description

When trying to edit a Workflow attribute qualifier value that contains SQL, if the SQL is invalid there is no way to get back in to the attribute to correct it; the UI locks the user out of editing the attribute. This applies to attributes of type single-select, multi-select. I haven't looked to see if this also applies to other attribute types.

May be related to #6847 and #6846.

Refer to Workflow /Samples/Test wf 1 in the demo site, if it hasn't been updated.

Actual Behavior

The UI gives an error message if a workflow attribute, of type single-select or multi-select, TYPE field contains invalid SQL. The error message is given when the user tries to edit the attribute, not when the attribute is saved.

There is then no way using the UI to get back to the attribute value to correct it. The attribute must be deleted and re-entered, or corrected using an SQL update.

Expected Behavior

The error message should be given as soon as the attribute is saved, possibly even to prevent saving?

The error should not prevent the user from editing the workflow attribute to update the QualifierValue.Type field.

Steps to Reproduce

1. Add or maintain a workflow.
2. Add a workflow attribute, e.g., ListOfNames, make it of Field Type Single-Select.
3. Add the SQL: SELECT Id, FirstName FROM Person p WHERE p.[LastName] = 'Decker';
4. Observe that the attribute can be saved and later re-edited. (Existing correct functionality.)
5. Edit the attribute, and make the SQL invalid: SELECT Id, Name FROM Person p WHERE p.[LastName] = 'Decker';
6. Save the attribute. Note that NO WARNING OR ERROR MESSAGE IS GIVEN alerting the user to the fact that the SQL is invalid.
7. Try to edit the attribute. Observe the error message: Invalid column name. The actual error is irrelevant, it could be for any reason the SQL is invalid.
8. There is now no way the user can get back to the value to correct it.

For reference, the following SQL can be used to locate and update the QualifierValue.Value using SQL:

-- Find the AttributeQualifier row holding the SQL where @WorkflowTypeId is the id of the workflow.
DECLARE @WorkflowTypeId INT = 24;
SELECT
a.[Id] AS [AttributeId],
a.[Key],
a.[Name] AS [AttributeName],
ft.[Name] AS [FieldType],
aq.[Id] AS QualifierId],
aq.[Key] AS [QualifierKey],
aq.[Value] AS [QualifierValue]
FROM [Attribute] a
INNER JOIN [FieldType] ft
ON ft.[Id] = a.[FieldTypeId]
LEFT JOIN [AttributeQualifier] aq
ON aq.[AttributeId] = a.[Id]
WHERE a.[EntityTypeQualifierColumn] = 'WorkflowTypeId'
AND a.[EntityTypeQualifierValue] = CAST(@WorkflowTypeId AS VARCHAR(20))
AND ft.[Name] = 'Single-Select'
ORDER BY
a.[Order],
a.[Name],
aq.[Key];

-- Use the QualifierId to view and update the qualifier value
SELECT *
FROM AttributeQualifier
WHERE Id = 14428;

UPDATE AttributeQualifier
SET Value = 'SELECT Id, FirstName FROM Person p WHERE LastName = ''Decker'';'
WHERE Id = 14428;

Issue Confirmation

• Perform a search on the Github Issues to see if your bug is already reported.
• Reproduced the problem on a fresh install or on the demo site.

Rock Version

19.1.7 (and earlier)

Client Culture Setting

en-NZ