I have received the formula I want to discuss almost 3 years ago from @txnelson, never let go of it since and thought about it many times before now, slowly coming somewhat more seriously to grips with scripting. In the meantime, the formula as saved me a lot of time.
In the example below, the same formula is added 3 times, each time with a little bit of variation. The issue I want to discuss are the different error messages thrown when copying and running the table script of the table including the newly added formula columns.
Names Default To Here( 1 );
// first, we need an example table
dt = New Table( "ExampleTable",
Add Rows( 24 ),
New Column( "BatchId",
Character,
"Nominal",
Set Values(
{"A", "A", "A", "A", "A", "A", "B", "B", "B", "C", "C", "C", "D", "D",
"D", "E", "E", "E", "F", "F", "F", "G", "G", "G"}
)
),
New Column( "StorageCondition",
Character,
"Nominal",
Set Values(
{"25Ā°C/60%RH", "25Ā°C/60%RH", "25Ā°C/60%RH", "5Ā°C", "5Ā°C", "5Ā°C",
"25Ā°C/60%RH", "25Ā°C/60%RH", "25Ā°C/60%RH", "25Ā°C/60%RH", "25Ā°C/60%RH",
"25Ā°C/60%RH", "25Ā°C/60%RH", "25Ā°C/60%RH", "25Ā°C/60%RH", "25Ā°C/60%RH",
"25Ā°C/60%RH", "25Ā°C/60%RH", "25Ā°C/60%RH", "25Ā°C/60%RH", "25Ā°C/60%RH",
"25Ā°C/60%RH", "25Ā°C/60%RH", "25Ā°C/60%RH"}
)
),
New Column( "Time",
Numeric,
"Continuous",
Format( "Best", 12 ),
Set Values(
[0, 1, 2, 0, 1, 2, 0, 1, 2, 0, 1, 2, 0, 1, 2, 0, 1, 2, 0, 1, 2, 0, 1, 2]
)
),
New Column( "QcParameter",
Numeric,
"Continuous",
Format( "Best", 12 ),
Set Values(
[0.2, 0.3, 0.4, 0.2, 0.21, 0.22, 0.3, 0.9, 1.2, ., 0.2, 0.4, 0.1, ., 0.1,
0, 0, 0, 0.1, 0, 0, 0, 0.1, 0]
)
)
);
// adding formula column via approach nr. 1
// this adds the formula alright, but when copying and executing the resulting table script will leave the formula column empty
// no need to discuss this, because this obviously missing the 'eval(eval expr(expr()))' wrapping (see screenshot below)
dt << New Column( "QcParameter [% rel.]_1",
numeric,
formula(
curBatch = :BatchId;
curCondition = :StorageCondition;
theRows = dt << get rows where(
:BatchId == curBatch & :StorageCondition == curCondition & :Time == 0
);
d0Row = theRows[1];
d0Time = :QcParameter[d0Row];
(100 * :QcParameter / d0Time);
)
);
// adding formula column via approach nr. 2
// this will fill the formula column as desired when copying and running the table script with the added formula columns
// but it will also throw an error message when new rows are added to the table then (see screenshot below)
Eval(
Eval Expr(
dt << New Column( "QcParameter [% rel.]_2",
numeric,
formula(
curBatch = :BatchId;
curCondition = :StorageCondition;
theRows = Expr( dt ) << get rows where(
:BatchId == curBatch & :StorageCondition == curCondition & :Time == 0
);
d0Row = theRows[1]; // removed from approach nr. 3
d0Time = :QcParameter[d0Row];
(100 * :QcParameter / d0Time);
)
)
)
);
// adding formula column via approach nr. 3
// this will fill the formula column as desired when copying and running the table script with the added formula columns
// and it will NOT throw an error message when new rows are added to the table then (see screenshot below)
Eval(
Eval Expr(
dt << New Column( "QcParameter [% rel.]_3",
numeric,
formula(
curBatch = :BatchId;
curCondition = :StorageCondition;
theRows = Expr( dt ) << get rows where(
:BatchId == curBatch & :StorageCondition == curCondition & :Time == 0
);
d0Time = :QcParameter[theRows];
(100 * :QcParameter) / d0Time;
)
)
)
);
Screenshot of error message for formula column "QcParameter [%r rel.]_1]" when rerunning table script with additional formula columns:
Screenshot of error message for formula column "QcParameter [%r rel.]_2]" when rerunning table script with additional formula columns:
Screenshot of error message for formula column "QcParameter [%r rel.]_3]" when rerunning table script with additional formula columns:
The reason why I am asking this lengthy question is that I assume there must be some reason why variable "d0Row" is used in approach nr. 2, no? For approach nr. 3 I have simply deleted this variable (see screenshot below) and the formula behaves "much more nicely" as a result, i.e., there is no error message when new rows are added ad-hoc by point & click to the data table.
My question is therefore, if I am doing something wrong by deleting the "d0Row" variable. In real life I am working with larger data tables and won't be able to check each row individually for its accuracy.
