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?
Alternatively
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?
