Remove all but newest related record?

Collapse
X
 
  • Time
  • Show
Clear All
new posts

  • jbaugher
    replied

    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

    Leave a comment:


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

    Leave a comment:


  • lazovic
    replied
    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.​

    Leave a comment:


  • jbaugher
    started a topic Remove all but newest related record?

    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?
Working...