How to autofill a value with corresponding value in another field?

[microlocalfarm]

I am trying to create a table for my microgreen growing log. I would like to have a function that when I create a new record and fill in the crop name of that record, the corresponding harvest days will be autofilled in another field. But I don't want to use formula as the field type of harvest days as I might have to adjust the harvest days in some situation.

[TheTimeSavingCo]

Where are each crop's corresponding harvest days stored?  Could you share some screenshots of your tables and the relevant fields please?

[microlocalfarm]

This one is my planting record table

This table is where my harvest day stored

The first photo is the planting record table where I will mark down the info for every tray of microgreen that I sowed, e.g. sowing date, type of crop. The second photo is the microgreen data table and the field "sow to harvest day" is the number of days that I have to harvest that crop aftering sowing. So now I need to have a way that whenever I create a new record on the planting record table, the harvest date will be calculated by adding the "sow to harvest day" from the microgreen data table to the sowing date of that new record, but I don't want to use formula field type for the "harvest date" field as I might have to change the date if the microgreen is not growing well as expected.

[TheTimeSavingCo · Accepted solution]

Ah I see now.  I think if I were you I'd create a formula field for the harvest date called "Calculated harvest date" to get the correct date, and then have an automation that triggers when "Calculated harvest date" is not empty and "Harvest Date" is empty, and its action would be to paste the value of "Calculated harvest date" into "Harvest Date", does that make sense?

[microlocalfarm]

That's indeed a good way to implement my idea. Thanks!! 🙂