Hi All
Can this be done…
I have a date column with a date 01/01/2019 , when I add a new row I would like the next date in that column to be in 3 months time i.e 01/03/2019
i’ve tried: thisRow.count()-1.count()+3
Not even close.
Hi, there’s no support in the formula language to locate the relative row values. While you could potentially use the RowId formula with a filter, there’s no guarantee the rows are going to be in the correct order due to sorting, different creation times, etc. Instead, I’d recommend you use a default value formula on the column. Your formula would be: DatetimeTruncate(thisTable.Date.Max() + 3 * 31, 'month').
Breaking this down:
- Find the largest date in the current column:
thisTable.Date.Max() - Add 3 months to it -
RelativeDate(thisTable.Date.Max(), 3)
An alternate approach is to create a formula on your date column and use thisRow.RowId(). Since RowId returns the integer number of the row representing the creation order (1, 2, … n) you could create a formula:
RelativeDate(Date(2019, 1, 1), 3 * (thisRow.RowId() - 1)).
The one thing to be careful of in using RowId is that the numbers are never recycled so if you delete a row value there’ll be a hole left in the sequence.
If I were doing this, I’d take the default value approach. See https://coda.io/formulas#RelativeDate for more help on other handy formulas.
Nigel.
Thank you for your reply. I tried
I created a date/formula outside of my table and called it [start date] i then used the following code that you posted:
RelativeDate(thisTable.Date.Max(), 3)
Although it adds 3 months from the start date, it doesn’t change for each row, meaning each row is the same?
If you want to have a canvas control that sets the initial startdate value (when there are no rows), then you’ll need to adjust the default value formula.
Steps:
- Add a canvas date control named
DefaultStartDate. Set it to some start date - ex: 2011/06/09. - Add a default value formula on your VAT returns date column. Make sure you add this as a default value formula, not a column formula:
If(thisTable.Date.Max(0) = 0, DefaultStartDate, RelativeDate(thisTable.Date.Max(), 3))
Breaking this down:
- See if there are any existing date values in the table:
If(thisTable.Date.Max(0) = 0, <<when no>>, <<when yes>>) - If there are no existing date values, then use the start values:
If(thisTable.Date.Max(0) = 0, DefaultStartDate, <<when yes>>) - If there are existing data values, take the max and add 3 months to it
If(thisTable.Date.Max(0) = 0, DefaultStartDate, RelativeDate(thisTable.Date.Max(), 3))
The trick here is in finding if there are existing values - since default values are calculated after a blank row has been added and then applied using an update, we need to account for the fact that a blank may be present and hence we’ll try to “max” an empty value which will return an error. The use of 0 in thisTable.Date.Max(0) = 0 means we’ll get zero back when no date is present and hence can use that to select the default start date.
Nigel.
Dear Nigel,
As long as there is not support in the formula language, means that 3 * 31, ‘month’ = 93 days is the nearest solution, although like this month having 28 days we will get some difference.
Is this correct?
Thanks for your valuable input,
//JP
@nigelHi the following formula worked but it’s not the ideal way you suggested.
RelativeDate([Start Date], 3 * (thisRow.RowId() - 1))
I cannot get the following code to work:
If(thisTable.Date.Max(0) = 0, DefaultStartDate, RelativeDate(thisTable.Date.Max(), 3))
Currently there is nothing in the date column?
See attached my document. I added a returns 2 table to show it working correctly using your other method.
If this is the case wit the 31 days, seeing as I am wanting the beginning and end of a month, could you use endofmonth as the extra few days wouldn’t matter?
Thanks by the way.
Juan, it looks like you missed the crucial setting of the default value formula on the date column. I added it (see pic below) and it’s working fine.
To the question on adding a multiple of 31 days - you can do this and then use the formula DateTimeTruncate(<date>, 'month') to get the start of the month. The only issue with that is that one 9 or 10 quarters have gone by 10 * 3 ==> 30 you’ll end up adding an extra month. This is why DateRelative is the better solution.
If you wanted to use the end of the month or another intermediate date, just set your start day to another day - DateRelative adds in a multiple of months.
Nigel.
Juan, it looks like you’re trying to add this as a column formula rather than a default value formula. Open the column format menu for date and enter it under “value for new rows”. See https://help.coda.io/faq/editing-docs/how-can-i-set-the-default-value-for-a-column to learn more about default value formulas.
Nigel.
You’re a star, not only does it work, I now understand it. Thank you.
Stay tuned, as it’s now working I can implement the next part.
2 Likes