How to show list of parent records without children records

[peter_repan]

Hi all,

I have a tables with relationship 1:N. (TableA   1---->N TableB).

I want to show a list of records from TableA, for which do not exist any records in table B.

How can I do it?

My first try was through condition where "sys_id NOT IN javascript:custom_func()", but custom_func() returns hundreds of sys_ids.

This approach is not working because of URL length restriction.

I should probably find a way where the system does not put all filter conditions into URL.

Any other ideas?

Thank you

[zac]

Can you do it in Javascript?

Here's some code, don't know if they syntax is correct but this should get you what you're looking for:

var exceptionTable;

for each (var item in tableA) {

  if(item.tableBList == null) {

        exceptionTable.add(item);

  }

}

[BenPhillipsSNC]

Hi Peter,

I don't imagine this should be too hard. So these tables are a one-to-many relationship... As an example I ran the following query on the change_task table in my demo. The change_request:change_task relationship is 1-to-many.

Search for all change_task records where there is no related change_request:

https://<instance>.service-now.com/change_task_list.do?sysparm_query=change_requestISEMPTY

Does that work for what you're asking?

[peter_repan]

Hi Ben,

I'm looking on it from the opposite side.

I want to see a list of all change_requests without any change_tasks.

f.e.

https://<instance>.service-now.com/change_request_list.do?sysparm_query= ... something like "change_tasks NOT exists"

however there is a problem that it is one-to-many relationship and I cannot check on parent record if it does not have any child items.

[MGanon]

https://community.servicenow.com/community?id=community_question&sys_id=86b8c361db5cdbc01dcaf3231f9619f3