Help

Re: Anyway to make script wait to next record is updated?

Solved
Jump to Solution
1610 2
cancel
Showing results for 
Search instead for 
Did you mean: 

I’m trying to make a script to make product planning easier in conjunction with the gantt chart.

I have four fields.

  1. startDate (date)
  2. endDate (formular)
  3. depended (link field to same table)
  4. lookUp (looks up the endDate via the depended link field)

In the first record I put I set a startDate and endDate gets automatically calculated based on various parameters.
in the second record link to to the first record and and the look up field looks up the above endDate, which my script then passes into the startDate and then it stops.

My problem is that I want to have a bunch of records stacked on top of each other. Set the dependency field (which job it should follow) and press a button and then it’ll pass the lookUp endates into every start date, but because the lookUp takes a little time to update, it stops after the the first date.

Is there away to make kind of make if loop over itself again and again until all dates with dependencies are filled?

const luPlanRecords = await prodView.selectRecordsAsync();

console.log(luPlanRecords.records)

for(let record of luPlanRecords.records){
    let startDate = record.getCellValue(prodStartDate);
    let endDate = record.getCellValue(prevFinishDate)

    if(startDate !== endDate && endDate !== null ){
        
        await prodTable.updateRecordAsync(record, 
        {'start (vs2)': new Date(endDate)});
    }
    
};

I’ve been playing around with a while loop, but that didn’t help me much.
Does anyone have any idea how I can solve this problem?

1 Solution

Accepted Solutions

In general, @Kim_Trager1, it’s a bad idea to make a query inside a loop (I did an internship last year as a software developer and got dinged on code-reviews for that very thing several times before I learned my lesson)-- and your particular structure may be causing issues because you are querying inside a loop based on your previous query – might not be a problem, but it just looks strange to me. I feel like there could be a conflict in the state of the records between the two queries, and perhaps you have an incorrect assumption about what the record under iteration in the internal loop looks like after the first loop is done :man_shrugging:t2: .

I’d suggest trying to do the work that your Lookup field is currently doing in your Scripting Block logic instead, if that’s possible (and it should be). I’m still not wrapping my head around exactly what you are trying to do, so I’m just going to make that general suggestion for now. But basically, you’ll want to build an array out of modified records (adding one at a time) that can emulate the structure of your table enough so that you can make the date checks you want for each subsequent record.

It may take significantly more code, but ultimately I think it will be simpler in that it will be easier to follow what is happening in the Script, because there isn’t this black box of the logic that the Lookup field in your base is doing. When you go back to look at this 6 months from now because something broke, you won’t be scratching your head thinking, “Why am I making another query to the same records here? What was the purpose of doing that inside my loop? What fields was I waiting on?”.

That’s just my humble, and perhaps only slightly informed take on your situation here :slightly_smiling_face:

See Solution in Thread

10 Replies 10

I tried a few things like putting a for-loop inside a for-loop

but now it seems my code never breaks out of the inner for-loop which I’m not sure why.

let luPlanRecords = await prodView.selectRecordsAsync();

console.log(luPlanRecords.records)

for(i = 0; i < luPlanRecords.records.length; i++){
    
    console.log('el1',i)

    let newUpd = await prodView.selectRecordsAsync();
    
    for(let record of newUpd.records){
        let startDate = record.getCellValue(prodStartDate);
        let depDate = record.getCellValue(prevFinishDate)

        console.log('el2',record)

        if(startDate !== depDate && depDate !== null ){
    
        
         await prodTable.updateRecordAsync(record, 
            {'start (vs2)': new Date(depDate)});
        }
    }
    
}

but added a break
now it runs through the code as intended but it still does not update my startDate with my depDate except for the first record

let luPlanRecords = await prodView.selectRecordsAsync();

console.log(luPlanRecords.records)

for(i = 0; i < luPlanRecords.records.length; i++){
    
    console.log('el1',i)

    let newUpd = await prodView.selectRecordsAsync();
    
    

    for(let record of newUpd.records){
        let startDate = record.getCellValue(prodStartDate);
        let depDate = record.getCellValue(prevFinishDate)

        console.log('el2',record)

        if(startDate !== depDate && depDate !== null ){
    
        
         await prodTable.updateRecordAsync(record, 
            {'start (vs2)': new Date(depDate)});
            break
        }
break
    }
    
}

Anyone who know how best to deal with this problem?
I assume it has to do with the lookup delay.

You need to do a fresh read/select of the records after each update to get the new values in the lookups and the formulas.

If a fresh read doesn’t do the trick by itself, you may also need to wait until the formula field and lookups have recalculated. Just because the update request is done doesn’t mean all the calculations are done.

Try the setTimeout function described in this thread. Contrary to the post title, setTimeout does work.

isn’t this the fresh read?

Ah. I was just looking at your code in your first post.

The code in you second post is more than I can really picture on my phone, so I can’t comment on it now.

Been playing around with the setTimeout - however I can’t get it to timeout, am I doing something wrong?

let luPlanRecords = await prodView.selectRecordsAsync();

console.log(luPlanRecords.records)

for(i = 0; i < luPlanRecords.records.length; i++){
    
    setTimeout(console.log('waiting'),4000);
    
    let newUpd = await prodView.selectRecordsAsync()


    for(let record of newUpd.records){
        let startDate = record.getCellValue(prodStartDate);
        let depDate = record.getCellValue(prevFinishDate)

        console.log('el2',record)

        if(startDate !== depDate && depDate !== null ){
    
        
         await prodTable.updateRecordAsync(record, 
            {'start (vs2)': new Date(depDate)});
            setTimeout(4000)
            break
        }

       break
                
    }

Setting a timeout, will that have some implication if a calculation for whatever reason is slow like internet connection etc…?

You need to create a new function for setTimeout that returns a promise, then, when you want a pause, call the function and await the result. See the example function and the example call by OpenSide in the second post in the topic.

I’m not sure why your code doesn’t break out of the inner for-loop. It’s hard to tell without knowing any of the values or how they are calculated.

Have you checked the comments/history for the records in question. Do they show any changes?

Here is another idea that does away with the inner for loop. For each record,

  1. do a fresh read of the data
  2. get the next record by id using the getRecord function on the query results. This will eliminate the inner for loop.
  3. update the record with the values you want
  4. pause for a second or so for Airtable to update its formula fields and lookups (you may need to play around with this. Some formulas & lookups recalculate very quickly and some take longer depending on how complex they are)
  5. continue to the next record

Depending on how your tasks and dependencies are linked, you might need to have a separate pass through the records to identify the order to process the records.

In general, @Kim_Trager1, it’s a bad idea to make a query inside a loop (I did an internship last year as a software developer and got dinged on code-reviews for that very thing several times before I learned my lesson)-- and your particular structure may be causing issues because you are querying inside a loop based on your previous query – might not be a problem, but it just looks strange to me. I feel like there could be a conflict in the state of the records between the two queries, and perhaps you have an incorrect assumption about what the record under iteration in the internal loop looks like after the first loop is done :man_shrugging:t2: .

I’d suggest trying to do the work that your Lookup field is currently doing in your Scripting Block logic instead, if that’s possible (and it should be). I’m still not wrapping my head around exactly what you are trying to do, so I’m just going to make that general suggestion for now. But basically, you’ll want to build an array out of modified records (adding one at a time) that can emulate the structure of your table enough so that you can make the date checks you want for each subsequent record.

It may take significantly more code, but ultimately I think it will be simpler in that it will be easier to follow what is happening in the Script, because there isn’t this black box of the logic that the Lookup field in your base is doing. When you go back to look at this 6 months from now because something broke, you won’t be scratching your head thinking, “Why am I making another query to the same records here? What was the purpose of doing that inside my loop? What fields was I waiting on?”.

That’s just my humble, and perhaps only slightly informed take on your situation here :slightly_smiling_face:

Thank you @kuovonne

That worked to get my setTimeout sorted - I was not paying attention and still trying to wrap my head around JS.

Thank you @Jeremy_Oglesby and thank you for the advise - I’ll try to keep my queries out of my loops, its just so tempting… :grimacing:

For some reason my brain have been fixating on working with the look up that I completely blocked out better ways.
Basically I just want the calculated end-date of the above record passed in as a start date in the below record.
It’s a small script for the Gaant chart block. Instead of using dependencies I just want to quickly stack a bunch of records. Put a startDate in the first record click a button and then have the script to calculate all the other start and end dates based upon a few different parameters.

As you point out I assume I can just save the endDate Value outside the loop and use it in the next iteration, or use part of the other code you helped me with the other day and grab the endDate directly from the record below.
These ways might also be more desirable as it takes out the need to use dependencies which was just one extra step to get the look up to work.