Help with making a "smart" form

[J_Udub]

I'm trying make this form that when you select the month it would automatically fill in weekends, however, I do not know if that is feasible. If anything I would like the DAYS portion of the form be able to count the text fields above to automatically update. I already was able to do the Total field since it is an Easy calculation. But I cannot find or figure out how to make the days fields count the corresponding Letter(s) or symbol to automatically populate the amount that were filled in. Any help would be truly appriciated along with guidance on how to. I'm trying to learn.

[try67 · Accepted solution]

Much better.

1. You can use this code as the custom Validation script of your Month fields (I would set them to commit the selected value immediately, so the fields are updated as soon as you make the selection):

for (var i=1; i<=31; i++) {
	this.getField("Day"+rowNumber+"_"+i).value = "";
}
if (event.value!=event.target.defaultValue) {
	var rowNumber = event.target.name.replace("MonthRow", "");
	var d = util.scand("mmmm dd, yyyy", event.value + " 01, " + this.getField("Year").valueAsString);
	var selectedMonth = d.getMonth();
	while (d.getMonth()==selectedMonth) {
		if (d.getDay()==5) this.getField("Day"+rowNumber+"_"+d.getDate()).value = "X";
		d.setDate(d.getDate()+1);
	}
}

2. And this as the custom Calculation script of the first total field, for example, to count all the times "V" is entered into one of the fields (note that it's case-sensitive, so it will not count "v", although that can be adjusted):

var total = 0;
for (var i=1; i<=4; i++) {
	for (var j=1; j<=31; j++) {
		var f = this.getField("Day"+i+"_"+j);
		if (f.valueAsString=="V") total++;
	}
}
event.value = total;

You just need to change "V" in line #5 of that code to calculate the other totals.

[try67 · Accepted solution]

Sorry, small mistake there... Use this version:

var rowNumber = event.target.name.replace("MonthRow", "");
for (var i=1; i<=31; i++) {
	var fname = "Day"+rowNumber+"_"+i;
	var f = this.getField(fname);
	if (f==null) {console.show(); console.println("Missing field: " + fname);}
	else f.value = "";
}
if (event.value!=event.target.defaultValue) {
	var d = util.scand("mmmm dd, yyyy", event.value + " 01, " + this.getField("Year").valueAsString);
	var selectedMonth = d.getMonth();
	while (d.getMonth()==selectedMonth) {
		if (d.getDay()==5) this.getField("Day"+rowNumber+"_"+d.getDate()).value = "X";
		d.setDate(d.getDate()+1);
	}
}

[try67]

It seems you're asking for two different things...

1. Fill in the weekends (I assume you mean Saturday and Sunday) with some kind of value, or fill color.

2. Count all the instances of a certain value (say "V") in all of the fields and show that total in a text field.

Is this correct?

If so, both are possible, but the first is a bit trickier. For starters, it will require either specifying the year somewhere (as the dates change per year), or to assume it's the current year. And either way it will require a more complex script, since dealing with Date objects in JavaScript is not straight-forward.

The latter simply requires writing a loop to iterate over all of the fields, incrementing a counter variable each one one of them equals the value we're interested in.

[J_Udub]

You're correct I am asking for 2 things.

1. Yes I'd like it to be possible to put * on Friday's. Current year and
then be able to modify it for the next etc.

2. Yes count all the instances of "V", "H", etc listed in the fields.

[try67]

1. This is not a trivial script, especially because of your field names. Also, you're missing some fields in the middle (like Text19), which makes it even more difficult...

2. This is also affected by that issue. If you could rename your fields to something that makes a bit sense it will be easier. For example "Day1_1", "Day1_2", etc., for the first row, and "Day2_1", "Day2_2", etc. for the second. And make sure the last field in each row ends with "31"...

[J_Udub]

So I modified the "TEXT' blocks as specified and then added drop downs for each row. So maybe that helps.

I was also wondering, if I added a drop down for calendar year would that help with the script, like if I made the default 2022, and then added 2023, 2024, 2025. just in general for future planning?

I appreciate the help and thank you for your attention to help me out.

[try67]

Yes, that would help. Make that change and then share the new version of the file.

[J_Udub]

I did, it's above your last reply.

[try67]

But it doesn't have the years...

[J_Udub]

Ahh, yeah... I will upload it when I get back to work. That will be in
about 14 hrs.

[J_Udub]

Here, you go! updated

[try67 · Accepted solution]

Much better.

1. You can use this code as the custom Validation script of your Month fields (I would set them to commit the selected value immediately, so the fields are updated as soon as you make the selection):

for (var i=1; i<=31; i++) {
	this.getField("Day"+rowNumber+"_"+i).value = "";
}
if (event.value!=event.target.defaultValue) {
	var rowNumber = event.target.name.replace("MonthRow", "");
	var d = util.scand("mmmm dd, yyyy", event.value + " 01, " + this.getField("Year").valueAsString);
	var selectedMonth = d.getMonth();
	while (d.getMonth()==selectedMonth) {
		if (d.getDay()==5) this.getField("Day"+rowNumber+"_"+d.getDate()).value = "X";
		d.setDate(d.getDate()+1);
	}
}

2. And this as the custom Calculation script of the first total field, for example, to count all the times "V" is entered into one of the fields (note that it's case-sensitive, so it will not count "v", although that can be adjusted):

var total = 0;
for (var i=1; i<=4; i++) {
	for (var j=1; j<=31; j++) {
		var f = this.getField("Day"+i+"_"+j);
		if (f.valueAsString=="V") total++;
	}
}
event.value = total;

You just need to change "V" in line #5 of that code to calculate the other totals.

[J_Udub]

I laoded the custom validation script to the month drop downs but it doesnt do anything, the counting one works perfectly although when values are deleted the "total" sometimes still shows 1? until I put an erronious value in the field and then it updates to 0.

If I need to specifically target or put names in the 1st script then I will adjust, but for now the counter works.

[try67 · Accepted solution]

Sorry, small mistake there... Use this version:

var rowNumber = event.target.name.replace("MonthRow", "");
for (var i=1; i<=31; i++) {
	var fname = "Day"+rowNumber+"_"+i;
	var f = this.getField(fname);
	if (f==null) {console.show(); console.println("Missing field: " + fname);}
	else f.value = "";
}
if (event.value!=event.target.defaultValue) {
	var d = util.scand("mmmm dd, yyyy", event.value + " 01, " + this.getField("Year").valueAsString);
	var selectedMonth = d.getMonth();
	while (d.getMonth()==selectedMonth) {
		if (d.getDay()==5) this.getField("Day"+rowNumber+"_"+d.getDate()).value = "X";
		d.setDate(d.getDate()+1);
	}
}

[try67]

Regarding the issue with the delayed calculation of the Total field: This is caused by an incorrect fields calculation order. You need to first apply all the calculations and then edit that list (under More in Prepare Form mode) and move the Total field (DaysRow10) all the way down, after all the others.

[J_Udub]

I appreciate the help, I think I understood some of it. Its been awhile since I read a Javascript book. But, by looking at it  I took this:

var total = 0;
for (var i=1; i<=4; i++) {
	for (var j=1; j<=31; j++) {
		var f = this.getField("Day"+i+"_"+j);
		if (f.valueAsString=="V") total++;
	}
}

and then modified it to this:

var total = 0;
for (var i=1; i<=4; i++) {
	for (var j=1; j<=31; j++) {
		var f = this.getField("Day"+i+"_"+j);
		if (f.valueAsString=="V") total++;
		if (f.valueAsString=="v") total++;
	}
}

which effectively made the script read upper and lowercase.

I also took the advice of changing the DaysRow10 and changed it to Total, and moved it to the very bottom along with clearing and reaccomplishing the sum value and everything works to perfection!

Thank you so much with your help and appreciate your time!

[J_Udub]

Try67,

Reaching out to you, or anyone interested in helping, again for some help.

Now I need the form to cross reference "site" to fill in the months.

Q is fine at "5" for Fridays

O needs to be 5 & 6

And U need to be 6 only.

Is that doable?

[try67]

Sorry, I don't understand what you're asking for.

[J_Udub]

I'm sorry if what I wrote was confusing.
Ok, so you made it so the form references the yr and combine with month it
fills in every "5" (friday) for each week with a "*".
So there are 3 sections that use the form and each section has different
days that have the "*" exemption. So i created a drop down with; Q, O, and
U.
My question is, is there a way to have the from reference the letter to
fill out the appropiate days with the *.

For example; you select 2022, Q and then Apr, the form fills out 1, 8, 15,
22, and 29.
You select 2022, O, Apr and it fills out 1, 2, 8, 9, 15, 16, 22, 23, 29,
and 30.
You select 2022, U, Apr and it fills out 2, 9, 16, 23, and 30.

Does that help?

[try67 · Accepted solution]

Yes, I think I understand now. So add this line to the top of the code:

var site = this.getField("Site").valueAsString;

And replace this line:

if (d.getDay()==5) this.getField("Day"+rowNumber+"_"+d.getDate()).value = "X";

With this:

if ((site=="Q" && d.getDay()==5) || (site=="O" && (d.getDay()==5 || d.getDay()==6)) || (site=="U" && d.getDay()==6))
this.getField("Day"+rowNumber+"_"+d.getDate()).value = "X";