Coming up empty with Merge by Key

Earlier today, I posed a question about adding time entries from Toggl to InvoiceNinja.

@RicardoE105 made a rough workflow suggestion using a number of the core nodes, and I’ve slowly been trying to work my way through it.

I’m opening a new thread since my current question isn’t related to the Toggl node anymore.

So… I have successfully figured out how to request a list of projects and a list of time entries in a date range from the Toggl API.

My next step is to merge the two based on a key. In my workflow, that means finding the project whose id matches the Project ID (pid) of the time entry.

I’ve done my best to set up a Merge node with property inputs that correspond to the above keys. But after several attempts, I have yet to get any results, so I must be doing something wrong. I’ve read the Merge Node docs and searched the forum, but I the light hasn’t gone on yet. So I’m hoping that someone might help me sort this out.

My workflow in progress is below:

{
  "name": "Toggl Test",
  "nodes": [
    {
      "parameters": {},
      "name": "Start",
      "type": "n8n-nodes-base.start",
      "typeVersion": 1,
      "position": [
        100,
        350
      ]
    },
    {
      "parameters": {
        "mode": "mergeByKey",
        "propertyName1": "={{$node[\"Time Entries\"].json[\"0\"][\"pid\"]}}",
        "propertyName2": "={{$node[\"Projects\"].json[\"0\"][\"id\"]}}"
      },
      "name": "Merge",
      "type": "n8n-nodes-base.merge",
      "typeVersion": 1,
      "position": [
        650,
        350
      ]
    },
    {
      "parameters": {
        "triggerTimes": {
          "item": [
            {
              "mode": "everyMinute"
            }
          ]
        }
      },
      "name": "Cron",
      "type": "n8n-nodes-base.cron",
      "typeVersion": 1,
      "position": [
        250,
        350
      ]
    },
    {
      "parameters": {
        "authentication": "basicAuth",
        "url": "https://api.track.toggl.com/api/v8/time_entries",
        "options": {},
        "queryParametersUi": {
          "parameter": [
            {
              "name": "start_date",
              "value": "2021-01-30T00:00:00+00:00"
            },
            {
              "name": "end_date",
              "value": "2021-03-31T23:59:59+00:00"
            }
          ]
        }
      },
      "name": "Time Entries",
      "type": "n8n-nodes-base.httpRequest",
      "typeVersion": 1,
      "position": [
        450,
        200
      ],
      "credentials": {
        "httpBasicAuth": "Toggl API Basic Auth"
      }
    },
    {
      "parameters": {
        "authentication": "basicAuth",
        "url": "https://api.track.toggl.com/api/v8/workspaces/1234567/projects",
        "options": {},
        "queryParametersUi": {
          "parameter": []
        }
      },
      "name": "Projects",
      "type": "n8n-nodes-base.httpRequest",
      "typeVersion": 1,
      "position": [
        450,
        500
      ],
      "credentials": {
        "httpBasicAuth": "Toggl API Basic Auth"
      }
    }
  ],
  "connections": {
    "Cron": {
      "main": [
        [
          {
            "node": "Time Entries",
            "type": "main",
            "index": 0
          },
          {
            "node": "Projects",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Time Entries": {
      "main": [
        [
          {
            "node": "Merge",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Projects": {
      "main": [
        [
          {
            "node": "Merge",
            "type": "main",
            "index": 1
          }
        ]
      ]
    },
    "Merge": {
      "main": [
        []
      ]
    }
  },
  "active": false,
  "settings": {},
  "id": "12"
}

Hey @matw!

I noticed that you’re using expression in the property fields in your Merge node that is causing the issues. The following workflow should work:

{
  "nodes": [
    {
      "parameters": {
        "triggerTimes": {
          "item": [
            {
              "mode": "everyMinute"
            }
          ]
        }
      },
      "name": "Cron",
      "type": "n8n-nodes-base.cron",
      "typeVersion": 1,
      "position": [
        250,
        600
      ]
    },
    {
      "parameters": {
        "authentication": "basicAuth",
        "url": "https://api.track.toggl.com/api/v8/time_entries",
        "options": {},
        "queryParametersUi": {
          "parameter": [
            {
              "name": "start_date",
              "value": "2021-01-30T00:00:00+00:00"
            },
            {
              "name": "end_date",
              "value": "2021-03-31T23:59:59+00:00"
            }
          ]
        }
      },
      "name": "Time Entries",
      "type": "n8n-nodes-base.httpRequest",
      "typeVersion": 1,
      "position": [
        450,
        450
      ],
      "credentials": {
        "httpBasicAuth": "Toggl API Basic Auth"
      }
    },
    {
      "parameters": {
        "authentication": "basicAuth",
        "url": "https://api.track.toggl.com/api/v8/workspaces/1234567/projects",
        "options": {},
        "queryParametersUi": {
          "parameter": []
        }
      },
      "name": "Projects",
      "type": "n8n-nodes-base.httpRequest",
      "typeVersion": 1,
      "position": [
        450,
        750
      ],
      "credentials": {
        "httpBasicAuth": "Toggl API Basic Auth"
      }
    },
    {
      "parameters": {
        "mode": "mergeByKey",
        "propertyName1": "pid",
        "propertyName2": "id"
      },
      "name": "Merge",
      "type": "n8n-nodes-base.merge",
      "typeVersion": 1,
      "position": [
        650,
        600
      ]
    }
  ],
  "connections": {
    "Cron": {
      "main": [
        [
          {
            "node": "Time Entries",
            "type": "main",
            "index": 0
          },
          {
            "node": "Projects",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Time Entries": {
      "main": [
        [
          {
            "node": "Merge",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Projects": {
      "main": [
        [
          {
            "node": "Merge",
            "type": "main",
            "index": 1
          }
        ]
      ]
    }
  }
}

Thanks so much for taking a look at my workflow.

Your suggestion is definitely something that I tried in my troubleshooting, but I wasn’t able to pull up any results.

I copied your workflow, updated my user in the projects auth, and executed the workflow. It again came up empty…

Is it possible that this is the expected result? I honestly don’t know.

For what it’s worth, here is a sample node from the Time Entries JSON:

{
  "id": 1234567890,
  "guid": "123456bade8e5dea6b1a62a5f2654321",
  "wid": 7777777,
  "pid": 999999999,
  "billable": true,
  "start": "2021-02-18T15:18:46+00:00",
  "stop": "2021-02-18T15:20:06+00:00",
  "duration": 80,
  "description": "Task Name for Time Entry",
  "tags": [
    "tagname"
  ],
  "duronly": false,
  "at": "2021-03-24T16:51:04+00:00",
  "uid": 1234500
}

And a sample node from the Projects JSON:

{
  "id": 999999999,
  "wid": 7777777,
  "cid": 12345678,
  "name": "Project Name",
  "billable": true,
  "is_private": true,
  "active": true,
  "template": false,
  "at": "2021-03-30T16:11:35+00:00",
  "created_at": "2021-02-19T14:11:55+00:00",
  "color": "0",
  "auto_estimates": false,
  "actual_hours": 5,
  "hex_color": "#0b83d9"
}

Obviously, I’ve obfuscated the actual numbers.

I’ve checked with each JSON, and the pid and id definitely match where you would expect them to match. (The reason I had expressions in the first example was because I was using them to test the pid and id fields to make sure that I could bring up matching results. And I could.)

I’ve had fun learning about the ins and outs of the nodes so far. But I’m still puzzled.

Any ideas?

The issue was with the structure of the data that was returned. The HTTP Request nodes were returning a single item. To create multiple items from the single item you have to use the Function node.

Checkout the workflow below

{
  "nodes": [
    {
      "parameters": {
        "triggerTimes": {
          "item": [
            {
              "mode": "everyMinute"
            }
          ]
        }
      },
      "name": "Cron",
      "type": "n8n-nodes-base.cron",
      "typeVersion": 1,
      "position": [
        408,
        523
      ]
    },
    {
      "parameters": {
        "authentication": "basicAuth",
        "url": "https://api.track.toggl.com/api/v8/time_entries",
        "options": {},
        "queryParametersUi": {
          "parameter": [
            {
              "name": "start_date",
              "value": "2021-01-30T00:00:00+00:00"
            },
            {
              "name": "end_date",
              "value": "2021-03-31T23:59:59+00:00"
            }
          ]
        }
      },
      "name": "Time Entries",
      "type": "n8n-nodes-base.httpRequest",
      "typeVersion": 1,
      "position": [
        608,
        373
      ],
      "credentials": {
        "httpBasicAuth": "Toggl"
      }
    },
    {
      "parameters": {
        "authentication": "basicAuth",
        "url": "https://api.track.toggl.com/api/v8/workspaces/123456789/projects",
        "options": {},
        "queryParametersUi": {
          "parameter": []
        }
      },
      "name": "Projects",
      "type": "n8n-nodes-base.httpRequest",
      "typeVersion": 1,
      "position": [
        608,
        673
      ],
      "credentials": {
        "httpBasicAuth": "Toggl"
      }
    },
    {
      "parameters": {
        "functionCode": "return items[0].json.map(item => {\n  return {\n    json: item\n  }\n});\n \n"
      },
      "name": "Function1",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        808,
        673
      ]
    },
    {
      "parameters": {
        "functionCode": "return items[0].json.map(item => {\n  return {\n    json: item\n  }\n});\n \n"
      },
      "name": "Function",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        808,
        373
      ]
    },
    {
      "parameters": {
        "mode": "mergeByKey",
        "propertyName1": "pid",
        "propertyName2": "id"
      },
      "name": "Merge",
      "type": "n8n-nodes-base.merge",
      "typeVersion": 1,
      "position": [
        1020,
        480
      ]
    }
  ],
  "connections": {
    "Cron": {
      "main": [
        [
          {
            "node": "Time Entries",
            "type": "main",
            "index": 0
          },
          {
            "node": "Projects",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Time Entries": {
      "main": [
        [
          {
            "node": "Function",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Projects": {
      "main": [
        [
          {
            "node": "Function1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Function1": {
      "main": [
        [
          {
            "node": "Merge",
            "type": "main",
            "index": 1
          }
        ]
      ]
    },
    "Function": {
      "main": [
        [
          {
            "node": "Merge",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}
1 Like

Ohhhhhh. I see how that works. Cool.

I now have the Merge working properly, thanks to your help. I’ve also figured out a function to set the start and end times to include the previous 24 hours from the cron trigger (in my local time). And I’ve figured out how to add the tasks and times into InvoiceNinja.

Then I went to assign those tasks to Projects in InvoiceNinja. Aaaaaand there’s no way to query the list of projects. I don’t think that’s an issue with the InvoiceNinja node. It doesn’t appear that they’ve exposed Projects to the API.

I’ve submitted a support request, but I can’t say I’m optimistic.

I’ll have to think about whether the workflow is still worthwhile. At the very least, I learned a lot about the Core Nodes of n8n!

Thanks for helping me explore and learn.

2 Likes

The project route is there. Probably the docs are not complete. At least, that is why I found out when developing the node. To make a couple of operations work, I had to look at the code.

1 Like

Ooh, thanks. I guess undocumented is better than unsupported.

For what it’s worth, here is what support responded:

I believe you should be able to create projects using the API, I suggest starting by sending a GET request to /api/v1/projects

Maybe this will still work out. I’ll keep at it.

Well, that ended up being a lot more complex than I expected. But it works. And I learned a ton about the core nodes and n8n data structure.

@RicardoE105 @harshil1712 Thanks for helping nudge me in the right directions.

2 Likes

Oops. Spoke too soon. It looks like the Invoice Ninja node won’t update the project_id. At least not the ways I’ve tried to configure it.

Here is what I have tried:

Invoice Ninja Node:
Resource: Task
Operation: Create
Additional Fields > Add Field: Project

Expressions had worked for the Client and Description fields, so I first tried an expression with the Project field: {{$node["Merge Time Entries"].json["id"]}}

That gave me the proper number for the project_id in the Result, and it pulled up the name of the project in the Project field. So that was encouraging.

But when I executed the node, the project_id remained 0.

I tried putting just id in the field, but the project_id remained 0.

I tried putting just a number in the field, but the project_id remained 0.

It seems to me that either I haven’t found the right way to connect the Project field to the incoming JSON, or there is a bug in the node. I’d bet on the former, since I’ve made tons of mistakes in building this workflow.

What do you think, @RicardoE105?

Could it be line 465 of InvoiceNinja.node.ts?

Should body.project be body.project_id?

			if (resource === 'task') {
				if (operation === 'create') {
					const additionalFields = this.getNodeParameter('additionalFields', i) as IDataObject;
					const body: ITask = {};
					if (additionalFields.client) {
						body.client_id = additionalFields.client as number;
					}
					if (additionalFields.project) {
						body.project = additionalFields.project as number;
					}

Yes, it seems like it has to be project_id. Sadly, I have not been able to test it because to use the API; a paid plan is needed.

Is this something I could test? If so, feel free to message me, and I’ll see what I can do.

Yes, it was an issue with the node. Just fixed it. Thanks for sharing the account. We will let you know when it is released.

Got released with [email protected]

It works nicely! Thanks, @RicardoE105 and @jan

Have a lovely day.

Glad to hear @matw ! Have fun!