Update loop not actually updating

[Sarah_Viljoen]

Hi there :slightly_smiling_face:
I’m trying to batch update a date field in a specific view. The new dates are coming from a formula field (I’ve assumed the output of the formula field in the scripting app is a date because it’s formatted as such in the table). It runs with no errors but the dates aren’t actually being updated in the table and I’m not sure why. If anyone can pick up from my code why it isn’t updating, I would really appreciate it.

[TheTimeSavingCo]

Hey Sarah, could you include the code where you’re declaring field_next and field_ed?

(If you could paste everything that might make it easier too)

[Sarah_Viljoen]

Hi Adam. Here’s the relevant coding:

let dev = await input.tableAsync("Which development do you want to update?");

let field_ed = dev.getField("ESC DATE");
let field_rent = dev.getField("RENTAL (incl. VAT)");
let field_next = dev.getField("ESC DATE NEXT");
let field_nextrent = dev.getField("NEXT RENTAL");

if (sure == "Yes, Update.")
  { 
    //filters out records that need a general update
    let view = dev.getView("NEEDS UPDATING");
    
    let query = await view.selectRecordsAsync({fields: ['ESC DATE', 'ESC DATE NEXT', 'RENTAL (incl. VAT)', 'NEXT RENTAL']});

    for (let i = 0; i < query.records.length; i++)
    {
      let next_date = query.records[i].getCellValue(field_next);
      let next_rent = query.records[i].getCellValue(field_nextrent);
      
      dev.updateRecordAsync(query.records[i].name, {[field_ed.name]: next_date});
      dev.updateRecordAsync(query.records[i].name, {[field_rent.name]: next_rent});
      
    }

[kuovonne · Accepted solution]

Sarah_Viljoen:

(I’ve assumed the output of the formula field in the scripting app is a date because it’s formatted as such in the table).

This is a dangerous assumption. It probably is a date, but it might be a text string that looks like a date. Can you post the formula?

Sarah_Viljoen:

if (sure == "Yes, Update.")

How sure are you of the value of the variable sure? Can you do a console.log("sure is " + sure) right after it is set? Can you put another console.log inside the if block to be sure that it is running?

Sarah_Viljoen:

dev.updateRecordAsync(query.records[i].name, {[field_ed.name]: next_date});
      dev.updateRecordAsync(query.records[i].name, {[field_rent.name]: next_rent});

These lines are probably part of the reason you don’t see any errors. Try replacing them with this.

await dev.updateRecordAsync(query.records[i], {
    [field_ed.name]: next_date,
    [field_rent.name]: next_rent,
});

I removed the .name from the record because the first parameter to updateRecordAsync needs to be a record or a record id, not a record name. I added the keyword await so that the script will wait for the update to complete before continuing. The error was probably due to passing in the record name, and not having await hid the error because the script ended before it had a chance to show you the error.

I also combined updating both fields in one call. Eventually you will want to batch process the updates in batches of 50 outside the loop, but that is a different skill for a different day.

[Sarah_Viljoen]

I took the .name out and it worked. Thank you !!!

I used buttons for the sure variable, shown below:

let sure = await input.buttonsAsync("Are you sure you want to do a general update for Bizana table?
Once it has been updated, any mistakes made will have to be remedied individually
by selecting 'Specific update'", ["Yes, Update.", "No, cancel."]);

if (sure == "Yes, Update.")
  { 

Below is the formula for the ‘ESC DATE NEXT’ field in the table:
(The ‘ESC DATE’ field is just a date field.)

DATEADD({ESC DATE}, 1, 'years')

It is working so the formula field must be returning a date but I’m never sure how to know what variable type the formula field is returning.

Thanks for all the help !

[kuovonne]

Sarah_Viljoen:

I’m never sure how to know what variable type the formula field is returning

If the result is left aligned, the result is usually text. If the result is right aligned, the result is usually a number. You can also look at the formatting options for the result—if the formatting options apply to a date or number, the result is usually a date or number. Occasionally the result is an array, which is a bit trickier to identify.