Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jun 08, 2020 12:49 PM
I’m trying to make a script to make product planning easier in conjunction with the gantt chart.
I have four fields.
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?
Solved! Go to Solution.
Jun 09, 2020 09:45 AM
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:
Jun 08, 2020 10:41 PM
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.
Jun 08, 2020 11:15 PM
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.
Jun 08, 2020 11:17 PM
isn’t this the fresh read?
Jun 08, 2020 11:22 PM
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.
Jun 09, 2020 01:01 AM
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…?
Jun 09, 2020 08:21 AM
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.
Jun 09, 2020 09:02 AM
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,
getRecord
function on the query results. This will eliminate the inner for loop.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.
Jun 09, 2020 09:45 AM
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:
Jun 11, 2020 08:16 AM
Thank you @kuovonne
That worked to get my setTimeout sorted - I was not paying attention and still trying to wrap my head around JS.