Hello below are my requirements:
Data load uniqueness is defined by customer_code Informatica.
For each record in data from source:
1. search for an account record in target by customer_code (ulkp_lookupKey) value. If found then update else goto next step;
2. search for an account record in target by residence and legal_id (ulkp_Residence_LegalId) and empty customer_code (ulkp_lookupKey) value. If found then update else goto next step;
3. Insert new account into target.
I tried adding the following conditions in a Router transformation to route records in to 2 target objects (object is the same in both targets).
Router parameter 1 (Inserts and Update):
NOT ISNULL(ulkp_lookupKey) OR
(ISNULL(ulkp_Name) OR ByPassNameMatch='TRUE'))
Router parameter 2 (Update only):
ISNULL(ulkp_lookupKey) AND NOT ISNULL(ulkp_Residence_LegalId)
The above 2 routes doesn't seem to work since Informatica tries to Insert records and then tries to update the same records again and again until Salesforce (target) gives an error out something like this:
"","false","false","DUPLICATE_VALUE:Maximum number of duplicate updates in one batch (12 allowed). Attempt to update Id more than once in this Api call: 0010900000iwbBq:--"
Please note i have already checked the source extract, there are no duplicates at all so not sure the reason behind this error.
Is there any other way I can go about the requirements?
Thanks in advance!
You can try Dynamic lookup as well, where the lookup table will be the target table.
In the above mentioned scenario please divide the conditions in to different variables and get the verbose mode log to understand if the calculation of above expression is generating expected result or not.