How to get the INTERSECTION (AND logic) for a Request Type field based on a Multi-select Sub-Categor

Bảo Phạm Gia

I'm designing an IT Service Catalog using JSM Assets and I've run into a hard limit with AQL cascading logic on the Customer Portal.

I want users to select multiple Sub Categories (Systems) and then have the Request Type field only show the options that are common (the exact intersection) across ALL selected Sub Categories.

My current CMDB Schema:

I have 4 standard Object Types that just act as generic lists (No direct references between them):

 

• Service Catalog (e.g., Software, Hardware)

• Sub Category (System names e.g., Jira, ERP)

• Request Type (Generic actions e.g., "Report Bug", "Request Access")

• Service Item (Specific tasks)

The Mapping Object:

I have a 5th Object Type called IT Service Catalog.

Every single record (object) in this table contains exact 1-to-1-to-1-to-1 mapping references to the 4 objects above. This defines valid combinations. (e.g., Record 1: Software + Jira + Request Access + Grant Admin)

My Custom Fields (Type = Assets object field) on the Portal:

1. Service Catalog (Single-select)

2. Sub Category (Multi-select) - AQL Filter: "Service Catalog is Asset object field" = ${Service Catalog}

3. Request Type (Single-select)

 

The Problem:

Because customfield (Sub Category) is a multi-select array

If a user selects System A (has "Report Bug", "Request Access") and System B (only has "Report Bug"), the Request Type dropdown will show BOTH "Report Bug" and "Request Access".

I need it to act as ONLY shows "Report Bug" (the intersection).

My question:

Is there any Native AQL syntax, or a CMDB structural workaround utilizing this Mapping Table approach, to achieve a strict intersection dynamically on the Customer Portal

Germán Morales

Hi @Bảo Phạm Gia , the short answer is that native AQL can't produce a dynamic intersection here. Expanding a multi-select with ${customfield_XXXXX.label${0}} drops the values into IN (...), and IN is OR, so object HAVING inboundReferences(... IN (...)) returns Request Types tied to ANY selected Sub Category. AQL has no "matches all" or count operator to turn that into an AND across a variable list, so the union is unavoidable with the all-values placeholder.

The only native way to get AND is to reference each selection by index and chain the clauses, on the Request Type field's Filter work scope, where XXXXX is your Sub Category field id:

• object HAVING inboundReferences(objectType = "IT Service Catalog" AND "Sub Category" = ${customfield_XXXXX.label${1}}) AND object HAVING inboundReferences(objectType = "IT Service Catalog" AND "Sub Category" = ${customfield_XXXXX.label${2}})

That is a real intersection, but only for a fixed count: an unused index resolves to nothing, so it works reliably only when the user selects exactly as many Sub Categories as you wrote clauses for. There is no dynamic form of it.

For a portal that stays robust the fix is structural, not syntactic. Make Sub Category single-select so the Service Catalog to Sub Category to Request Type cascade resolves through your mapping table with no intersection needed, or let users pick the mapping object directly since each IT Service Catalog record already encodes one valid combination. If free multi-select with strict AND is a hard requirement, that logic has to live in JSM Forms conditional logic or a dynamic-forms Marketplace app rather than in the Assets field AQL.

Indexed vs all-values placeholders are documented here: Configure an Assets object custom field.