Date for new row from last of other column

1

So I have 2 columns.
End date and Start date. I am trying to make it that Start date will have the “value for new row” done by taking the date from the last row of End date and add it a day (+1).
Both columns are Date type.
I tryed several formulas such as Last(), totext() and ect. but always came up with the same result, it automatically fill it with the date of 1/1/1970.
Any ideas what is it that im doing worng?
Captura date

Thanks for the help!

2

Hi Asaf,

Working with the “previous row” is not a simple concept in Coda.

I unfortunately do not have time to give you a specific example, but I explain why it is not simple, and also an example that explains the concept in thi sdoc:

Regards
Piet

1 Like
3

in case the support of @Piet_Strydom is not enough, you can have a look here:

1 Like
4

Hey guys.
First of all thank you for your help!!!
So I had a look and tried to both ways yet for some reason I totally failed.

Piet, I tried the button without modify the row but with adding new one. (Modify the row will make it a bit more tricky for me)

@Christiaan_Huizer,

I got a bit lost in the explanation and haven´t managed to get the same results.

So the table is full of those rows. The moment I mark the checkbox it is being filtered out of the table(which I think affects a bit the sort() and Index().
The column on the left is the one to determine whether it will be 7 or 21 days (thats why I made it a Relation column to a specific table of those 2 options).
Captura coda
So in the end if i can´t find a formula that automatically fills the “start date” with the date of “End date” +1 day ill just stick to the manually way since its also pretty easy.

Thank you for your time and help!!
Asaf

5

hi @Asaf_Chip

maybe this blog is more helpful.

Cheers, Christiaan

6

Set the default value for new rows (for Start Date) to be:

[End Date]
.Filter(CurrentValue.IsNotBlank())
.Sort(True)
.Last() + Days(1)

This is first taking ALL end dates, then filtering out the blanks, then sorting the data in ascending order, then using the last date + 1 day.
This is NOT necessarily using the previous row, it’s using the last known end date.

1 Like
7

Guys first of all THANKS!!
So combining all of your answers and seeing that I still cant make it work, I came up with a conclusion that maybe there are 2 things that might make it a mess:

  1. The fact the first column in the table is a relation column and has a specific time duration for it.
  2. Though its clear for us how the table repeat it self (the rows at least) I think that transmiting it to Coda is working so well for the reason that its hard enough to work with cells in Coda.

Those are just assumptions but it was like the only explanation for me why i couldn´t quite get the result i wished for.

Thank you all for your help, I am going to leave it for now. Though if anybody still have any idea I´ll be more than happy to hear. (If in the future I´ll find the answer by some mistake, I´ll make sure to let you guys know)
Thanks!

8

hi @Asaf_Chip , you tried and did your best, that matters to me
please share a sample doc with in it two tables

  • one that contains the puzzle
  • one that contains the puzzle solved (hard coded answer for a few rows) so I understand the desired outcome

once I see this I’ll give it a try, Cheers, Christiaan

9

Hey @Christiaan_Huizer Sure thing!
So here is the table.
The columns that interest us our mainly the “Start Date” and “End date”
The idea is that the moment to press “new row” it will have the new date already in, which will be the last date of “End date” +1 day (since its a treatment that start the next day).
The final result I hoped to achieve is that, with pressing new row and choosing the type of row/treatment, The row will be with all the info it needed. (Trying to get the most with the least of actions)

10

hi @Asaf_Chip ,

I created a button with this function:

AddRow(
  [Tandas de Pastillas],
  [Tandas de Pastillas].[Start date],
  thisTable.[End date].Last() + 1,
  [Tandas de Pastillas].Mes,
  If(
    thisTable.Mes.Last().Contains(Anticonceptiva.Mes),
    Descanso.Mes,
    Anticonceptiva.Mes
  )
)

It seems to do what you want, but I don’t understand the logic behind your table. An if statement like this only works with two variables, for 3 and more you need a different (better) logic.

Anyway, I hope it helps , Cheers, Christiaan

11

Hey @Christiaan_Huizer !
Luckly this table is only will be with this 2 variables.
Also i have to admit that its not the full table. The thing is that its a table that later on open another table ever date (anticonceptiva) and like this on.
I saw what you did and it works GREAT! though I did a slight change and instead of a button for every row I just made a general one outside the table, which also work perfect!!!
But for some reason when i copied it to the main document it cant find the last condidion:
“If(
thisTable.Mes.Last().Contains(Anticonceptiva.Mes),
Descanso.Mes,
Anticonceptiva.Mes
)”
I will investigate more in to it!
THANK YOU SO MUCH!!!

12

hi @Asaf_Chip ,
I referenced the value using @ and then I typed the value, when you do this in your main doc, it works.

Cheers, Christiaan

13

@Christiaan_Huizer AWESOME! thanks!!! i actually didnt know it is a thing so thanks for teaching me a bunch of new stuff!!!
Thanks for all the help!!!

14

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.