function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion

how do i reference columns from sub query

Hi, I am using vb and I am returning a results of object from this query

 qr = x.query("SELECT Amount, Id, Name, (SELECT Quantity, ListPrice, PriceBookEntry.UnitPrice, PricebookEntry.Name, PricebookEntry.product2.Family FROM OpportunityLineItems)FROM Opportunity where  Opportunity.Pricebook2Id<> null")

I am trying to list the "ListPrice" from the sub query, I can see it in the object explorer but do not know know how to reference it, in the code below it is the variable price2 that I am trying to assign to "listprice". I have tried attempting to cast  the query result record to "OpportunityLineItem" but get errors, also tried using a function call GetPrice but still casting errors. Can someone please point me in the right direction.

  dt.Columns.Add(New DataColumn("Id"))
                dt.Columns.Add(New DataColumn("Oppurtunity Name"))
                dt.Columns.Add(New DataColumn("Price"))
                Do While (i < qr.records.Length)
                    Opportunity = CType(qr.records(i), Opportunity)
                    id = Opportunity.Id
                    Name = Opportunity.Name
                    Price = Opportunity.Amount
                    Dim price2 = Opportunity.OpportunityLineItem.listprice
                    dt.Rows.Add(id, Price, Name)
                    i = (i + 1)

 Function GetPrice(ByVal qr As sforce.QueryResult)
        Dim OpportunityLineItem As New sforce.OpportunityLineItem

        If qr.size > 0 Then
            Dim Product2 = CType(qr.records(0), OpportunityLineItem)
            Return OpportunityLineItem.ListPrice
        End If

    End Function
here is an example i used for cases. (note the c.Owner.Name subquery field reference and how it is referenced later in vb)

Public Function GetCase(ByVal CaseID As String) As sfCase
            Dim oSelfServiceUser As SelfServiceUser = System.Web.HttpContext.Current.Session("SalesForceSelfServiceUserData")
            Dim oProxy As SforceService = sfUtilities.GetProxy()
            Dim oDataSet As New DataSet
            Dim oCase As New sfCase
            Dim oResult As sforce.QueryResult

            oResult = oProxy.query("Select c.CaseNumber, c.Type, c.Owner.Name, c.Contact.Name, c.CreatedBy.Name, " & _
            "c.Contact.Phone, c.Contact.Email, c.CreatedDate, c.ClosedDate, " & _
            "c.Subject, c.Product__c, c.Priority, c.Contact.owner.Region__c, c.Status from Case c " & _
            "Where Id='" & CaseID & "'")

            If oResult.records.Length > 0 Then
                For Each oSalesForceCase As sforce.Case In oResult.records
                    With oCase
                        If Not oSalesForceCase.Id Is Nothing Then .ID = oSalesForceCase.Id
                        If Not oSalesForceCase.CaseNumber Is Nothing Then .CaseNumber = oSalesForceCase.CaseNumber
                        If Not oSalesForceCase.Type Is Nothing Then .CaseType = oSalesForceCase.Type
                        If Not oSalesForceCase.Contact.Name Is Nothing Then .ContactName = oSalesForceCase.Contact.Name
                        If Not oSalesForceCase.Contact.Phone Is Nothing Then .ContactPhone = oSalesForceCase.Contact.Phone
                        If Not oSalesForceCase.Contact.Email Is Nothing Then .ContactEmail = oSalesForceCase.Contact.Email
                        If oSalesForceCase.CreatedDate.HasValue Then .DateOpened = oSalesForceCase.CreatedDate.Value.ToString
                        If oSalesForceCase.ClosedDate.HasValue Then .DateClosed = oSalesForceCase.ClosedDate.Value.ToString
                        If Not oSalesForceCase.Subject Is Nothing Then .Subject = oSalesForceCase.Subject
                        If Not oSalesForceCase.Product__c Is Nothing Then .Product = oSalesForceCase.Product__c
                        If Not oSalesForceCase.Contact.Owner.Region__c Is Nothing Then .Region = oSalesForceCase.Region__c
                        If Not oSalesForceCase.Owner.Name1 Is Nothing Then .CaseOwner = oSalesForceCase.Owner.Name1
                        If Not oSalesForceCase.Status Is Nothing Then .Status = oSalesForceCase.Status
                        If Not oSalesForceCase.Priority Is Nothing Then .Priority = oSalesForceCase.Priority

                    End With

            End If

            Return oCase
        End Function