Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

I'm not sure if this can be done or not but I'm creating a vehicle reservation site and need to populate a dropdownlist with only cars that are both active and not requested for the same period of the current request.

I have two lists, one that has the list of vehicles and a reservation list. I've created a custom web form to request a vehicle reservation with the field:

  <SharePoint:FormField runat="server" id="ff15{$Pos}" ControlMode="New" FieldName="Vehicle_x0020_Used" __designer:bind="{ddwrt:DataBind('i',concat('ff15',$Pos),'Value','ValueChanged','ID',ddwrt:EscapeDelims(string(@ID)),'@Vehicle_x0020_Used')}"/>

  <SharePoint:FieldDescription runat="server" id="ff15description{$Pos}" FieldName="Vehicle_x0020_Used" ControlMode="New"/>

which is the Vehicle Used/Assigned field for the ones approving the use of a vehicle. On the vehicle list I have a field that is called Active and that is a yes/no field. In the reservation list I have a field for the start time a vehicle is needed and a finish time of when they will bring the vehicle back. In the drop down list of Vehicle Used/Assigned I only want to display the vehicles that are both active and available during the time requested.

I'm new to SharePoint, but essentially what I want to do would be equivalent to this:

VehicleList table:
Name of Vehicle  |  Active

Reservation table:
PickupDate  |  ReturnDate

For dropdownlist:

SELECT [Name of Vehicle] FROM VehicleList
WHERE Active = 'Yes'
AND [Pickup Date (on form)] NOT BETWEEN dbo.Reservation.PickupDate AND dbo.Reservation.ReturnDate
AND [Return Date (on Form)] NOT BETWEEN dbo.Reservation.PickupDate AND dbo.Reservation.ReturnDate

I was able to use SPServices to populate the dropdown with just the active vehicles, but I don't think there is anything in SPServices for me to do the date filtering:

function popVehicleList() {
  $().SPServices.SPFilterDropdown({
    relationshipList: "Vehicle List",
    relationshipListColumn: "Title",
    columnName: "Vehicle Used",
    CAMLQuery: "<Eq><FieldRef Name='Active' /><Value Type='Boolean'>1</Value></Eq>",
    completefunc: null,
    debug: false
});
share|improve this question
    
Alright, I think I got it all figured out. Was a little bit more involved than I thought it would be, but for anyone else who is interested I've posted my solution below. –  Djuwal Dec 30 '13 at 18:53

3 Answers 3

Here's the solution I went with:

I first built my dropdown on the form itself (still using the built in web form):

<tr>
    <td style="width:190px;border-left:thin black solid;border-top:thin black solid;" valign="top" class="ms-formlabel">
        <H3 class="ms-standardheader">
            <nobr>Vehicle Assigned / Used</nobr>
        </H3>
    </td>
    <td style="width:400px;border-right:thin black solid;border-top:thin black solid;" valign="top" class="ms-formbody">
        <SharePoint:FormField runat="server" id="ff15{$Pos}" ControlMode="New" FieldName="Vehicle_x0020_Used" __designer:bind="{ddwrt:DataBind('i',concat('ff15',$Pos),'Value','ValueChanged','ID',ddwrt:EscapeDelims(string(@ID)),'@Vehicle_x0020_Used')}"/>
        <SharePoint:FieldDescription runat="server" id="ff15description{$Pos}" FieldName="Vehicle_x0020_Used" ControlMode="New"/>
    </td>
</tr>

Once that was done I wrote my javascript for the rest:

// what to do when the page loads
function pageLoad(sender, args) {   
    DelaySPScripts();
}
// delays the running of the user check until SP.js is loaded
function DelaySPScripts() {
    ExecuteOrDelayUntilScriptLoaded(popVehicleList, "SP.js");
}

// populate Vehicle List with all vehicles
function popVehicleList() {
    $().SPServices.SPFilterDropdown({
        relationshipList: "Vehicle List",
        relationshipListColumn: "Title",
        columnName: "Vehicle Used",
        CAMLQuery: "<Eq><FieldRef Name='Active' /><Value Type='Boolean'>1</Value></Eq>",
        completefunc: null,
        debug: false
    });
    popDates();
}

// populate vehicle list stripping the vehicles already reserved or in use
    var _ctxDate;
    var _webDate;
    function popDates() {
        //Get the list
        _ctxDate = SP.ClientContext.get_current();
        _webDate = _ctxDate.get_web();
        var list = _webDate.get_lists().getByTitle("Vehicle Reservation");
        //Create the query and get the results
        var datePickedUp = getField('input','Date Time Expected Pick Up').value;
        var datePickedUpHour = getDateFieldHours('Date Time Expected Pick Up').value;
        var datePickedUpMinute = getDateFieldMinutes('Date Time Expected Pick Up').value;
        var pickUpDateTime = FormatDate(datePickedUp,datePickedUpHour,datePickedUpMinute);
        var dateReturned = getField('input','Date Time Expected Return').value;
        var dateReturnedHour = getDateFieldHours('Date Time Expected Return').value;
        var dateReturnedMinute = getDateFieldMinutes('Date Time Expected Return').value;
        var returnDateTime = FormatDate(dateReturned,dateReturnedHour,dateReturnedMinute);
        var vehicleUsing = getField('select','Vehicle Used');
        var query = new SP.CamlQuery();
        query.set_viewXml("<View>"+
                    "<Query>"+
                        "<Where>"+
                            "<And>"+
                                "<Leq>"+
                                    "<FieldRef Name='Date_x0020_Time_x0020_Expected_x' />"+
                                    "<Value Type='DateTime' IncludeTimeValue='TRUE'>"+returnDateTime+"</Value>"+
                                "</Leq>"+
                                "<Geq>"+
                                    "<FieldRef Name='Date_x0020_Time_x0020_Expected_x0' />"+
                                    "<Value Type='DateTime' IncludeTimeValue='TRUE'>"+pickUpDateTime+"</Value>"+
                                "</Geq>"+
                            "</And>"+
                        "</Where>"+
                    "</Query>"+
                "</View>");
            _allDepartmentItems = list.getItems(query);
            _ctxDate.load(_allDepartmentItems, "Include(Vehicle_x0020_Used,ID)");
            _ctxDate.executeQueryAsync(Function.createDelegate(this, this.DropDownDateSuccess),
            Function.createDelegate(this, this.DropDownDateFailed));
        }

        function DropDownDateSuccess(sender, args) {
            //Clear out current entries
            var ddlDepartment = getField('select','Vehicle Used');
            var listEnumerator = _allDepartmentItems.getEnumerator();
            while (listEnumerator.moveNext()) {
                var currentItem = listEnumerator.get_current();
                var vehicleUsed = currentItem.get_item("Vehicle_x0020_Used").get_lookupValue();
                for (i=0; i < ddlDepartment.length; i++) {
                    if (ddlDepartment.options[i].text == vehicleUsed) {
                        ddlDepartment.remove(i);
                    }
                }
            }
        }
        function DropDownDateFailed() {
            alert("DateFailed");
        }

As you can see I've included my own functions to deal with the date field formats that SharePoint is so picky about, but essentially what I'm doing is filling in the dropdown list with all the vehicles and then when the user chooses a date range, the vehicles get stripped off of the choices for the vehicles available. I'm also doing it on load for when the managers edit the form to assign the vehicles.

share|improve this answer

I would look into using knockout.js

On Codeplex - there is a Knockout library that does essentially what you are looking for - utlizing Knockout, jQuery and the native SharePoint REST services

http://kosp.codeplex.com/

And a further explanation of Knockout & SharePoint by Marc Anderson - the guy who wrote the SPServices library for jQuery you are referencing

http://sympmarc.com/2013/09/10/spservices-stories-16-beginning-sharepoint-development-with-kosp-knockout-for-sharepoint-rest-api-and-sp-services/

share|improve this answer

I think it could be done in InfoPath, if that is an option on the table, pretty easily with sending filters into dropdowns. This is a pretty common request.

http://office.microsoft.com/en-us/infopath-help/filter-the-data-that-is-displayed-in-a-control-HA010024281.aspx

share|improve this answer
    
This form was originally done in InfoPath, but we are trying to limit the use of it where we can. If I can keep it strictly to a list without InfoPath, that'd be great. I'm looking into knockout.js now. –  Djuwal Dec 19 '13 at 21:57

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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