BLOG: Advanced Reference Qualifier – Return Account-Based Locations (Return NULL on Empty Account)

[Sushma Pavani K]

Use Case

You have a Location reference field on a Catalog Item or Record Producer.

Requirement:

• If Account is selected → show only Locations related to that Account
• If Account is NOT selected → show NO locations
• Do not show all locations by default
• Avoid returning an empty qualifier (which exposes all records)

❌ Common Mistake

Many implementations use something like this:

javascript: current.variables.account ? 'account=' + current.variables.account : '';

Problem:

• When Account is empty, this returns ''
• An empty qualifier means no filtering
• Result: ALL locations are shown ❌

✅ Correct Approach

When Account is not selected, explicitly return a condition that matches nothing.

sys_id = NULL

✅ Final Advanced Reference Qualifier Script

javascript:current.variables.account? 'account=' + current.variables.account: 'sys_id=NULL';

How This Works?

Scenario	Returned Qualifier	Result
Account selected	account=<sys_id>	Shows only matching locations
Account not selected	sys_id=NULL	Shows no records

✔ Prevents accidental data exposure
✔ Clean user experience
✔ Works consistently in Catalog Items & Record Producers

📌 Why sys_id = NULL Is Important

• sys_id is always populated for real records
• No record ever matches sys_id = NULL
• This guarantees zero results, not a fallback list

Result:

• Users must select Account first
• Location dropdown stays empty until then
• No confusion, no wrong selections

🎯 Key Point

Never return an empty string in an advanced reference qualifier when you want “no results.”

Always return an impossible condition like: sys_id = NULL

This small change prevents big data and UX issues.