Subscription space template: Formula missing zero padding?

Bugs & Issues
1

Just tried installing the Subscription template yesterday. Didn’t do anything else with it. An automation was executed this morning with the following error.

It seems the month (and perhaps day?) needs som zero padding?

image
image1293×251 16.6 KB

2

It’s actually not a zero-padding issue, but rather the automation is attempting to set the month to -1
We’ll look into it and update it (and post the fix here)

1 Like
3

Ah yes, I see it now that I looked more clearly :smiley: Thanks!

4

You’ll need to update this automation rule:
image

The update action has an error in the formula.

image
image1352×287 5.85 KB

The correct formula is this:

Date(
  Year([Step 1 Subscription].[Next billing date]) +
    RoundDown(Month([Step 1 Subscription].[Next billing date]) / 12, 0),
  Month([Step 1 Subscription].[Next billing date]) +
    1 -
    12 *
      RoundDown(Month([Step 1 Subscription].[Next billing date]) / 12, 0),
  Least(
    Day(
      Date(
        Year([Step 1 Subscription].[Next billing date]) +
          RoundDown(
            Month([Step 1 Subscription].[Next billing date]) / 12,
            0
          ),
        Month([Step 1 Subscription].[Next billing date]) +
          2 -
          12 *
            RoundDown(
              (Month([Step 1 Subscription].[Next billing date]) + 1) /
                12,
              0
            ),
        1
      ) - Days(1)
    ),
    Day([Step 1 Subscription].[Next billing date])
  )
)
5

And you will probably want to run the formula, to take care of the previous failed automation:
image

6

Thanks, works correctly now!

7

Would it be possible to add an automation for Quarterly updates as well? We have a few subscriptions that are billed on a quarterly basis.

8

Have a look at this thread:

I hope you’ll be able to figure out from the current automations in the template, together with the code snippet in that topic how to do it.

9

I was already on my way to simplify the setup by introducing an Occurrence field (number of times, 1-12, in a year) and this suggestion thread was exactly what I needed. Thanks!

Final working formula:

Date(
  Year([Step 1 Subscription].[Next billing date]) +
    RoundDown(
      (Month([Step 1 Subscription].[Next billing date]) +
        [Step 1 Subscription].Occurrence -
        1) /
        12,
      0
    ),
  Month([Step 1 Subscription].[Next billing date]) +
    [Step 1 Subscription].Occurrence -
    12 *
      RoundDown(
        (Month([Step 1 Subscription].[Next billing date]) +
          [Step 1 Subscription].Occurrence -
          1) /
          12,
        0
      ),
  Least(
    Day(
      Date(
        Year([Step 1 Subscription].[Next billing date]) +
          RoundDown(
            (Month([Step 1 Subscription].[Next billing date]) +
              [Step 1 Subscription].Occurrence -
              1) /
              12,
            0
          ),
        Month([Step 1 Subscription].[Next billing date]) +
          [Step 1 Subscription].Occurrence +
          1 -
          12 *
            RoundDown(
              (Month([Step 1 Subscription].[Next billing date]) +
                [Step 1 Subscription].Occurrence) /
                12,
              0
            ),
        1
      ) - Days(1)
    ),
    Day([Step 1 Subscription].[Next billing date])
  )
)

I now have one automation instead of two which is perfect for my needs.

1 Like
10

Revisited this with a much simpler to read (and understand) script version. Leaving it here for anyone interested.

// Developer reference is at api.fibery.io/#action-buttons
const fibery = context.getService('fibery');

for (const entity of args.currentEntities) {
    // Get current date
    const currentDate = new Date(entity['Next billing date']);

    // How many months per year does this happen?
    const occurrence = entity['Occurrence'];

    // Calculate next date
    const nextDate = new Date(currentDate.setMonth(currentDate.getMonth() + 12 / occurrence));

    // Update next billing date
    await fibery.updateEntity(entity.type, entity.id, {
        'Next billing date': nextDate.toISOString()
    });
}
2 Likes