Automation flow adding new rows per unique value

I am working on an database set-up where I want to store both historical and future data on a quarterly basis (e.g. Q1 2020, Q2 2020, Q1 2021 etc.) for different European cities (e.g. London, Madrid, Berlin, Prague etc.).

What I want to do is build an automation flow that will automatically add a new row per city when a new quarter has started. For instance, on the 1st of January 2023 add a new row Q1 2023 for each city. I do not want to convert my cities into columns as I have quite some metrics I want to add per city and therefore need the cities to be on the rows.

I am relatively new in building these automation flows and thus far can only see the option to add this new row but only one at the end of the sheet and not per 'unique' location. So in the screenshot below add a new line per city for Q4 2022 and then another new one for Q1 2023 when a certain date (01 Jan 2023) is met.

Hope the above makes sense and thank you in advance,

Bram


Answers

  • Paul McGuinness
    Paul McGuinness ✭✭✭✭✭✭

    HI @BramdR

    Best way to accomplish this is to have a separate sheet with 1 row for each area name. you can then have this operate as a live sheet with an active date so it effectively ticks along.

    You can have it automatically copy row(s) to each of your country sheets.

    You can write some formulas to calculate Quarters based on start dates then and use the copy row automation to move them every three months.

    Once completed the system would theoretically copy a row over for you every quarter without end.

    Hope that helps

    Thanks

    Paul