Help

The Community will be undergoing maintenance from Friday February 21 - Friday, February 28 and will be "read only" during this time. To learn more, check out our Announcements blog post.

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

Topic Labels: Data Formulas
233 6
cancel
Showing results for 
Search instead for 
Did you mean: 
SpaceDandy
6 - Interface Innovator
6 - Interface Innovator

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 ? 

6 Replies 6
Milan_Automable
6 - Interface Innovator
6 - Interface Innovator

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%:

MushroomYield1.gif

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

MushroomYield2.gif

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.

MushroomYield3.gif

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

Milan_Automable
6 - Interface Innovator
6 - Interface Innovator

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

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

SpaceDandy
6 - Interface Innovator
6 - Interface Innovator

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 !

SpaceDandy
6 - Interface Innovator
6 - Interface Innovator

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

Milan_Automable
6 - Interface Innovator
6 - Interface Innovator

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