What is the best way to go about using multiple eval commands, subsearches, and foreach commands?

[jackstephenson9]

I'm trying to sort smartsheets by certain combinations of row/column values. If I remove one of the 'foreach' blocks, the search works, outputting a new field. With both, however, the search returns 0 results. I'm wondering if there's a better way to do this.

Also, the reason I can't just eval them separately is that one of the fields (a column) "Final" appears both in "Project scheduling" row events and "Project closed" row events

index=main sourcetype=smartsheet 
| rename metadata.smartsheet_name as sheetname 
| dedup metadata.id 
| foreach sheetname 
         [ search "Task Name"="Project scheduling" 
         | eval nowtime=strftime(now(), "%Y-%m-%d") 
         | eval nowtime=strptime(nowtime, "%Y-%m-%d") 
         | eval scheduledtime=strptime(Finish,"%Y-%m-%d") 
         | eval scheduledOk=if(scheduledtime<=nowtime, "true", "false")] 
| foreach sheetname
         [ search "Task Name"="Project closed" 
         | eval nowtime=strftime(now(), "%Y-%m-%d") 
         | eval nowtime=strptime(nowtime, "%Y-%m-%d") 
         | eval finishtime=strptime(Finish,"%Y-%m-%d") 
         | eval finishedOk=if(finishtime>nowtime, "true", "false")]

[renjith_nair · Accepted solution]

@jackstephenson96,

Assuming that the Status condition on the task depends on the Task Name and rest of the fields are same for both, give this a try and see if its matching with your requirement

index=main sourcetype=smartsheet 
| rename metadata.smartsheet_name as sheetname 
| dedup metadata.id
| eval nowtime=strftime(now(), "%Y-%m-%d") 
| eval nowtime=strptime(nowtime, "%Y-%m-%d") 
| eval Status=case("Task Name"=="Project scheduling" ,if(strptime(Finish,"%Y-%m-%d")<=nowtime,"true","false")
                   ,"Task Name"=="Project closed" ,if(strptime(Finish,"%Y-%m-%d")>nowtime,"true","false"))

[jackstephenson9]

Renjith, you are a genius. Thank you