Automated Status messages for when to do Analytical PCR need update

[bthuronyi]

Currently, Analytical PCR is a Dashboard task but is handled differently than other tasks; it doesn't have a Status "ready to queue analytical PCR" message. Golden Gate g and Minipreps m both currently generate automated analytical PCRs. The relevant columns are "Queue analytical PCR?" and "Status of analytical PCR if queued". "Analytical PCR overall outcome" and "Outcomes of individual analytical PCRs" interact with Analytical PCR outcomes and are supposed to use these to influence downstream steps like whether something should be sequenced. These formulas should have good starting point code to fold into Status code.

"Analytical PCR Options" in Settings & admin now contains several checkbox settings relating to analytical PCR queueing. These specify different options for marking analytical PCRs ready to queue and for requiring that they be done before gIDs or mIDs are marked ready for later steps.

Status of gIDs and mIDs, and mID sequencing appropriateness, all need to be updated to reflect these options.

A formula needs to be added on Settings & admin below the analytical PCR Options that warns about inconsistent choices -- e.g. it's a bad idea to require that analytical PCRs be completed before mIDs derived from gID sources are marked ready to queue sequencing, but NOT show "ready to queue analytical PCR" in their statuses.

[bthuronyi]

@ethanjeon Expecting you/we can delegate this once we're ready to work on it, so just assigning to you for now.

[bthuronyi]

The same kind of analytical PCR requirements/options, enabled by Settings and implemented in Statuses, should also be enabled for Assemblies.

[evelynqi]

Settings_analytical_PCR_QueueOnTransformation Settings_analytical_PCR_RequireQueueingAfterTransformation Settings_analytical_PCR_QueueOnMiniprep Settings_analytical_PCR_RequireQueueingAfterMiniprep

[evelynqi]

A formula needs to be added on Settings & admin below the analytical PCR Options that warns about inconsistent choices -- e.g. it's a bad idea to require that analytical PCRs be completed before mIDs derived from gID sources are marked ready to queue sequencing, but NOT show "ready to queue analytical PCR" in their statuses.

https://docs.google.com/spreadsheets/d/1qXJL8xT-XuxN5ruPN3QUzeIuULg7BqmcrzEbKLBLIP0/edit?usp=sharing

=if( and(Settings_analytical_PCR_RequireQueueingAfterTransformation,not(Settings_analytical_PCR_QueueOnTransformation)),"WARNING: Analytical PCRs are required for Golden Gate assemblies before they can be queued for miniprep but Golden Gate gIDs are not currently automatically marked ready to queue analytical PCR.","") & if(and(Settings_analytical_PCR_RequireQueueingAfterMiniprep,not(Settings_analytical_PCR_QueueOnMiniprep)),char(10)&"WARNING: Analytical PCRs are required for gID-derived minipreps before they can be sequenced but gID-derived minipreps are currently not automatically marked ready to queue analytical PCR.", "")

It would also be excessive for the opposite to happen, where the minipreps and ggs are always marked ready to queue analytical PCR when it is not required for further steps. Should there be a warning for that too?

[bthuronyi]

It would also be excessive for the opposite to happen, where the minipreps and ggs are always marked ready to queue analytical PCR when it is not required for further steps. Should there be a warning for that too?

I agree that might seem annoying but it's a reasonable stance to take if you want to allow sequencing to proceed just in case analytical PCRs are not done.

[bthuronyi]

It also occurs to me that we could, and maybe should, handle this the way we do quantitation and gel -- set up exactly parallel settings for analytical PCR, with priority level choices set in dedicated columns. We would need to add "analytical PCR priority" to Minipreps and Transformations (I think on reflection that's better than doing it in Assemblies and Golden Gates; if you care enough to want to set high analytical PCR priority at the time you queue the assembly, you can also queue it to be transformed and then set it there). Then the analytical PCR problem becomes as simple as applying the same paradigm from quant/gel -- settings and Dashboard and Statuses.

[evelynqi]

I think that's a great idea!

[evelynqi]

Okay, my thoughts are that it makes a lot of sense to model it after the other QC tasks we have in place, but also that would maybe involve incorporating the analytical PCR sheet into the transformation/miniprep sheet.. (as quantitation and gel don't have their own sheets) although I think we can also do a version that involves the priority that gel/quant uses and we incorporate the relevant columns --""Queue analytical PCR?" and "Status of analytical PCR if queued". "Analytical PCR overall outcome" and "Outcomes of individual analytical PCRs" interact with Analytical PCR outcomes and are supposed to use these to influence downstream steps like whether something should be sequenced."-- to influence a change in status.

[evelynqi]

https://docs.google.com/spreadsheets/d/1qQcIUiZkr79_W31zWOaUK_gukkNSHRrFDSQ5In5KSSM/edit?usp=sharing Miniprep Status: added the middle line

if(and(AN3="",iferror(xmatch(N3,'Settings & admin'!$BF$26:$BF$32),1.5)>='Settings & admin'!$BF$22),char(10)&"ready to quant","")& if(and(BI3="",BH3="yes",iferror(xmatch(P3,'Settings & admin'!$BF$26:$BF$32),1.5)>='Settings & admin'!$BF$24),char(10)&"ready for analytical PCR","")& if(and(AT3="",iferror(xmatch(O3,'Settings & admin'!$BF$26:$BF$32),1.5)>='Settings & admin'!$BF$20),char(10)&"ready to prep gel sample",

--added in settings settings_qcAnalyticalPCRAfterTransformationPriority settings_qcAnalyticalPCRmIDPriority

Designate analytical PCR as a Dashboard task for a/gIDs when the sample "analytical PCR priority" column is set to:	High priority only
Designate analytical PCR as a Dashboard task for mIDs when the sample "analytical gel priority" column is set to:	Normal or higher

and the corresponding values table:

a/gID analytical PCR threshold:	4
mID analytical PCR threshold:	3

[evelynqi]

For transformations: -changed golden gate code "Queue analytical PCR?" (CW) from: =ifs(A422="","", regexmatch(F422,"done"),"possible to queue", TRUE,"don't queue") to =ifs(A422="","", regexmatch(F422,"done"),"possible to queue", regexmatch(F422,"ready to score plates"),"possible to queue", TRUE,"don't queue")

Then, added this col and another to the transformation sheet -- "Queue analytical PCR?"(AK) and "Status of analytical PCR if queued" (AL). Status uses these cols to inform the status message.

and(AC1462="",value(text(today(),"yymmdd"))-value(Z1462)>0),"ready to score plates"& if(and(AK1462="possible to queue",AL1462="no",iferror(xmatch(O1462,'Settings & admin'!$BF$26:$BF$32),1.5)>='Settings & admin'!$BF$23),char(10)&"ready for analytical PCR",""),

[evelynqi]

This code assumes that a user would only do one analytical PCR per construct... (which I will work on) and for analytical PCRs after transformations, it would only happen when plates are also ready to score.

Update from future Evelyn: Actually, I think the first part is fine and makes sense and matches how we do gel/quant now. It is queued to do when it is marked high priority and when it hasn't been done before. Once it is done, it will get mark as done. This seems fine as far as dashboard tasks go. If someone wanted to run another run, they would have to know and drive it forward themselves, which isn't too much work.. although we can add a checkbox on analytical PCR sheet that is run again? And if run again is checked, it would override the status of analytical PCR is done and say it is ready to do again. We can also add this to the other gel too...

[evelynqi]

Golden Gate g and Minipreps m

Do we not want to do analytical PCRs for assemblies with aIDs?

The miniprep sheet seems to imply that we only do analytical PCRs for golden gate assemblies "Analytical PCR possible to queue?": =if(left(L2058)="g","yes","")

This prompt and issue also seem to imply that we only do analytical PCRs for golden gate constructs and minipreps, but the analytical PCR sheet allows a/g/m Template IDs

[evelynqi]

• [x] settings: get rid of the warning things
• [x] settings: get rid of the cells that automatically add message to do analytical PCRs (the high/low flags do this)
• [x] settings: add a setting for assemblies too (we will support it :))
• [x] transformations: tie message with miniprep ready status -- dependent on whether user wants an analytical PCR done before minipreps or not
• [ ] transformation/miniprep: support for analytical PCRs of assemblies

[evelynqi]

• [x] transfer "queue analytical pcr?", "status of analytical pcr if queued if queued?" and "any pcr fails?" to transformations, get rid of it in golden gate; add code for aIDs

[evelynqi]

Progress: Whether or not to do analytical PCRs of colonies after transformations before minipreps for both gg and assemblies is implemented in the transformation, gg, and assemblies sheet.

To do: Implement whether or not to do analytical PCRs of minipreps before sequencing.

[bthuronyi]

Go ahead and add/remove columns and add the non-Status code to CC main whenever you're ready (provided columns you want to remove are not referenced, which they might be). If we know we're going to change columns around, might as well do it as early as possible.

[evelynqi]

Go ahead and add/remove columns and add the non-Status code to CC main whenever you're ready

done

[evelynqi]

https://docs.google.com/spreadsheets/d/1tdRDoNpYwKgI6YgaF4zh1FAnPGHXM0NKuluQTOX6Ths/edit?usp=sharing Miniprep, Transformation status ready for review: -miniprep status ready for analytical PCR if setting is turned on that requires it for sequencing and it is flagged for analytical PCR -transformation status ready for analytical PCR if setting is turned on that requires it for minipreps and it is flagged for analytical PCR

[evelynqi]

Named some ranges in settings:

• [ ] Settings_analytical_PCR_RequireQueueingAfterTransformation
• [ ] Settings_analytical_PCR_RequireQueueingAfterMiniprep (would you want these settings to be separated based on aID and gID? -- so these two checkboxes become four checkboxes?)

Analytical PCR Options
If a construct is marked abandoned in the Registry, never mark analytical PCRs related to that construct as ready	TRUE
Require analytical PCRs to be queued and completed before gIDs and aIDs are marked "ready to queue miniprep"?	TRUE
Require analytical PCRs to be queued and completed before gID and aID-derived minipreps are marked "ready to queue sequencing"?	TRUE

• [ ] added to QC options with named ranges (settings_qcAnalyticalPCRAfterTransformationPriority,settings_qcAnalyticalPCRmIDPriority)

Designate analytical PCR as a Dashboard task for a/gIDs when the sample "analytical PCR priority" column is set to:	High priority only
Designate analytical PCR as a Dashboard task for mIDs when the sample "analytical gel priority" column is set to:	Normal or higher

• [ ] individual name ranges for sample thresholds (settings_qcdIDGelSampleThreshold,settings_qcmIDGelSampleThreshold,settings_qcdIDQuantThreshold,settings_qcmIDQuantThreshold,settings_qcColonyAnalyticalPCRThreshold,settings_qcmIDAnalyticalPCRThreshold) for the table below:

dID gel sample threshold:	4
mID gel sample threshold:	3
dID quant threshold:	2
mID quant threshold:	3
a/gID analytical PCR threshold:	4
mID analytical PCR threshold:	3

• [ ] added name: settings_qcPriorityList

QC priority list, in priority order low to high:	do not do
Note: lowest priority will always prevent automatic QC	low
	normal
	high

[bthuronyi]

Other thing to add: in Minipreps, when Status is going to output "ready to queue sequencing," append a message if analytical PCRs have been queued but not finished: "wait on analytical PCR?"

Consider making this a Setting where you can choose to either append the message OR replace "ready to queue sequencing" with it. That would be ideal!

[bthuronyi]

Implemented w/ @evelynqi!

• [x] Final task: separate priority threshold settings for aID vs. gID on both Transformations and Minipreps

[evelynqi]

It added four new lines, 2 more of the word codes that say high priority only/normal or higher/etc.. and 2 more of their numerical value equivalents.

In settings: settings_aIDColonyAnalyticalPCRThreshold settings_gIDColonyAnalyticalPCRThreshold settings_aIDMiniprepAnalyticalPCRThreshold settings_gIDMiniprepAnalyticalPCRThreshold

In miniprep: if(let(threshold,if(left(L4)="a",settings_aIDMiniprepAnalyticalPCRThreshold,settings_gIDMiniprepAnalyticalPCRThreshold), iferror(xmatch(P4,settings_qcPriorityList),1.5)>=threshold), if(BI4="",char(10)&"ready to queue analytical PCR",if(regexmatch(BI4,"progress"),char(10)&"analytical PCR pending","")),"")

In transformations: if(not(AJ3), if(let(threshold,if(left(A3)="a",settings_aIDColonyAnalyticalPCRThreshold,settings_gIDColonyAnalyticalPCRThreshold), iferror(xmatch(O3,settings_qcPriorityList),1.5)>=threshold), ifs( AN3="","ready to queue analytical PCR", regexmatch(AN3,"progress"),"waiting for analytical PCR results", TRUE,"done"), "done")