Tutorial - How to eliminate choices based on count

[Philip Mills]

I have a workshop registration form where students register for workshops, but each workshop has a limited number of spaces.

I've seen a bunch of tutorials showing how to use FormRanger to replace entries with "This session is full" based on the number of times a drop-down option is selected, such as this:

https://aryanirani123.medium.com/restricting-form-responses-for-each-option-in-google-forms-21f594327d6

However, it is actually quite easy to also remove entries from your drop down completely base on their count.

I just wrote a tutorial for it here:

https://community.spiceworks.com/how_to/184368-google-forms-use-form-ranger-to-populate-drop-downs-and-remove-choices-based-on-count

[Joseph Schmidt]

I just did a quick view of your process and it should work.  There will be an issue if there are no options in the list because all of the options are full.

Google will not replace the list in the form with no options.  I don't know if it is Google or Autocrat but there must be at least one option.

Joe Schmidt

 GLUA...@gmail.com

New Visions Blog Entry - Joe Schmidt

--
You received this message because you are subscribed to the Google Groups "NV Form Ranger Add-on" group.
To unsubscribe from this group and stop receiving emails from it, send an email to nv-form-ranger-a...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/nv-form-ranger-add-on/8fb2020b-c855-43d1-9768-24dfaea687b7n%40googlegroups.com.

[Philip Mills]

It does work, I went live with it today, and it's working great 

Good point about no entries. I had actually thought about that, but for my project it's not gonna be an issue.

But I should address it in the solution. I'll think about how...

You received this message because you are subscribed to a topic in the Google Groups "NV Form Ranger Add-on" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/nv-form-ranger-add-on/3JWekF3m5wU/unsubscribe.
To unsubscribe from this group and all its topics, send an email to nv-form-ranger-a...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/nv-form-ranger-add-on/CAPWPTOR-gyfFb22jXq1Wq77x6ZXQHOJurBZwQTz0GUxXBZt7dQ%40mail.gmail.com.

[Philip Mills]

I added an example google sheet to the tutorial

https://docs.google.com/spreadsheets/d/16oSCn28XvfBvKkEvx7M8CZKR8C_1HELxF-TSjfMm9_U/edit?usp=sharing

[Karla DeCramer]

Exactly what I needed - thank you!!

I was having issues with my options being available to select even though it said they were full. The workaround to deleting those options was solved by using the Query Formula. Pays to search for solutions on the web.

[Philip Mills]

you're welcome, glad you found it useful

--
You received this message because you are subscribed to a topic in the Google Groups "NV Form Ranger Add-on" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/nv-form-ranger-add-on/3JWekF3m5wU/unsubscribe.
To unsubscribe from this group and all its topics, send an email to nv-form-ranger-a...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/nv-form-ranger-add-on/00035651-704f-450e-a8de-706f29488fd7n%40googlegroups.com.

[Joseph Schmidt]

A good tutorial.  A couple of comments.  Instead of marking an option as "full" set it to nothing "".  If two or more classes become full, FormRanger will not update because of multiples.  See the comment in the description of the group.

I added a formula to check for no entries available.  

See FormRanger example

You may want to update your example.  It is so close.  I hope you don't mind my comments.  

Folks also have to be aware of which countries use commas and which use semicolons.in formulas.

Joe Schmidt

https://www.youtube.com/c/JoeSchmidt

 GLUA...@gmail.com

New Visions Blog Entry - Joe Schmidt

You received this message because you are subscribed to the Google Groups "NV Form Ranger Add-on" group.

To unsubscribe from this group and stop receiving emails from it, send an email to nv-form-ranger-a...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/nv-form-ranger-add-on/CAJpKwuQqNs-qGNPzJ1TjCXcPMeEwE8UcNCZ1Erp9AMdhTS3big%40mail.gmail.com.

[Philip Mills]

Hi Joe, and thanks.

Though it appears you've misunderstood something important.

According to my tutorial, formranger must retrieve its values from column E (FilterList), not D.

Column E (FilterList) already filters out any entries containing = 'FULL', so there will never be duplicates in column E and users should never even see the word "FULL".

If you're hitting a problem with duplicates its because you pointed formranger at column D instead of E.

About the semicolons vs commas i'll add that to the tutorial

[Joseph Schmidt]

Phil,

In my example, you don't need column E.  Look at D9 to see =if(CONCATENATE($D$2:$D$8)="";"none available";""). That formula handles the problem of no entries in the list.

We all have different methods of doing things. The important thing is that the formula works. Your method will work until all options are taken and then the form will not update because there are no values.

Joe Schmidt

https://www.youtube.com/c/JoeSchmidt

 GLUA...@gmail.com

New Visions Blog Entry - Joe Schmidt

--

You received this message because you are subscribed to the Google Groups "NV Form Ranger Add-on" group.
To unsubscribe from this group and stop receiving emails from it, send an email to nv-form-ranger-a...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/nv-form-ranger-add-on/6c4a63ab-9357-49c7-97c0-afd2f928daafn%40googlegroups.com.

[Philip Mills]

Hi Joe

Aha, so your solution solves the problem when all options are taken.

I get it now, I'll update the tutorial