Room reservation code

Forms SharePoint Online
1

Hi, I've been trying out filtering with pnp, and created the lists and form as per https://plumsail.com/docs/forms-sp/examples/reservation-system.html.

Most of it works fine, but the line

roomIds = items.map(function(i) { return i.RoomId });

requires a semicolon after RoomId.

Even with this change, this line doesn't work. When testing, the content of roomIds shows [object Object],[object Object]... (one [object Object] for each relevant entry, so it gets the correct number of entries)

Checking roomIds[0], etc, gives a value of 'undefined'. The filterString contains 'Id ne and Id ne and Id ne and Id ne '

This is the code, amended for the field names in my form:

fd.spRendered(function() {

    //set the time interval in minutes
    fd.field('Reservation_x0020_Date').widgetOptions = {
        interval: 60
    };
    
    var list = pnp.sp.web.lists.getByTitle("CR Room Reservations");
    
    fd.field('Reservation_x0020_Date').$on('change', function(date) {
        var rDate = date.toISOString();
        
        list = pnp.sp.web.lists.getByTitle("CR Room Reservations");
        list.items.filter("Reservation_x0020_Date eq '" +  rDate +"'").get().then(function(items) {
            
            var roomIds = items.map(function(i) { return i.RoomId; });
            
            if(roomIds.length > 0) {
                //filter Conference room lookup field values
                var filterString = "Id ne " + roomIds.join(" and Id ne ");

                fd.field('Conference_x0020_Room').filter = filterString;
                fd.field('Conference_x0020_Room').refresh();
            }
        });
    });
});

Can you let me know what's wrong? Have I implemented it incorrectly?

Edit: I think i.RoomId is supposed to get the ID of the room from the ConferenceRooms list, but the room selection is a lookup field... wouldn't that need some way of referencing the ConferenceRooms list in the code? Maybe using get_lookupID?

2

Dear @njones,
Can you attach a screenshot of what result you get? Screenshots of the source lists could also help. Also, do you have any errors in the console?

3

Hi Nikita, sorry for the delay.

I've added some code to output the variables to Title as it goes through.

fd.spRendered(function() {

fd.field('Title').value += "st ";

//set the time interval in minutes
fd.field('Reservation_x0020_Date').widgetOptions = {
    interval: 60
};

var list = pnp.sp.web.lists.getByTitle("CR Room Reservations");
var listRooms = pnp.sp.web.lists.getByTitle("CR Conference Rooms");

//fd.field('Title').value += list;    

fd.field('Reservation_x0020_Date').$on('change', function(date) {
    var rDate = date.toISOString();

    fd.field('Title').value += "RD1 ";
    
    list = pnp.sp.web.lists.getByTitle("CR Room Reservations");
    fString = "Reservation_x0020_Date eq '" +  rDate +"'";
        fd.field('Title').value += fString;
        fd.field('Title').value += " fs ";
    list.items.filter("Reservation_x0020_Date eq '" +  rDate +"'").get().then(function(items) {
        
        fd.field('Title').value += items;
        fd.field('Title').value += " i1 ";
        
        var roomIds = items.map(function(i) { return i.RoomId; });
        
        fd.field('Title').value += roomIds;

        fd.field('Title').value += "i2 ";

        fd.field('Title').value += roomIds.length;
        
        if(roomIds.length > 0) {
            //filter Conference room lookup field values
            var filterString = "Id ne " + roomIds.join(" and Id ne ");

            fd.field('Title').value += " i3 ";
            fd.field('Title').value += filterString;
            
            fd.field('Conference_x0020_Room').filter = filterString;
            fd.field('Conference_x0020_Room').refresh();

            fd.field('Title').value += " i4 ";
        }
    });
});
});

image
image698×338 120 KB

image
image

The out put I get:
st RD1 Reservation_x0020_Date eq '2020-08-17T23:00:00.000Z' fs i1 i2 0RD1 Reservation_x0020_Date eq '2020-08-18T10:00:00.000Z' fs [object Object],[object Object],[object Object] i1 ,,i2 3 i3 Id ne and Id ne and Id ne i4

It seems to be getting the date filter right, but the line "var roomIds = items.map(function(i) { return i.RoomId; });" doesn't fetch anything back, so filterString doesn't contain the Room IDs.

Is it because the rooms are a Lookup?

Regards
Nick

4

Hello @njones,

What is the internal name of the Conference room (Lookup field) in the Room Reservations list?

You need to replace Room with the internal name of the field. As this is a lookup field, to get the Id of the item we add Id to the internal name, e.g. your field internal name is ConfRoom so the code line would be:

var roomIds = items.map(function(i) { return i.ConfRoomId });

Please update the code and try it out.

1 Like
5

Thank you!

The internal name was Conference_x0020_Room, and when I put in Conference_x0020_RoomId, it's worked perfectly.

:+1:

Regards
Nick

1 Like
6

Just a quick update:

The code above worked, but when I was testing it, I discovered that if a time was selected which had rooms booked (so the filter kicked in, correctly, and not all rooms were shown), and then a time was selected where no rooms were booked (so all rooms should have been shown), then the filter from the previous selection was still being applied, which meant that not all rooms were shown even though none had been booked.

I found that I had to add the 'else' code below:

        if(roomIds.length > 0) {
            //filter Conference room lookup field values
            filterString = "Id ne " + roomIds.join(" and Id ne ");

            fd.field('Conference_x0020_Room').filter = filterString;
            fd.field('Conference_x0020_Room').refresh();

        } else {
            filterString = "";
            fd.field('Conference_x0020_Room').filter = filterString;
            fd.field('Conference_x0020_Room').refresh();
        }

This reset the filter and showed all the rooms correctly.

Regards
Nick

7

Hello @njones,

Yes, you are absolutely right! Thank you for sharing this!

We will update the code in the article.

1 Like