Remove all but newest related record?

[jbaugher]

I'd like to do some data clean up on a one-to-many relationship. Is there a way to keep the newest related record (e.g. Max: Date Created is safe) and remove all others via the Report or Workflow tools?

[lazovic]

Hi jbaugher,

You can use the following formula in Workflow in Execute Formula Script Action:

$entityType = 'Case'; // replace Case to your entity type (which is One in your relationships)
$entityId = workflow\targetEntity\attribute('id');
$relatedEntitiesLinkName = 'contacts'; // replace contacts to your link (which is Many in your relationships)
$relatedEntitiesCount = 1000; // you can insert here the approximate maximum number of related records
$relatedEntityType = 'Contact'; // replace Contact to your entity type (which is Many in your relationships)

$relatedEntitiesIds = record\findRelatedMany($entityType, $entityId, $relatedEntitiesLinkName, $relatedEntitiesCount, 'createdAt', 'desc');
$relatedEntitiesIds1 = array\removeAt($relatedEntitiesIds, 0);

$i = 0;

while(
$i < array\length($relatedEntitiesIds1),
( record\unrelate($entityType, $entityId, $relatedEntitiesLinkName, array\at($relatedEntitiesIds1, $i));
record\update($relatedEntityType, array\at($relatedEntitiesIds1, $i), 'deleted', 1);
$i = $i + 1;
)
);

You can create a Workflow with your main entity Target Entity and Scheduled Trigger Type so that, for example, every week all records, except for the last created one, are unlinked from the main entity and deleted.

If there is anything you don't understand, please don't hesitate to let me know. Also, I strongly advise you to check this solution first on test records in order to avoid problems and errors.

[jbaugher]

Thank you for your help! I will review this and get back to you with results.

[jbaugher]

This worked perfectly except for one thing I wasn't aware of. This was setup as a way to have the latest contract show on an account for the portal, but a contract is several parts. The description was used to state which part each was.

So instead of the layout above
Account
-> Most Recent Contract (created date)

it is being used as
Account
-> Name: "Contract" CreatedAt: 1/1/23
-> Name: "Terms and Conditions" CreatedAt: 1/1/23
-> Etc

Looking at your code and reading through the function instructions (https://docs.espocrm.com/administrat...indrelatedmany) it seems like I should modify your code to add in ", 'name=','Contract'" at the tail end of the findrelatedmany function, like so:

$relatedEntitiesIds = record\findRelatedMany($entityType, $entityId, $relatedEntitiesLinkName, $relatedEntitiesCount, 'createdAt', 'desc', 'name=', 'Contract');
​

This edit seems to work well and I'm testing it.

Your help was invaluable, @lazovic