How to fix the global search results?

1 Incorrect results in global search when searching MRN due to a bug in v6.3
2 Specimen migrated using fast-loader, not global searchable using its Barcode.

Incorrect results in global search when searching MRN due to a bug in v6.3

In version 6.3 and before, there was a bug that caused incorrect results to come up in the global search on searching for an MRN (Medical Record Number). For example, a user wants to look for a participant with MRN' 12345' (imaginary numbers); the global search would show a participant with PPID' 10102', which does not have the MRN. This issue is fixed in v7.0, hence for versions prior to that; this issue needs to be set manually from the database.

The table OS_SEARCH_ENTITY_KEYWORDS is used to show the results for the items searched in the global search.

How to fix the search issue?

Please execute all the below SQLs on a test environment and then on the production server.

Step 1: Delete the incorrect MRN search keywords.

delete from
  os_search_entity_keywords
where
  entity = 'collection_protocol_registration' and 
  name = 'medicalRecordNumber'

Step 2: Feed the correct MRN search keywords pointing to the correct registration records

insert into
  os_search_entity_keywords(identifier, entity, entity_id, name, value, status)
select
  os_search_entity_keywords_seq.nextval, 
  'collection_protocol_registration', r.identifier, 
  'medicalRecordNumber', lower(pmi.medical_record_number), 1
from
  catissue_part_medical_id pmi
  inner join catissue_participant p on p.identifier = pmi.participant_id
  inner join catissue_coll_prot_reg r on r.participant_id = p.identifier
where
  p.activity_status != 'Disabled' and
  r.activity_status != 'Disabled' and
  pmi.medical_record_number is not null;

Specimen migrated using fast-loader, not global searchable using its Barcode.

How to fix the search issue?

Please execute all the below SQLs on a test environment and then on the production server.

Step 1: Delete Barcode entries of already present specimens. As only executing the insert statement will lead to duplicate entries.

delete 
  from os_search_entity_keywords 
where 
  entity = 'specimen' 
  and name = 'barcode';

Step 2: Insert Specimen Barcode and required details in os_search_entity_keywords table.

insert into 
  os_search_entity_keywords(entity, entity_id, name, value, status)
select 
  'specimen',
  s.identifier,
  'barcode',
  lower(BARCODE),
  1
from 
  catissue_specimen s
where 
  s.activity_status != 'Disabled'
  and s.barcode is not null;

OpenSpecimen

How to fix the global search results?

Incorrect results in global search when searching MRN due to a bug in v6.3

Specimen migrated using fast-loader, not global searchable using its Barcode.

Related content