2

I have looked far and wide to see if I could piece this answer together myself from others who've asked for conditional drop-down selections, so please forgive me if this is redundant and answered elsewhere.

I'd simply like to create a condition in which a drop-down selection for Test Slot (CHOOSE, DM1, DM2, DM3, DR1, DR2, DR3, R1, R2, R3, R4, R5, R6, R7, R8, R9, R10, R11, R12, M1, M2, M3, M4, M5, M6) is limited to a subset of that if the list contains an item with Status=In Progress and Test Slot=DM1.

I'm using InfoPath 2013, SharePoint Designer 2013

Final Code, as advised by Joanna, within EditForm and NewForm.aspx

<script src="https://code.jquery.com/jquery-1.11.3.min.js" type="text/javascript"></script>
<script type="text/javascript">
$(document).ready( function() {
  var dropdown = $(":input[title='Test Slot']");
  if(checkItem("DM1")){
    dropdown.find("option[value='DM1']").remove();  
  }
  if(checkItem("DM2")){
    dropdown.find("option[value='DM2']").remove();    
  }
  if(checkItem("DM3")){
    dropdown.find("option[value='DM3']").remove();    
  }
  if(checkItem("DR1")){
    dropdown.find("option[value='DR1']").remove();  
  }
  if(checkItem("DR2")){
    dropdown.find("option[value='DR2']").remove();    
  }
  if(checkItem("DR3")){
    dropdown.find("option[value='DR3']").remove();    
  }
  if(checkItem("R1")){
    dropdown.find("option[value='R1']").remove();  
  }
  if(checkItem("R2")){
    dropdown.find("option[value='R2']").remove();    
  }
  if(checkItem("R3")){
    dropdown.find("option[value='R3']").remove();    
  }
  if(checkItem("R4")){
    dropdown.find("option[value='R4']").remove();  
  }
  if(checkItem("R5")){
    dropdown.find("option[value='R5']").remove();    
  }
  if(checkItem("R6")){
    dropdown.find("option[value='R6']").remove();    
  }
  if(checkItem("R7")){
    dropdown.find("option[value='R7']").remove();  
  }
  if(checkItem("R8")){
    dropdown.find("option[value='R8']").remove();    
  }
  if(checkItem("R9")){
    dropdown.find("option[value='R9']").remove();    
  }
  if(checkItem("R10")){
    dropdown.find("option[value='R10']").remove();  
  }
  if(checkItem("R11")){
    dropdown.find("option[value='R11']").remove();    
  }
  if(checkItem("R12")){
    dropdown.find("option[value='R12']").remove();    
  }
  if(checkItem("M1")){
    dropdown.find("option[value='M1']").remove();  
  }
  if(checkItem("M2")){
    dropdown.find("option[value='M2']").remove();    
  }
  if(checkItem("M3")){
    dropdown.find("option[value='M3']").remove();    
  }
  if(checkItem("M4")){
    dropdown.find("option[value='M4']").remove();  
  }
  if(checkItem("M5")){
    dropdown.find("option[value='M5']").remove();    
  }
  if(checkItem("M6")){
    dropdown.find("option[value='M6']").remove();    
  }
});

function checkItem(option) {
    var result = false;
    var requestUri = "https://company.sharepoint.com/sites/EXT-sfants/_api/Web/Lists/getbytitle('700 MHz Provisioning List')/Items?$select=Status,Slot&$filter=( Status eq 'In Progress') and (Slot eq '"+option+"')";
    $.ajax
    ({
    url: requestUri,
    type: "GET",
    cache: true,
    async: false,
    headers:{
    "ACCEPT": "application/json;odata=verbose"
         },
    success: function (data) {
    if($(data.d.results).length !== 0) 
    result = true;
    },
    error: function () {
    }
    });
    return result;
}
</script>
0

1 Answer 1

1

We need script to achieve that instead of using an InfoPath form.

Use Rest API or CSOM to check whether there are items which "Site" value has status "In Progress", then remove this "Site" option value from the drop-down in the New Form and Edit Form page.

You can try the demo below. Add Script Editor web part into the NewForm.aspx and EditForm.aspx, then enter the following script.

Note: Change the list name and site path to yours.

<script src="https://code.jquery.com/jquery-1.11.3.min.js" type="text/javascript"></script>
<script type="text/javascript">
$(document).ready( function() {
  var dropdown = $(":input[title='Site']");
  if(checkItem("A")){
    dropdown.find("option[value='A']").remove();  
  }
  if(checkItem("B")){
    dropdown.find("option[value='B']").remove();    
  }
  if(checkItem("C")){
    dropdown.find("option[value='C']").remove();    
  }
});

function checkItem(option) {
    var result = false;
    var requestUri = "https://tenant.sharepoint.com/sites/<site>/_api/Web/Lists/getbytitle('List B')/Items?$select=Status,Site&$filter=( Status eq 'In Progress') and (Site eq '"+option+"')";
    $.ajax
    ({
    url: requestUri,
    type: "GET",
    cache: true,
    async: false,
    headers:{
    "ACCEPT": "application/json;odata=verbose"
         },
    success: function (data) {
    if($(data.d.results).length !== 0) 
    result = true;
    },
    error: function () {
    }
    });
    return result;
}
</script>

Testing Results:

enter image description here

enter image description here

Update:

  1. Use F12 to check the drop-down element.

enter image description here

  1. Use the internal name of the column in REST API.

To get the actual internal name for your column, browsing to the list/library Settings > Edit Column and look at the path.

The path will be like:

<site>/_layouts/15/FldEditEx.aspx?List=%7B<list GUID>%7D&Field=<column internal name>

enter image description here

Code should be:

<script src="https://code.jquery.com/jquery-1.11.3.min.js" type="text/javascript"></script>
<script type="text/javascript">
$(document).ready( function() {
  var dropdown = $(":input[title='Test Slot']");
  if(checkItem("DM1")){
    dropdown.find("option[value='DM1']").remove();  
  }
  if(checkItem("DM2")){
    dropdown.find("option[value='DM2']").remove();    
  }
  if(checkItem("DM3")){
    dropdown.find("option[value='DM3']").remove();    
  }
  if(checkItem("DR1")){
    dropdown.find("option[value='DR1']").remove();  
  }
  if(checkItem("DR2")){
    dropdown.find("option[value='DR2']").remove();    
  }
  if(checkItem("DR3")){
    dropdown.find("option[value='DR3']").remove();    
  }
  if(checkItem("R1")){
    dropdown.find("option[value='R1']").remove();  
  }
  if(checkItem("R2")){
    dropdown.find("option[value='R2']").remove();    
  }
  if(checkItem("R3")){
    dropdown.find("option[value='R3']").remove();    
  }
  if(checkItem("R4")){
    dropdown.find("option[value='R4']").remove();  
  }
  if(checkItem("R5")){
    dropdown.find("option[value='R5']").remove();    
  }
  if(checkItem("R6")){
    dropdown.find("option[value='R6']").remove();    
  }
  if(checkItem("R7")){
    dropdown.find("option[value='R7']").remove();  
  }
  if(checkItem("R8")){
    dropdown.find("option[value='R8']").remove();    
  }
  if(checkItem("R9")){
    dropdown.find("option[value='R9']").remove();    
  }
  if(checkItem("R10")){
    dropdown.find("option[value='R10']").remove();  
  }
  if(checkItem("R11")){
    dropdown.find("option[value='R11']").remove();    
  }
  if(checkItem("R12")){
    dropdown.find("option[value='R12']").remove();    
  }
  if(checkItem("M1")){
    dropdown.find("option[value='M1']").remove();  
  }
  if(checkItem("M2")){
    dropdown.find("option[value='M2']").remove();    
  }
  if(checkItem("M3")){
    dropdown.find("option[value='M3']").remove();    
  }
  if(checkItem("M4")){
    dropdown.find("option[value='M4']").remove();  
  }
  if(checkItem("M5")){
    dropdown.find("option[value='M5']").remove();    
  }
  if(checkItem("M6")){
    dropdown.find("option[value='M6']").remove();    
  }
});

function checkItem(option) {
    var result = false;
    var requestUri = "https://tennat.sharepoint.com/sites/<site>/_api/Web/Lists/getbytitle('List B')/Items?$select=Status,Test_x0020_Slot&$filter=( Status eq 'In Progress') and (Test_x0020_Slot eq '"+option+"')";
    $.ajax
    ({
    url: requestUri,
    type: "GET",
    cache: true,
    async: false,
    headers:{
    "ACCEPT": "application/json;odata=verbose"
         },
    success: function (data) {
    if($(data.d.results).length !== 0) 
    result = true;
    },
    error: function () {
    }
    });
    return result;
}
</script>
5
  • Joanna, thank you for adding the sample code. but it is not working. I modified the Site column to Test Slot and added in the values it should look for instead of ABC. I inserted the code into a WebPartPages:ScriptEditorWebPart within the WebPartZone on NewForm and EditForm. I also updated the requestUri to "h t t p s ://company.sharepoint.com/sites/EXT-sfants/WorkflowTasks/Inclusive.aspx('700 MHz Provisioning List')/Items?$select=Status,Test Slot&amp;$filter=(Status eq 'In Progress') and (Test Slot eq &'"+option+"'); Aug 23, 2018 at 18:53
  • You should use REST API "<site>/_api/Web/Lists/getbytitle("<list>")" as the request Url. Aug 24, 2018 at 1:33
  • I have modified the syntax (shown in edit above) and no joy there Aug 24, 2018 at 3:30
  • I checked your code, found some problems. please check my update in my answer. Aug 24, 2018 at 6:30
  • Joanna, this works now! Thank you very much for your help, this has taught me a few things about the API and SharePoint. Aug 24, 2018 at 13:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.