query with 3 tables (BR)

[christiancardos]

Hello everybody,

I am trying to use now these three tables to perform the following calculations, only clarifying that already worked, but now, due to the fact that i made a many-to-many relationship and add that related list so now you must apply from that table calculations.

I'll explain a little better how it works:

The code makes the value of "u_estimacion_de_hrs", and bring it to the subtract with "u_hrs_disponibles_del_periodo" and put it in the variable "u_hrs_restantes".

this is the code:

When to run: After

Insert

Update

condition: estimación de Hrs changes.

This BR is located in the table of incidents

Here I have no problem.

--------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------------

and now   I have a new related list because I did a new relationship many to many, the idea of having this relation is because i need to have the same incident in different periods.

That relationship was not possible because only he could have an incident at a period.

The code should be the value of "u_estimacion_de_hrs", and bring it to the subtract with "u_hrs_disponibles_del_periodo" and put it in the variable "u_hrs_restantes".

Only that now the table is different, the fields that are relational in that list with the exception of the incident number were brought by dot walking.

Could you help me with the BR to work now with this new table, thank you for your help.

Regards,

CC

dvp, I appeal to you, you always have a good answer for this type of unknowns.

I appreciate your help.

thanks for advance.

[dvp · Accepted solution]

Hello Christian,

Write a business rule on m2m table with the below script

In this script you need to update the field name on mem table

var prj = new GlideRecord('u_periodo_de_servicio');
prj.addQuery('sys_id', current.YOUR_PERIOD_FIELD_NAME_in_M2M table);
prj.query();
while(prj.next()){
 var rp = new GlideRecord('incident');
 rp.addQuery('u_periodo', current.YOUR_PERIOD_FIELD_NAME_in_M2M table);
 rp.query();
   var count = 0;
 while(rp.next()){
 
   count += rp.u_estimation_de_hrs
 
 }
prj.u_hrs_restantes = prj.u_hrs_disponibles_del_periodo - count;
 prj.update();
}

[christiancardos]

thank youdvp

I have been doing a lot of testing and it has been wonderful.

Thanks again you're amazing.

[christiancardos]

Hi   dvp,

I've been doing some tests and found a problem, I tried to fix it but even I don't get it, Could you help me?

I explained the problem below:

I have two incidents, these incidents have "estimated hours" each.

The code make that the value of "u_estimacion_de_hrs", and bring it to the subtract with "u_hrs_disponibles_del_periodo" and put it in the variable "u_hrs_restantes"

this is the script:

• var prj = new GlideRecord('u_periodo_de_servicio');  
• prj.addQuery('sys_id', current.u_periodo_de_servicio);  
• prj.query();  
• while(prj.next()){  
• var rp = new GlideRecord('incident');  
• rp.addQuery('u_periodo', current.u_periodo_de_servicio);  
• rp.query();  
•   var count = 0;  
• while(rp.next()){  
•    
•   count += rp.u_estimacion_de_hrs   ;
•    
• }  
• prj.u_hrs_restantes = prj.u_hrs_disponibles_del_periodo - count ;
• prj.update();  
• }

The problem is the following:

I have the incident number INC0013727 in this contract   "contrato 1", but when the script is run

the variable "count" is equal to "18" because it's the sum of the two incidents mentioned above, because they have a value assigned to each, in the variable estimated hours., The problem is that should not make the sum so, You should only add the incident or incidents that relate at the time.

You should only add the incident or incidents that relate at the time and that appear in the list of each contract separately, not as now the operation is carried out.

In the example should only appear to have been used 9 hrs.

could you help me please.

Thanks for advance.

[dvp]

can you tell us on which table the business rule is written?

also are INC 13727 and 13725 both related to contracto 1 at some point or no??