Back to Home

Negative Values in Checklist Setup

Overview

Due to either a bug or fields being updated in Checklist Management during the middle of an audit, an audit can end up with a negative count in Checklist Setup. This prevents the audit from moving forward.

Steps

1. Run the top of Script 1 to find any instances where a field was used that was deleted or inactivated
2. Add the update script in Script 1 as a SAFE script
3. If Step 1 does not identify the audit in question, more digging/research is needed.

Script 1

declare @UniqueInstanceNo int = 47590

select * from smart.p11_AuditChecklist_FieldList where uniqueinstanceno1 = @UniqueInstanceNo
and fieldno not in (select fieldno from smart.p11_fieldlist where currentstatus = 1 and deleted = 0)

select * from smart.p11_auditchecklist_confirmed where uniqueauditchecklistno in (
select uniqueauditchecklistno from smart.p11_auditchecklist where uniqueinstanceno = @UniqueInstanceNo)
and fieldno not in (select fieldno from smart.p11_fieldlist where currentstatus = 1 and deleted = 0)

--Update Script
--update smart.p11_AuditChecklist_FieldList set Deleted = 1 where uniqueinstanceno1 = @UniqueInstanceNo
--and fieldno not in (select fieldno from smart.p11_fieldlist where currentstatus = 1 and deleted = 0)

--update smart.p11_auditchecklist_confirmed set Deleted = 1 where uniqueauditchecklistno in (
--select uniqueauditchecklistno from smart.p11_auditchecklist where uniqueinstanceno = @UniqueInstanceNo)
--and fieldno not in (select fieldno from smart.p11_fieldlist where currentstatus = 1 and deleted = 0)


-- find all
select distinct b.uniqueinstanceno from smart.p11_AuditChecklist_FieldList a
inner join smart.P11_IntakeForm b on a.uniqueinstanceno1 = b.uniqueinstanceno and stageno < 3
where fieldno not in (select fieldno from smart.p11_fieldlist where currentstatus = 1 and deleted = 0)
and a.deleted = 0

select distinct c.uniqueinstanceno from smart.p11_auditchecklist_confirmed a
inner join smart.p11_auditchecklist b on a.uniqueauditchecklistno = b.uniqueauditchecklistno
inner join smart.P11_IntakeForm c on c.uniqueinstanceno = b.uniqueinstanceno and stageno < 3
and fieldno not in (select fieldno from smart.p11_fieldlist where currentstatus = 1 and deleted = 0)
and a.deleted = 0

-- places where incorrect fields were selected
select * from smart.p11_auditchecklist_confirmed a
inner join smart.p11_auditchecklist b on a.uniqueauditchecklistno = b.uniqueauditchecklistno and UniqueInstanceNo = 47590
left join smart.p11_fieldlist c on b.SectionNo = c.SectionNo and a.FieldNo = c.FieldNo
where c.fieldno is null