This site was built to compliment the existing NetSuite Professionals Slack Community by adding a means for members to easily find answers to common questions and to create a searchable database of those questions and answers.

NetSuite Professionals

Does anyone know how to merge two extract lists together in Celigo, or how to perform a lookup using a field in an extract?

If I have one extract with values:  A, B, and cExternalId
And another query that has cExternalId and C

I want to combine both together, joining on cExternalId, and produce a combine result of:  A, B, C   to import into NetSuite

It seems like it should be possible, but I can’t seem to find the right documentation in Celigo for how to accomplish this.  I thought a Lookup would work, but I don’t see how to map the Lookup field cExternalId to the extract’s cExternalId to merge them together

The lookup happens on a per record basis. Typically you can pass some value from your source record to the lookup endpoint to get a result back only for what you need. If that's possible, you would use some handlebar expressions to reference the source data and pass it to the lookup endpoint.

If you can't pass a parameter and are only able to get the entire list each time, then you would need a postResponseMap script to do the merge.

Does that make help?

Does the lookup have to be an endpoint (a web service) ?  I am trying to use a Salesforce lookup, but it requires me to enter a SOQL query, and I don't see a place to specific an endpoint URL.

I also don’t see where I can specify a postResponseMap script for the lookup either.

These are the steps I followed to add a Lookup:

Click on + in DESTINATIONS &amp; LOOKUPS
Click on Add destination / lookup
Under Application, I chose the Salesforce connector
In the “What would you like to do?” drop-down, I chose Look up additional records (per record)
After that, the required options are:
SOQL query
Export type

Am I choosing the wrong type of lookup?

My problem, high level, is having two queries:
```SELECT Seller_Id__c, Channel_Account__c FROM Case

SELECT Account__c, Channel_Id__c FROM Channel__c```
I need to export the `Seller_Id__c` and `Channel_Id__c` to NetSuite.
The `Case` and the `Channel__c` records both have an Account lookup field, `Case.Channel_Account__c`  and  `Channel__c.Account__c`, however due to the limitations of Salesforce, it’s not possible to join them together in one SOQL query.  I simply want to merge these two queries logically, or perform the equivalent of:

```SELECT Seller_Id__c, (SELECT Channel_Id__c FROM Channel__c WHERE Account__c = Channel_Account__c) FROM Case```

But unfortunately, that is not valid in SOQL

I gotcha. In this case, you are on the right path. On the lookup step, you would just have something like:
```select Channel_id__c from Channel__c WHERE Account__c = {{record.Channel_Account__c}}```

Next add a result mapping to map in the returned lookup data to your source data

Those {{}} handlebar expressions dynamically reference the data from your source. If you go into the editor it helps you build it out and has a ling to the help center to show examples and what functions are available

thanks, I had tried various things, but had not tried putting `record.` before the `Channel_Account__c` field in my extract.  I’ll see if this works

Thanks so much Tyler, that was exactly what I was missing.  It’s working as expected now.  I really appreciate the help!