How to take a list of projects, filter out the new ones, and pass on the data if there is more than one new project?

nle · March 9, 2023, 9:43am

Describe the issue/error/question

How do I take a list of projects from Harvest, filter only the new items, create Trello card and send a message to RocketChat, also if there are more than one new project?

I’ve gotten it to work if I limit the list from Harvest to ´1´ and by using this “updates”-node to check if it’s new/old, before sending it to Trello/RocketChat. The issue is when there is more than one project created within a short time span.

I did try with split-in-batches, but I did not get it to work any better.

Please let me know if I need to provide any more information.

Please share the workflow

Example Data from Harvest

[
	{
		"id": 123456781,
		"name": "Project title",
		"code": "1231",
		"is_active": true,
		"is_billable": true,
		"is_fixed_fee": false,
		"bill_by": "Project",
		"budget": null,
		"budget_by": "none",
		"budget_is_monthly": false,
		"notify_when_over_budget": false,
		"over_budget_notification_percentage": 80,
		"show_budget_to_all": false,
		"created_at": "2023-03-08T09:41:00Z",
		"updated_at": "2023-03-08T09:41:00Z",
		"starts_on": null,
		"ends_on": null,
		"over_budget_notification_date": null,
		"notes": "",
		"cost_budget": null,
		"cost_budget_include_expenses": false,
		"hourly_rate": null,
		"fee": null,
		"client": {
			"id": 123456,
			"name": "Client",
			"currency": "USD"
		}
	},
	{
		"id": 123456782,
		"name": "Project title",
		"code": "1231",
		"is_active": true,
		"is_billable": true,
		"is_fixed_fee": false,
		"bill_by": "Project",
		"budget": null,
		"budget_by": "none",
		"budget_is_monthly": false,
		"notify_when_over_budget": false,
		"over_budget_notification_percentage": 80,
		"show_budget_to_all": false,
		"created_at": "2023-03-08T09:42:00Z",
		"updated_at": "2023-03-08T09:42:00Z",
		"starts_on": null,
		"ends_on": null,
		"over_budget_notification_date": null,
		"notes": "",
		"cost_budget": null,
		"cost_budget_include_expenses": false,
		"hourly_rate": null,
		"fee": null,
		"client": {
			"id": 123456,
			"name": "Client",
			"currency": "USD"
		}
	},
	{
		"id": 123456783,
		"name": "Project title",
		"code": "1231",
		"is_active": true,
		"is_billable": true,
		"is_fixed_fee": false,
		"bill_by": "Project",
		"budget": null,
		"budget_by": "none",
		"budget_is_monthly": false,
		"notify_when_over_budget": false,
		"over_budget_notification_percentage": 80,
		"show_budget_to_all": false,
		"created_at": "2023-03-08T09:43:00Z",
		"updated_at": "2023-03-08T09:43:00Z",
		"starts_on": null,
		"ends_on": null,
		"over_budget_notification_date": null,
		"notes": "",
		"cost_budget": null,
		"cost_budget_include_expenses": false,
		"hourly_rate": null,
		"fee": null,
		"client": {
			"id": 123456,
			"name": "Client",
			"currency": "USD"
		}
	},
	{
		"id": 123456784,
		"name": "Project title",
		"code": "1231",
		"is_active": true,
		"is_billable": true,
		"is_fixed_fee": false,
		"bill_by": "Project",
		"budget": null,
		"budget_by": "none",
		"budget_is_monthly": false,
		"notify_when_over_budget": false,
		"over_budget_notification_percentage": 80,
		"show_budget_to_all": false,
		"created_at": "2023-03-08T09:44:00Z",
		"updated_at": "2023-03-08T09:44:00Z",
		"starts_on": null,
		"ends_on": null,
		"over_budget_notification_date": null,
		"notes": "",
		"cost_budget": null,
		"cost_budget_include_expenses": false,
		"hourly_rate": null,
		"fee": null,
		"client": {
			"id": 123456,
			"name": "Client",
			"currency": "USD"
		}
	},
	{
		"id": 123456785,
		"name": "Project title",
		"code": "1231",
		"is_active": true,
		"is_billable": true,
		"is_fixed_fee": false,
		"bill_by": "Project",
		"budget": null,
		"budget_by": "none",
		"budget_is_monthly": false,
		"notify_when_over_budget": false,
		"over_budget_notification_percentage": 80,
		"show_budget_to_all": false,
		"created_at": "2023-03-08T09:45:00Z",
		"updated_at": "2023-03-08T09:45:00Z",
		"starts_on": null,
		"ends_on": null,
		"over_budget_notification_date": null,
		"notes": "",
		"cost_budget": null,
		"cost_budget_include_expenses": false,
		"hourly_rate": null,
		"fee": null,
		"client": {
			"id": 123456,
			"name": "Client",
			"currency": "USD"
		}
	}
]

Information on your n8n setup

n8n version: 0.218.0
Database: SQLite
Running n8n with the execution process: Own (default)
Running n8n via: Docker

Jon · March 9, 2023, 12:48pm

Hey @nle,

I have fixed the workflow so it appears, It looks like while you were redacting you changed the data type which caused the workflow to no longer be valid.

Looking at the workflow I guess a couple of options could be to get the cards from Trello and see if it already exists before making it that would then let you know if it is new or missing.

Another option would be to use the created_at field and do a if created_at is after now minus interval so something like the workflow below.

There is also a state node recently created by @pigri which might help.

pigri · March 9, 2023, 1:26pm

Thanks for the mention!

pigri · March 9, 2023, 1:28pm

Yeah, I made a simple state machine for a similar case. GitHub - pigri/n8n-nodes-statemachine

One requirement is that you need a Redis instance with disk save.

nle · March 9, 2023, 1:37pm

Thank you. I tried out your solution, and it seems like it’s a bit fragile if i.e the results hangs a bit, etc. – and the creation date of the project becomes older than currentTime -30 seconds – it will not go through.

I’m really struggling with this – and in my mind it should be pretty simple (but apparently easier said than done).

Get list of projects (Harvest only offers GetAll).
Identify new projects from list (using json.id? – each project has unique id)
Forward the new projects – one by one – and create a Trello card and send to RocketChat channel.
Profit.

Issue 1: Identifying the new projects (if it is more than one). I managed to do this with the “updates”-node node – but only with one. Giving it more than one output/project does not work for me.

I did try with split in batches, but does not work for more than one.

pigri · March 9, 2023, 3:10pm

I’m not sure what you mean. Can you share an example of how you can use it? Because the state depends on the input values because the state has created a hash from the value.

nle · March 9, 2023, 3:55pm

It was a reply to @Jon’s suggested solution.

I have not tested your node (it feels a bit over my skill level tbh).

Jon · March 9, 2023, 4:54pm

Hey @nle,

Typically our solutions won’t involve community nodes as we don’t directly support them but I can’t think of why the node doesn’t do it unless it doesn’t loop over the input items.

Another approach could be to get all the projects then get all the cards from trello and remove anything that already exists that way.

Generally with the earlier solution I would probably run it every 10 minutes which would allow enough time for it to complete the idea is that we get anything created since the last run so in theory it should always work.

nle · March 13, 2023, 1:36pm

I ended up using @pigri’s n8n-nodes-statemachine node to solve my issue. Thanks a lot for all your help @pigri!

I was overthinking it, thinking the statemachine-node required more instructions, but all I had to do was to put statemachine-node in between the harvest node and my output nodes – and it worked. It only outputs the new data it receives. Awesome.

I used Docker to run the Redis instance – just combined it in the same docker-compose that I have n8n.

  redis:
    container_name: redis
    image: redis:7
    restart: always
    network_mode: service:n8n
    #ports:
    #  - '6379:6379'
    command: redis-server --save 20 1 --loglevel warning --requirepass ${REDIS_PASSWORD}
    volumes:
      - ./redis:/data

Using network_mode: service:n8n lets me connect to “localhost” instead of hostnames, etc. It keeps it simple imho.

My end workflow looks like this (just a screenshot):

system · March 20, 2023, 1:36pm

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.