Alpha Video Training
Results 1 to 3 of 3

Thread: Salesforce api Question

  1. #1
    "Certified" Alphaholic
    Real Name
    Larry Grupido
    Join Date
    May 2010
    Posts
    1,714

    Default Salesforce api Question

    I am trying to gather some data from a salesforce application through the api. I am connected fine and can view the tables I need.

    The problem is I only seem to be able to view them separately.

    I can get a list of tasks, for instance, but I can't connect them to the account table to see which account each task is for.

    I want to do this:

    Select *
    from tasks
    inner join accounts on tasks.accountid = accounts.id
    inner join users on tasks.ownerid = users.id
    where task.status <> 'Completed'

    When I try to do this or variations of it, I keep getting errors. The most common error is that there is an unexpected '('. Alpha is putting in parenthesis around the join statements and Salesforce doesn't seem to like it.

    How do I join tables using the salesforce api in Alpha?

  2. #2
    "Certified" Alphaholic
    Real Name
    Larry Grupido
    Join Date
    May 2010
    Posts
    1,714

    Default Re: Salesforce api Question

    Ok,

    I found some of my answer googling on the internet and salesforce forums, but I'm still stuck.

    I salesforce, there are no joins in a traditional SQL query sense. Instead, you have to create related fields in a table and then you can see the related fields. (Theoretically.)

    But what I am looking for should be standard. The account name displays on the task form in salesforce, so there must already be a related field from tasks to accounts, but I can't see it when I query the task table in the api.

    As before anyone with experience with this would be appreciated!

  3. #3
    "Certified" Alphaholic
    Real Name
    Larry Grupido
    Join Date
    May 2010
    Posts
    1,714

    Default Re: Salesforce api Question

    I got it.

    Anything that has an ID in the table can be accessed, otherwise it doesn't look like it's possible.

    So, in the task table, they have a whatID and an accountID

    so you can say

    select ID, whatID, AccountID, what.Name, Account.Name from task


    This will get you the three id's, plus the name of the what and the name of the account.

    I did this in place of the script inside of Selwyns example in the getSalesforcedatabyaccount ux. You then have to parse the Json correctly.

    So you can add this to the parsing section:

    p3[i].AccountName = pd[i].Account.Name
    if variable_exists("pd[i].What.Name") then
    p3[i].WhatName = pd[i].What.Name
    else
    p3[i].WhatName = ""
    end if
    That will extract the account name and the what name. Note that the what name is optional, thus the if/then statement to test.

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •