Entity relationship issues while calling salesforce query in my outsystems

Hi , 

i am calling a salesforce query from my outsystem application where the query looks like this


SELECT OrderNumber,PHNX_Site_Id__c,PHNX_Paying_Party__c,PHNX_Port__c,
PHNX_Port__r.PHNX_Port_Code__c,Opportunity.PHNX_Nominating_Party__c,PHNX_Port_Call_ETA_Time__c,PHNX_Vessel_ETD_Time__c,PHNX_Vessel__c,PHNX_Credited_Amt_USD__c,Status,PHNX_Sub_Status__c,PHNX_DA_Amt__c FROM Order

Getting this error while executing the query:

{"Errors":["INVALID_FIELD: No such relation 'Opportunity' on entity 'Order'. If you are attempting to use a custom field, be sure to append the '__c' after the custom field name. Please reference your WSDL or the describe call for the appropriate names."],"StatusCode":500}

How can i configure these Opporunity entity with order details on the structure,

Can anyone help me on this multiple entity relationships.



 

Hi laxmiprasanna,

I know nothing about SalesForce, but looking solely at the error and your query, if that's really the entire query, you have "Opportunity.PHNX_Nominating_Party__c", and "Opportunity" isn't a table that's part of that query.


Kilian Hekhuis wrote:

Hi laxmiprasanna,

I know nothing about SalesForce, but looking solely at the error and your query, if that's really the entire query, you have "Opportunity.PHNX_Nominating_Party__c", and "Opportunity" isn't a table that's part of that query.


I was not able to get the data for this query as it is throughing error for me, Can you guide me how can i create a structure for this query

SELECT OrderNumber,PHNX_Paying_Party__r.Name,PHNX_Port__r.Name,PHNX_Port__r.PHNX_Port_Code__c,

Opportunity.PHNX_Nominating_Party__r.Name,PHNX_Port_Call_ETA_Time__c,PHNX_Vessel_ETD_Time__c,PHNX_Vessel__r.Name,

PHNX_Credited_Amt_USD__c,Status, PHNX_Sub_Status__c,PHNX_DA_Amt__c,Opportunity.PHNX_Site__r.Name FROM Order

Hi laxmiprasanna b,

I have no knowledge of Salesforce, but I do have a bit of OutSystems, and your problem seems to be 100% related to Salesforce: that query is being sent to Salesforce for it to execute on its own database. You will definitely get better answers if you ask your question to the Salesforce community.

That being said, I'm with Kilian, if you pasted the entire query, purely from a SQL perspective I believe you will need to somehow JOIN the Order and Opportunity tables (that I assume are available in Salesforce).

Hope this helps

Did you know how can i create a structure for this type of query 


SELECT OrderNumber,PHNX_Paying_Party__r.Name,PHNX_Port__r.Name,PHNX_Port__r.PHNX_Port_Code__c,

Opportunity.PHNX_Nominating_Party__r.Name,PHNX_Port_Call_ETA_Time__c,PHNX_Vessel_ETD_Time__c,PHNX_Vessel__r.Name,

PHNX_Credited_Amt_USD__c,Status, PHNX_Sub_Status__c,PHNX_DA_Amt__c,Opportunity.PHNX_Site__r.Name FROM Order


this query is having relation on other like PHNX_Paying_Party__r.Name so what should i give a name to this field on my strucuture like Name it will not work i guess  because i have multiple names in this query. if i give like PHNX_Paying_Party__r.Name this attribute name is not accepting the field name with ' . ' . Can you suggest me the structure creation for this?

Hi laxmiprasanna

I think this link may help. It shows how to create joins in SQL, as your SQL is missing the joins with the other tables you are trying to fetch data (like Kilian and Jorge have told you before):

https://www.w3schools.com/sql/sql_join.asp

After you fix your query with the correct joins, you can create a structure in OutSystems to use as output structure of the SQL:

https://success.outsystems.com/Documentation/11/Developing_an_Application/Use_Data/Query_Data/SQL_Queries

And

https://www.outsystems.com/learn/lesson/1769/advanced-queries-sql/?LearningPathId=0

Hope this helps. 

Cheers