Crew Scheduling - finding duplicate assignments

API & Customization Ask Developers
1

Hi all,

I have created an app for my employees to access their work schedules. Employees are scheduled to events throughout the year, each event has a name and a date, among other fields. Each event has at least 2 employees scheduled. The scheduling is done with an "Assigned Crew" field for the event where I can enter multiple names from a drop down select box.

One thing I am struggling with is how to find duplicate assignments for a date range.

Currently the workaround I have been using is, creating a pivot table with employee names and a field that counts how many events they've been assigned to. To filter by date range, I filter the raw Count field by date - in the Data section of my app.

2

Julian, thank you the replies. That code works great but you're right, it fails when multiple people are assigned to an event. Eg. A and B are assigned to an event 1, if I assign person A or B to event 2 first then the code will function as intended. If I assign person C to event 2, and then try person A or B - the code will fail.

Is there anyway I could override the duplicate booking warning if I needed to?

3

Julian, it's great. It worked without problems, this way of passing values ​​as a filter, to determine if there are duplicate values, will help me in many projects.

4

Thanks Julian & Tim. I will also try this JS for my project. In my case i need to look duplicate assignments for a date and time range.

5

Looking back at your question - you may need to loop through all the staff you are booking in the multiple names - I'm afraid I have no idea how you would do that (I try to avoid Javascript if I can).

Another option would be to use Integromat to search for overlaps when the record is created - you could then flag any issues in the records themselves. In this use case, the downside is that it wouldn't be absolutely immediate.

See my series Programming Knack with Integromat

6

Hi Tim

To do this you'll need some Javascript to look for possible duplicates. The following is some code I put together to look for duplicate bookings:

// add booking view 135
// account field 149
// event field 151

var message = '';

$(document).on('knack-view-render.view_135', function (event, view, data) {
	$('#' + view.key + '-field_151').chosen().change(function() {
      	check_duplicate();
	});
  
    $('<div class="kn-message error" id="duplicate_alert"></div>')
        .insertAfter($('#kn-input-field_151'))
        .hide();

	function check_duplicate () {
        var bookingFilters = [
          {
            'field':'field_149',
            'operator':'is',
            'value':$('#'+ view.key + '-field_149').val()
          },
          {
            'field':'field_151',
            'operator':'is',
            'value':$('#'+ view.key + '-field_151').val()
          }          
        ];
     	var fullUrl = 'https://api.knack.com/v1/objects/object_30/records?filters=' + encodeURIComponent(JSON.stringify(bookingFilters));
        Knack.showSpinner();
        $.ajax({
          url: fullUrl,
          async: false,
          type: 'GET',
          headers: {'X-Knack-Application-Id': 'XXXXXXXX', 'X-Knack-REST-API-Key': 'YYYYYYYYYYY'}
        }).done(function(data) {
          // Make sure we have no duplicate records
          if (data.records.length !== 0) {
            Knack.hideSpinner();
            $('#duplicate_alert').html($('<p><strong>You had already booked onto that event - please select another! </p></strong>')).show();
            $('#view_132-field_151').val([]).trigger('liszt:updated');
          }
          else
          {
         	$('#duplicate_alert').hide();
            Knack.hideSpinner();
          }
        }
	)}
});

It's not exactly the same but near enough to your use case I think?

Not nice but it worked.