Autogenerate records based on multiple variables

[oreocereus]

I have a slightly complicated project. I use airtable for our farm records. I want to use the information in a planning table to partially autogenerate an annual crop plan. 

We grow 40+ varieties a year. I have created a table that is an overview of all the variables I factor when making crop planning decisions. In essence different crops have different seed sowing frequencies. E.g. in a year I might do:
-1 sowings of pumpkins
-3 sowings of field tomatoes (4 weeks apart)
-26 sowings of lettuce (2 weeks apart)

I know this information based on:
1) Expected yield per sowing
2) The harvestable period of a crop (e.g. pumpkins are a single harvest, field tomatoes we harvest from for 4-8 weeks)
3) Target harvest frequency (e.g. I might only want to harvest carrots every 4 weeks - I don't need to sow it so that I have a harvest every week on it).
4) The season I can grow it - i.e. first and last sowing dates

So I have all the information I use to make decisions against records of each crop. 

I now want to use those records to partially generate unique sowing plan records - the only info I need to manually input will be choosing locations and potentially adjusting the plan based on more complex variables that can't be simplified into a spreadsheet. 

So for example, we know:
1) Bush Tomatoes - we can first sow on Sept 15, we can harvest each sowing for 4 weeks and we want to be harvesting bush tomatoes every week of their season - therefore we need 3x sowings of bush tomatoes spaced 4 weeks apart.
2) Lettuce - we want to harvest lettuce weekly, and one sowing can be harvested over a 2 week period, therefore we need to sow lettuce every week of the year, every 2 weeks.

Is there a way in airtable to generate unique sowing planning records for this? So it would automatically create a records that are "Tomato - sow 15 sept" "Tomato - sow 15 october" "Tomato - sow 15 november"?

All the info is there, I just don't know how to leverage the power of airtable to do it!


(If anyone is a gardener, you'll know that dates to maturity shift through the year, I'm just trying to keep the examples relatively easy to understand

[oreocereus]

Heck, even if I could simply autogenerate records for x dates based on the "frequency" value would simplify things a lot!

[Stephen_Orr1]

Hi @oreocereus,

A junction table approach would be absolutely perfect for this. It works like this:

You would create three tables:
Seasons (unique) <-linked-> Sowing Plan (junction) <-linked-> Crops (unique)

The two linked record fields in Sowing Plan to the other two tables would be configured to not allow multiple records to be linked. So, every record in Sowing Plan is a cross reference between Season and Crop. This allows you to have different data per "season crop" such as sow dates or field location stored in Sowing Plan while also maintaining a unique list of crops in another table (Crops) with details that will always be the same for each crop.

The Seasons table is important because it lets you distinguish one sowing plan from another, and maybe you want to store details about each season that could impact your sowing plan (or be used in an automation to generate a sowing plan!). In your Sowing Plan table, group by the Season linked record field.

Please let me know if you have any questions!
-Stephen

[oreocereus]

Hi Stephen, thanks for your reply. 

In this case, you're using the "season" table in a way that is something like "2023/24 plan" "24/25 plan" right?

I think you *might* have missed the thrust of the question. I'm trying to find a way to use the data stored against crops to generate a sowing plan. 

E.g. if I want to sow carrots every 2 weeks, starting from October 1 and ending July 1, I should be able to put a "first sowing" date, and a "final sowing" date value stored against carrots, and I should have a entry for "sowing frequency" (in this case 2 weeks) stored against carrots. I am trying to find a way to automatically create a set of records in the Sowing Plan table which will be something like unique records: "Carrots 1 October" "Carrots 14 October" "Carrots 28 October" - to which I would manually assign location.

So instead of needing to create all those records manually, they are partially automatically generated and I manually input the part that needs the more intelligent human brain 🙂

[Stephen_Orr1]

Makes sense. Do you recreate your sowing plans every year? Season is a way to distinguish one sowing plan that contains the same crops from another and store any info about a particular time period that spans crops (maybe profit/loss or field rotation or other consideration). You could also have season just be a drop-down that you group by in the Sowing Plan table or not have it at all and rely on dates to distinguish sowing plans. 

As for crop start and end dates and frequency, these go in the sowing plan table. To calendar them, you could have a formula field generate the “next sow date” based on start, end, and frequency fields and then calendar only this next sow date. If you prefer to calendar everything ahead of time, you’ll need one record per actual sow date per crop and an Airtable script or third party service to generate them from start, end, and frequency inputs. No-code automations in Airtable won’t be able to do this. This could be activated by a button in the Crops table that reads temporary field values in the Crops table and generates records in the Sowing Plan table accordingly + clears the temporary field values.

I write Airtable scripts exactly like this very frequently and if you’re interested in hiring this out, please let me know!

-Stephen

[oreocereus]

Gotcha. So a script is needed to do what I need, it seems. Yep, needs to be an individual record for each crop + sowing date, in order to plan things ahead of time (order of crops through a bed is important). 

Probably don't have the capacity to learn right now, which is a shame, as I had hoped when we started playing with airtable this would be something I could use it for. 


And unfortunately we are a charity with no budget at the moment Stephen, so no capacity for hiring!