Transform map using sys_created_on as a parameter to update records with coalesce

[HudyssonPOntes]

Good afternoon everyone, how are you? Could someone please help me with a transform map using coalesce, so that when the transform map finds duplicate records, it updates only the oldest one? For example:

ServiceNow Table:

Name	sys_created_on	Value
Jhon	03-10-2023 00:00:00
Jhon	04-10-2023 00:00:00
Jhon	05-10-2023 00:00:00

XLSX to upload:

Name	Value
Jhon	A

Expected Result:

Name	sys_created_on	Value
Jhon	03-10-2023 00:00:00	A
Jhon	04-10-2023 00:00:00
Jhon	05-10-2023 00:00:00

[Ankur Bawiskar · Accepted solution]

@HudyssonPOntes 

you will have to use onBefore transform script for that without coalesce and without any field map

in transform script query target table with the coalesce value and use orderBy('sys_created_on') in query and update the target record

If my response helped please mark it correct and close the thread so that it benefits future readers.

[Ankur Bawiskar]

@HudyssonPOntes 

Thank you for marking my response as helpful.

If my response helped please mark it correct and close the thread so that it benefits future readers.

[HudyssonPOntes]

Good morning, how are you? Thank you very much for your response! I'm not sure how to do this exactly, I have never done it before. If it's not too much trouble, could you please give me an example of how the script might look, assuming the destination table would be called, for example, my_table, and with the values from the previous message? Thank you very much for your attention.