how to connect two tables.

[Aditya02]

- Create a table called 'incident_temp' with below two fields

      Incident Number

      Short Description

- Create a business rule 'Remove Temp Incident  Rule' , This should execute only whenever any record removed from 'Incident' table.

- Query the incident number from 'incident_temp' table based on removed Incident Number record from 'Incident' table

- Remove the corresponding incident record from 'Incident_temp' table

anyone help me to perform this.

[Dr Atul G- LNG]

Hi @Aditya02 

- Create a table called 'incident_temp' with below two fields

      Incident Number

      Short Description

Atul: Create a new tables , if you need only 2 fields, don't extend.

Create Field - Short Description - Type - String

https://youtu.be/a7A-wuWirIY

- Create a business rule 'Remove Temp Incident  Rule' , This should execute only whenever any record removed from 'Incident' table.

- Query the incident number from 'incident_temp' table based on removed Incident Number record from 'Incident' table

- Remove the corresponding incident record from 'Incident_temp' table

Atul: What is exact use case? Look like 2-3 things mixed here.

[Amit Verma]

Hi @Aditya02 

Just to be clear with the requirement, you want to delete the record on incident_temp table corresponding to the record on incident table and this needs to be done via a Business rule on incident table. Can you please confirm ?

Thanks & Regards

Amit Verma

[Vishal Savajia1]

Step 1 : Execute Below Script in background script.  

cpTable('incident', 'u_incident_temp', true);
 
function cpTable(strOldTable, strNewTable, bCopyIndexes) {
    var tu = new TableUtils(strNewTable);
    var bNewTableAlreadyExists = tu.tableExists();
    if (bNewTableAlreadyExists) {
        gs.print("WARNING: Target Table " + strNewTable + " already exists!  Please choose a new target table name");
    } else {
        var gr = new GlideRecord(strOldTable);
        gr.initialize();
        var td = GlideTableDescriptor.get(strOldTable);
        var tdNewTable = new SNC.TableRotationBootstrap(strNewTable, gr.getLabel());
        var dbo = new GlideRecord("sys_db_object");
        dbo.addEncodedQuery("super_classISNOTEMPTY^name=" + strOldTable);
        dbo.setLimit(1);
        dbo.query();
        if (dbo.next()) {
            tdNewTable.setExtends(dbo.super_class.name + '');
        }
        tdNewTable.setFields(gr);
        tdNewTable.copyAttributes(td);
        tdNewTable.create();
        if (bCopyIndexes) {
            tdNewTable.copyIndexes(strOldTable, strNewTable);
        }
    }
}

Step 2 : Inactive remaining Fields

Step 3 : Create Your required Business rule. 

benefit of doing this if in future if you want to add more columns from table you can do it and all roles and rules will be along with your newly customized table 

[SunilKumar_P · Accepted solution]

Hi @Aditya02,  Create a custom table (Incident Temp) which contains two field 'Incident Number' & 'Short Description'. Its not suggested to delete the records instead if its possible, mark it Inactive but if your use case demands to remove the record from Incident Temp table based on the Incident table, you can try the after delete Business Rule.

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

    var grIncTemp = new GlideRecord('u_incident_temp');
    grIncTemp.addQuery('u_incident_number', current.number);
    grIncTemp.setLimit(1);
    grIncTemp.query();
    if (grIncTemp.next()) {
        grIncTemp.deleteRecord();
    }

})(current, previous);

Regards,

Sunil