Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Salesforce Connector not parsing JSON for "deep" relationship queries

Highlighted
7 - Meteor

Designer version: 2019.2.5.62427

SalesforceInput_4.1.0

 

I have a relationship query that is not being parsed. I am getting the raw JSON back as the result. Has anyone run into this issue? Is there a workaround or a fix in the works to address this?

 

Query not being parsed:

SELECT Username,
FederationIdentifier,
Contact.FirstName,
Contact.LastName,
CompanyName,
Contact.Account.Name,
Title,
Email,
Phone,
LastLoginDate,
Profile.Name,
ContactID
FROM User
WHERE Profile.Name = 'System Administrator'
AND IsActive = TRUE

 

Query being parsed:

SELECT Username,
FederationIdentifier,
Contact.FirstName,
Contact.LastName,
CompanyName,
Title,
Email,
Phone,
LastLoginDate,
Profile.Name,
ContactID
FROM User
WHERE Profile.Name = 'System Administrator'
AND IsActive = TRUE

 

The only difference is the first query contains "Contact.Account.Name"...

Highlighted
7 - Meteor

Update:

So it looks like parsing will work for one layer but not more than one. I'm running into this same issue with another workflow I'm working on...

 

Highlighted
Alteryx Certified Partner

Hi,

 

 

When using the Custom Query option the result is a raw Json unlike Query Builder. Here's a workflow to generate the json extraction resultgaod for you Replace the Salesforce Input tool with yours with the query that is returning json. attached workflow

7 - Meteor

geraldo,

Thanks for this. I was able to get this to work with one side effect and a question. The side effect is that the Crosstab tool converts the "." in the field name to a "_". I think I can live with this.

 

Your comment below ("When using the Custom Query option the result is a raw Json unlike Query Builder.") is not accurate. I have other custom queries that are parsed correctly by the connector which leads to my question: since some queries are parsed correctly, will I have to implement this only when I see that the results are not parsed correctly? This seems problematic. As a result, I've opened a support call with Alteryx to address this within their connector...

 

I do have a current workaround for now and I thank you for that!

Highlighted
Alteryx Certified Partner

Hi,

 

 

When you use "Contact.Account.Name", "Contact.FirstName" and "Contact.LastName" it's just like a traditional SQL join. In cases that do not "join" If you go through Custom Query without checking "Attempt to Parse JSON Response" option the result is a JSON if marking the result will be in columns. In the cases of "join" I only got results in JSON. The replace of "." for "_" was due in the construction of the final columns there was a problem truncating columns.

 

 

clipboard_image_1.png

 

 

Thanks for sharing knowledge!

 

[]

 

 

 

Highlighted
7 - Meteor

As an FYI, I did have the "Attempt to Parse JSON Response" option checked. The feedback I got from Alteryx is the key word here is "attempt". They do not guarantee the result will be able to be parsed... so it looks like your solution is the solution and I will mark it as such.

 

Thanks again for your help!

Labels