Skip to main content

I feel like I’m missing something pretty easy. I’m having a hard time converting what I do in Excel into our Airtable base. Each year our clients' total sales (a rollup of their sales for a given year) converts to shares based on a sales/shares tier. The tier changes every year. Because of this, we want to track the data historically.

 

For example:

Client ABC’s 2024 Total Sales were $250,000 and they earned 4 shares based on the 2024 shares tier.

Client ABC’s 2025 Total Sales were again $250,000, but this year they earned 2 shares because the tiers changed in 2025.

 

We need to track the tier changes each year as to keep data integrity of the shares earned each year by our clients. (We don’t want ABC’s 2024 points to change from 4 to 2, which it would if we use a nested if formula - the change in criteria for 2025 would mean an edit to the formula which would have a cascade affect and change all previous year's data.)

 

ANNUAL CLIENT TABLE - rolls up sales totals for each client for all their supplier sales for the given year. This is where the shares value should be calculated based on the Rollup Sales field when looked up in the Share Conversion Table - how do I do this??

Annual Client ID LINK Client Shell LINK Year LINK Client Sales ROLLUP Ttl Sales Shares Last Year Sales
ABC-2024 ABC 2024 2024-ABC-Holland America, 2024-ABC-Princess Cruise Lines, etc $250,000

4

 $12,727
ABC-2025 ABC 2025 2025-ABC-Princess Cruise Lines, 2025-ABC-Viking River Cruises, etc $250,000

2

 $250,000

 

SHARE CONVERSION TABLE

Share Conversion Table

Any help would be much appreciated.

 

Please let me know if I didn’t provide enough detail.

 

As an added question...how do I auto populate a client’s Rollup Sales from the previous year, into the agency’s current year record? For example, in the Annual Client Table, how do I get ABCj’s 2024 Rollup Sales into the “Last Years Sales” field in their 2025 record? We would like to report back to our clients their YOY sales growth. I don’t want to build columns for all the sales years in any of the tables (which wouldn’t work well for many reasons)

 

Thanks again,

Sue

 

 

Hm I think we have to use an automation for this I’m afraid

I’ve set it up here for you to check out and this is how it looks running:

And the idea is to use a Find Records step that matches the year and has the Sales figure within the Min and Max:



As an added question...how do I auto populate a client’s Rollup Sales from the previous year, into the agency’s current year record? For example, in the Annual Client Table, how do I get ABCj’s 2024 Rollup Sales into the “Last Years Sales” field in their 2025 record? We would like to report back to our clients their YOY sales growth. I don’t want to build columns for all the sales years in any of the tables (which wouldn’t work well for many reasons)

Hmm, what if there was an automation that would trigger when a record has a Year and a Client linked, and its action would be to find all the sales records in the previous year and link them up?


You’ll need to create helper fields for your year values to convert them to numbers, but once that’s done you’ll be able to use them in your Find Record automation.  I’ve set up those fields in the base above so you can refer to it!

Annual Client: Previous Year

Sales: Year as text

AWESOME! The lookup for the shares conversion works great! I get a little lost with the automations, so having your examples really helped me understand. Thank you so much for taking the time!

 

As far as populating last year's sales into this year’s client record - I think that would work great. My only hesitancy is I think if we had to update last year's sales (which happens), it wouldn’t automatically update the associated field in this year’s record...is that correct? That still works for us as right now it’s a manual process anyway (hand entering last year’s sales into this year’s record).

 

Thanks again!

Terms & ConditionsAccessibility statement