Remove from dropdown if value has been taken on date

[kpcollier]

I'm trying to figure out how to make some sort of scheduling calendar, where I have a small list of assistants from my users table that can be scheduled on a day chosen from the monthly calendar. If one of the assistants have already been scheduled for the day, I would like their name to be taken out of the dropdown list for the next user, so that they cannot be double-scheduled. 

I'm struggling on finding the workflow to filter the dropdown (to choose which assistant) if the assistant has already been chosen for that day.

Currently, I have the monthly calendar set up with a scheduling submission form.

Any help is appreciated!

[TellMeWhy]

2 hours ago, kpcollier said:

I'm trying to figure out how to make some sort of scheduling calendar, where I have a small list of assistants from my users table that can be scheduled on a day chosen from the monthly calendar. If one of the assistants have already been scheduled for the day, I would like their name to be taken out of the dropdown list for the next user, so that they cannot be double-scheduled. 

I'm struggling on finding the workflow to filter the dropdown (to choose which assistant) if the assistant has already been chosen for that day.

Currently, I have the monthly calendar set up with a scheduling submission form.

Any help is appreciated!

This is quite tricky, basically, you have lookup table for assistants, and they can only be booked PER DAY, and if they're booked for that date already, then they're not selectable in the dropdown.

 

Here's what I tested

 

Virtual Field 1

STUFF(
    (SELECT ' , ' + CAST (FIELDNAME AS nvarchar)
    FROM TABLENAME
    WHERE DATEFIELD = target.[@field:DateFIELD]
    FOR XML PATH (''))
, 1, 2, '')

What this does is combine all records that meet the criteria. We want to use this to select values where the date is the specific date that the user is booking.

For example here, I'm using June 28 2021 as the date and the three values showed up.

 

Virtual 2 is a Custom and LookUp Table Dropdown

 

Script

<script>

document.querySelector("input[name='cbParamVirtual1']").addEventListener("change", function(event){


var d2 = document.getElementById("cbParamVirtual2");

var a= document.querySelector("input[name='cbParamVirtual1']").value;



for (var i=0; i<d2.length; i++) {

    if (a.includes(d2.options[i].value)){ 
        d2.options[i].remove();
       }
    else {
         d2.options[i].style.display = "block";
       }
  }


});


</script>

 

What this script does is loop through the options in dropdown and check if 'a' (or the Virtual 1 Calculated Value) includes the said option then removes it from the dropdown.

I hope this helps!

 

 

[kpcollier]

Thanks again, @TellMeWhy. This is an awesome workflow that is working just like I had envisioned in my mind. 

[amillernwg]

Hi I would love to tag onto this and ask for more information on how to set this up, I am not sure where to put the data that you gave to get it to populate the page

 

[kpcollier]

Hi @amillernwg. 

First off, do you have a user table that stores the information for your users? And is your submission form running off of a different table that has a date field?

If so, your going to need to add a virtual field  to your submission form. You'll set the virtual field to a Calculated Value element. Use the code below in the calculated field where TABLENAME is the name of your table, and DateFIELD is the name of your date field your table:

STUFF(
    (SELECT ' , ' + CAST (FIELDNAME AS nvarchar)
    FROM TABLENAME
    WHERE DATEFIELD = target.[@field:DateFIELD]
    FOR XML PATH (''))
, 1, 2, '')

Then, on the same submission form, you will want to click on the Insert Picker and insert a Header & Footer. Go to Footer, click Advanced, and uncheck 'Enable HTML editor'. Then, go back to the Standard tab, and insert this code:

<script>

document.querySelector("input[name='cbParamVirtual1']").addEventListener("change", function(event){


var d2 = document.getElementById("cbParamVirtual2");

var a= document.querySelector("input[name='cbParamVirtual1']").value;



for (var i=0; i<d2.length; i++) {

    if (a.includes(d2.options[i].value)){ 
        d2.options[i].remove();
       }
    else {
         d2.options[i].style.display = "block";
       }
  }


});


</script>

The only thing you need to change is the 'cbParamVirtual2'. Here you will put the name of your user field. You might need to put 'InsertRecordFIELDNAME' since it is a submission form, where FIELDNAME is the name of your user field. 

[amillernwg]

that helps a lot. let me see how that works

[amillernwg]

that system works perfectly except for one small item i am not sure how to get the virtual field to pass the data to the new record, it shows blank. I feel like the "insertRecordFIELDNAME" is what i am trying to do but not sure how or where to add it to the script. 

[amillernwg]

actually I found the answer. In case anyone needs to know I added the ID field and changed the form element to calculated field. In the formula field i added [@cbParamVirtual2] . When you click Dropdown list in virtual field 2 It updates the ID field.

[amillernwg]

@kpcollier last issue i am having with this script, It seems to leave the last employee still showing even if that employee is not available. Would you know how to make the dropdown show blank or a lack of options if all employees have been used?

[kpcollier]

12 hours ago, amillernwg said:

Would you know how to make the dropdown show blank or a lack of options if all employees have been used?

Let me see if I can replicate the issue and I'll let you know if I find a solution.