Problem
I have a list of calendar events, each with an array of attendees. I want to iterate over each attendee in each event and check if their email address exists in our CRM. To do this I need to get a list of airtable domains and crosscheck the email domain against the airtable domain.
e.g. The calendar event json can contain 10 events in an array of jsons.
- Event_1 has 3 attendees. [email protected], [email protected], [email protected].
- I then need to get a list of jsons from airtable and check if we have coolcorp.com on any of the entries.
Attempted Solution I: Merge Node Append
The merge block allows me to append the two list. But this is not suitable as I don’t know how many Events or airtable elements I will get. So separating them in a code block is not possible without this information.
Attempted Solution II: Merge Node Combine
Secondly I cannot easily “combine” and “merge by fields” because the number of attendees to assign “Input Fields” to varies. Some events have 1 attendee. Others have 8 or more. As these objects are nested I cannot assign the value of attendees. It would have to be {{ $json["attendees"][0] }}, {{ $json["attendees"][1] }}, … etc. Which varies depending on the event…
Workflow of attempted Merge with pinned arrays
Example of input arrays
Here is an example of a single email event (we need to iterate over at least 10 of these in an array).
{
"kind": "calendar#event",
"etag": "\"334erg53363040410000\"",
"id": "_60q30c1g60o30e34534r40o30c1g8ko3id9k70rk4gpg6cq48e9g64o30c1g60o30c1g60o30c1g60o32c1g60o30c1g6cpj0h1h8or36ghp65238cpk710j8dpj6t24ce9m88sk2c266p0g",
"status": "confirmed",
"htmlLink": "",
"created": "2023-01-30T14:37:50.000Z",
"updated": "2023-01-30T14:38:40.205Z",
"summary": "TEST TEST ",
"description": "MEETING WITH COOLCORP",
"location": "Microsoft Teams",
"creator": {
"email": "[email protected]",
"self": true
},
"organizer": {
"email": "[email protected]",
"displayName": "notmister coolman"
},
"start": {
"dateTime": "2023-02-22T13:00:00Z",
"timeZone": "Europe/London"
},
"end": {
"dateTime": "2023-02-22T13:30:00Z",
"timeZone": "Europe/London"
},
"iCalUID": "0400000487BC034D901000000000000000010000000330D1F63B91D4348A4737DF96B9A0F6A",
"sequence": 0,
"attendees": [
{
"email": "[email protected]",
"self": true,
"responseStatus": "accepted"
},
{
"email": "[email protected]",
"displayName": "dave coolman",
"optional": true,
"responseStatus": "needsAction"
}
],
"guestsCanInviteOthers": false,
"privateCopy": true,
"reminders": {
"useDefault": true
},
"eventType": "default",
"calcStartNormalised": "2023-02-22T13:00:00.000+00:00",
"calcStart": "2023-02-22T13:00:00.000+00:00"
},
{
"kind": "calendar#event",
"etag": "\"334erg53363040410000\"",
"id": "_60q30c1g60o30e34534r40o30c1g8ko3id9k70rk4gpg6cq48e9g64o30c1g60o30c1g60o30c1g60o32c1g60o30c1g6cpj0h1h8or36ghp65238cpk710j8dpj6t24ce9m88sk2c266p0g",
"status": "confirmed",
"htmlLink": "",
"created": "2023-01-30T14:37:50.000Z",
"updated": "2023-01-30T14:38:40.205Z",
"summary": "TEST TEST ",
"description": "MEETING WITH COOLCORP",
"location": "Microsoft Teams",
"creator": {
"email": "[email protected]",
"self": true
},
"organizer": {
"email": "[email protected]",
"displayName": "notmister coolman"
},
"start": {
"dateTime": "2023-02-22T13:00:00Z",
"timeZone": "Europe/London"
},
"end": {
"dateTime": "2023-02-22T13:30:00Z",
"timeZone": "Europe/London"
},
"iCalUID": "0400000487BC034D901000000000000000010000000330D1F63B91D4348A4737DF96B9A0F6A",
"sequence": 0,
"attendees": [
{
"email": "[email protected]",
"self": true,
"responseStatus": "accepted"
},
{
"email": "[email protected]",
"displayName": "notdave cooldude",
"optional": true,
"responseStatus": "needsAction"
}
],
"guestsCanInviteOthers": false,
"privateCopy": true,
"reminders": {
"useDefault": true
},
"eventType": "default",
"calcStartNormalised": "2023-02-22T13:00:00.000+00:00",
"calcStart": "2023-02-22T13:00:00.000+00:00"
}
]
Here is an example of a list from airtable
[
{
"id": "rec00edwefewnn3N",
"createdTime": "2021-06-24T09:55:17.000Z",
"fields": {
"Status": "Passed",
"Domain": "giggety.io"
}
},
{
"id": "rec00234t3wmde1",
"createdTime": "2022-08-15T03:03:43.000Z",
"fields": {
"Status": "Passed",
"Domain": "coolcorp.com"
}
}
]
I want to be able to iterate over each calendar event and match the tail end of their email domain with the domains on airtable. This breakdown to wanting to iterate over two different lists of jsons. from two separate Nodes. How can I best achieve this given the limitations I’ve desribed?
Ideal Solution
Essentially, the simplest equivalent python code I’d like to run is a nested loop over the two different array objects above, and append some elements into a new array. Something like this:
output_array: List = []
for event in calendar_events: # iterate over 10 or so calendar events
for attendee in event["attendees"]: # iterate over up to 10 attendees
for airtable_element in airtable_array: # iterate over 1000 airtable rows
if airtable_element["fields"]["Domain"] == attendee["email"]:
output_array.append(airtable_elemen["id"])
