Sequence formula add one row per month

1

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

2

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.

3

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

1 Like
4

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

1 Like
5

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
6

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
7

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
8

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