Upsert error with apex batch job

Question

I have a custom object named AttendanceRate with 5 fields "AttendanceRate_' + weekDay + -'__c' for each day of the week. When a user creates an activity, he has 100% of attendanceRate for this day, and 0% when he doesn't create anything.

A record is created every Monday and then the record is updated until the end of the week. The field 'Tech_External_Id__c' is used for the upsert. It's filled with the id of the user + the number of the week the activity was created + the year the activity was created.

The issue I have is that I need to upsert AttendanceRate records for the activity created since may 2018. When I try the following code, I have this error :

First error: Upsert failed. First exception on row 1 with id a120Q00000GJCIdQAP; first error: DUPLICATE_VALUE, Duplicate external id specified: 0051n000003atukaau252019: [Tech_External_Id__c]

    private void createAttendanceRate(List<User> users, Map<Id, Decimal> mapActivityByUser){
    List<Taux_Presence__c> listAttendanceRate = new List<Taux_Presence__c>();

    Map<Id, Map<Set<String>, Set<Date>>> datesWithActivitiesByUserAndDay = new Map<Id, Map<Set<String>, Set<Date>>>();

   // Date limitDate = getLast53WeeksLimitDate(System.today());
    String numberWeek;

    List<AggregateResult> aggrsTasks = [SELECT OwnerId,  TechDateCreation__c
                                        FROM Task WHERE OwnerId IN :users AND TechDateCreation__c  > 2018-05-01
                                        GROUP BY ownerId, TechDateCreation__c];
    List<AggregateResult> aggrsEvents = [SELECT OwnerId,  TechDateCreation__c
                                         FROM Event WHERE OwnerId IN :users AND TechDateCreation__c > 2018-05-01
                                         GROUP BY ownerId, TechDateCreation__c];
    aggrsTasks.addAll(aggrsEvents);


    Map<Set<String>, Set<Date>> tempsDatesWithActivitiesByDay;
    Set<Date> tempsDatesWithActivities;
    Set<String> dateActivities;       

    //Récupère activité / jour d'un user grâce au champ ownerId
    for(AggregateResult aggr : aggrsTasks){
        tempsDatesWithActivitiesByDay = datesWithActivitiesByUserAndDay.get((Id) aggr.get('OwnerId'));

        if(tempsDatesWithActivitiesByDay == null){
            tempsDatesWithActivitiesByDay = new Map<Set<String>, Set<Date>>();            
            datesWithActivitiesByUserAndDay.put((Id) aggr.get('OwnerId'), tempsDatesWithActivitiesByDay);     
        }

        //Set récupérant activités/jour grâce au champ TechDateCreation__c
        tempsDatesWithActivities = tempsDatesWithActivitiesByDay.get(String.valueOf(aggr.get('TechDateCreation__c')));
        dateActivities = String.valueOf(aggr.get('TechDateCreation__c'));

        if(tempsDatesWithActivities == null){
            tempsDatesWithActivities = new Set<Date>();

            tempsDatesWithActivitiesByDay.put(dateActivities, tempsDatesWithActivities);
        }

        tempsDatesWithActivities.add((Date) aggr.get('TechDateCreation__c'));

        Date createdDateActivity = (Date)aggr.get('TechDateCreation__c');
        String activityCreatedToday = String.valueOf(createdDateActivity);

        String weekDay = String.valueOf(AP01_Utils.getWeekDay(createdDateActivity));
        String thisWeek = String.valueOf(AP01_Utils.getIsoWeekNumber(createdDateActivity));
        String thisDay = String.valueOf(createdDateActivity.day());
        String thisYear = String.valueOf(createdDateActivity.year());
        String thisMonth = String.valueOf(createdDateActivity.month());

        for(User user: users){
            AttendanceRate__c ar = new AttendanceRate__c();

            //Map contenant toutes les activités d'un user par jour
            tempsDatesWithActivitiesByDay = datesWithActivitiesByUserAndDay.get(user.Id);

            ar.Name = user.Name + ' Semaine ' + thisWeek + ' ' + thisYear;
            ar.User__c = user.Id;
            ar.Tech_External_Id__c = ar.Id;

            //Calcul taux de présence journalier 
            tempsDatesWithActivities = (tempsDatesWithActivitiesByDay == null ? null : tempsDatesWithActivitiesByDay.get(activityCreatedToday));

            ar.put('AttendanceRate_' + weekDay + '__c', (tempsDatesWithActivities == null ? 0 : 100));
            ar.put('Tech_Date_Creation_' + weekDay + '__c', thisDay + thisMonth + thisYear);  


            String thisUserId = user.Id;

            //alimentation champ External Id permettant d'updater un record tant que la semaine n'est pas terminée,
            // et d'en créér un nouveau la semaine suivante en concaténant le userId + le n° de semaine + année en cours
            ar.Tech_External_Id__c = thisUserId + thisWeek + thisYear;
            listAttendanceRate.add(ar);

        }     
        upsert listAttendanceRate Tech_External_Id__c;           


    }

I don't know what I'm doing wrong since the code works if the activity is created today. I only got an issue with the old activities...

Answer 1

After you upsert listAttendanceRate you do not reinitilize it. So after you upserted entries for all users, you just keep adding additional entries (with the same external ID). This explains why it is not happening if the activity is only today - you only call upsert once.

move listAttendanceRate = new List(); to before your loop over the users, and you should not get any more duplicates.