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?

0

Comments

4 comments
Date Votes
  • 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!

    0
  • 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.

    0
  • 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?

    1
  • 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:

    • id, recordType, Project ID, Name, Billing Milestone, Amount, Amount Remaining, Exported to SF (boolean T/F)
    • Criteria = Exported to SF = False, Billing Milestone is not empty
    • In Celigo, Export type is set to "Once - export records only once" and "Boolean field to mark..." is set to "Exported to SF".

    Lookup1 Input Filter: Only records with a Project ID and Billing Milestone should be fed into the following lookup.

    • record.[Project ID] is not empty, record.[Billing Milestone] is not empty.

    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.

    SELECT id, Name, Project__r.Project_Number__c
    FROM Milestone__c
    WHERE Funding__r.Solar_Project__r.Project_Number__c = '{{record.[Project ID]}}' AND Name = '{{record.[Billing Milestone]}}'

    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:

    data": [
        {
          "attributes": {
            "type": "Milestone__c",
          "url": "www.fake.com"
          },
        "Id": "a1B2abcdefghijklmn",
        "ExampleField1": "John Doe",
        "Name": "Milestone XYZ",
         "Project__r": {
              "attributes": {
              "type": "Project__c",
              "url": "www.fake.com"
              },
            "Project_Number__c": "ABC123456"
            }
          }
        }
      ],
    ...etc.

    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.

    • Lookup response field: data[0].Id
    • Source record field: MilestoneId

    A couple of notes on the above mapping:

    1. The SOQL query should in theory only provide a single record per query, but regardless of this, the JSON format results will be formatted as an array. For this reason, we need to tell Celigo which array result to use in the mapping - hence the data[0].Id. If 2 or more results happen to be returned, this mapping will only use the "Id" field on the first record returned.
    2. MilestoneId is a new field that did not exist prior to this step. This results mapping will append this new field and its value to the source record. Another option would be to overwrite an existing source data field with this query result data.

    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".

    Field: Internal ID (Number)Operator: equal to | Search Value: {{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:

    RANK() OVER (PARTITION by {internalid} ORDER BY {messages.internalid} DESC)

    Lookup2 Output Filter: Since there's a possibility of multiple results being returned from the lookup, I apply an output filter:

    Rule: record.[Message Rank] equals 1

    Lookup2 Results Mapping:

    • Lookup response field: data[0].[Email DateTime]
    • Source record field: EmailDateTime

    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!

    0

Please sign in to leave a comment.

 

Didn't find what you were looking for?

New post