Helpful Formula

Showcase Tips and Hacks
1

:wave: Hey Community!

Im currently building a Strava pack and came across a problem I wanted to solve for which I developed a new formula I’ve never used before and thought I’d share it with the community! (If someone already shared a similar formula let me know, just couldn’t find one)

What the formula does generally:
Allows you to run loops of actions X number of times with each iteration having different specific and general inputs

Stated another way, it allows you to increment inputs at different rates in loops

That probably made no sense. It sure didn’t to me. Let me explain with my use case.

My use Case
In my Strava pack (and Strava Doc Template) I want a user to instantly generate a table with 12 rows per year for every year they have been using strava. This will be different per user as people begin using Strava at different times.

Therefore if a user has been on Strava since 2017 and todays year is 2022, the button will need to generate 12 rows (numbered 1-12) per year. Each row will have to be numbered 1-12 sequentially, but the first time the loop runs it should number each year as 2017.

The second time the loop runs it should once again run 12 times (numbering rows 1-12 sequentially) and then number each year as 2018.

Heres the formula I used inside the button:

WithName(
  Sequence([First Strava Activity].Year(), ToDay().Year()),
  allYears,
  Sequence(1, allYears.Count())
    .FormulaMap(
      WithName(
        CurrentValue,
        currentNth,
        Sequence(1, 12)
          .FormulaMap(
            WithName(
              CurrentValue,
              currentMonthNumber,
              AddRow(
                [DB Months],
                [DB Months].[Month Number],
                currentMonthNumber,
                [DB Months].Year,
                allYears.Nth(currentNth)
              )
            )
          )
      )
    )
)

Heres an example doc where you can see it in action:

8 Likes
2

Hi @Scott_Collier-Weir , thanks so much for sharing!

I wouldn’t have thought to simply set the date type as a formula, that’s much more efficient than what I would have done (which would have been to set up a master table containing information of month number and month name, and then pulling each row’s date from that master table).

Here’s a thought I have:
Let’s say, for your Strava users, you want to assign a level of expertise to them. So let’s say that in the first year of them using the service, they are “Beginner”, in the second year they are “Intermediate”, and thereafter they are “Expert”.

How would you incorporate that into the FormulaMap formula? That is, for the first sequence 1-12 rows, change the text in the new-column “Skill” to “Beginner”, for the next sequence 13-24 change the text to “Intermediate”, and thereafter to “Expert”?

I tried to do it, but wasn’t able to integrate the Switchif-formula into the formulamap-formula.

Would you happen to have any insights into this?

Thanks,
Nina