I was wondering how to add a calendar month to a date please?
I have a field {next_rent_due} which may contain for example 20/04/2017 (UK format) and I wish another calculated field {next_rent_plus_one} to auto-calculate to 20/05/2017?
Think it can be done using formula fields and a couple fo tasks - just testing now ...
As an alternative (suitable if the result isn't needed instantly), you could use an Integromat Scenario triggered by the new record which then calculates and updates the record with the new value:
This gets the calculation correct every time - but Scenarios don't run instantly (but can be every minute).
I often use Integromat or Zapier to automate things with Knack and other databases - but in this case chose Integromat because Zapier can't update a Knack record (but Zaps DO run instantly).
Bizarrely, it is possible to use a Zap to trigger Integromat which would make Integromat effectively run instantly for Knack - but on this occasion I'm really just trying to show a basic option.
This is relatively straightforward in Javascript - the following code was all adapted from examples in the Knack developer documentation and adds 6 months to one date field (field_2) and stores it in another (field_3):
// this loads datejs...
LazyLoad.js(['https://cdnjs.cloudflare.com/ajax/libs/datejs/1.0/date.min.js'], function () {
//alert('All my files have completed loading!');
});
// Listen for a record being created on view_3
$(document).on('knack-record-create.view_3', function(event, view, record) {
// Set up PUT request to update the record
// Note that we use the URL for a separate edit form which has an input for the ID field
var url = 'https://api.knack.com/v1/pages/scene_3/views/view_4/records/' + record.id;
var headers = {
'X-Knack-Application-ID': Knack.application_id,
'X-Knack-REST-API-Key': 'knack',
'content-type': 'application/json'
};
// Set our short text field's value to the newly created record's Knack ID
var startDate = new Date(record.field_2.substr(3,3) + record.field_2.substr(0,3) + record.field_2.substr(6,4));
console.log(startDate);
var newDate = startDate.add(6).month();
var data = { field_3: newDate };
Knack.showSpinner();
// Make the AJAX call
$.ajax({
url: url,
type: 'PUT',
headers: headers,
data: JSON.stringify(data),
}).done(function(responseData) {
console.log('Record updated!');
Knack.hideSpinner();
});
});
This isn't perfect - must be an issue with datejs - but I have't tested it that thoroughly.
The question is why don't Knack implement this as an option in date equations?
Adrian - don't be shy, what platform are you using to achieve this?
Yes, I got same response which is why I had no option but to leave.
The solution I'm using now has monthly date calculations, triggers and workflows.
I'm also amused that knack bills its customers every month so it's clearly possible!
Just got confirmation from Knack support that it's apparently "too hard" to implement. Never mind the fact that Excel has been able to do it for years...
Unfortunately it's not possible with Knack. I tried to work out a formula with set of workflows (by comparing day of the month) but it got too complex. Leap years got really complex! Support were good but in the end conceded it was not possible
In the end I had to move to another app which I won't mention here but as it turns out is fantastic and a single trigger running every midnight automatically does the whole monthly calculation and works out when monthly rents are due.
Adrian, did you ever get an answer from Knack on this? I thought maybe I was missing something when I needed to do this same thing today...
Is this not possible to accomplish? The options in the date calculation are days, weeks or years. If there was a month option, this would be easy I suppose but surely this must be possible somehow? I'm just not sure how to accomplish it.
This is possible with native Knack features using a workaround that is not obvious but works. See Solution for adding months to a date (native Knack features)