1

I have an application which pulls through a set list of Placement__c records. The user is able to:

  • Use select boxes to select certain records.
  • Specify a start date
  • Specify an end date

Those that are selected should then create a Timecard__c record.

What I want to do is ensure that no duplicates are created based on the start date and end date and the Contact lookup in question!

At the moment I have the following:

List<ts2__Placement__c> selectedPlacements = new List<ts2__Placement__c>();

for (pPlacement pPla : getPlacements()) {
  if (pPla.selected == true) {
    selectedPlacements.add(pPla.pla);
  }
}

if (!selectedPlacements.isEmpty()) {
  for (ts2__Placement__c pla : selectedPlacements) {
    System.debug(pla);
  }
}

Now I know I could run a query, something like this:

if (!selectedPlacements.isEmpty()) {
  for (ts2__Placement__c pla : selectedPlacements) {
    Integer checkExists = [SELECT Count() FROM Timecards__c 
                            WHERE Contractor__c = :pla.ts2__Employee__c AND
                                  Week_start_date__c = :startDate AND
                                  Week_end_date__c = :endDate];

      if (checkExists == 0) {
        // Do stuff!
      }
  }
}

But I know I'll quickly hit the SOQL governor limit if I do this.

What would be the most efficient way of checking if a record already exists and removing that record from the list so no duplicates are created?

  • 3
    I might be misunderstanding what your saying, but why not use a Map? – EricSSH Feb 25 '16 at 17:39
  • 1
    (1) no soql in for loops - you need to fetch all TimeCards in one call for all pla.ts2__Employee__c that were selected. Then you can run your logic (2) both of these statements if (!selectedPlacements.isEmpty()) are superfluous – cropredy Feb 25 '16 at 18:18
  • Or just make selectedPlacements a Set. Sets only allow a single instance of each object it holds. – drakored Feb 25 '16 at 18:31
1

My first thought is to turn to a pair of Set<Timecard__c>.

  • Start by gathering the set of Ids representing the contractors from the selected Placement__c records
  • create Timecard__c records (only in memory, no DML insert at this stage), setting Contractor__c, Week_Start_Date__c, and Week_End_Date__c. Put these records into a Set<Timecard__c>
  • Run a query for the Contact records matching those contractor Ids, and include a parent-child subquery for all related Timecard__c records
    • Make sure the subquery only pulls in the same fields as you set when creating the initial set of Timecard__c records
  • Iterate over the Contacts, and put all of the related Timecard__c records into a different set
  • Finally, set1.removeAll(set2)

The idea is that the hash function underlying the Set type should produce identical results if you pass in different object instances with exactly the same fields populated, and exactly the same values for those fields.

That allows you to use remove() or removeAll() to do the heavy lifting.

In testing this myself, the general idea works but a query will also pull the Id and RecordTypeId, which means you'd need to iterate over the related Timecard__c records and explicitly create new Timecards that omit the Id and RecordTypeId rather than just being able to directly addAll() on the second set from the list of related Timecard__c records on the Contact.

code would look like this:

// startDate and endDate defined elsewhere
List<ts2__Placement__c> selectedPlacements = new List<ts2__Placement__c>();
Set<Id> contactIds = new Set<Id>();
Set<Timecard__c> timecards = new Set<Timecard__c>();
Set<Timecard__c> existingTCs = new Set<Timecard__c>();

for (pPlacement pPla : getPlacements()) {
  if (pPla.selected == true) {
    selectedPlacements.add(pPla.pla);
    contactIds.add(pPla.ts2__Employee__c);
    timecards.add(
      new Timecard__c(
        Contractor__c = pPla.ts2__Employee__c,
        Week_Start_Date__c = startDate,
        Week_End_Date__c = endDate
      )
    );
  }
}

if (!selectedPlacements.isEmpty()) {
  for (Contact con :[SELECT Id, (SELECT Contractor__c, Week_Start_Date__c, Week_End_Date__c FROM Timecards__r) FROM Contact WHERE Id IN :contactIds]) {
    for(Timecard__c tc :con.Timecards__r){
      existingTCs.add(new Timecard__c(
      Contractor__c = tc.ts2__Employee__c,
      Week_Start_Date__c = tc.Week_Start_Date__c,
      Week_End_Date__c = tc.Week_End_Date__c);
    }
  }
}

timecards.removeAll(existingTCs);

insert timecards;
1

Use aggregation to your benefit:

Set<Id> contractorIds = new Set<Id>();
for (ts2__Placement__c placement : getPlacements())
    contractorIds.add(placement.ts2__Employee__c);
Set<Id> contractorIdsWithTimesheet = new Set<Id>();
for (AggregateResult timecard : [
    SELECT
        count(Id) records, Contractor__c
    FROM Timecards__c
    WHERE Contractor__c IN :contractorIds
    AND WEEK_IN_YEAR(Weed_Start_Date__c) = :desiredWeek
    GROUP BY Contractor__c
]){
    contractorIdsWithTimesheet.add(timecard.Contractor__c);
}

It doesn't look like your start and end dates are defined in the loop, so you can definitely filter on those fields instead if you like it better. I just threw in WEEK_IN_YEAR to show you how Date Functions might be useful to you. Note that you can figure out the whitelist of contractors who do not yet have a timecard as follows:

contractorIds.removeAll(contractorIdsWithTimesheet);
0

Okay, so I've finally come to a solution largely based on maps.

So in a nutshell I was creating a Timecards__c record based off of ts2__Placement__c records and wanted to avoid duplication.

I created 2 sets:

Set<Id> existingTimecards = new Set<Id>();
Set<Id> selectedContractors = new Set<Id>();

The latter would store Ids of selected contractors whilst the other would select existing timecards based off of:

  • The selected start date
  • The selected end date
  • Who was in the selectedContractors list.

I'd also created a Map:

Map<Id, ts2__Placement__c> placementsMap = new Map<Id, ts2__Placement__c>();

The key would be the employee's Id. Using that I could use the following snippet to remove duplicate keys:

for (Timecards__c t : [SELECT Contractor__c FROM Timecards__c WHERE Contractor__c IN :selectedContractors AND Week_start_date__c = :start AND Week_end_date__c = :finish]) {
  existingTimecards.add(t.Contractor__c);
}

placementsMap.keySet().removeAll(existingTimecards);

Basically, I was selecting all timecards where they had the same start and end time and who's employee Id was in the selectedContractors list. Then because I set the Maps key to the employee's Id, I could remove those matching Ids.

I just then had to create a final, clean ts2list type and add the values to it:

finalPlacements.addAll(placementsMap.values());

I could then add those final values to a ts2__Placement__c list and iterate over that, adding them to a list that would be inserted.

The final thing looks like this:

public PageReference sendTimesheets() {
  errorList = new List<String>();
  List<ts2__Placement__c> selectedPlacements = new List<ts2__Placement__c>();
  List<ts2__Placement__c> finalPlacements = new List<ts2__Placement__c>();
  List<Timecards__c> toInsert = new List<Timecards__c>();

  Set<Id> existingTimecards = new Set<Id>();
  Set<Id> selectedContractors = new Set<Id>();

  Map<Id, ts2__Placement__c> placementsMap = new Map<Id, ts2__Placement__c>();

  for (pPlacement pPla : getPlacements()) {
    if (pPla.selected == true) {
      selectedPlacements.add(pPla.pla);
      selectedContractors.add(pPla.pla.ts2__Employee__c);
      placementsMap.put(pPla.pla.ts2__Employee__c, pPla.pla);
    }
  }

  if (errorList.isEmpty()) {
    try {
      Date start = Date.valueOf(startDate);
      Date finish = Date.valueOf(endDate);

      if (!selectedPlacements.isEmpty()) {
        for (Timecards__c t : [SELECT Contractor__c FROM Timecards__c WHERE Contractor__c IN :selectedContractors AND Week_start_date__c = :start AND Week_end_date__c = :finish]) {
          existingTimecards.add(t.Contractor__c);
        }

        placementsMap.keySet().removeAll(existingTimecards);

        finalPlacements.addAll(placementsMap.values());

        for (ts2__Placement__c pla : finalPlacements) {
          toInsert.add(
            new Timecards__c(
              // New stuff goes in here
            )
          );
        }

        INSERT toInsert;
      }
    }
    catch(Exception e) {
    }
  }

  return null;
}

Thanks to the guys who answered, really pushed me in the right direction!

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.