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;