How can I join the ScheduledScriptInstance table to the scheduledScriptDeployment (or the scheduledScript) using SuiteQL?

AI generated answer (using internal Slack threads):

To join the ScheduledScriptInstance table to the scheduledScriptDeployment (or the scheduledScript) using SuiteQL, you would need to use the TaskID field. The TaskID field in the ScheduledScriptInstance table corresponds to the ID of the script deployment in the scheduledScriptDeployment table. Here is an example of how you might do this:

sql
SELECT 
    ScheduledScriptInstance.StartDate,
    ScheduledScriptInstance.EndDate,
    ScheduledScriptInstance.TimeStampCreated,
    ScheduledScriptInstance.MapReduceStage,
    ScheduledScriptInstance.Status,
    ScheduledScriptInstance.TaskID,
    scheduledScriptDeployment.ScriptName
FROM 
    ScheduledScriptInstance
JOIN 
    scheduledScriptDeployment
ON 
    ScheduledScriptInstance.TaskID = scheduledScriptDeployment.ID

This will give you a result set that includes the start date, end date, timestamp created, map reduce stage, status, task ID from the ScheduledScriptInstance table, and the script name from the scheduledScriptDeployment table. ****** Disclaimer: This response was generated by an artificial intelligence assistant based on previous conversations from multiple Slack channels, and may contain inaccuracies or mistakes. ******

cheduledScriptInstance.TaskID != scheduledScriptDeployment.ID

Any joy? Facing the same problem. FTR, saved-search looks like below. However, there is no script id field exposed in the schedule script instance table. There are internal and task IDs in this table but no idea how they relate back to the script.

var mapReduceSearch = search.create({
    type: search.Type.SCHEDULED_SCRIPT_INSTANCE,
    filters: [
        ['script.internalid', 'is', scriptId], 
        'AND',
        ['status', 'anyof', ['PROCESSING', 'PENDING']] 
    ],
    columns: ['status']
});