How do I filter on both parent and a child

[michaelrodway]

I have the following situation:

Employee:

-Name

-Status

...

Certificates:

-ID

-Name

-Employee (Reference to above table)

On a separate request form, we are selecting an employee to save in the form.

The default reference functionality is fine as they need to be able to select anyone, but we would like to also provide the end user with a more guided search: ie. We would like to be filter out those employees with a status of "Ready", who have a certificate with the Name "H2S". Search parameters depend on the request form.

I've been able to create a custom UI Macro with help from this post.

The easy part is querying just the employee table, the trickier part is including the cert query (is a database view needed, is this even possible)?

Does anyone have any recommendations?

[Chuck Tomasi]

Hi Michael,

This sounds like a simple list filter using dot walking.

Start with the certificate list and build in your filter the employee.status part.

https://wiki.servicenow.com/index.php?title=Dot-walking

[michaelrodway]

Yes, we've thought of that, however, I want a list of Employees, as opposed to a list of multiple certificate records.

Cert Table:

Cert 1 - Mike

Cert 1 - Chris

Cert 2 - Mike

Cert 3 - Mike

Cert 2 - Chris

If want to display employees, as opposed to the cert record, so if I search for cert name = "*Cert"), i would only want to return:

Mike

Chris

[Chuck Tomasi]

Hi Michael,

Thanks for the clarification. I thought you were just looking to put together a list. You want a reference qualifier to help filter things out for you. This is a situation where you need to build an advanced reference qualifier. You create a script that returns the query you need to do the filtering. This allows it to be parametric (dynamically driven by fields and other information) and return exactly what you want in that reference field's results.

Reference Qualifiers - ServiceNow Wiki

[Chuck Tomasi]

As a simple example, here's records on task_sla that reference all kinds of tasks. I'm just interested in the ones that have a stage=breached (from the task_sla) table, but want to filter on the "Task type" field from the upstream task table. I can dot-walk to Task.Task Type.