Skip to main content
Solved

Formula to show the date on which a certain threshold was crossed

S

Hey there,

Thank you for your time helping me !

So i'm a mushroom farmer. I cultivate different batchs simultaneously. Each time I harvest one, I create a record for it, with the date of harvest, the batch harvested and the weight harvested. There is a tab dedicated to it.

I also have another tab which contains more infos about each batch, and also a sum rollup of the harvests of each batch.

My main metric to know if a batch is good is the yield, I compare the total weight of the batch to the amount harvested. For example, I harvested 10 times a batch, the total is 300kg of fresh mushrooms on a batch of 1000kg of mushroom substrate, hence 30% yield.

But I'd like more complex infos, for instance I'd like to know on which specific date of harvest I crossed the 10% yield mark, the 20% ... So i could know for each batch after how many days I crossed the 10%/20% mark

I've tried some combos of rollup and formulas but nothing works so much, any ideas ? 

Best answer by Milan_Automable

Hi @SpaceDandy ,

I love your unique use case ‍🟫; there are so many different uses for Airtable 🙂

Rollups and formulas are not aware of when a field was modified, so you need automation to accomplish this.

1) Create a Yes/No formula each threshold you're interested in, here is an example with 20%:

2) Create an automation that watches if this Yes/No field changes, and sets the Date field to the current date:

This is it in action (the automations take a few seconds to run). Here I'm editing the Yield directly, but it will work the same way if it was a rollup or formula result.

I'd love to explore more use-cases of Airtable and automation specific to your business, if you're interested feel free to book a free call for any time that works for you 🙂

-- Best, Milan - Automable.AI

View original
Did this topic help you find an answer to your question?

7 replies

Milan_Automable
Forum|alt.badge.img+3
  • Milan_Automable
  • Participating Frequently
  • 43 replies
  • Answer
  • February 19, 2025

Hi @SpaceDandy ,

I love your unique use case ‍🟫; there are so many different uses for Airtable 🙂

Rollups and formulas are not aware of when a field was modified, so you need automation to accomplish this.

1) Create a Yes/No formula each threshold you're interested in, here is an example with 20%:

2) Create an automation that watches if this Yes/No field changes, and sets the Date field to the current date:

This is it in action (the automations take a few seconds to run). Here I'm editing the Yield directly, but it will work the same way if it was a rollup or formula result.

I'd love to explore more use-cases of Airtable and automation specific to your business, if you're interested feel free to book a free call for any time that works for you 🙂

-- Best, Milan - Automable.AI

S
1 person likes this
  • S
    SpaceDandy
Milan_Automable
Forum|alt.badge.img+3
  • Milan_Automable
  • Participating Frequently
  • 43 replies
  • February 19, 2025

And here is the base link; you can duplicate it to take a look at how it works!

S
1 person likes this
  • S
    SpaceDandy
Mike_AutomaticN
Forum|alt.badge.img+8

Hey @SpaceDandy!

Do you harvest only once per batch, or do you harvest multiple times for one same batch?

Assuming the latter, I'd like to make a small clarification! You'll probably have the following architecture (tables):
Mushrooms
Batches
Harvests

If so, you'll want to have a the rollup described by @Milan_Automable at the batches level for getting the total harvested of the batch. If needed, you can find more information on Rollups here.
You'll then want to lookup such total value on the harvest level, to be able to have the formula to calculate the % of each harvest. If needed, you can find more on Lookups here.

Just in case 😄

Mike, Consultant @ Automatic Nation

S
1 person likes this
  • S
    SpaceDandy
S
  • SpaceDandy
  • Author
  • Known Participant
  • 11 replies
  • February 20, 2025

Hey guys,

Thanks a lot ! @Milan_Automable solution seems fine, I'll try to implement it on monday but it seems to do the job, I didn't thought about this kind of automation 🙂

 

@Mike_AutomaticNI do harvest multiple times each batch yep. I already have a sum rollup on the batches tab indeed. I was just lacking the date of thresholds 🙂

 

I'll keep you posted, thanks again !

S
  • SpaceDandy
  • Author
  • Known Participant
  • 11 replies
  • February 20, 2025

Actually I just realised that it won't work retroactively and I'd love that. Do you think of something ?

Milan_Automable
Forum|alt.badge.img+3
  • Milan_Automable
  • Participating Frequently
  • 43 replies
  • February 21, 2025

Yeah, this would only work for Batches that pass the thresholds later on.

You have a Harvests table with dates, right? Then it should be possible, but a bit more complicated than the original solution.

Something you can do would go along these lines:

1) Add a Lookup into Harvest to get the batch weight; so you can make a formula to see what percentage this particular harvest adds to the Batch yield.
2) You can set up a "Cumulative Yield" field to see what is the total yield up until that time. This would require a helper table and an automation; the output would look like this.
3) Then you can make a Rollup formula with a filter to see what is the minimum date among those harvests by batch where the cumulative yield is greater or equal to the threshold.

Alternatively, the logic can be encapsulated in a script that runs on an interval and updates all relevant records.

-- Best, Milan - Automable.AI Free Airtable consultation

S
1 person likes this
  • S
    SpaceDandy
S
  • SpaceDandy
  • Author
  • Known Participant
  • 11 replies
  • February 25, 2025
Milan_Automable wrote:

Yeah, this would only work for Batches that pass the thresholds later on.

You have a Harvests table with dates, right? Then it should be possible, but a bit more complicated than the original solution.

Something you can do would go along these lines:

1) Add a Lookup into Harvest to get the batch weight; so you can make a formula to see what percentage this particular harvest adds to the Batch yield.
2) You can set up a "Cumulative Yield" field to see what is the total yield up until that time. This would require a helper table and an automation; the output would look like this.
3) Then you can make a Rollup formula with a filter to see what is the minimum date among those harvests by batch where the cumulative yield is greater or equal to the threshold.

Alternatively, the logic can be encapsulated in a script that runs on an interval and updates all relevant records.

-- Best, Milan - Automable.AI Free Airtable consultation


Ok I understand but it seems too much for the amount of data, I dit it manually in the end 🙂

Your first solution is working as intended, that's perfect, thanks a lot !

Reply


Most helpful members this week

Terms & Conditions