0

If i run this in Salesforce workbench I get one line with three lines under OpportunityLineItems

 SELECT Amount, Account.Name, Account.AccountNumber, Name,
(
 SELECT Quantity, UnitPrice, ProductCode,Product2.Name
 FROM OpportunityLineItems
)
FROM Opportunity where Opportunity.Id IN ('07357Jez0204768')

The output looks like this Workbench query output

Whereas I want the output to be like the following Expected Workbench output

How can i get the expected output ? Do i need to change the way the query is written , if so how can i do it ?

UPDATE:

  {
""attributes"": {
  ""type"": ""Opportunity"",
  ""url"": ""/services/data/v57.0/sobjects/Opportunity/q44l3KquPik6xxxxxxxx""
},
""Amount"": 115.00,
""AccountId"": ""ihdvg2cCRj6Dxxxxxxxx"",
""Name"": ""realtimetest"",
""Id"": ""q44l3KquPik6xxxxxxxx"",
""OpportunityLineItems"": {
  ""totalSize"": 1,
  ""done"": true,
  ""records"": [
    {
      ""attributes"": {
        ""type"": ""OpportunityLineItem"",
        ""url"": ""/services/data/v57.0/sobjects/OpportunityLineItem/IWHHTghK6MA8xxxxxxxx""
      },
      ""OpportunityId"": ""q44l3KquPik6xxxxxxxx"",
      ""Id"": ""IWHHTghK6MA8PzX8HQD9"",
      ""Quantity"": 4.00,
      ""UnitPrice"": 5.00,
      ""ProductCode"": ""product1Code"",
      ""Product2Id"": ""MFqTUKEOHojaxxxxxxxx"",
      ""Product2"": {
        ""attributes"": {
          ""type"": ""Product2"",
          ""url"": ""/services/data/v57.0/sobjects/Product2/MFqTUKEOHojaxxxxxxxx""
        },
        ""Id"": ""MFqTUKEOHojaxxxxxxxx"",
        ""Name"": ""product1Name""
      }
    }              
  ]
},
""Account"": {
  ""attributes"": {
    ""type"": ""Account"",
    ""url"": ""/services/data/v57.0/sobjects/Account/ihdvg2cCRj6Dxxxxxxxx""
  },
  ""Id"": ""ihdvg2cCRj6Dxxxxxxxx"",
  ""Name"": ""My Test Opp""
}

}

Christy
  • 19
  • 6

1 Answers1

0

Your query is written "top-down", looks similar to Salesforce UI with header and related lists. It behaves a bit like that:

    SELECT Id, Name,
       (SELECT Id, Name, CloseDate, StageName FROM Opportunities),
       (SELECT Id, Email FROM Contacts),
       (SELECT Id, Subject FROM Tasks)
    FROM Account

It's more useful when you want to see header + line items (Orders? Case Comments?) in one go. Nicely grouped together even if you'd see 10 opportunities.

Sounds like you'd prefer a "bottom-up" where we start from Opportunity Line Item. And that's OK - but for different applications. With that form it's bit trickier to detect boundaries if you see lines that belong to 10 opps - you'd need to order them etc.

SELECT Opportunity.Amount, Name, Opportunity.Account.Name,
Opportunity.Account.AccountNumber, Quantity, UnitPrice, ProductCode, Product2.Name
FROM OpportunityLineItem
eyescream
  • 18,088
  • 2
  • 34
  • 46
  • I have this query on a SF trigger so that if i edit the description of an Opportunity then the query will run . So i have created a query on the edit of an opportunity , not the opportunityLineItem – Christy Apr 11 '23 at 08:29
  • Think about bulk operations. Directly querying line items is simpler to understand but think what if in future you mass edit the descriptions and need to attribute them down to line items. Whatever you do, copy the description down or kinda rebuild it from line items - the "top-down" form has stuff nicely grouped together, saving you the trouble of working with hand-crafted mess like Map>? – eyescream Apr 11 '23 at 08:57
  • So in other words it is better to directly querying the items ? Is there no way i can for now re-craft the query to give the desired outcome ? As my trigger is on an edit of opportunity description not opportunitylineitem ? – Christy Apr 11 '23 at 09:20
  • Both queries will work, both need opportunity id. Workbench displays them different, true - but for both you can use apex to access the results, fields similar way. Maybe post more code and what exactly you're trying to do with line items. I'm trying to say that what you had originally may look unnatural but (depending on what you're doing with them) can make developer's job much easier later on – eyescream Apr 11 '23 at 09:42
  • I have a Json result set that has a parent and child records. Is there a standard way in c# i can traverse this SF json result set to create a datatable ? – Christy Apr 12 '23 at 02:38
  • If required i can update the question to include a sample of the json – Christy Apr 12 '23 at 04:25
  • I have updated the question to include the json, thanks – Christy Apr 12 '23 at 06:13
  • I'm not a C# person, sorry. https://stackoverflow.com/a/17842600/313628 looks promising? And then if your original query - `stuff.OpportunityLineItems.records` should work. If my query - just `stuff.records` I think – eyescream Apr 12 '23 at 07:25