I have a script section that finds errors in the data set. I want to find these errors and color the cells then create a subset of all rows with errors that group into the first subset. I got it working for most of the error types, but when I used the same theory for the two conditions that go into the the second subset it gives an alert that says "Invalid Row". When I run the sections manually they work, but when I run the script all the way through the error stops the run. I can't figure out why one section works and the other doesn't.
Here's the script for the expression cause the problem. The section titles "Find and highlight data entry errors" works, but the one titled "Find and highlight missing failure mode or no explanation when failure mode is Other" does not work.
find errors = Expr(
//Find and report data entry errors
//Create subset of only select date range
DS<<Select Where(Informat(sdate, "m/d/y") <= :Date <= Informat(edate, "m/d/y"));
DO = DS<<Subset(Output Table("Date Range Only"), Selected Rows(1), Selected Columns(0));
DS<<Clear Select;
//Find and highlight date entry errors
//Sealer number isn't on list
DO<<Select Where(!Contains(all sealers, :Sealer Number));
selected = As List(DO<<Get Selected Rows());
:Sealer Number<<Color Cells(73, selected);
DO<<Clear Select;
//Pouch CPN isn't on list
DO<<Select Where(!Contains(all pouches, :Pouch CPN));
selected = As List(DO<<Get Selected Rows());
:Pouch CPN<<Color Cells(73, selected);
DO<<Clear Select;
//A Lot contains sealer number or pouch CPN
DO<<Select Where(Or(Contains(all sealers, :Pouch A Lot), Contains(all pouches, :Pouch A Lot)));
selected = As List(DO<<Get Selected Rows());
:Pouch A Lot<<Color Cells(73, selected);
DO<<Clear Select;
//A Lot contains "a" instead of "A"
DO<<Select Where(Contains(:Pouch A Lot, "a"));
selected = As List(DO<<Get Selected Rows());
:Pouch A Lot<<Color Cells(73, selected);
DO<<Clear Select;
//Reselect rows with data entry errors to subset
DO<<Select Where(!Contains(all sealers, :Sealer Number), Current Selection("Extend"));
DO<<Select Where(!Contains(all pouches, :Pouch CPN), Current Selection("Extend"));
DO<<Select Where(Or(Contains(:Pouch A Lot, "a"), Contains(all sealers, :Pouch A Lot), Contains(all pouches, :Pouch A Lot)), Current Selection("Extend"));
DEE check = NRows(DO<<Get Selected Rows);
//Subset rows with data entry errors
DEE = DO<<Subset(Output Table("Date Entry Errors"), Selected Rows(1), Selected Columns(0));
DO<<Clear Select;
//Find and highlight missing failure mode or no explanation when failure mode is Other
//No failure mode selected
DO<<Select Where(:Failure=="<None>");
selected = As List(DO<<Get Selected Rows());
:Failure<<Color Cells(77, selected);
DO<<Clear Select;
//Failure mode Other with no explanation
DO<<Select Where(And(:Recoded Failure == "Other", Is Missing(:Failure If Other)));
selected = As List(DO<<Get Selected Rows());
:Failure If Other<<Color Cells(77, selected);
DO<<Clear Select;
//Reselect missing failure mode or no explanation when failure mode is Other
DO<<Select Where(:Failure == "<None>");
DO<<Select Where(And(:Recoded Failure == "Other", Is Missing(:Failure If Other)), Current Selection("Extend"));
MD check = NRows(DO<<Get Selected Rows);
//Subset rows with missing failure mode or no explanation when failure mode is Other
MD = DO<<Subset(Output Table("Missing Data"), Selected Rows(1), Selected Columns(0));
DO<<Clear Select;
//MD check = 0;
//Report A lots that have more than one CPN entered
//Establish list of shading color number references
colors = [67, 70, 73, 76, 68, 71, 74, 77, 69, 72, 75, 78];
//Create and format summary table by A lot
MCC = DO<<Summary(
Group(:Pouch A Lot, :Pouch CPN),
Freq("None"),
Weight("None"),
Statistics Column Name Format("Column"),
Link to Original Data Table(0),
Output Table Name("Multiple CPN Check")
);
:N Rows<<Set Name("Occurrences");
//Find and color code A lots with multiple CPNs entered
//Find and remove rows with no A Lot recorded or with only one pouch CPN entered for the A Lot
MCC<<Select Duplicate Rows(Match(:Pouch A Lot));
MCC<<Invert Row Selection;
MCC<<Select Where(:Pouch A Lot=="Not Recorded", Current Selection("Extend"));
MCC<<Delete Rows;
MCC<<Clear Select;
MCC check = NRows(MCC);
//Summarize MCC by A Lot to show the Pouch CPNs entered for each A Lot with multiple CPNs
Summarize(duplicated lots = By(:Pouch A Lot));
//Color each A Lot section to make distinguishing between them easier
For(d=1, d<=NItems(duplicated lots), d++,
MCC<<Select Where(:Pouch A Lot == duplicated lots[d]);
to color = As List(MCC<<Get Selected Rows);
:Pouch A Lot<<Color Cells(colors[d], to color);
:Pouch CPN<<Color Cells(colors[d], to color);
:Occurrences<<Color Cells(colors[d], to color);
);
//Clear selection
MCC<<Clear Select;
);
