1

I am new to SalesForce and SOQL so sorry in advance if the question has already been answered, if yes link it to me.

The aim of my SOQL query is to get all the contract information to generate PDF.

There are tables: Contract, Contact and Account

In the Contract table there are fields: Maitre_d_apprentissage__c, MaitreApprentissageNom1__c, MaitreApprentissagePrenom1__c, Apprenti__c, ApprentiNom__c, ApprentiPrenom__c

There are relationships:

  • Apprenti__r which link Apprenti__c to Contact table
  • Maitre_d_apprentissage__r which link Maitre_d_apprentissage__c to Contact table

When I looked at table, I saw that MaitreApprentissageNom1__c was equal to Maitre_d_apprentissage__r.LastName and ApprentiNom__c was equal to Apprenti__r.LastName. So I conclude I could get other information of Apprenti__c and Maitre_d_apprentissage__c from the Contact Table following the same principle. So I added to my query Apprenti__r.Date_de_naissance__c and Maitre_d_apprentissage__r.Date_de_naissance__c to get the Date_de_naissance__c field which is in my Contact table.

I see in the results that the query succeeds in getting the information but some values have changed column (lines 6 and 7), you can see the difference between query 1 and query 2. In the first query I only return the Apprenti__r.Date_de_naissance__c and in the second query I return Apprenti__r.Date_de_naissance__c and Maitre_d_apprentissage__r.Date_de_naissance__c

Query 1:

SELECT ApprentiNom__c, ApprentiPrenom__c, Apprenti__r.Date_de_naissance__c, MaitreApprentissageNom1__c, MaitreApprentissagePrenom1__c
FROM Contract

Result 1:

Result of query 1

Query 2:

SELECT ApprentiNom__c, ApprentiPrenom__c, Apprenti__r.Date_de_naissance__c, MaitreApprentissageNom1__c, MaitreApprentissagePrenom1__c, Maitre_d_apprentissage__r.Date_de_naissance__c
FROM Contract

Result 2:

Result of query 2

I would like to understand from where is coming the problem and how to correct it. Thank you in advance.

Cobra
  • 200
  • 9

1 Answers1

2

It's possible that it's just your query editor displaying stuff incorrectly. You can see it got confused with 2 lookups to Contact table, why there's even a column header "Contact.Date_de_naissance__c" (and why it's there twice). And they aren't shown in the order you requested...

What editor you're using? You could try built-in "Developer Console" or http://workbench.developerforce.com/

What do you need it for? In Apex order of fields won't matter, in REST API query the values fetched via lookup will come as JSON sub-objects so there will always be a way to figure out exactly which value is coming from which relation.

In Dev Console try to run this and check if it solves your fears:

System.debug(JSON.serializePretty([SELECT 
    ApprentiNom__c, ApprentiPrenom__c, 
    Apprenti__r.Date_de_naissance__c, 
    
    MaitreApprentissageNom1__c, MaitreApprentissagePrenom1__c, 
    Maitre_d_apprentissage__r.Date_de_naissance__c
FROM Contract]));

Then add Maitre_d_apprentissage__r.LastName to query and see what changed, what stayed as is.

eyescream
  • 18,088
  • 2
  • 34
  • 46
  • Thank you for the answer, I'm using http://workbench.developerforce.com/ to develop my query. We don't know yet if we will be using APEX or REST API. We use Node.js to query SalesForce. Anyway as you said we will have a way to get what we want. I will try your solution on Monday (I can't try before) and come back to you after :) – Cobra May 13 '21 at 11:16
  • 1
    Yes, Workbench behaves bit funny with displaying "parent" relationship queries (the ones where you use dot to go "up"). Don't worry, you will probably be all right, try my version or in workbench use "View as: Bulk CSV" for example and view it in Excel. – eyescream May 13 '21 at 12:44
  • It seems that the "Developer Console" is giving me the right result. Thank you :) – Cobra May 17 '21 at 07:51