Sequence formula add one row per month

Hello everyone.
I’ve only been discovering Coda for a few days and I’m already starting to have trouble with the “sequence” function.
I have a Subscription table and a Payments table.

Subscription has a start date and an end date. For each 1st of the Month from the month following the start date, I’d like to create a row in the Payment table with the first date of each month between MONTH(Start + 1) and End.
I’ve created a NextDate field that calculates the first day of the following month: RelativeDate(DateTimeTruncate(Now(), "month"), 1).ToDate()

I created a button with this formula but it doesn’t work. I don’t know how to iterate over 1st day of eah month between NextDate and EndDate at each iteration:

Sequence(thisRow.NextDate.ToDate(), thisRow.[EndDate].ToDate(), Month(1))
  FormulaMap(
    AddRow(
      Payments,
      Payments.Subscriptions, thisRow.Service,
      Payments.[Amount Paid], thisRow.Amount,
      Payments.Date,  CurrentValue.ToDate(),
      Payments.Type,  "Balance",
      Payments.Status,"Forecast"
    )
  )

Thank for you help

Hi @nicopoal,

It would be great if you could share your doc, or a copy of your doc with dummy data, as this would make it much easier to help. There are a few ways you could approach this.

Hi,
Thanks for your ansmer. I made an example for help : https://coda.io/d/_dUZB-XhVaT9/Test-Nico_su2yo
As you can see, I manage to create one line per day between start and end, but I’d like to create only one line per month starting from the month following the current date. each new line must have the 1st day of the month of the iteration as its date.
for example, if start = 05/21/2024 and end = 07/31/2024, I want to create only the lines with date :
06/01/2024
07/01/2024

Hi it’s Ok :smiley:
you can look :wink:

1 Like

wow ! Flash, is it you ? :smiley: at first glance it looks perfect. now I’ll try to figure out how it works :smiley:

1 Like

yes.

first step, I count the number of months. The calculation will change if the end date is more than one year. It will start from the current month, regardless of the days selected.

the sequence button adds a row with a month increment

2 Likes

Just for the sake of showing how powerful Coda is - and how elegant CFL can be, here is another solution (for the date calculations):
image

2 Likes