Help me with calculating minimum of which some fields will be blank

[Mark24657610fm60]

I will let you know before posting this I did try to search for help to no avail.

I have uploade a form that I am using.

I have 18 feilds that I want to find the minimum value for, some of the feilds will remain blank so I cannot have it counted as a minimum value because it will thought of as a zero.

18 Field Names: PL1, PL2, PL3, PL4, PL5, PL6, PL7, PL8, FLS, LCS, RCS, FRS, SL1, SL2, SL3, SL4, SL5 and SL6.

The calculated feild; MCV

MCV stands for Minimum Vetical Clearance

I do not know anything about Jave Script but am trying to learn.

[try67 · Accepted solution]

You can use this code to achieve it, as the custom calculation script of MVC:

var fields = ["PL1", "PL2", "PL3", "PL4", "PL5", "PL6", "PL7", "PL8", "FLS", "LCS", "RCS", "FRS", "SL1", "SL2", "SL3", "SL4", "SL5", "SL6"];
var minValue = Infinity;
for (var i in fields) {
	var f = this.getField(fields[i]);
	if (f==null) {console.println("Error! Can't find: " + fields[i]); continue;}
	var v = f.valueAsString;
	if (v!="") minValue = Math.min(minValue, Number(v));
}
if (minValue==Infinity) event.value = "";
else event.value = minValue;

However, there are errors in some other calc scripts in your file that must be fixed for it to work correctly.

[try67 · Accepted solution]

You can use this code to achieve it, as the custom calculation script of MVC:

var fields = ["PL1", "PL2", "PL3", "PL4", "PL5", "PL6", "PL7", "PL8", "FLS", "LCS", "RCS", "FRS", "SL1", "SL2", "SL3", "SL4", "SL5", "SL6"];
var minValue = Infinity;
for (var i in fields) {
	var f = this.getField(fields[i]);
	if (f==null) {console.println("Error! Can't find: " + fields[i]); continue;}
	var v = f.valueAsString;
	if (v!="") minValue = Math.min(minValue, Number(v));
}
if (minValue==Infinity) event.value = "";
else event.value = minValue;

However, there are errors in some other calc scripts in your file that must be fixed for it to work correctly.

[Mark24657610fm60]

Try67, Thanks so much for your help. You are amazing. Thanks for the quick reply.

[Mark24657610fm60]

You can use this code to achieve it, as the custom calculation script of MVC:

var fields = ["PL1", "PL2", "PL3", "PL4", "PL5", "PL6", "PL7", "PL8", "FLS", "LCS", "RCS", "FRS", "SL1", "SL2", "SL3", "SL4", "SL5", "SL6"];
var minValue = Infinity;
for (var i in fields) {
	var f = this.getField(fields[i]);
	if (f==null) {console.println("Error! Can't find: " + fields[i]); continue;}
	var v = f.valueAsString;
	if (v!="") minValue = Math.min(minValue, Number(v));
}
if (minValue==Infinity) event.value = "";
else event.value = minValue;

However, there are errors in some other calc scripts in your file that must be fixed for it to work correctly.

 By @try67

What are the other errors did you see? I dont recall any other calcs on this sheet, please advise!

[Nesa Nurani]

In some fields you have scripts inside Simplified field notations, you can't use script there.

[Mark24657610fm60]

You were right, I found them. I was trying to figure out how to make those cells look empty.

Like I said before I do not work with Java Script, mainly AUTOHOTKEY and Visual Basic for Applications.

Thanks for your help.

[try67]

Every calculation script under the SLx fields, basically. You've written code but placed it in the Simplified Field Notation section, instead of the Custom Calculation Script section.

[try67]

Also, PL1 and MVC (the previous code, that is).

[Mark24657610fm60]

Quick question. Using the same form I sent, is there a way to have a checkbox checked if the MVC goes below a certain number like 19.0 or 17.5 I will have a separate checkbox for each, 1 for below 19.0 and one for 17.5.

If not is there a way to display a message in a text box if this is the case?

Thanks in advance

[try67]

Sure, that's possible. But do you want that box to be ticked if MVC is empty, too?

[Mark24657610fm60]

So the scenerio is this:

There will be two checkboxes on the same page as the clearences, One of the checkboxes is going to be named CB19_0 and the other one named CB17_5. So if the form is blank nothing should be checked, when someone enters a number in one of the fields the Minimum Vertical Clearance "MVC" will start showing numbers. If one of these numbers goes below 19.0  say 18.9 then the checkbox "CB19_0" will be checked and same for "CB17_5" if the MVC goes below 17.5. As another note the "CB17_5" will be dependent on two other checkboxes on another sheet. The checkboxes will be called "Lum_Present" and "WW_Present". If either one of these are checked and if the MVC goes below 17.5 say 17.4 then The checkbox "CB17_5" will become checked. If the two checkboxes  "Lum_Present" and "WW_Present" are not checked then CB17_5 will never be checked.

I really hope this makes sense. Thanks again in advance, I really do appreciate your help.

[try67]

Try this as the last part of the code:

this.getField("CB19_0").checkThisBox(0, false);
this.getField("CB17_5").checkThisBox(0, false);
if (minValue==Infinity) {
	event.value = "";
} else {
	event.value = minValue;
	if (minValue<19)
		this.getField("CB19_0").checkThisBox(0, true);
	if (minValue<17.5 && (this.getField("Lum_Present").valueAsString!="Off" || this.getField("WW_Present").valueAsString!="Off"))
		this.getField("CB17_5").checkThisBox(0, true);
	
}

[Mark24657610fm60]

Beautiful, the code works like a charm. Thank you so much.

I did forget one thing, is it possible to have the checkbox "CB19_0" become unchecked if the the conditions for the checkbox "CB17_5" make it checked?

[try67]

I don't follow... So if CB17_5 is checked CB19_0 will always be unchecked, regardless of the value?

[Mark24657610fm60]

Yes it weird, and I have never understood this, but if the structure does not have luminaires or a walkway the minimum clearance can only be 19.0 feet. If there are luminaires and/or a walkway the minimum can only be 17.5' to pass underneith. So if the structure has luminaires and/or a walkway then the "CB17_5" has the oportunity to be be checked if the clearence is below 17.5'. If there are no luminaires or walkways then the minumum clearanc can only be 19.0'.

in other worrd if there are no luminaires or walkway then the "CB19_0" will be the only check box that will become checked even if the MVC goes below 17.5'. If either of the other two checkboxes are checked the the CB17_0 comes into play. Whew! I sure hope this makes sense!

[try67 · Accepted solution]

IF I understood you correctly, this should work:

if (this.getField("Lum_Present").valueAsString!="Off" || this.getField("WW_Present").valueAsString!="Off") {
	if (minValue<17.5) {
		this.getField("CB17_5").checkThisBox(0, true);
	}
} else if (minValue<19) {
	this.getField("CB19_0").checkThisBox(0, true);
}

[Mark24657610fm60]

Perfect, worked like a charm. Sorry about all the confusion.

Thanks for your time, this community is the best.

[nandag91807232]

Hi, Could you show me how to use this code to find maximum value? Thanks in advance

[Nesa Nurani]

Instead of Math.min use Math.max

[nandag91807232]

Thanks that didn't work but I managed to get it working by using -infinity