Remove all but newest related record?

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • jbaugher
    Junior Member
    • Mar 2023
    • 3

    Remove all but newest related record?

    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
    Super Moderator
    • Jan 2022
    • 810

    #2
    Hi jbaugher,

    You can use the following formula in Workflow in Execute Formula Script Action:
    Code:
    $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.​

    Comment

    • jbaugher
      Junior Member
      • Mar 2023
      • 3

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

      Comment

      • jbaugher
        Junior Member
        • Mar 2023
        • 3

        #4

        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:

        Code:
        $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

        Comment

        Working...