Sum of multiple records in formula field OR default lookup field?

Topic Labels: Formulas
151 1
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer

I am creating a record keeping system for our market garden. Part of what I am trying to automate is a crop plan that generates a sowing calender - e.g. if tell my system I want to transplant 2x 10 metre rows of Cabbage in April, then Airtable will tell me I need to sow 1.5 trays of Cabbage in mid february in the nursery.

I am however having trouble bringing collating data across multiple records. Say that I want cabbage in 4x beds on the same dates in April, each bed may be 0.75 trays of sowing. Because the information is stored against a record which is "Crop + Date + Bed + Row" - there isn't an easy way for me get "oh, I need to sow 3x trays on x date" without manually calculating "4*0.75."

I would like to be able to automatically create a record that is a task for x sowing date that would say "3x beetroot"

Is there a way to sum a column that is grouped by conditions (in this case, date & crop) in a formula field?



Obviously I could have a field that specifies how many transplants per tray, and use a formula to multiply that + transplants column, and a grouping view to sum that info. I actually have a table of our different nursery trays, so I would prefer to use that as a look up field (rather than the user needing to remember how many cells are in our different kinds of trays and manually enter that). However, 90% of our sowing happens in 1 kind of tray. Is there a way to set a default value for a look up field? I.e. when a record is created it automatically chooses "TPS100" tray? So that the user isn't manually inputting that data every time, when the only need to 10% of the time?


1 Reply 1

You cannot set a default value for a lookup field. However, you can set a default value for a rollup field. Note that this is a default value hardcoded in to the rollup formula, not a default value from a specific linked record.

You can see an example in my discussion of rollup fields in my Guide to Airtable.