I am struggling with parent-child relation in Talend. I have two tables (crm and sms) which are connected between each other.enter image description here

Initially ParentId of sms is empty or populated but needs to be updated. This ParentId of sms needs to be populated with Id from sms where CRM_legacy_ID__c matches ParentId from crm. But to find the ParentId from sms to populate with the found Id, it will be the ParentId where CRM_legacy_ID__c matches the Id from crm. And this Id from crm should correspond to the ParentId from crm taken previously. It’s very tricky but the screenshot will explain it better in 3 steps.

I have tried different expressions but doesn’t help. Here are other screenshots : enter image description here

Thank you a lot.

I tried different connections :

1. CRM_legacy_ID__c from account_sms on Id from account_crm
2. CRM_legacy_ID__c from account_sms on ParentId from account_crm