Checking and updating the "Sum Defects" field

In some versions of GainSeeker, deleting all defects from a DMS data record did not reset the "Sum Defects" field for that record. This behavior was fixed in GainSeeker version 8.7.

Checking your DDATA tables

If you wish to manually check your system for DMS data records where the "Sum Defects" field does not match the sum of all the defects in the record, run the following SQL query. You will need to change the table name in the query for each copy of the DDATA table in your GainSeeker database (e.g. DDATA, DDATA_TOUR, DDATA_PRODUCTION, etc.)

 

Select

  PARTNO,

  DATETIME,

  SUMDEFECTS,

  (DEFECTCNT1 + DEFECTCNT2 + DEFECTCNT3 + DEFECTCNT4 + DEFECTCNT5 + DEFECTCNT6 + DEFECTCNT7 + DEFECTCNT8 + DEFECTCNT9 + DEFECTCNT10 + DEFECTCNT11 + DEFECTCNT12 + DEFECTCNT13 + DEFECTCNT14 + DEFECTCNT15 + DEFECTCNT16 + DEFECTCNT17 + DEFECTCNT18 + DEFECTCNT19 + DEFECTCNT20) as DefectCount

 

From DDATA

 

Where SUMDEFECTS <> (DEFECTCNT1 + DEFECTCNT2 + DEFECTCNT3 + DEFECTCNT4 + DEFECTCNT5 + DEFECTCNT6 + DEFECTCNT7 + DEFECTCNT8 + DEFECTCNT9 + DEFECTCNT10 + DEFECTCNT11 + DEFECTCNT12 + DEFECTCNT13 + DEFECTCNT14 + DEFECTCNT15 + DEFECTCNT16 + DEFECTCNT17 + DEFECTCNT18 + DEFECTCNT19 + DEFECTCNT20)

 

Updating your DDATA tables

If the above check returns any data records, you can run the following SQL query to reset the "Sum Defects" field to the correct value. As with the script above, you may need to change the table name in the query.

 

Update DDATA

 

Set SUMDEFECTS = (DEFECTCNT1 + DEFECTCNT2 + DEFECTCNT3 + DEFECTCNT4 + DEFECTCNT5 + DEFECTCNT6 + DEFECTCNT7 + DEFECTCNT8 + DEFECTCNT9 + DEFECTCNT10 + DEFECTCNT11 + DEFECTCNT12 + DEFECTCNT13 + DEFECTCNT14 + DEFECTCNT15 + DEFECTCNT16 + DEFECTCNT17 + DEFECTCNT18 + DEFECTCNT19 + DEFECTCNT20)

 

Where SUMDEFECTS <> (DEFECTCNT1 + DEFECTCNT2 + DEFECTCNT3 + DEFECTCNT4 + DEFECTCNT5 + DEFECTCNT6 + DEFECTCNT7 + DEFECTCNT8 + DEFECTCNT9 + DEFECTCNT10 + DEFECTCNT11 + DEFECTCNT12 + DEFECTCNT13 + DEFECTCNT14 + DEFECTCNT15 + DEFECTCNT16 + DEFECTCNT17 + DEFECTCNT18 + DEFECTCNT19 + DEFECTCNT20)