Asst: Minipreps multi-resistance construct support

[bthuronyi]

Asst: Minipreps media/antibiotic calculation doesn't handle multi-resistance constructs gracefully

Related to #83

[evelynqi]

Add new col in minipreps that handles adding in a different antibiotic than you plated on

[evelynqi]

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

[evelynqi]

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

Added new column in Minipreps sheet (U: Culture Antibiotic) In the asst Miniprep sheet, I can replace the K(Marker) with K(Culture Antibiotic) and if users input one resistance marker media, the media/antibiotic calculation works. However, if users want to use a multi-resistance marker media, the calculation breaks because the code looks at the first two letters of the antibiotic marker. So Cm, Km would be automatically Cm. The code needs to be rewritten.. I'm thinking of using a query, look below:

Original Code: =if(counta(iferror(filter(K3:K,U3:U="",K3:K<>"")))=0,"","Media needed: "& if(counta(iferror(filter(K3:K,U3:U="",left(K3:K,2)="Cm")))>0,">"&round(counta(iferror(filter(K3:K,U3:U="",left(K3:K,2)="Cm")))*4+4,-1)&" mL LB-Cm ","") &if(counta(iferror(filter(K3:K,U3:U="",left(K3:K,2)="Km")))>0,">"&round(counta(iferror(filter(K3:K,U3:U="",left(K3:K,2)="Km")))*4+4,-1)&" mL LB-Km ","") &if(counta(iferror(filter(K3:K,U3:U="",left(K3:K,2)="Cb")))>0,">"&round(counta(iferror(filter(K3:K,U3:U="",left(K3:K,2)="Cb")))*4+4,-1)&" mL LB-Cb ","") &if(counta(iferror(filter(K3:K,U3:U="",left(K3:K,2)="Sp")))>0,">"&round(counta(iferror(filter(K3:K,U3:U="",left(K3:K,2)="Sp")))*4+4,-1)&" mL LB-Sp ","") &if(counta(iferror(filter(K3:K,U3:U="",left(K3:K,2)="Tc")))>0,">"&round(counta(iferror(filter(K3:K,U3:U="",left(K3:K,2)="Tc")))*4+4,-1)&" mL LB-Tc",""))

Potential new code: =JOIN(", ", ARRAYFORMULA( QUERY( {UNIQUE(K3:K), IFERROR(COUNTIF(K3:K, UNIQUE(K3:K)))}, "select Col1, Col2 label Col1 '', Col2 ''" ) & ": " & QUERY( {UNIQUE(K3:K), IFERROR(COUNTIF(K3:K, UNIQUE(K3:K)))}, //this count would also not just be count but the calculation formula "select Col2 label Col2 ''" ) ) )

where K3:K is the culture antibiotic, and i would revise the countif to be the calculation formula.

[evelynqi]

Need some quick guidance on query..

[evelynqi]

Note for self: the minipreps with assort numbers have altered culture antibiotic; be wary before copying and pasting into CloneCoordinate

[evelynqi]

=JOIN(", ", flatten(transpose( QUERY( {K3:K,L3:L}, "select Col1,sum(Col2)+4, where Col1 is not null group by Col1 label Col1 ''" ) )))

[bthuronyi]

Add new col in minipreps that handles adding in a different antibiotic than you plated on

Looks good; I put the title in Sentence case. Also will need a Named Range. Go ahead and implement this. When you populated with data, did you manually look over the rows whose constructs are marked as having multiple antibiotics? Those might say what antibiotic was actually used in the queue notes or inoculation / growth notes, or you can guess.

[bthuronyi]

=JOIN(", ", flatten(transpose( QUERY( {K3:K,L3:L}, "select Col1,sum(Col2)+4, where Col1 is not null group by Col1 label Col1 ''" ) )))

Let's follow up on this live. We can parse the output from QUERY using BYROW and turn it into a nicely formatted summary. We should also use something not hard-coded for the +4 above.

[evelynqi]

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

-Added new columns to miniprep sheet (V-Media, W-Culture antibiotic) -Added new columns to asst miniprep sheet (K-Media, W-Culture antibiotic, M-Culture volume (mL) -Implemented new code for media needed in the header (Asst: minipreps F)

=if(counta(iferror(filter(L3:L,W3:W="",L3:L<>"")))=0,"","Media needed: "& trim(join(" ",byrow(QUERY( {K3:K,L3:L,M3:M}, "select Col1,Col2,sum(Col3) where Col1 is not null group by Col1,Col2 label Col1 ''" ),lambda(x,let(media,index(x,1),label,index(x,2),value,index(x,3),if(label="","",">"&round(value+settings_mDefaultCultureVolume,-1)&"mL "&media&"-"&label)))))))

[bthuronyi]

Great! Let's add a default for media in Settings, and maybe a 20-row Named Range of "media options" as is currently set up for Competent cell batch. Then add data validation to the Media column in Minipreps to draw from that range.

Might also look nice to separate the media needed list with newlines rather than spaces, but it's a judgment call.

Go ahead and implement in main!

[evelynqi]

-Add new column to settings (Settings_mMediaOptions, Minipreps m Media options) -Added new cells to settings (Default media, LB: settings_mDefaultMedia) // implemented the "default: LB" in minipreps sheet -Added columns in miniprep sheet (V-Media, W-Culture antibiotic) --- added data validation for media using the new column in settings -Added columns to asst: miniprep (Media, Culture antibiotic, Culture volume) // got rid of Marker col -Added header code and edited it so that the new medias are separated by a new line //also edited the code so that it does not show up if A3 is empty.