Implement consistency checking for operation logging across all tabs

[bthuronyi]

In general, some operations can only be done, or almost always are done, after others have been completed first. Status or some other field could check for these dependencies and flag rows that don't match the expected order.

E.g. "gel run" logged before "gel sample prep" logged. E.g. "PCR purification" logged before "put in thermocycler" logged.

[bthuronyi]

From #57: Right now we have weak or no engineering controls around ensuring that a given operation -- queueing, OR finishing a task, like purifying PCRs -- has had all its relevant fields (green column headers) filled in. I'd like to change that so that there's a consistent mechanism -- like in the Registry for "fully queued" -- that people will recognize where you get a check box or something when you've fully finished a given set of stuff by filling in the cells. It shouldn't appear as "unchecked" until someone fills in one of the fields, and then it should bug you (yellow highlight? appearing as an empty box?) until you do all the fields.

[bthuronyi]

One angle to approach this is to put a single formula in row 1 with a checkbox controlling it, "Check tab for consistency?" -- then have that formula identify the first row it can find, if any, that has a consistency violation. The other angle would be to just add a new column and have the formula calculate for every row. The former approach is less computationally expensive, but has a "let people make mistakes, then someone fixes them later" framing, while the latter is more like "fix your errors as soon as you make them". That's probably best! And although this formula would almost always get evaluated, it wouldn't be a complicated one necessarily.

[bthuronyi]

One way we could do this is to JOIN the required-to-fill-in cells together in the expected fill-in order using a special (not ever typed on a keyboard) character, then REGEXMATCH the result and look for a case like -- if we JOINed using | for example -- "some data||some data". This should never occur if the cells are being filled in order.

[santiagochrist]

Oligos Prototype Example:

Column 'Entries consistent?':

=if(
H3="",false,
NOT(regexmatch(join("◆",$H3:$J3,$N3,$P3,$T3:$V3),"\◆{2}"))
)

[santiagochrist]

Oligos o:

Here is a more "full" code snippet that makes the consistency check more consistent across all tabs:

=let(
entry_list, {$I3:$K3,$O3,$Q3,$U3:$W3}, // here we can just neatly define an entry list
if(
index(entry_list,1)="","Awaiting first entry", // we check first list element to initiate the check
NOT(regexmatch(join("◆",entry_list),"[^◆]◆{2,}[^◆]")) // a more robust regex
)
)

Update 1:

• To explain the check of the first element, we should look at the regex first.
• The regex ensures that only ◆◆ in the format <non-◆ character>◆◆<non-◆character> are detected. Let me highlight the detected ones. Examples include: a[◆◆]a; word1[◆◆]word2[◆◆]word3; ◆◆entry2[◆◆]entry3◆◆◆◆◆◆
• The reason I set it up this way is that if inputs well in advance are left blank, I don't want to trigger the consistency check. Unless we want to deal with subarrays, we can just check that we don't skip an entry adjacent to the current entry.
• Thus, I put the check of the first element to avoid a situation where we fill out everything but forget the first element, since the regex will NOT check the missing entry 1-> (◆◆entry2[◆◆]entry3◆◆◆◆◆◆).

The Oligos o can be the "base code." I am working on code for dsDNA, which has to check for PCR/anneal vs. digest.

Update 2:

I now use "◆{2,}" to indicate the fact that we could have multiple missing entries. This means that it will now read if there are three diamonds in a row (◆◆◆), although this shouldn't really be an issue.

The code snippet from Update 1 should already be updated, so no worries, just leaving a little note here.

[santiagochrist]

dsDNA:

=let(
entry_list,
if(M3="", {$J3:$K3,$M3:$N3},
if(M3="PCR",{$J3:$K3,$M3:$U3,$W3,$AI3,$AQ3:$AV3,$AZ3:$BC3},
if(M3="anneal",{$J3:$K3,$M3:$O3,$AI3,$AQ3:$AS3,$BB3:BC3},
if(M3="digest",{$J3:$K3,$M3,$W3:$AB3,$AE3:$AF3,$AI3,$AP3:$AT3,$AW3,$AZ3:$BC3}
)))),
ifs(
index(entry_list,1)="","",
NOT(regexmatch(join("◆",entry_list),"[^◆]◆{2,}[^◆]")),"",
true,char(10)&"entry consistency?"
))

As noted by B, ifs does not like outputting arrays. As noted by me, switch also does not like outputting arrays. Thus, we used a little workaround trick where we nest a bunch of if statements in order to output different arrays depending on whether the dsDNA procedure is a PCR, anneal, or digest.

If left blank, I check for two cells, $M3:$N3, after the input to ensure that the user does not forget to select a dsDNA type.

[santiagochrist]

Assemblies a:

=let(
entry_list, {$K3:$L3,$N3:$P3,$Y3,$BD3,$BN3:$BP3},
ifs(
index(entry_list,1)="","",
NOT(regexmatch(join("◆",entry_list),"[^◆]◆{2,}[^◆]")),"",
true,char(10)&"entry consistency?"
))

In the entry list, I only check for BD3 and not BD3:BK3 (assembly actual volume entries) since you don't actually have to fill out every single cell despite being green (green does not imply "MUST" fill out, but this is just a note to myself since I am largely guiding myself with green cells).

[santiagochrist]

Golden Gate g:

=let(
entry_list, {$K3:$L3,$N3,$Z3,$AV3,$BF3:$BG3,$BW3,$CK3:$CL3,$CN3:$CO3},
ifs(
index(entry_list,1)="","",
NOT(regexmatch(join("◆",entry_list),"[^◆]◆{2,}[^◆]")),"",
true,char(10)&"entry consistency?"
))

Same concept as Assemblies a, where a lot of the fields with 7-8-9-10 GG parts don't require you to literally have 10 components. This does potentially put out there the case where there is no check for a [Part 1, No Part 2, Part 3, ...] kind of entry where a part is skipped. However, I think there are other checks in place..?

[santiagochrist]

Transformations:

=let(
entry_list, {$M3:$N3, $Q3, $S3:$AA3, $AC3:$AD3, $AF3:$AJ3},
ifs(
index(entry_list,1)="","",
NOT(regexmatch(join("◆",entry_list),"[^◆]◆{2,}[^◆]")),"",
true,char(10)&"entry consistency?"
))

Pretty straightforward here.

[santiagochrist]

Minipreps m:

=let(
entry_list, {$K3:$M3,$R3,$AB3:$AD3,$AF3:$AH3,$AJ3:$AK3,$AM3:$AN3,$BU3:$BX3},
ifs(
index(entry_list,1)="","",
NOT(regexmatch(join("◆",entry_list),"[^◆]◆{2,}[^◆]")),"",
true,char(10)&"entry consistency?"
))

Might be one of the few Minipreps m issues that were straightforward, haha.

[santiagochrist]

Analytical PCR:

=let(
entry_list, {$I3:$J3,$L3,$S3:$U3,$Z3:$AJ3},
ifs(
index(entry_list,1)="","",
NOT(regexmatch(join("◆",entry_list),"[^◆]◆{2,}[^◆]")),"",
true,char(10)&"entry consistency?"
))

Straightforward.

[santiagochrist]

Sequencing:

=let(
entry_list, {$M3:$O3,$Q3,$Y3,$AD3:$AF3,$AH3,$AJ3,$AY3:$BA3,$BC3:$BE3},
ifs(
index(entry_list,1)="","",
NOT(regexmatch(join("◆",entry_list),"[^◆]◆{2,}[^◆]")),"",
true,char(10)&"entry consistency?"
))

sf

[santiagochrist]

Strains s:

=let(
entry_list, {$J3,$L3:$M3,$Q3,$V3:$X3},
ifs(
index(entry_list,1)="","",
NOT(regexmatch(join("◆",entry_list),"[^◆]◆{2,}[^◆]")),"",
true,char(10)&"entry consistency?"
))

sf

[santiagochrist]

Note: some statuses are showing a #VALUE! error, but it's not caused by the consistency check concatenated at the end of every status.

[bthuronyi]

Added to Experiments