Produce a range of dates from 2 inputs: day-of-week and week-of-month?

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

We provide service to our customers once a month. For example, Ms. Jones' gets her service on the 3rd Tuesday of the month. In her customer record, we have fields <preferred-day-of-week> (represented as text) and <preferred-week-of-month> (also represented as text). For a given month, how to convert these to dates? I'd like to use the List function to produce a year's worth of service dates as below, dynamically:

Inputs: Tuesday, 3rd week of month

Result:

  • Feb 16, 2016
  • Mar 15, 2016
  • Apr 19, 2016
  • May 17, 2016
  • etc
Photo of Jonathan Heuer

Jonathan Heuer

  • 350 Points 250 badge 2x thumb

Posted 3 years ago

  • 0
  • 1
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,064 Points 20k badge 2x thumb
The datejs library will do these types of calculations and is used by QuickBase (somewhere?). However it is not loaded on all pages like jQuery and underscore is so you may as well load it yourself.


$.getScript("https://cdnjs.cloudflare.com/ajax/libs/datejs/1.0/date.min.js")
  .then(function() {
    //Third Sunday of the month
    console.log(Date.today().moveToNthOccurrence(0, 3));
  });

This is the signature for calling moveToNthOccurrence:

.moveToNthOccurrence ( Number dayOfWeek, Number occurrence )

Sunday is represented in the first argument as 0 and "third" is represented as the second argument as 3.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,064 Points 20k badge 2x thumb
Try this:

Reoccurring Dates
https://haversineconsulting.quickbase.com/db/bkkber4v6?a=td

Pastie Database
https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=477

On a new record select a [Day of Week] (Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday) and [Ordinal] (First, Second, Third, Fourth) and save the record. When the record is displayed 12 future dates will be display which match the [Day of Week] and [Ordinal]. The code can easily be extended to work on pages other than view record pages.
Photo of Jonathan Heuer

Jonathan Heuer

  • 350 Points 250 badge 2x thumb
Hi Dan, this code is not working for me. I have an Image Onload Field with the formula [iol] & "moduleSuggestedServiceDates.js" & [/iol]. I've pasted the script I'm using below. A few questions:

1. Are both dbid and dbidTable1 necessary? In your example they have the same value, and I'm not sure where they are used in the script.
2. How are the values for ordinal passed into the script, using this.dataset? I've named my fields "ordinal" and "dayofweek" (though they have different display labels), but not sure if that's working.
3. Will I be able to use the value of this field as a lookup (which is what I ultimately want)?

(function(){
  var querystring=document.location.search;
//check if the script is being called
console.log("Script moduleSuggestedServiceDates.js is being called");
  if(/a=dr/i.test(querystring)) {
    $("img[qbu=module]").closest("td").css("background-color","#FFFFFF");

    var dbid = "biyzwyt9e";
    var dbidTable1 = "biyzwyt9e";
    var apptoken = "my_app_token";
    $.ajaxSetup({data: {apptoken: apptoken}});

    $.getScript("https://cdnjs.cloudflare.com/ajax/libs/datejs/1.0/date.min.js")
      .then(function() {
        $("span.QBU_Dates").each(function() {
          var dayofweek = this.dataset.dayofweek;
          var ordinal = this.dataset.ordinal;
          console.log(dayofweek, ordinal);

          var days = ["Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday"];
          var ordinals = ["First Week", "Second Week", "Third Week", "Fourth Week"];

          var idayofweek = days.indexOf(dayofweek);
          var iordinal = ordinals.indexOf(ordinal) + 1;

          var result = "";
          for (var i=0; i< 12; i++) {
            var dateString = Date.today().addMonths(i)
              .moveToFirstDayOfMonth()
              .moveToNthOccurrence(idayofweek, iordinal)
              .toString("MMM d, yyyy");
            result += dateString + "<br/>";
          }

          $(this).html(result);
        });
      });
 
  }

})();
//The code in this page is copyright by Dan Diebolt and may not be used without including my phone number (734-985-0721) and email address (dandiebolt@yahoo.com) in the footer of where you may want to use it.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,064 Points 20k badge 2x thumb
>, this code is not working for me. ...
I left out a critical part in the pastie. The formula for the image onload field is this:

[iol] & "module.js" & [/iol]
&
"<span class='QBU_Dates' " &
"  data-dayofweek='" & [Day of Week] & "'" &
"  data-ordinal='" & [Ordinal] & "'" &
"></span>"

Also instead of the image onload filed being named [-] it is named [Dates].

I updated the pastie to include the formula:

Pastie Database
https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=477
Notes:
(1) Field values for [Day of Week] and [Ordinal] are passed to the script as data attributes on the <span> tag. These values a read of the <span> tag with these statements in the script:
var dayofweek = this.dataset.dayofweek;
var ordinal = this.dataset.ordinal;
(2) As to the output that is displayed it is placed within the <span> tag and appears to show up in the position where the image onload field is located.
Your Questions:
1. Are both dbid and dbidTable1 necessary? In your example they have the same value, and I'm not sure where they are used in the script.

The are slightly different in the last character. the dbid is the application dbid and the dbidTable1 is the table dbid.
var dbid = "bkkber4vb"; var dbidTable1 = "bkkber4v6";
2. How are the values for ordinal passed into the script, using this.dataset? I've named my fields "ordinal" and "dayofweek" (though they have different display labels), but not sure if that's working.
The are introduced in the formula via a data attribute on the <span> and read with the dataset method.
3. Will I be able to use the value of this field as a lookup (which is what I ultimately want)?

When used in this fashion you can think of the values of dates as a formula that is evaluated client side in comparison with QuickBase evaluating formulas on the server.the value can "land" anywhere you want it to (report, add/view/edit form, dashboard etc). I actually submitted a presentation dealing with client side formulas for Empower 2016 but I am not sure it will get expected because QuickBase is pushing this "low code" concept and my concept of "low code" embraces using libraries and abstractions that allow you to write extremely concise code.
Client Side Formula Magic
https://intuit.quickbase.com/db/bj94yxzax?a=dr&rid=44

Script the Base Fantastic
https://intuit.quickbase.com/db/bj94yxzax?a=dr&rid=45

Using The QuickBase API Like A Boss
https://intuit.quickbase.com/db/bj94yxzax?a=dr&rid=46

The QuickBase Category
https://intuit.quickbase.com/db/bj94yxzax?a=dr&rid=54
Photo of Jonathan Heuer

Jonathan Heuer

  • 350 Points 250 badge 2x thumb
Got it working Dan. Thanks for this elegant solution!