Apologies for the confusing title. To not confuse things further, I'll minimise the story/context. I'm developing a crop planning and record keeping system for our farm.
For this issue, I have 3 tables in question.
1) Crops (basic info about all our crops)
2) Crop Plan (for planning)
3) Field (what actually happens in the field, as plans are never followed 100%).
More than one Crop is added to the crop plan via a linked record. An automation creates a record for each crop with a "planned" status in Field. The planning info and dates are dynamically updated between tables.
So there are two linked records in the Crop Plan table - Crop and Field.
The Field table also have a linked record to Crops. This is automatically filled when the Crop field in the Crop Planner is filled.
So if you add the crop "Beetroot" and "Radish" to the crop plan table to plant on 01/01/24, 1 record is created in the Field table for "Beetroot - 01/01" and another for "Radish - 01/01" - both linking to their respective crop records in the Crops table, and a single record in the Crop Plan table.
The issue I have is that I need to be able to detect changes in these linked fields, and update them accordingly.
For example, if the plans change before planting, and we're no longer sowing Radish, I currently need to find the "Radish 01/01" plan in the Field Table and delete it, as well as removing the "Radish" linked cropped in the Crop Plan table.
I would like to be able to detect these changes, so I can archive or run a script to remove the now irrelevant linked record.