Jump to content
  • 0

Remove from dropdown if value has been taken on date


kpcollier
 Share

Question

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!

Link to comment
Share on other sites

9 answers to this question

Recommended Posts

  • 0
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.

image.png.5f0de7adcb846f39feff4410f6ad9fc9.png

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!

 

 

Link to comment
Share on other sites

  • 0

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. 

Link to comment
Share on other sites

  • 0

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. 

Link to comment
Share on other sites

  • 0

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.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
 Share

×
×
  • Create New...