How to update the record of a table on the basis of other table's record?

[#1]

Requirement: Need to update a particular record in a table if a certain record is inserted in other table.

Scenario: 

• There is a table X and other table is Y
• When a record is inserted in table X, then a certain record in table Y whose 'created'  time is same as 'created' time of record in table X. If this condition matches then  a field in the matched record of table Y should be updated to 'Record Inserted'

Example:

• A record is inserted in table X, whose 'created' date-time is '2022-07-22 13:58:46'.
• Now in table Y, need to filter the record that 'created' date-time is same as '2022-07-22 13:58:46'.
• Then in Table Y. one field 'Status' will be updated to 'Record Inserted' in that filtered record only.

Any idea to achieve this solution?

[Sagar Pagar · Accepted solution]

Hi,

You have to write after insert business rule on your table X.

Need to GlideRecord table Y and query with crated time same as current record and the GlideRecord then updated the status to Record inserted.

Sample script here -

var record_y_table = new GlideRecord("table_y");
record_y_table.addQuery("sys_created_on", current.sys_created_on); // query with created field
record_y_table.query();
while(record_y_table.next()){
record_y_table.status = "record_inserted";
// add other fields or comments

record_y_table.update();

}

Feel free to mark helpful & correct, if applicable.

Thanks,
Sagar Pagar

[#3]

Thanks @Sagar Pagar  it's working.