Question

Specify a table dropdown field where all values to selections are all equal to 1

Forms

Updated June 14, 2018

asked on June 8, 2018 • Show version history

Hello All,

I wondering if it's possible through javascript or otherwise to have a drop down in a table where all of the values in it can equal 1? The use case is to get the sum of all selections of the same kind tabulated to respective fields. All selections are only worth 1 so that each can be calculated properly since each value equals a day.

So far my experience is that the values need to be unique to calculate properly using the functions on the advanced tab. If I make all values set to 1, then Forms versions it like in the screen shot.

I'm really interested to see if there's a way for javascript to handle this use case.

Thanks in advance,

Brian

Table Calculation Versions.PNG (9.79 KB)

| Download

0 0

Replies

replied on June 8, 2018 • Show version history

Instead, use javascript to get the .length from the table. This will return how many rows are present.

  var totalRows = $('#q119 tr').length-1;

or

  var totalRows = $('#q119 tr').length;

The 'tr' is row, so it counts all rows. I use the "-1" to exclude the header label row.

Test it by putting the code into the console.log to make sure you get back the value you are looking for:

console.log(  $('#q119 tr').length-1 );

or

console.log(  $('#q119 tr').length );

Hope this helps!

0 0

replied on June 13, 2018 • Show version history

Hi Chris,

I guess my question is what is a row for you? With this syntax you specify "#q119" that looks like columns in my table when I inspect the element. So I tried to do this to see if it would add them up

console.log( $('q910, #q911 tr').length-1 );

And I get "Undefined" Any ideas? I'm pretty much a Javascript toddler, so sorry if I'm way off or not getting this.

Thanks for your help.

0 0

replied on June 14, 2018

LOL!, no worries. I like the term toddler. I am going to steal that.

So here is the thing. the '#q119' in my code is the ID for my table. When I ad the 'tr' at the end, my selector becomes 'all rows in table #q119'. Stay with me here. When I ask for the '.length' of all the rows, it tells me how many rows there are. You should get a number in the console.

In your code, you ask for 2 different IDs (q910 & q911). I would start your testing with just the ID for your table. Be sure to include a '#' before the id every time as well. So your code might be:

$('#q910 tr').length-1

$('#q911 tr').length-1

-depending on which ID is for your table.

First figure out what the ID is for your table. You should be able to see it in the CSS & Javascript tab of your form. Or inspect for it.

After I re-read your question, it looks like you would need more javascript that just this to determine how many rows you have of a particular value. It's totally do-able with javascript and Steve is a wiz at it. Let me know if you want to dig in deeper.

Hopefully this clarified a little about javascript syntax!

0 0

replied on June 11, 2018

Hi Brian

Are you trying to total this in the table row our to a field outside the table. i've used the Countif function to count selections in a table before to an outside field which would tell me how many "w" were selected for example.

Steve

0 0

View 9 previous replies

replied on June 12, 2018

Hi Steve,

That is exactly what I'm trying to do, but it doesn't seem to like it when the values are not unique, and the result, it seems, is that they will not calculate. Just trying to get the sum of all W's and V's, but both values equal 1.

Thanks for your help,

Brian

0 0

replied on June 12, 2018

Change your values to the same as the Choices (W=W,V=V, etc), COUNTIF would tell you how many are Chosen so they don't need a value of 1, as if three of the tables row had W as a selection, it would return "3".

0 0

replied on June 12, 2018

I like it, Steve! I'll give that a try. Thanks so much!

0 0

replied on June 12, 2018

Hi Steve,

For some reason it's giving me a "Calculation contains invalid syntax" error. This is my formula as it appears

=COUNTIF([July_Week1.July_Week1_2, July_Week1.July_Week1_3, July_Week1.July_Week1_4, July_Week1.July_Week1_5, July_Week1.July_Week1_6 "=W")

Calculating only the 1_2=Monday, 1_3=Tuesday, 1_4=Wednesday, 1_5=Thursday and 1_6=Friday fields that =W.

I see many examples that specify either < or >, but none with =. Is that what it doesn't like about the syntax? Seems straight forward otherwise.

Thanks again

0 0

replied on June 12, 2018

I did make the change to the drop down fields so that W=W and V=V for 1_2 thru 1_6.

0 0

replied on June 12, 2018

You have an open "[" in you formula, but I believe you are actually going to need to use COUNTIFS now that I see your Formula has multiple fields. A picture of this section of the form would be helpful or attach the form itself.

=COUNTIFS(July_Week1.July_Week1_2,"W",July_Week1.July_Week1_3, "W",July_Week1.July_Week1_4, "W",July_Week1.July_Week1_5,"W", July_Week1.July_Week1_6,"W")

Unfortunately, I haven't had a chance to test this. You may want to try one field first and expand from there as Single Columns versus Multiple is slightly different. ie: This is for a Single column =COUNTIF(July_Week1.July_Week1_2,"W")

0 0

replied on June 13, 2018

Thanks, Steve. I found the open ]. I hit up support with this and they said that this below is the correct syntax, but it will still not work.

=COUNTIF([July_Week1.July_Week1_2, July_Week1.July_Week1_3, July_Week1.July_Week1_4, July_Week1.July_Week1_5, July_Week1.July_Week1_6], "=W")

Support notes: "But even though the above changes can be placed in the system to prevent the error from taking place, the formula will not work why? because countif works over a single array, while the table has multiple columns and rows. that is not a single array."

So they thought that I should start answers page to get the correct formula. Also, I tested COUNTIFS with the proper syntax, and it shows a calculation error, and that does not appear in the list when selecting a formula.

Going to try the javascript, Chris.

Thanks,

Brian

0 0

replied on June 14, 2018

This is just bugging me now, I tried setting up the COUNTIF as a test as you had stated and it's not working either, so I came up with a work around. Instead of COUNTIF, I used SUMIF to count the W's in the Table. For each of the Drop Down Options, I gave them a numeric value

I then used the following formula to SUM the W Values which is "1" by Column

=SUM(SUMIF(Table.Column1,"1"),SUMIF(Table.Column2,"1"),SUMIF(Table.Column3,"1"))

There is likely a better way for this but I basically handle each column individually at this time. The Picture below shows the results when I made the W selections in the table.

0 0

replied on June 14, 2018 • Show version history

Hi Steve,

I thought your were onto something, but where it all goes bad for me is that I essentially need all of the values to be equal to 1. W=Workday and V=Vacation Day those of the same values must be tabulated to separate fields. Because of the way that the days of the calendar must be laid out the form was created so that 1 table = a week. This allows for the first day of the month for January be in the third column instead of the first. While adding more rows to the table sounded like a good idea to me it didn't allow it to retain the different formats for each month. So I really just want to see if I can get one week/row to calculate properly for each V and W value in it, and the issue to me is the fact that it seems that each item in the drop down all can't be a value of 1. Thanks again for your help.

0 0

replied on June 14, 2018

Hi Brian

What I was thinking was that with other values 1,2,3,4, you would divide that total buy the value to know how many of each. examples below.

=DIV(SUM(SUMIF(Table.Column1,"2"),SUMIF(Table.Column2,"2"),SUMIF(Table.Column3,"2")),2)

=DIV(SUM(SUMIF(Table.Column1,"3"),SUMIF(Table.Column2,"3"),SUMIF(Table.Column3,"3")),3)

=DIV(SUM(SUMIF(Table.Column1,"4"),SUMIF(Table.Column2,"4"),SUMIF(Table.Column3,"4")),4)

0 0

replied on June 14, 2018

If you have a chance to share your form, I'd be interested in seeing it.

0 0

replied on June 14, 2018 • Show version history

No problem, any help is appreciated. Thanks

0 0

You are not allowed to follow up in this post.

Question

Question

Specify a table dropdown field where all values to selections are all equal to 1

Replies

Sign in to reply to this post.