topic Re: Change formula in Discussions

Change formula

Chris_Liu — Tue, 11 Feb 2025 06:07:56 GMT

As the attachments show, the formula of column ROW(3) is effective but just for one row.

The formula of column "Used For loop " as below color shows, I know that formulas can't contain For loops， so remove the For loop parts and change ":Scan input[i]" to other expressions or other functions to Implement from ": Scan input[1]" to": Scan input[end row number]", then automatically updated based on each row of the "Scan input" column. Please help to correct it, thanks！

For(i = 1, i <= N Rows(), i++,
	If(Is Missing(:Scan input),
		"",
		If(Col Sum(:Blacklist == :Scan input[i]) > 0,
			"NG",
			"OK"
		)
	)
)

Re: Change formula

txnelson — Tue, 11 Feb 2025 05:12:02 GMT

You stated that you can not have a For Loop in a formula. The actual statement about For Loops in formulas is that there is not need to use a For Loop in a formula to loop through each row in a data table, because formulas are automatically applied to each row.

But if there is a need for a For Loop to be run on each row, then it can certainly be used.

Here are 3 ways to write a formula that will do what I believe is your desired result

If( Is Missing( :Scan input ),
	theResult = "",
	theResult = "OK";
	For( i = 1, i <= N Rows( Current Data Table() ), i++,
		If( :Blacklist == :Scan input[i],
			theResult = "NG";
			Break();
		)
	);
);
theResult;

If( Is Missing( :Scan input ),
	"",
	curBlacklist = :Blacklist;
	If( N Rows( Current Data Table() << get rows where( :Scan input == curBlacklist ) ) > 0,
		"NG",
		"OK"
	);
)

As Constant( Summarize( ScanList = by( :Scan input ) ) );
If( Is Missing( :Scan input ),
	"",
	If( N Rows( Loc( ScanList, :Blacklist ) ) > 0,
		"NG",
		"OK"
	)
);

Re: Change formula

Chris_Liu — Tue, 11 Feb 2025 06:10:00 GMT

Thanks! However, the correct result on rows 3 and 5 should show NG. It hasn't been solved yet.

Re: Change formula

Chris_Liu — Tue, 11 Feb 2025 06:07:48 GMT

Thanks! However, the correct result on rows 3 and 5 should show NG. It hasn't been solved yet.

Re: Change formula

jthi — Tue, 11 Feb 2025 06:14:28 GMT

How is "NG" or "OK" result to be determined (in words, not as a JSL)?

Re: Change formula

txnelson — Tue, 11 Feb 2025 06:19:14 GMT

The formulas that I provided all look at the value of Black List for a given row, and then look through all of the rows for Scan Input to see if there is any row that has the same value.

I must have misinterpreted what you are trying to do. I thought that was what your supplied JSL was trying to do. Can you describe what your desired results are?

Re: Change formula

Chris_Liu — Tue, 11 Feb 2025 06:32:29 GMT

The results of all Blacklist columns are NG. The actual scenario is that the products produced by a fixture are already known to be NG. They need to be found out and scanned into the Scan input column with a barcode gun, and NG will be displayed as long as the value of the "Scan input" column is in any row of the Blacklist column. For example, row 1 is exactly equal so it shows NG, and row 3 is not exactly equal, but "X1" appears in row 1 of the Blacklist column, so need shows NG.

Re: Change formula

jthi — Tue, 11 Feb 2025 06:35:07 GMT

Collect all blacklist values to list/associative array and compare current rows value to that

As Constant(
	bl = :Blacklist << get values;
);
If(!IsMissing(:Scan input),
	If(Contains(bl, :Scan input),
		"NG"
	,
		"OK"
	);
,
	""
);

Re: Change formula

Chris_Liu — Tue, 11 Feb 2025 06:39:11 GMT

The results of all Blacklist columns are NG. In no order, as long as the value of the "Scan input" column appears in any row of the Blacklist column, NG will be displayed. Using the formula makes it easy to find the defective product during the scanning process.