How to find oldest ticket in array for scripting / filter by string for multi select

When people have trouble writing an automation script I always recommend writing the script first in Scripting App. It is much easier to debug a script in Scripting App, and then make the handful of changes necessary for the script to run as an automation.

It is hard to tell exactly what is going on because you show only a portion of the script and none of the data.

For filtering the records, it looks like you are trying to see if the string for the cell number matches the config string. Is that working as expected? Your code does not yet include checking the VMorText field. One method would be to split the body of the anonymous filter function into three lines instead of one. The first line would check the first condition. The second line would check the second condition. Then return whether or not both conditions are met.

For problem 2, selecting the first record, note that [0] will include the first record that is stored in the array, not necessarily the first record that was created. It might be the first record that was created, but it might not be. There are a variety of ways of getting the first record that was created. You can sort the array based on the created time and take the first record after the sort, or you can use reduce() to reduce the array to the earliest one based on created time.

Thanks for your response. I’ve included the full script at the bottom, although I can’t really share the data (although I can share any information required such as cell formatting.

The filtering of records based on cell number works well - can I just add a similar line beneath the first condition and it will also check that? I’ve tried to design one as follows:
// list records that match cell number

let matchingTxtRecords = Txtsearcharea.records.filter(cellnumber => {

    if(cellnumber.getCellValueAsString(Txtsearchfield).includes(config.TxtCellnumber) 

    &&  cellnumber.getCellValueAsString(Typesearchfield).includes(textstring)) 

    {return;

    }

    

})

console.log(matchingTxtRecords)

Unfortunately this does not work - it’s seemingly unable to access the string of Type(previously VMorText) to compare it to, instead it shows ’[]' in the console log

Regarding sorting the array based on date - I understand how to sort an array, but the array of ‘matchingTxtrecords’ only includes record IDs and cell-numbers - how to I get to the cell data those IDs refer to so that I can compare the dates?

// setting up tables code is looking at

let Txttable = base.getTable('Voicemails and messages');

// getting ID for new record

let config = input.config();

let TxtrecordID = config.TxtRecordID;

let Txtcellnumber = config.TxtCellnumber;

// get cell number

let Txtsearchfield = await Txttable.getField('Cell number');

let Typesearchfield = await Txttable.getField('Type');

const textstring = "Text Message"

// define search area as Ticket table

let Txtsearcharea = await Txttable.selectRecordsAsync()

console.log(Typesearchfield)

// list records that match cell number

let matchingTxtRecords = Txtsearcharea.records.filter(cellnumber => {

    if(cellnumber.getCellValueAsString(Txtsearchfield).includes(config.TxtCellnumber) 

    &&  cellnumber.getCellValueAsString(Typesearchfield).includes(textstring)) 

    {return;

    }

    

})

console.log(matchingTxtRecords)

// OK so - before this, we need to sort the array by date, - get the date field out of record, sort by it to make sure [0] is the oldest

// and make sure we are only selecting text messages (now that we are looking at all records created in this tab)

let firstTxtrecord = matchingTxtRecords[0];

let newmessageID = Txtsearcharea.getRecord(TxtrecordID);

let timenewmessage = newmessageID.getCellValueAsString("scripttimedate")

let newmessage = newmessageID.getCellValueAsString("Message")

// adds old message onto top of old message set (but for newest message - probably needs to be for oldestmessage)

//firstTxtrecord.getCellValueAsString("Message").concat(firstTxtrecord.getCellValueAsString("Text conversation"),'test');

let firstTxtrecordtextconvo = firstTxtrecord.getCellValueAsString("Text conversation")

//checks if existing convo exists, makes existing message set either just the message or the text conversation

if (firstTxtrecordtextconvo = "") {

var existingmessageset = firstTxtrecord.getCellValueAsString("Message");

} else {

var existingmessageset = firstTxtrecord.getCellValueAsString("Text conversation");

}

await Txttable.updateRecordAsync(firstTxtrecord, {

    "Text conversation" : timenewmessage.concat(': ', newmessage, "\n\n", existingmessageset), 

    "Status": {id: "selJIq9thWDouAmFZ"},

})

await Txttable.updateRecordAsync(config.TxtRecordID, {

    "Status": {id:"selPu9qw4ptaky21c"},

});

console.log(firstTxtrecord.getCellValueAsString("Text conversation"))

console.log('first text below')

console.log(firstTxtrecord)

Thanks for your response. I’ve included the full script at the bottom, although I can’t really share the data (although I can share any information required such as cell formatting.

The filtering of records based on cell number works well - can I just add a similar line beneath the first condition and it will also check that? I’ve tried to design one as follows:
// list records that match cell number

let matchingTxtRecords = Txtsearcharea.records.filter(cellnumber => {

    if(cellnumber.getCellValueAsString(Txtsearchfield).includes(config.TxtCellnumber) 

    &&  cellnumber.getCellValueAsString(Typesearchfield).includes(textstring)) 

    {return;

    }

    

})

console.log(matchingTxtRecords)

Unfortunately this does not work - it’s seemingly unable to access the string of Type(previously VMorText) to compare it to, instead it shows ’[]' in the console log

Regarding sorting the array based on date - I understand how to sort an array, but the array of ‘matchingTxtrecords’ only includes record IDs and cell-numbers - how to I get to the cell data those IDs refer to so that I can compare the dates?

// setting up tables code is looking at

let Txttable = base.getTable('Voicemails and messages');

// getting ID for new record

let config = input.config();

let TxtrecordID = config.TxtRecordID;

let Txtcellnumber = config.TxtCellnumber;

// get cell number

let Txtsearchfield = await Txttable.getField('Cell number');

let Typesearchfield = await Txttable.getField('Type');

const textstring = "Text Message"

// define search area as Ticket table

let Txtsearcharea = await Txttable.selectRecordsAsync()

console.log(Typesearchfield)

// list records that match cell number

let matchingTxtRecords = Txtsearcharea.records.filter(cellnumber => {

    if(cellnumber.getCellValueAsString(Txtsearchfield).includes(config.TxtCellnumber) 

    &&  cellnumber.getCellValueAsString(Typesearchfield).includes(textstring)) 

    {return;

    }

    

})

console.log(matchingTxtRecords)

// OK so - before this, we need to sort the array by date, - get the date field out of record, sort by it to make sure [0] is the oldest

// and make sure we are only selecting text messages (now that we are looking at all records created in this tab)

let firstTxtrecord = matchingTxtRecords[0];

let newmessageID = Txtsearcharea.getRecord(TxtrecordID);

let timenewmessage = newmessageID.getCellValueAsString("scripttimedate")

let newmessage = newmessageID.getCellValueAsString("Message")

// adds old message onto top of old message set (but for newest message - probably needs to be for oldestmessage)

//firstTxtrecord.getCellValueAsString("Message").concat(firstTxtrecord.getCellValueAsString("Text conversation"),'test');

let firstTxtrecordtextconvo = firstTxtrecord.getCellValueAsString("Text conversation")

//checks if existing convo exists, makes existing message set either just the message or the text conversation

if (firstTxtrecordtextconvo = "") {

var existingmessageset = firstTxtrecord.getCellValueAsString("Message");

} else {

var existingmessageset = firstTxtrecord.getCellValueAsString("Text conversation");

}

await Txttable.updateRecordAsync(firstTxtrecord, {

    "Text conversation" : timenewmessage.concat(': ', newmessage, "\n\n", existingmessageset), 

    "Status": {id: "selJIq9thWDouAmFZ"},

})

await Txttable.updateRecordAsync(config.TxtRecordID, {

    "Status": {id:"selPu9qw4ptaky21c"},

});

console.log(firstTxtrecord.getCellValueAsString("Text conversation"))

console.log('first text below')

console.log(firstTxtrecord)