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

Development and integrations Scripting app
#1

I’m trying to do the following:

filter records in the table by a cell number AND by whether a cell says “voicemail” or “text message” - so I want to find records that match the cell number and where the VMorText cell is “text message”. Cell type is computed (problem 1)

After that - find the record that was created first in the resulting array (problem 2)

Part of the script below

// 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');

// define search area as Ticket table
let Txtsearcharea = await Txttable.selectRecordsAsync()

// list records that match cell number - problem 1
let matchingTxtRecords = Txtsearcharea.records.filter(cellnumber => {
return cellnumber.getCellValueAsString(Txtsearchfield).includes(config.TxtCellnumber)   
})

// try to select first record from array - problem 2
let firstTxtrecord = matchingTxtRecords[0];

problem 2 I don’t even know how to start - I wondered if [0] would always be the first but it seems to change

Can anyone provide any hints toward a solution? Sorry this is quite open ended!

#2

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.

#3

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)
#4

Notice that you’re never returning a “true” value, so you will always get an empty array

Instead, try

let cellNumberMatches = cellnumber.getCellValueAsString(Txtsearchfield).includes(config.TxtCellnumber)
let typeMatches = cellnumber.getCellValueAsString(Typesearchfield).includes(textstring) 
return cellNumberMatches && typeMatches

This will return true only when both conditions are true.

#5

matchingTxtrecords is an array of records. Each record contains its own field data, even though the field data isn’t displayed with console.log. You get the field data the same way you have been before, with getCellValue() or getCellValueAsString()

If you would like additional scripting coaching where you could show your data in a private meeting, I offer that service. The link is in my profile.