Find records that share a common field?

[oreocereus]

I have a series of automations with various linked fields, but there is one user action I am struggling to create. 

For some minimal context, this is for recording and calculating mineral amendments to garden beds on a small market garden.

In a particular use-case, I end up with multiple records for each bed. 

When this happens, I need to be able to consolidate the records that share a common field (bed) into a single record. So in this case, the 6 records need to become 2 records (GH3 & Gh4) that have multiple "amendments" linked.

Can anyone advise how to go about this?

[TheTimeSavingCo]

Hmm, so those two new records get created in this table, and then the 6 records will be deleted?

[oreocereus]

That's right, that's what I'm trying to do. 

I guess I can probably do it with another linked base, but there feels like there should be a way to do this within the automations. I just can't seem to figure out a way to "find records that share bed & and Created Time"

I can handle the deletion. I usually tag these things as archive and they get deleted via a script after x time, or manually.

[TheTimeSavingCo]

Ah, without the created time complication this should be doable with automations alone.  With the created time thing I think you're going to need a script I'm afraid

[oreocereus]

Doesn't need to be "created time", but some field that helps ID that the "batch" of records were generated by the automation. I could probably use a checkbox that is ticked at the start of the run, and unchecked at the end of the run. 

But I'm struggling with the first step of "find records that share bed"

[oreocereus]

Bumping this as I haven't puzzled my way around it yet.