4 Replies Latest reply on Apr 18, 2013 6:54 AM by 1001648

    How to build a query across parent and child object fields?

    1001648
      As a part of an Integration Requirement, I need to query Opportunity records that have been Modified after a specific date and time?

      Now, Opportunity has a child object called ProductRevenue with a one to many relationship. Is there anyway I can construct a querypage that will fetch records whose Opportunity fields 'OR' its child ProductRevenue's fields have been modified after a specific date and time?

      I have tried using the SearchSpec argument, but it does not let me query across child object fields.


      For eg:-
      ObjOpptyQueryPageInput.ListOfOpportunity.Opportunity.searchspec = "([ModifiedDate] > '01/01/2013 00:00:00') OR ([ProductRevenueData.ModifiedDate] >= '01/01/2013 00:00:00')";

      [This above code written in C# thew me an error saying - The object Opportunity does not have an integration component called - ProductRevenueData.ModifiedDate.]

      Any help will be greatly appreciated. Thank you.
        • 1. Re: How to build a query across parent and child object fields?
          Charles Dubant[eFrontech]
          Hi,

          As far as I know this can't be done at once because you have to consider :
          - Every Opportunity and their time-limited ProductRevenues
          AND
          - Time-limited Opportunities

          If you want to achieve this, you have to consider the 2 datasets separately and make your first query :

          ObjOpptyQueryPageInput.ListOfOpportunity.Opportunity.searchspec = "([ModifiedDate] >= '01/01/2013 00:00:00')";

          but also another query with the restriction on the ProductRevenue Searchspec.

          This shouldn't be too hard because the searchspec functionality is present at each level :
          - ListOfOpportunity -> Opportunity (the top-level that you used for your query)
          - ListOfOpportunity -> Opportunity -> ListOfProductRevenue -> ProductRevenue (the sub-level that you should use for the second query)
          - ...

          Then in your C# code, you merge the 2 datasets and you end up with your expected result.
          Hope this helps,
          Charles.

          http://www.dubant.com
          1 person found this helpful
          • 2. Re: How to build a query across parent and child object fields?
            1001648
            Thank you for the quick response.

            I had thought of the approach you mentioned and I'm currently using it. But of course I was trying to avoid two subsequent web-service calls, since I'm pulling quite a few records and hence it becomes a performance constraint. But since this can't be done with a single query, I have to resort to this approach.

            Any ideas on how to merge the datasets efficiently? Currently, I'm looping through results of the first query, to eliminate recurring/redundant records. Any input will be greatly appreciated.

            Thanks once again.
            • 3. Re: How to build a query across parent and child object fields?
              Charles Dubant[eFrontech]
              Hi,

              If you're using C# and your 2 datasets are datatables, then you can easily find solutions on Google, though when it comes to merging data in C#, this is much easier.

              Then as a general advice, avoid the custom made solutions when language-keywords exist.
              For example in C# you can be tempted to create a custom-deduplication method, but you can sort it out in a few lines as stated here :
              http://stackoverflow.com/questions/4415519/best-way-to-remove-duplicate-entries-from-a-data-table

              Native methods are here to help and you have great implementation examples if you search a little bit. Custom-solutions will always end up with poor performance when compared to native solutions.

              Good luck,
              Charles.

              http://www.dubant.com
              • 4. Re: How to build a query across parent and child object fields?
                1001648
                Hi,

                Unfortunately, I'm not using a data table. I'm using arrays, since I'm trying to achieve a bulk insert of the resultant data into an Oracle DB using Array binding. But, I'm sure I'll figure it out and thanks for pointing the direction to move forward.

                You've been of great help. Appreciate it.

                Thanks and Regards