How to apply multiple OR conditions to addQuery - on Relationship (sys_relationship) script

[Andrew Newman]

Hello,

On the change request form, I need to add a second related list which refers to conflicts, in this case purely scheduling conflicts (i.e. date and time, as opposed to CI conflicts) and the table we are querying is a custom table which contains a blend of Change and Release records.

I created a sys_relationship and added the section to the Change request form, and called it 'Scheduling Conflicts'. It is displaying some records as expected. However, the query I have is not applying all of the conditions. 

I could really use some help to guide me where I have gone wrong. I suspect I have misused the addCondition / addOrConditions.

The sys_relationship record:
Name: Scheduling Conflicts

Applies to table: change_request
Queries from table: u_my_table (this contains the blended list of change and release records).
Here is the script containing the conditions.


(function refineQuery(current, parent) {
current.addQuery('u_state', '!=', 'Closed Complete');
current.addQuery('u_state', '!=', 'Cancelled');
current.addQuery('u_number.number', '!=', parent.number)


current.addQuery('u_planned_start_date', '<=', parent.start_date).addCondition('u_planned_end_date', '>=', parent.start_date);
current.addOrCondition('u_planned_start_date', '>=', parent.start_date).addCondition('u_planned_end_date', '<=', parent.end_date);
current.addOrCondition('u_planned_start_date', '>=', parent.start_date).addCondition('u_planned_start_date', '<=', parent.end_date).addCondition('u_planned_end_date', '>=', parent.end_date);
current.addOrCondition('u_planned_end_date', '<=', parent.start_date).addCondition('u_planned_end_date', '>=', parent.end_date);
;

})(current, parent);

[Hitoshi Ozawa]

Is it something like below?

u_state <> 'Closed Complete' AND u_state <> 'Cancelled' AND u_number.number <> parent.number
AND
((u_planned_start_date <= parent.start_date AND u_planned_end_date >= parent.start_date)
   OR u_planned_end_date >= parent.start_date
   OR (u_planned_start_date >= parent.start_date AND u_planned_end_date <= parent.end_date)
   OR (u_planned_start_date >= parent.start_date AND u_planned_start_date <= parent.end_date AND u_planned_end_date >= parent.end_date)
   OR (u_planned_end_date <= parent.start_date AND u_planned_end_date >= parent.end_date))

[Andrew Newman]

Hello Hitoshi,

thanks for your reply. 

Actually if you remove the following line from your sql it will be correct:

OR u_planned_end_date >= parent.start_date

Do you think I need to amend my javascript logic in any way?

Thanks

Andy