PDF download failing after time request included in instrument picker question.

[Bhaswati1148]

Proposal pdf download is failing after the time request functionality is integrated with instrument picker question.

• Update end to end tests to include time request field in instrument picker question when downloading proposal pdfs.
• Migrating old instrument picker answer's data structure from json integer to json object in database.

[Bhaswati1148]

Query below to update answers with single instrument id

UPDATE answers SET answer = jsonb_set(answer, '{value}', jsonb_build_object('instrumentId', answer->>'value','timeRequested',null)) where question_id ='<instrument_picker_question_id>'

PL/SQL block below to update answers with multiple instrument ids

do
$$
declare rec record;
begin
	for rec in 
	with item as (
	select ('{value,' || pos - 1 || '}')::text[] as path, answer->'value'->((pos - 1)::int) as instrumentId, answer_id from answers, 
	jsonb_array_elements(answer->'value') with ordinality arr(item, pos) where question_id ='<instrument_picker_question_id>'
	)select * from item
	
	loop
		if jsonb_typeof(rec.instrumentId)='number' then
			update answers a
			set answer = jsonb_set(answer, rec.path, jsonb_build_object('instrumentId',rec.instrumentId::text,'timeRequested',null)) 
			where a.answer_id = rec.answer_id;
		raise notice 'Updated answer_id: % ', rec.answer_id;
		end if;
	end loop;
end;
$$;

[jekabs-karklins]

Hello @Bhaswati1148

I wanted to ask if we could reopen the issue with the query for updating answers with instrument IDs.
We had an recently experienced an issue in another facility with this patch. It looks like the main problem is a mismatch between where the patch pulls the is_multiple_select value from. It’s using default_config in the Questions table, but it should actually be looking at config in the templates_has_questions table since that’s what the answers are based on.

This mismatch is likely why the patch gets the datatype in answer wrong—especially in cases where a question changed from single-select to multi-select.

To make things simpler, maybe we could skip relying on ->is_multiple_select altogether and just handle it based on the datatype in the answer. If it’s an integer, treat it as one; if it’s an array, handle it as an array—regardless of the config value.

Let me know what you think!
I’m happy to jump on a quick call to check the current database state and figure out which questions might need temporary adjustments to get the migration working.