Merge data only on success

Describe the issue/error/question

I’m querying my database to then retrieve corresponding entries in Pipedrive. The thing is that Pipedrive will usually not return as many items as I send it.
However, I need the data from the first Postgres node to be matched with the output of Pipedrive, but I can’t find a way to skip the items in the Postgres data that have failed for Pipedrive.

For now, I get the following result

Postgres → [a,b,c] → Pipedrive → [A, null, C]
It will merge to [{a, A}, {b, C}]

What I would like to do is only pick Postgres items corresponding to succeded Pipedrive requests.

I tried several things even with the split in batches node, to make a loop to treat all the elements one by one but I can’t figure it out.

I hope I was clear.

PS: I removed info from the workflow cause it’s for work so it’s normal that it’s not complete, I just need an example

Please share the workflow

{
  "nodes": [
    {
      "parameters": {
        "resource": "note",
        "content": ""
        }
      },
      "name": "add note1",
      "type": "n8n-nodes-base.pipedrive",
      "typeVersion": 1,
      "position": [
        1780,
        2180
      ],
      "credentials": {
        "pipedriveApi": {
          "id": "5",
          "name": "Pipedrive account"
        }
      }
    },
    {
      "parameters": {
        "operation": "executeQuery",
        "query": "",
        "additionalFields": {}
      },
      "name": "select organizations with new users",
      "type": "n8n-nodes-base.postgres",
      "typeVersion": 1,
      "position": [
        1120,
        2160
      ],
      "credentials": {
        "postgres": {
          "id": "4",
          "name": "Prod Read Only Postgres"
        }
      }
    },
    {
      "parameters": {},
      "name": "Merge",
      "type": "n8n-nodes-base.merge",
      "typeVersion": 1,
      "position": [
        1580,
        2180
      ]
    },
    {
      "parameters": {
        "options": {}
      },
      "name": "SplitInBatches",
      "type": "n8n-nodes-base.splitInBatches",
      "typeVersion": 1,
      "position": [
        1400,
        2360
      ]
    }
  ],
  "connections": {
    "select organizations with new users": {
      "main": [
        [
          {
            "node": "Merge",
            "type": "main",
            "index": 1
          }
        ]
      ]
    },
    "Merge": {
      "main": [
        [
          {
            "node": "add note1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

Hi @nohehf

Do you have a key for the data to match on? like an Id of some kinds for example?

It would be really helpful if you could post some sample data, of course you can remove all sensitive data. It would give us more of a sense of what you need to do and give you an example of how to do it.

1 Like

The data really doesn’t matter, as no field between SQL and Pipedrive is really the same. And no, no key can be used to match
The thing I would just like to do is:

for each item of SQL data:
   if Pipedrive succeded on this index:
       associate the SQL data to the Pipedrive 
   else:
       pass

The ideal solution would be that the pipedrive node returns the same number of items, with empty ones

Hi @nohehf

I am not entirely sure I understand correctly.
Sound like you want to use merge by index and then the left join option.

Unfortunately no… Left join will mess up things as the failed index will not be skipped, creating a shift in the data merging.
For if I merge Left (pipedrive): [a, b, d] with right (SQL input): [A, B, C, D] I’ll get [a + A, b + B, d + C]. I want [a + A, b + B, d + D]. What would be perfect is to be able to forward input data through nodes.

Hi @nohehf

Sorry I am not sure what you want to do.
You have no keys to use. so how do you determine that d goes with D in that example?
Or is this what you want to do:


Where you need to bring together all data from a previous stated list?

How I want to determine that D goes with d is cause the run for c failed in pipedrive. We should be able to run it one by one, or at least return the same number of items but telling which one failed. One other way would be to programmatically call previous nodes but in the execution context, not via global properties. I really don’t understand why it’s not possible to get “what does node n - 1 or n - 2 … sent as input for item n° i”, like what was the postgres output used for the pipedrive output n°x ?
And no this is what I want to do:

Hi @nohehf,

Ok, now I understand what you are trying to do.
I think there is a feature in development to keep track of the index throughout the workflow. If it wasn’t released already It should be soon I think. Not completely sure. @MutedJam could you share some info on that.

1 Like

As I’m a developer myself I thought about opening an issue / feature request and contribute to the repo, i’ll be happy to do it. I’ll be glad to have more info about this and discuss the solutions I’ve think about for this. Thx ! Sorry if I was not clear, was pretty hard to explain at first ahah

Thats where screenshots and such come in. :wink:
A picture says more than a thousand words.

I think there is a feature in development to keep track of the index throughout the workflow

This would be the pairedItem functionality. A beta version was introduced with [email protected] and you can already test it but probably don’t want to build any production workflows based on this yet (as it might still change).

2 Likes

Thanks, this is exactly what I was looking for. Is there still work on this to do on GH ? I have several implementation / design ideas for such features. For instance I think it should be in the expression GUI.
And do you know when this will be in stable ?

I don’t know about the current status, but perhaps @sirdavidoff can share more details on the exact plans around this :slight_smile:

1 Like

An other way to obtain similar results would be to be able to forward input data to output in nodes, for selected fields. It’s definitely not incompatible but I can think of a lot of scenarios where this could be useful, and easier to use.

1 Like

The pairedItem functionality works already, it’s just not implemented in all nodes. So the risk is that you use it and get an error. What we’re in the middle of doing is updating all the existing nodes to play nicely with it, which of course takes some time. But we’re talking weeks, not months.

Right now you can use it in expressions like this: $("nodeName").pairedItem()

Soon we’ll release drag and drop mapping of data from previous nodes, and the idea is to have that use pairedItem behind the scenes.

2 Likes

That’s really nice, I’ll definitely take a look and this, and will be happy to contribute in any way if possible/needed. Btw I’ve opened a feature request describing what I said above, which is another way (easier but probably less powerful) of getting previous nodes data in a “flow-scoped” context, as the pairedItem feature: Forward input data to node output . I’ll be happy to know what you think about it. Let me know if I can contribute, and thanks for making this cool feature