Check Link Table Approval State NEW

Each item that is updated should have one DEF that has approval_state = NEW. We have found few items in several database that does not have any row with approval_state = NEW.

How it should look like

The Action 'Combine' and templates with more than one item are currently the main suspects.

Example where the first DEF has Combine instead of New

Example where the first DEF has Combine instead of New + the DEF template had 2 items

But does not happen every time Combine is used so Unapprove and deleting it may be involved. We have not seen this error created with 6.0.

Case Combine

The solution is actually not to add rows to the table link but to update some columns of existing rows.

Land

SELECT
"public".hazard.hazard_guid,
"public".hazard.location_guid,
"public".hazard.hazard_localid
from hazard
where not exists (
select 1
from hazardinfoversion
where hazard.hazard_guid = hazardinfoversion.hazard_guid
and exists (
select 1
from link
where approval_state = 'NEW'
and link.from_guid = hazardinfoversion.hazardinfoversion_guid
)
)
ORDER BY hazard_localid

Activity

select 
"public".hazreduc.hazreduc_guid,
"public".hazreduc.location_guid,
"public".hazreduc.hazreduc_localid
from hazreduc
where not exists (
select 1
from hazreducinfoversion
where hazreduc.hazreduc_guid = hazreducinfoversion.hazreduc_guid
and exists (
select 1
from link
where approval_state = 'NEW'
and link.from_guid = hazreducinfoversion.hazreducinfoversion_guid
)
)
ORDER BY "public".hazreduc.hazreduc_localid

Accident

select
"public".accident.accident_guid,
"public".accident.location_guid,
"public".accident.accident_localid
from accident
where not exists (
select 1
from accidentinfoversion
where accident.accident_guid = accidentinfoversion.accident_guid
and exists (
select 1
from link
where approval_state = 'NEW'
and link.from_guid = accidentinfoversion.accidentinfoversion_guid
)
)
ORDER BY "public".accident.accident_localid

Victim

SELECT
"public".victim.victim_guid,
"public".victim.location_guid,
"public".victim.victim_localid
from victim
where not exists (
select 1
from victiminfoversion
where victim.victim_guid = victiminfoversion.victim_guid
and exists (
select 1
from link
where approval_state = 'NEW'
and link.from_guid = victiminfoversion.victiminfoversion_guid
)
)
ORDER BY
"public".victim.victim_localid ASC

Education

SELECT
"public".mre.mre_guid,
"public".mre.location_guid,
"public".mre.mre_localid
from mre
where not exists (
select 1
from mreinfoversion
where mre.mre_guid = mreinfoversion.mre_guid
and exists (
select 1
from link
where approval_state = 'NEW'
and link.from_guid = mreinfoversion.mreinfoversion_guid
)
)
ORDER BY
"public".mre.mre_localid ASC

QM

SELECT
"public".qa.qa_guid,
"public".qa.location_guid,
"public".qa.qa_localid
from qa
where not exists (
select 1
from qainfoversion
where qa.qa_guid = qainfoversion.qa_guid
and exists (
select 1
from link
where approval_state = 'NEW'
and link.from_guid = qainfoversion.qainfoversion_guid
)
)
ORDER BY
"public".qa.qa_localid ASC

Template:NavBox Hub