topic Re: Compare two Json arrays to find the new and updated records in Workato Pros Discussion Board

Compare two Json arrays to find the new and updated records

ankuragarawal — Mon, 23 Dec 2024 13:10:34 GMT

Hello Team,

I am new to Workato, i am trying to build a data integration to sync data from Workday to SharePoint List.

I have employee data coming from Workday, which is also stored in a SharePoint list. My requirement is to set up a daily integration that checks for new employees in Workday and adds them to the SharePoint list. Additionally, the integration should identify any changes to existing employee records and update the SharePoint list accordingly.

What is the recommended way to achieve it without using Foreach loop?

JSON1=[
{
"lastName": "Smith",
"User_Name": "jsmith1",
"Email_-_Work": "jsmith@example.com",
"Company": "TechCorp",
"Position": "Developer",
"Legal_Last_Name": "Smith",
"Employee_ID": "12345"
},
{
"lastName": "Smith",
"User_Name": "jsmith2",
"Email_-_Work": "jsmith@example.com",
"Company": "TechCorp2",
"Position": "Developer",
"Legal_Last_Name": "Smith",
"Employee_ID": "12345"
}
{
"lastName": "Smith",
"User_Name": "jsmith3",
"Email_-_Work": "jsmith@example.com",
"Company": "TechCorp",
"Position": "Developer",
"Legal_Last_Name": "Smith",
"Employee_ID": "12345"
}
]
JSON2=[
{
"lastName": "Smith",
"User_Name": "jsmith1",
"Email_-_Work": "jsmith@example.com",
"Company": "TechCorp",
"Position": "Developer",
"Legal_Last_Name": "Smith",
"Employee_ID": "12345",
"ID": "1"
},
{
"lastName": "Smith",
"User_Name": "jsmith2",
"Email_-_Work": "jsmith@example.com",
"Company": "TechCorp3",
"Position": "Developer",
"Legal_Last_Name": "Smith",
"Employee_ID": "12345",
"ID": "2"
}
]

JSON1 is my main data source which should synced with SharePoint list, once we identify the changes, we can update/create in the SharePoint list, please note that SharePoint will have the extra column called "ID" which needs to be ignored when matching as this column is needed to update in SharePoint.

Any suggestions are highly appreciated.

Re: Compare two Json arrays to find the new and updated records

Prudvi — Mon, 23 Dec 2024 13:20:10 GMT

Hi Ankur,
Did you try this trigger option https://docs.workato.com/connectors/workday/new_updated_object.html.
This trigger picks each record whenever it is either created or updated.
PS: This is not a batch option.
Regards,
Prudvi

Re: Compare two Json arrays to find the new and updated records

nitishjain — Mon, 23 Dec 2024 13:52:50 GMT

you need to use Python script to compare the records and pick unique records.

Re: Compare two Json arrays to find the new and updated records

shivakumara — Mon, 23 Dec 2024 15:29:54 GMT

Hi @ankuragarawal ,
Assumptions: with the above requirements, you able to fetch the records from both Workday and Share-point application.
Problem statement: You need a help in comparing two output response of both the application (Workday and Share-point)

Solution: Pass the both the response as input to the Python component and where create two list
1. Create_list: To create new records in Share-point
2. Update_list: To update the existing records in Share-point

Feel free to reach out if you need any further assistance.

Thanks and Regards,
Shivakumara A

Re: Compare two Json arrays to find the new and updated records

ankuragarawal — Tue, 24 Dec 2024 10:40:37 GMT

Thank you all for your responses. I was looking for a built-in way to implement this without using any scripting language, but it seems that using Python or JavaScript is the way forward.

I tested a script to identify changed or new records, and it works with Workato. However, the challenge lies in making the data dynamic. Currently, it requires some manual steps to map the list datapill to the input fields of the Python action in Workato. It would be helpful to have examples demonstrating how to pass list data to the input of a Python action.

Script for the solution:

def main(input):
list1 = [
{
"Last name": "User1",
"User name": "eu111",
"Email - work": "User1@test.com",
"Company": "Company1",
"Position": "Team Leader",
"Employee ID": "111",
"First name": "Test",
"Department": "IT"
},
{
"Last name": "User2",
"User name": "eu112",
"Email - work": "User2@test.com",
"Company": "Company1",
"Position": "Team Leader",
"Employee ID": "112",
"First name": "Test",
"Department": "IT"
},
{
"Last name": "User3",
"User name": "eu113",
"Email - work": "User3@test.com",
"Company": "Company1",
"Position": "Team Leader",
"Employee ID": "113",
"First name": "Test",
"Department": "IT"
}
]

list2 = [
{
"Last name": "User1",
"User name": "eu111",
"Email - work": "User1@test.com",
"Company": "Company1",
"Position": "Team Leader1",
"Employee ID": "111",
"First name": "Test",
"Department": "IT",
"ID": 1
},
{
"Last name": "User2",
"User name": "eu112",
"Email - work": "User2@test.com",
"Company": "Company1",
"Position": "Team Leader",
"Employee ID": "112",
"First name": "Test",
"Department": "IT",
"ID": 2
}
]

# Function to remove the 'ID' field from each record
def remove_id_field(data):
return [{key: value for key, value in record.items() if key != "ID"} for record in data]

# Clean list2 by removing the 'ID' field
list2_cleaned = remove_id_field(list2)

# Find changed records (same Employee ID but differing other fields)
changed_records = []
list1_without_changes = []
for record1 in list1:
is_changed = False
for record2 in list2_cleaned:
if record1.get("Employee ID") == record2.get("Employee ID"):
if record1 != record2:
changed_records.append(record2)
is_changed = True
break
if not is_changed:
list1_without_changes.append(record1)

# Find new records (present in list1 but not in list2)
new_records = [record for record in list1_without_changes if record not in list2_cleaned]

return {
"new_records": new_records,
"changed_records": changed_records,
}

Re: Compare two Json arrays to find the new and updated records

blakesha — Tue, 25 Feb 2025 03:53:01 GMT

Both requests result in List datapills. Why not load them into two SQL Collections then just use SQL joins/queries with the two SQL collections to get the resulting List, then use that resulting List datapill to update/insert into the SharePoint list?