How to create a flow designer or BR to update all records of the same RITM number

[Peter Williams]

good day everyone,

i have this custom table that i created that gets written to via a flow designer. Basically what happens when people fills in an MRV those rows are entered in as separte records onto the custom table.

ex. an MRV has three rows in a same RITM number like RITM123456

in the custom table the RITM number for the three records will show as RITM123456

i also have a transaction status there with New, Send and Wait

what i like to create is either a flow or BR (i am not picky as long as it works) that when all the three records in the custom table with the same RITM number is set to Wait, it will automatically set the status of the three records to Send.

i need help as i have tried alot of stuff that doesnt work

[Brad Bowman · Accepted solution]

A before or after Insert/Update Business Rule on the custom table seems like a good approach.  You'll have to determine if there's a timing reason to not use before, but let's start with that just to avoid an unnecessary update - unless you need to see an audit history where the record was created or changed to Wait, then another update where it changed to Send, then you would want to use after.  In any event, since you have the Same RITM column you can add that to the FIlter conditions, so the script only runs when there's more than one.  The script will be something like this - depending on your field names and values...

(function executeRule(current, previous /*null when async*/) {
	var recordGr = new GlideRecord('table_name');
	recordGr.addQuery('ritm_number', current.ritm_number);
	recordGr.addQuery('transaction_status', '!=', 'wait');
	recordGr.query();
	if (!recordGr.hasNext()) {
		var record2Gr = new GlideRecord('table_name');
		record2Gr.addQuery('ritm_number', current.ritm_number);
		record2Gr.query();
		while (record2Gr.next()){
			record2Gr.setValue('transaction_status', 'send');
			record2Gr.update();
		}
	}
})(current, previous);

[Peter Williams]

Thank you for your guidance, i was able to use your code and expand on it, here was my find iteration 

(function executeRule(current, previous /*null when async*/) {

    var ritmNumber = current.u_ritm_number; // Get the RITM number from the current record

    if (!ritmNumber) {

        current.u_wait_count = 0; // Set to 0 if there's no RITM number

        return;

    }

    // Query to count records with the same RITM number and status 'Waiting on Other Vouchers'

    var grTotal = new GlideRecord('u_se_forms_finance_tranactions'); // Replace with your custom table name

    grTotal.addQuery('u_ritm_number', ritmNumber);

    grTotal.addQuery('u_transaction_status', 'Waiting on Other Vouchers');

    grTotal.query();

    var totalCount = 0; // Start count at 0

    while (grTotal.next()) {

        totalCount++;

    }

    // Update all records with the same RITM number to have the same total count

    var grUpdate = new GlideRecord('u_se_forms_finance_tranactions'); // Replace with your custom table name

    grUpdate.addQuery('u_ritm_number', ritmNumber);

    grTotal.addQuery('u_transaction_status', 'Waiting on Other Vouchers');

    grUpdate.query();

    while (grUpdate.next()) {

        grUpdate.u_wait_count = totalCount; // Set the same total count for all matching records

        grUpdate.update(); // Update each record

        gs.log('Updated u_wait_count to: ' + totalCount + ' for record: ' + grUpdate.sys_id);

    }

    var checkRecords = new GlideRecord('u_se_forms_finance_tranactions');

    checkRecords.addQuery('u_ritm_number', current.u_ritm_number);

    checkRecords.query();

    while (checkRecords.next()) {

        if (checkRecords.u_wait_count == checkRecords.u_same_ritm) {

            checkRecords.u_transaction_status = 'Ready to Send to CMS';

            checkRecords.update();

            gs.log('Updated u_transaction_status to "Ready to Send to CMS" for record: ' + checkRecords.sys_id);

        }

    }

})(current, previous);

[Nuno Oliveira]

Hi @Peter Williams ,

I would say to use the BR option it's easier, however, you can also achieve this with flow designer.

In the flow designer option, you can either create an action with a script to achieve that or have a for loop in the flow with an update record action. Both ways should work, although the BR option is easier.

The code below is just a sample, adapt to your needs.

var gr = new GlideRecord('table_name');
gr.addEncodedQuery('transaction_status=!wait^ritm_number=' + current.ritm_number);
gr.query();
if (!gr.hasNext()) {
    var updateGR = new GlideRecord('table_name');
    updateGR.addQuery('ritm_number', current.ritm_number);
    updateGR.query();
    updateGR.setValue('transaction_status', 'send');
    updateGR.updateMultiple();
}

 Best regards