search

bthuronyi / CloneCoordinate

CloneCoordinate issue tracking
1 stars 0 forks source link

Roughly track aID and gID volume remaining based on Transformations #165

Closed bthuronyi closed 3 months ago

bthuronyi commented 3 months ago

We have data on aID and gID assembly scales and we have a column for volume used in transformation - so, aID and gID status should be set to "ran out" when the transformed volumes are close to the scale. Transformations status should show "overqueued?" if any incomplete transformation(s) are queued AND the volume left of that assembly is below the default value for amount to transform (from Settings).

135 #136 #137 #64

ethanjeon commented 3 months ago

Will implement in a bit, but thinking something like this.

First, I calculate a total volume used in all previous transformations: =SUM(ARRAYFORMULA(VALUE(SPLIT(TEXTJOIN(", ", TRUE, filter( [corresponding transformed amount] , [list of transformations] = [aID or gID in question] )), ", "))))

Then, I can simply do a >= comparison to the original aID/gID scale (default 10ul) to see if it has run out.

In assemblies status, for example, I'll insert this "ran out" check in two places - once somewhere within and(regexmatch(H3,"(retransform)"),counta(iferror(filter(Transformations!$A$3:$A,Transformations!$A$3:$A=B3,Transformations!$V$3:$V<>"")))<2),"ready to transform"&char(10)&"(repeat)", for retransformations, and another time at the end appended.

ethanjeon commented 3 months ago

Okay, so assemblies status can be fully outfitted with "ran out!"s using this chunk in the middle:

FALSE,"bulky addition of volume check for retransformations", and(regexmatch(H3,"(retransform)"),counta(iferror(filter(Transformations!$A$3:$A,Transformations!$A$3:$A=B3,Transformations!$V$3:$V<>"")))<2),if( sum(iferror(arrayformula(value(split(textjoin(", ",TRUE,filter(Transformations!$M$3:$M,Transformations!$A$3:$A=B3)),", "))),0))>=O3,"ran out!","ready to transform"&char(10)&"(repeat)"), counta(iferror(filter(Transformations!$A$3:$A,Transformations!$A$3:$A=B3,Transformations!$V$3:$V="")))>0,if( sum(iferror(arrayformula(value(split(textjoin(", ",TRUE,filter(Transformations!$M$3:$M,Transformations!$A$3:$A=B3)),", "))),0))>=O3,"ran out!","ready to transform"&char(10)&"(repeat)"),

and this at the end: if(sum(iferror(arrayformula(value(split(textjoin(", ",TRUE,filter(Transformations!$M$3:$M,Transformations!$A$3:$A=B3)),", "))),0))>=O3,if(A3="","",char(10)&"ran out!"),"")

One thing is that ongoing retransformations can end up with a double "ran out!" status... I'll put the entire preceeding status output in a let statement to rule out double "ran out!"s.....

ethanjeon commented 3 months ago

Pretty sure I have all changes implemented in a copy of CC for assemblies status, GG status, and transformations status - gonna check a couple more cases tomorrow then implement in main sheet.