Hitachi Vantara Pentaho Community Forums
Results 1 to 8 of 8

Thread: Salesforce Field Retrieval

  1. #1
    Join Date
    Apr 2015
    Posts
    6

    Default Salesforce Field Retrieval

    I'm running a SOQL query in a Salesforce Input step which retrieves an object value:

    Code:
    SELECT Project_Checklist_Item__r.Project__r.ID
      FROM Project_Checklist_Item_Phase__c
     WHERE ID IN ('a03R0000009BwZ8IAK')
    On the Fields tab setting the field to Project_Checklist_Item__r.Project__r.ID returns null (it's non-null when I run the query in SF Workbench). What do I need to do to get the value?

  2. #2
    Join Date
    Aug 2011
    Posts
    236

    Default

    Hi JKimbel,

    Did you read the wiki - http://wiki.pentaho.com/display/EAI/SalesForce+Input

    Module = Project_Checklist_Item_Phase__c
    In the Fields tab - do 'Get Fields' delete the ones you don't need.
    in the filter field - ID IN ('a03R0000009BwZ8IAK') - you do not need to include the 'where' word
    Uncheck 'Specify query'

    Then 'Preview Rows' and it should return values

    HTH
    PDI 8.0.0
    MySQL - 5.6.27
    Redshift - 1.0.1485
    PostgreSQL 8.0.2
    OS - Ubuntu 10.04.2

  3. #3
    Join Date
    Apr 2015
    Posts
    6

    Default

    Thanks, but the problem is that the field is not in the parent object (Project_Checklist_Item_Phase__c) but in it's child (Project__c). I'm using a SOQL query and for my example simplified it to retrieve one value while what I really want involves multiple parent-child relationships. This query runs successfully, so I'm pretty sure it's returning the data, but I'm not sure how to get at it in Pentaho.
    Code:
    SELECT Project_Checklist_Item__r.Project__r.ID,
           Project_Checklist_Item__r.Project__r.Development__r.Development_Number__c,
           Project_Checklist_Item__r.Project__r.Development__r.Name,
           Project_Checklist_Item__r.Project__r.Project_Number__c,
           Project_Checklist_Item__r.Project__r.Secondary_Project_Number__c,
           Project_Checklist_Item__r.Name,
           Project_Checklist_Item__r.Dual_Application_Project_Number_Mapping__c,
           Funding_Round_Phase__r.Funding_Round__r.Round_Type__c,
           Funding_Round_Phase__r.Funding_Round__r.Round_Year__c,
           Funding_Round_Phase__r.Funding_Round__r.Tax_Credit_Allocation_Year__c
      FROM Project_Checklist_Item_Phase__c
     WHERE ID IN ('a03R0000009BwZ8IAK')

  4. #4
    Join Date
    Aug 2011
    Posts
    236

    Default

    Hi JKimbel,

    I must admin it's been a while since i used this method. From what i have read it should work.

    Can you not click preview rows and maybe it will show what the issue is?
    PDI 8.0.0
    MySQL - 5.6.27
    Redshift - 1.0.1485
    PostgreSQL 8.0.2
    OS - Ubuntu 10.04.2

  5. #5
    Join Date
    Apr 2015
    Posts
    6

    Default

    It displays the rows for the parent object but not the child fields in the query. I suspect it's in the XML that is returned but there isn't the logic to extract it. I think it might be possible to turn it into a HTTP request and parse the output but I haven't gotten very far with it.

  6. #6
    Join Date
    Aug 2011
    Posts
    236

    Default

    Hi JKimbel,

    Looking around it looks like it might not be supported right now - http://jira.pentaho.com/browse/PDI-7504

    I will keep looking a little longer to see if I find anything else.
    PDI 8.0.0
    MySQL - 5.6.27
    Redshift - 1.0.1485
    PostgreSQL 8.0.2
    OS - Ubuntu 10.04.2

  7. #7
    Join Date
    Apr 2015
    Posts
    6

    Default

    Thanks, I see that the issue has been around for a while.

  8. #8
    Join Date
    Apr 2015
    Posts
    6

    Default

    Responding to my question. This is the solution I arrived at. I searched quite a bit online and found the pieces independently but not in one place. For posterity, here is the process I went though.


    Salesforce REST Query in Pentaho - HOWTO

    Motivation: The current Pentaho Salesforce Input step does not parse Parent-Child object relationships such as MyParent.MyChild.MyFieldValue.
    However a query in a Pentaho REST client returns the JSON representation which can be parsed for the data.

    Generally, the steps are: (1) setup the application in Salesforce, (2) get the OAuth2.0 Refresh token, in Pentaho (3) create a REST client to
    get the Access token and (4) use it in another REST client in a query to get the values.

    This explains OAuth2.0 in Salesforce: https://developer.salesforce.com/pag...0_on_Force.com

    Set up application in Salesforce
    1. Create Application Setup/Build/Create/Apps/New
    2. Fill in required stuff then check "Enable OAuth Settings"
    3. Selected OAuth Scopes: "Full access", "Perform requests at any time" and maybe "Provide access to custom applications" (?)
    4. Callback URL - use Postman callback (see below) "https://www.getpostman.com/oauth2/callback" then Save
    5. Click on Manage/Edit Policies/Refresh Token Policy check "Refresh token is valid until revoked"

    Get refresh token in Postman
    Postman: https://www.getpostman.com/
    1. Click on Authorization/OAuth2.0/Get New Access Token
    2. Fill in the following:
    Name: <Any Name>
    Auth URL: https://test.salesforce.com/services/oauth2/authorize
    Access Token URL: https://test.salesforce.com/services/oauth2/token
    Client Id: From Salesforce Setup/Build/Create/Apps/Connected Apps/ click on Connected App Name link then use Consumer Key
    Client Secret: Same as above click on Click to reveal and use value
    Grant Type: Authorization Code
    Uncheck: Request access token locally
    Request Token - It will pop up a login screen then complete login, it will disappear.
    Click on the Existing Tokens/<Any Name> and copy the refresh_token.
    Comment: The access_token is good but expires. We need to get a new one when running Pentaho job instead of manually each time.

    Pentaho REST Client

    Create an access token transformation
    1. Create a Get Access Token transformation
    2. Add "Add Constant Row" step: Name: "REST_URL" Data: "https://cs2.salesforce.com/services/oauth2/token?grant_type=refresh_token&client_id=<client_id>&client_secret=<client_secret>&format=json&refresh_token=<refresh_token>"
    Comment: This URL points to a sandbox, production is a different URL.
    3. In same Constant Row step: Name: "REST_HEADER" Data: "Cache-Control=no-cache"
    4. Add Rest Client step:
    General Tab - Check URL from field: enter "REST_URL", HTTP Method: POST, Response JSON, result field: "result"
    Header tab: "REST_HEADER", Name: "Authorization" (not sure this matters)
    5. Add JSON Input step:
    File Tab - Check from previous result, field: "result"
    Field Tab - Name: "AccessToken" Path: "$.access_token" Type: string
    6. The AccessToken field will contain the new access token. This can be passed to the next transformation or saved in an Environment Variable, etc.

    Use access token in a SOQL query REST client and parse data
    1. Create SF REST Query transformation
    2. Add "REST_HEADER" row string: "Authorization=Bearer <AccessToken>,Cache-Control=no-cache" (The AccessToken value is from the previous transformation).
    3. Add "REST_URL" row string containing the query: "https://cs2.salesforce.com/services/data/v20.0/query?q=<SOQL query>&Content-Type=application/json"
    Comments: This URL points to a sandbox, production is a different URL. Beforehand it's useful to debug the SOQL query in the SF developer console.
    Also, rather than a query the Salesforce REST API is likely a better way to go: https://developer.salesforce.com/doc...s_rest_api.htm
    4. Add REST Client step as in above Get access token (#4)
    5. Add JSON Input Step as in above Get access token (#5)
    Field Tab - For each field returned add a Name: <column name> Path: <path to child object> e.g., "$..Project_Checklist_Item__r.Dual_Application_Project_Number_Mapping__c"
    Comments: http://jsonpath.com/ is useful for debugging paths, https://jsonformatter.curiousconcept.com/ makes the result more readable.
    6. Done. Add to result and use data in the next transformation

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.