2

I have a helper class in which I'm trying to create a new record using information from various other related records that are gathered using maps and lists.

My problem is that I'm trying to build a list that only pulls one record from each related object. Here is the structure of my problem:

Locations: Parent to Inventory control and Receipt objects, both Lookup relationships

IC: Object that I'm trying to build a list of. Child record of location, can be multiple per location.

Receipt: Object that trigger fires on, and Child record of location.

I've put all my Receipt ID's into a set along with the Location ID indicated on that Receipt record. I am building my IC list using the following Query: SELECT ID from IC WHERE Location__c IN :RecIds.keyset(); (Location__c is my key in the RecIds Map (Id of Location, Id of Receipt). Everything works just fine if I only have one IC record per location, but if I have more than one, than I have a problem.

I want to only return one IC record per Location in my list. So if I had 20 Receipts in my trigger, 10 different locations indicated on those Receipts and each location had 2 IC records associated with it, I would only want to populate my list with 10 IC records, essentially 1 per location.

I cannot limit my query to a certain number of records because that won't eliminate the correct IC records from the query.

I don't think I can use an AggregateResult list with this since I don't really have anything to aggregate? Not sure what the best approach would be so I figured I'd ask the experts!

Thanks in advance,

Improve this question
2
  • In your example, if the 10 different locations already had 2 IC records each, then there would be 20 IC records correct? Of the 20 IC records, are you looking to grab the first IC record per 10 locations.... resulting in 10 IC records? If so, you might need to sort the query unless it's at random. You can loop through the resulting query adding to a map. First you check to see if the map contains the location id, if not then put this IC record in the map keyed on the Location id with the value being the IC record itself. Can you explain "how" you choose 10 of the 20 IC records?
    – Mike Katulka
    May 18, 2015 at 20:16
  • Thanks @MikeKatulka. You're correct, there would be 20 IC records. In terms of which IC to choose from, it doesn't really matter all that much. We're not supposed to have two IC's open but it does happen so I need to account for it. If there are 2 or if there are 20, I just need to choose one per location and that's my challenge.
    – Sesaco Admin
    May 18, 2015 at 22:02

1 Answer 1

Reset to default
1

I think your best bet is to query from the Location object, looking through to your IC children, ie:

Location__c locations = [
    SELECT Id, 
        (SELECT Id, Name FROM IC__r LIMIT 1)
    FROM Location__c 
    WHERE Id IN :keys
];

You would then have to post process in code to get the ICs out, like this:

Set<Ic__c> Ics = new Set<Ic__c>();
for (Location__c loc : locations)
    for (Ic__c ic : loc.Ic__r){
        Ics.add(ic);
    }
}
Improve this answer
3
  • Sorry, had it wrong - removed group by part of answer
    – Caspar Harmer
    May 19, 2015 at 0:50
  • Thanks @CaspNZ, can I add a nested query to a set? as in: SELECT Id, (SELECT Id, Name FROM IC__c LIMIT 1) FROM Locations WHERE Id IN :keys; . IcSet.add(IC__r.Id);
    – Sesaco Admin
    May 21, 2015 at 15:44
  • You would need to iterate through the list. Amending my answer.
    – Caspar Harmer
    May 21, 2015 at 18:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.