Add "elongation time" field to dsDNA d queueing

bthuronyi commented 4 months ago

Add a column for queuer to specify an elongation time for a PCR and override recommendations. Make clear this is JUST for cases where such a time is absolutely needed. Set Asst: dsDNA tab to enforce requested elongation times and flag dIDs where "Actual elong" doesn't match "Required elong" or whatever it's called. (Maybe spell out "elongation time", it's not a big deal.) Simplest way to enforce required elongation time is to just split that PCR into its own block.

santiagochrist commented 3 months ago

Added "Required elong" tab, and added the settings_dPCRDefaultElongationPerKb named range to the Default values header. Left the column without specifying the "seconds per kb" unit for simplicity in the assistant tab, but we can add it no big deal. Tab is currently set to blue to show that it is "optional," although we can add something like "(optional)" to the header if needed.
Added "Required elong" to the assistant tab just to the left of "Actual elong", just copied over the code from another column to pull values. The conditional formatting I used sets the "Actual elong" cells red based on this: =ifs(A3="",FALSE,AM3<>"",AN3<>AM3)
Both columns have the '# s' custom format to show seconds.

Update: So things are always not as simple as they seem :-)

There is a calculated elongation time. If Manual ET > Calc ET -> set the Manual ET to all PCRs (1 block)
If Manual ET < Calc ET -> Set that PCR to its own block.
Columns O/AG currently branch off PCRs into different blocks based on Anneal Temp and Cycle Count, but we want to split based on ET as well.

santiagochrist commented 3 months ago

Column AG: Commented

=if(counta(iferror(filter(A3:A,A3:A<>"",BS3:BS="")))>0,
"Elongation times (minutes:seconds):"&char(10)&

join(char(10),byrow(filter(CA3:CA,CA3:CA<>""),lambda(combo,combo&" - "& // traverses through the array of unique thermocycler combinations and applies a lambda function
let(max_elong,settings_dPCRDefaultElongationPerKb*max(filter(BT3:BT,BS3:BS="",A3:A<>"",BZ3:BZ=combo)), // we define max_elong by going through the active PCR sizes and taking the longest size, multiplying to get our answer in seconds

if(
max_elong>settings_dPCRMinimumElongationTime, // if the maximum time is greater than the (15 second) minimum time
text(time(0,int(max_elong/60),mod(max_elong,60)),"m:ss"), // output the max time as m:ss
text(time(0,0,settings_dPCRMinimumElongationTime),"m:ss")))))) // otherwise return the minimum time
,"")

santiagochrist commented 3 months ago

Summary:

Created new "Actual elong" columns on both dsDNA d and Asst.
Added conditional formatting based on the same criteria mentioned somewhere above.
Created a 'Required elong' column that 'Thermocycler combination for incomplete PCRs' column can pull from to generate the 'Elongation times' description.
Wrote, after much assistance from B, the final code to 'Elongation times' (Column AG).
One niche bug: 'Blocks needed' column (O) double counts blocks that are equivalent.

Final Code: Elongation times

=if(counta(iferror(filter(A3:A,A3:A<>"",BT3:BT="")))>0,
"Elongation times (minutes:seconds):"&char(10)&
join(char(10),unique(byrow(filter(CC3:CC,CC3:CC<>""),lambda(combo,combo&
let(max_elong,settings_dPCRDefaultElongationPerKb*max(filter(BU3:BU,BT3:BT="",A3:A<>"",CB3:CB=combo)),
req_elong,iferror(filter(CA3:CA,BT3:BT="",A3:A<>"",CB3:CB=combo),""),
if(
req_elong="",

" - "&if(max_elong>settings_dPCRMinimumElongationTime,
text(time(0,int(max_elong/60),mod(max_elong,60)),"m:ss"),
text(time(0,0,settings_dPCRMinimumElongationTime),"m:ss")),

))

)))
),"")

Final code: Thermocycler combinations for incomplete PCRs

=if(and(BY11<>"",BN11="PCR",BT11=""),BY11&"º anneal, "&BZ11&" cycles"&if(CA11="",""," - "&text(time(0,int(CA11/60),mod(CA11,60)),"m:ss")),"")

Let me know whenever you get the chance @bthuronyi, and thanks a lot for everything!

bthuronyi commented 3 months ago

All set to implement in main CC based on our work yesterday. Go ahead and put it in and I'll do a final check over from there!

santiagochrist commented 3 months ago

I think I implemented it yesterday right before I left, so let me know if you see it!

bthuronyi commented 3 months ago

Looks good! The new optional user entry "Required elong (s)" column needed a Named Range (since it contains user data); added this.

Added conditional formatting based on the same criteria mentioned somewhere above.

I moved this to another Issue #156, I think it needs even stronger reinforcement.

bthuronyi / CloneCoordinate