Find records where linked record no longer matches nested linked record?

[oreocereus]

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.

[patrick521]

---

@oreocereus wrote:

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. AARPMahjongg
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.

---

Hello, @oreocereus 

Can you tell me your query is solve or not if yes please tell me I really need this type of info.

Best Regard,
patrick521

[oreocereus]

Hi, no I haven't found a solution.

[TheTimeSavingCo · Accepted solution]

Assuming you're logging this change in plans via the "Crop Plan" table by unlinking the "Radish", you could try:
1. In "Field", create a lookup field to display the "Crops" field from "Crop Planner"
2. In "Field" Create a formula field that'll check whether the currently linked Crop's name shows in up the lookup of the "Crops" field from "Crop Planner"

You could then use this formula field to trigger an automation that'll delete the record if it doesn't find a link

[oreocereus]

Thanks, I think that will work!