How to Update Old Data with updated calculate functions?

[carlfost7]

I recently modified some calculate functions on a form and it already has over 12000 old submission records that I need to recalculate with the new functions.

Please help me and let me know if there is a function or a calculate command I can invoke to recalculate a range of submission IDs or Submission_time in a project?

Or do I have to write a script to invoke the Kobo API and update each record with the Patch (Postman command)?

Thanks

[Kal_Lam]

@carlfost7, FYR …

Is it possible to change the value of the calculation type field in the database?

Welcome back to the community, @vasyl_karas! Editing a calculation field is not possible. But if there are any dependent values that should alter the calculation field, you could open the submission and then change the value of a previous question that should trigger the change in a calculation field. But saying this, you are not allowed to alter the calculation field solely.

[carlfost7]

Ok. So since I don’t want to change the dependent values, I would have to first change the dependent values to something and then change it back to the original value? This way the calculations will get recalculated (for all 12000+ records?

[wroos]

Would you mind to explain, please, why you want/need to change these data?

[carlfost7]

In January the form were 80% completed, and the users started using it. By the end of February when the form was completed with additional and modified calculate commands, the total submissions were 12000+ We would like to only update the calculate Meta_Names with the correct values (recalculate). derived from the dependent values.

Today, I was thinking if it is better to duplicate the project, and only insert the dependent values and then archive the old project, hoping that this method will be faster and resulted in the correct calculated values for each submission.

[osmanburcu]

Hi, I could suggest two option but i didn’t test the second option which is far easier than the first one. Unless you are familiar with the Python, i would recommend you use the second option.

1- you can create a python script to create an edit link, and combine it with the selenium, after that just provide the data to it, it will open the each form, and submit it again which will cause the calculate to run again. But i believe it will take 1-2 days to complete it. I had the script for a tutorial you can find it in the comments.

How create edit link in KoboToolBox

2- you can just import the new calculate to the form directly. I am sure you can directly import select one, text questions, but i didn’t test the importing calculate values i believe it would work and you could solve your problem with in 5-10 mins. You can copy your project, just fill few dummy data and test everything. After you are sure there is no problem, you can proceed with your main data

How to import data to KoboToolBox?

[carlfost7]

Thanks. I tested option2 with a test Kobo project using a Salesforce Apex script and it recalculate when I import the corrected dependent values, the Name columns changed for the related calculate type.

EXAMPLE OF THE KOBO FORM

EXAMPLE OF THE RELATED Salesforce Apex script to only change the value of the Name column according to the related dependent value (Child1_Age) - Date of Birth:

for(KoboReportMatric__c krm : recordList) {

    switch on krm.KoboLabels__c {
        when 'Child1_Age' {
            payload += Child_Age_Months(krm);
        }
        when 'Child2_Age' {
            payload += Child_Age_Months(krm);
        }                    
        when 'Child1_Edema' {
            payload += Child_Edema(krm);
        }              
        when 'Child2_Edema' {
            payload += Child_Edema(krm);
        }  
        when 'Child1_Boy' {
            payload += Child1_Boy(krm.KoboLabels__c);
        }
   }
}
if(payload != '' && submissionId !=''){
       KoboPatchList__c patchItem = new KoboPatchList__c();
       patchItem.Country__c = kb.Country__c;
       patchItem.SubmmisionId__c = submissionId;
       payload = '"payload" : {"submission_ids" : ["' + submissionId + '"],"data" : {' + payload;
       payload += '}}}';
       patchItem.PayLoad__c = payload;
       system.debug('PAYLOAD: ' + payload);
       patchItem.Status__c = 'New';
       patchlst.add(patchItem);
 }
         
 if(patchlst.size() > 0){
        insert patchlst;
        cef_fsKoboPatchBatch patchBatch = new cef_fsKoboPatchBatch(kb);
        database.executeBatch(patchBatch,1); //send the payload one line at a time to the patch process batch job.
 }

 private String Child_Age_Months(KoboReportMatric__c ChildObj){
     String retstr = '';
     Integer Child_Months	= Date.Valueof(ChildObj.ReportData__c).monthsBetween(date.today());  // Kobo Calculate command
     retstr = ',{"' +ChildObj.KoboLabels__c.substring(0,6) + '_Age_Months" : "' + Child_Months + '"}';  // patch payload json string
     return retstr;
 }

[carlfost7]

When I used Basic Auth (username and password), the patch command works OK, but when I used Bearer Token, it failed (even in Postman) - Response with the error message 404 Not Found
{
“detail”: “Not found.”
}

Please let me know the correct way to use Token ID.

[carlfost7]

In Postman, Tried API Key (as suggested by Osman) and the patch command worked!