Creating "Balance" column via formula from "Notifications" column

[Minette_Yu]

Hi there!

I have a slight feeling I might need to use scripting for this but I am NOT a javascript trained human.

I am trying to take the sum of the $ amounts in “January Birthday Notifications” and place it in the “January Balance” field. The trick here is… I need to do this for each month so efficiency is really important to me, haha. Any help would be greatly appreciated! Thanks so much!

[Minette_Yu]

For Clarity, January Gift Notifications formula is a CONCATENATE of all roll-up fields of important dates associated with the month of January (Work Anniversary, Birthday, Appreciation gifts, etc.)

The goal here is to send an email with the month’s notifications AND total balance.

[Kamille_Parks]

Minette_Yu:

For Clarity, January Gift Notifications formula is a CONCATENATE of all roll-up fields of important dates associated with the month of January (Work Anniversary, Birthday, Appreciation gifts, etc.)

Then shouldn’t you just use another Rollup field with the SUM(values) aggregation for whatever field the dollars are pulling from?

[Minette_Yu]

I was thinking about doing this… But it’s soooo much manual work and sooo many columns (I know I am going to hide most of them, but it still hurts), haha

[Kamille_Parks]

Minette_Yu:

sooo many columns

Why? I’m envisioning a single “Amount” column. Without more information on the table your rollups are pulling from I can’t tell if that table is terribly inefficient or you have a genuinely unique use case.

[Minette_Yu]

It could be either, honestly. Part of the limitation is that I can only roll-up one column at a time; I can’t roll up for multiple events (i.e. Birthday, work anniversaries, etc.) all at once. Here’s an overview of what I have set up and what I’m trying to accomplish:

Step One: Gift buyer fills out a form with details on what events they’d like to gift for and what their budget it. This generates a prefilled link to a form on the “recipient” table for them to send to their team or clients (the people they are giving gifts to)

Step Two: Recipient fills out the form with their Birthday and work anniversary and this automatically connect them to their gift buyer table so that I can create roll-ups.

Here is one of the field formulas that pull over on the roll-up I mention in the next step:

Step Three: I need to send the buyer an overview of their gifts for the month (birthdays, anniversaries, recurring appreciation gifts, etc.).

• I created a roll-up for each gifting scenario organized by month, then created the formula to bring all of those roll-ups into one monthly notification field so that I could list all gifts for each month in their notification email.

Here are some of the million roll-ups I already have (they are conditional roll-ups so I could organize by month):

Here is my Notifications formula:

• Now, I need to calculate the total balance of each month to also input into the monthly notification email, which is where I am stuck.

[Minette_Yu]

It’s also worth noting that I initially wanted to put these notifications on a separate table organized with a row for each month but that doesn’t appear to be possible since we can’t update records on a separate table in automation.

This is what I originally envisioned/wished for:

[Kamille_Parks]

Minette_Yu:

we can’t update records on a separate table in automation

Yes you can, if you know which records need to be updated. I.e. if your records are linked together properly.