Combine formula indicator and contributing indicators in one database view

[AndersBGS]

Hi all,

I'm currently working in a reporting governance database view etc., but have come to a struggle. 

My requirement is that I would like to combine the formula indicators together with the contributing indicators in a database view or similar. Both the formula indicator and the contributing indicators is placed in the pa_indicators table, where the are combined with the system definitions relationships. 

How can I combine this in one view - any good suggestions? Custom table, database view etc?

@Mark Manders, @Dr Atul G- LNG, @Community Alums, @Tai Vu @Ankur Bawiskar - I know you're highly skilled, so maybe a good suggestion from one of you guys? 

Best regards

Anders 

[Dr Atul G- LNG]

Hi @AndersBGS 

Thanks for adding me in this. I always follow you for reporting issue. 

I am happy to support but if you provide some kind of screenshot to understand your problem statement.

[Tai Vu]

Hi @AndersBGS 

From my perspective, it may not feasible to approach with database view.

• The Formula is storing the sys_id of the contributing Indicators via the Formula [formula] field.
• The Where Clause does not accept CONTAINS operator. So we cannot have something like: formula CONTAINS the sys_id of automate indicators

I'm thinking of a custom m2m table like below.

Then having a business that leverage the function below to handle auto-insert data to the relationship table. We retrieve the contributing indicators when insert/update a formula indicator, then change the data in the table accordingly.

SNC.PAFormula.getIndicators(indicator.formula.toString());

Cheers,

Tai Vu

[AndersBGS]

Hi @Tai Vu ,

I think i get your solution. Can you try to share how you have created the business rule for the insert/update (The script and the business rule itself)?

Best regards

Anders 

[Tai Vu · Accepted solution]

Hi @AndersBGS 

Not sure if this's late for you. It's been a marathon of long days lately.

There you go!

Business Rule Before Insert/Update/Delete

Order: 9999 (last execution)

Conditions: Type is Formula

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

	if(current.operation() === 'delete'){
		deleteContributors(current.getUniqueValue(), '');
		return;
	}

    var contributors = SNC.PAFormula.getIndicators(current.formula.toString());
    deleteContributors(current.getUniqueValue(), contributors);
	if(gs.nil(contributors)){
		return;
	}
	
    contributors = contributors.split(',');
    for (var i in contributors) {
        insertContributors(current.getUniqueValue(), contributors[i]);
    }

})(current, previous);

function insertContributors(formula_indicator, contributor) {
    var gr = new GlideRecord('u_formula_contributor');
    gr.addQuery('u_formula_indicator', formula_indicator);
    gr.addQuery('u_contributing_indicator', contributor);
    gr.query();
    if (gr.hasNext()) {
        return;
    }
    gr.initialize();
    gr.setValue('u_formula_indicator', formula_indicator);
    gr.setValue('u_contributing_indicator', contributor);
    return gr.insert();
}

function deleteContributors(formula_indicator, contributors) {
    var gr = new GlideRecord('u_formula_contributor');
    gr.addQuery('u_formula_indicator', formula_indicator);
    gr.addQuery('u_contributing_indicator', 'NOT IN', contributors);
    gr.query();
    gr.deleteMultiple();
}

The reason for choosing a Before rule is primarily due to the delete case. After deletion, we're unable to retrieve associated contributor records.

You may consider having an After rule for Insert/Update and another Before rule for Delete (or Cascade configuration or some other around).

Cheers,

Tai Vu