Query SF Relationships in Import Lookup Criteria
When creating a new import, the "How can we find existing records?" lookup criteria limits me to a dropdown list of field names on the destination record. That makes sense to me for import mapping, but for lookup criteria, I would expect there to be an option to use relationship/lookup fields. For example, the particular import I'm setting up is updating a few fields on a custom Salesforce sObject. For the "How can we find existing records?" lookup criteria, I'd like to match the NetSuite "Project Number" to the Salesforce "Funding__r.Project__r.Project_Number__c". But I don't see a way to use such a query on the "Define Lookup Criteria" page.
It appears as if Celigo would prefer that I add a field to the custom object, and populate it with the "Related to Project Number". Since that's redundant, and because I'm certain there's a better solution, I don't plan on using fields in this manner in Salesforce.
Is there a way to pull relationships into the criteria for record matching on a custom import?
Comments
Hi Matthew,
You are correct that the "find existing records" criteria is based on the destination record of that particular step. So it would be looking for a field on your custom record to do a lookup against.
If I am understanding your use case, what you need to do is create an additional lookup step in the flow prior to the final import on the custom record. The lookup step is where you would put in that lookup criteria of the related project objects to return the id of the custom record. Once you have that id you would response map that out of the lookup step and pass into the import step into that "find existing record" field.
Let me know if that makes sense!
Thank you, Nate. I'm hitting a wall trying to configure Response Mapping for this Lookup. I'll try to walk through my detailed objective and what I'm seeing. Invoices in NetSuite should usually align with a "Milestone" object in Salesforce (custom object). I'm trying to pass information from the Invoice in NetSuite to the Milestone in Salesforce. To find the corresponding Milestone in Salesforce, I need to match the "Project Number" on the NetSuite Invoice to the Milestone related to the same Project Number in Salesforce. There is not a field on the Milestone object, instead it is a relationship, each project has a "finance" object, and finance objects have "milestone" objects (Project --> Finance --> Milestone).
Per the Response Map instructions, I created a Salesforce lookup that occurs before the final "Salesforce Milestone Update" import. The lookup is simply an SOQL search that pulls the Project Number related to each Milestone Object. The SOQL query syntax is very simple:
SELECT Funding__r.Project__r.Project_Number__c
FROM Milestone__c
The lookup search is properly returning the Project Number for all Milestones. I think the next step would be for the lookup to find WHERE Project_Number__c = Project Number, and return the 18-digit SFDC ID of the Milestone, which I would append to the source data, and use as the import criteria, but at this point, the Response Map instructions are telling me to click the '+' sign and select 'response mapping', however I'm only given the 'results mapping' option.
I've spun a bit doing plenty of my own trial and error, but I can't seem to find a way to use the project numbers from the lookup as criteria in the import.
Results mapping is for a lookup step (use the results of a lookup in later steps) while response mapping is from an import step (use the data on the record that was just added). That's why it looks a little different in your flow since this is a pure lookup step.
Both response and results mapping allow you to use the outcome of that step, whether it be record ID's or other fields, in next steps in the flow.
Are you passing the project ID from the NetSuite invoice into the Lookup Step as a field (using the double curly brackets) to limit the results of the lookup to only the project with the matching ID?
Nate Briant,
Your response guided me to the solution - thank you! I'm new to handlebars expressions and I didn't know that I could use them to set up dynamic filters in an SOQL query. Once that understanding clicked, I was able to add a couple of "nice-to-haves" into this flow as well.
In case it's ever helpful for others searching this community thread, here are the details of my flow:
Sources: Oracle NetSuite - Saved Search with the following fields:
Lookup1 Input Filter: Only records with a Project ID and Billing Milestone should be fed into the following lookup.
Lookup1: SOQL query to match the NetSuite invoice to the correct custom object in Salesforce. As you can see in the SOQL syntax below, I'm able to use a handelbars expression to dynamically filter the SOQL query. This allows me to pinpoint the relevant custom sObject using the "Project Number" and "Billing Milestone" from the NetSuite invoice.
Lookup1 Results: The result data of the Lookup for a single record is a JSON page of data that consists of 33 different lines. For reference, here's an example of results that my lookup query returns:
Lookup1 Results Mapping: Obviously we can't pass this whole block of data to a field on the record (or we could, but we don't want to). "Results Mapping" allows us to specify which attributes we'd like to extract and append to our source data.
A couple of notes on the above mapping:
Lookup2: Oracle Netsuite Saved Search - This search list all invoices and associated "messages" (emails generated from the NetSuite UI). In my case, this saved search contains hundreds of thousands of rows - but I only care about rows related to my source record. Again, I'm able to use a handelbars expression to dynamically filter the lookup. To set the dynamic filter, this time we'll click "Additional search criteria" at the bottom of the "What would you like to export?" section of the lookup. I use a handlebars expression to filter results using the source record's "id".
Lookup2 Results: In this case, the lookup for a single record is often expected to return multiple records. Again, the results are a JSON page of data consisting of several lines. In my case, I'd only like to return the most recent message. Part of my saved search is a Formula (Numeric) field that ranks each message associated with an, where 1 is the most recent message. Here's my Oracle SQL syntax to perform this ranking in the saved search formula field:
Lookup2 Output Filter: Since there's a possibility of multiple results being returned from the lookup, I apply an output filter:
Lookup2 Results Mapping:
Again, EmailDateTime is a new field that did not exist prior to this step. This results mapping will append this field and its value to the source record.
Import: Nothing fancy here, so I won't go into great detail. However, the game-changer for me is that I can use the Lookup Results that we mapped in the import step - I can map them to fields in the "Import Mapping" or (essential for me in this case) I can use them in the "How can we find existing records?" lookup criteria.
Knowing how to properly set up additional record lookups and append their results to the Source Records is a huge productivity unlock for me. Thanks again!
Please sign in to leave a comment.