Transform Map script to validate data from another table

[ST9]

Hi All, 
I have 2 tables.. Table A with Fields- Number, Price, Address and Table B with fields- Price, Address, Group

I want to load Table A data but wanted to check if Price and Address data is already available in Table B data or not.
If yes, just wanted show warning for those record while importing and accept the data( record should be inserted into the table A)
Please help how to validate.

[Riya Verma]

Hi @ST9 ,

Hope you are doing great.

You can achieve this using steps:

1. create a BR on table A : 

(function executeRule(current, previous) {
    var tableBGr = new GlideRecord('TableB');
    tableBGr.addQuery('Price', current.Price);
    tableBGr.addQuery('Address', current.Address);
    tableBGr.query();
    
    if (tableBGr.hasNext()) {
        gs.addWarningMessage('Warning: Price and Address data already exist in Table B for this record.');
    }
})(current, previous);
​

• Import records into Table A:
  • During the import process, the "Import Script - Table A" business rule will execute for each record being inserted.
  • If a record in Table A has the same Price and Address values as any existing record in Table B, a warning message will be displayed.

[ST9]

Hi @Riya Verma : Thankyou, but i wanted to do through transform map script..can you help there?

[Riya Verma]

HI @ST9 ,

Create a transform map for Table A to define the field mappings between Table A and the import data source. try using below reference code .

// Validate if Price and Address exist in Table B
var tableB = new GlideRecord('table_b');
tableB.addQuery('Price', current.Price); // Assuming 'current' represents the current record being imported
tableB.addQuery('Address', current.Address);
tableB.query();

if (tableB.next()) {
    gs.addInfoMessage('Warning: Price and Address already exist in Table B for record ' + current.Number);
}

// Perform any additional validation or data manipulation if required

// Insert the record into Table A
current.insert();

[Manmohan K]

Hi @ST9 ,

You can use below code in Transform script to add warnings . Please make changes so that price and address field backend names are correct and match with Table B

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {

    if (JSUtil.notNil(target.price))
{
        log.warn("Price data available in Table B but Accepting value from Table A");
}
    if (JSUtil.notNil(target.address))
{
        log.warn("Address data available in Table B but Accepting value from Table A");
}

})(source, map, log, target);