Filtering values of a custom lookup field (non-system lookup)

Hello MANAF,
 

Have you checked the following page in our documentatio), which shows the availability and scope of expressions for the different types of fields:

Expressions and Filters in assyst - assyst25R2Wiki

A Lookup Filter Expression can be applied to Single Select Lookup and Multi Select Lookup fields (System or Custom fields).

Within a Lookup Filter Expression, you can refer to a Single Select Lookup (system or custom field).

I hope this helps.
 

Best regards,

Caroline

Hi Manaf,

Could you clarify your scenario : If the affected user is a technician not a Contact User, or a Contact User???
Affected user is a contact user in assyst. What is the criteria you are using here? You need to have an option, a setting on the Contact User form to base your filter on.

Few examples are available on the documentation : Creating Lookup Filters for System Fields - assyst25R2Wiki

Best Regards,

 Hi ​@ManafOmari01 

Having read your question and follow-up clarifications, I think I have an idea of what you’re trying to achieve. While this isn’t exactly what you asked for, it might serve as a workaround.

To start off, here’s my understanding of your issue:

1. You keep using contactUser, by this I’m assuming you mean an end user (someone outside your organization) and by technicians you mean all or some employees in your organizaition (with access to both assyst Self service portal and Assyst portal).
2. You want to filter the options presented in the service offering but have both end users and internal users utilize the same service offering.
   • You’ve tried using custom lookups but are open to other methods to get the desired outcome.
3. You want the end user to only see a select few options in the lookup, while if a technician is filling out the same form, all options should be presented.
   • If the event form is populated by a technician on behalf of an end user, you still want the lookup to only show the limited selection.

I’m sorry if this is an incorrect assessment of the issue, but the rest of this answer will be based on these assumptions. Note that these assumptions are not hard cut-offs, and there might still be ways to get the desired outcome, though this exact example and idea might not work “out of the box.”

Some groundwork

There don’t seem to be any examples on the wiki showing how (or if it’s even possible) to do a lookup filter expression to restrict options from a custom lookup. Nor do lookup filter expressions currently (to my knowledge) support outer conditional statements.

However, there might be a set of workarounds which, used together, could get you what you need.

Workaround Part 1 – Items as lookups

Instead of using a custom lookup, you could use a system lookup field set for items, which then can be used as an alternative selection method in the from. 

1. Create a product in your CMDB called LOOKUPS AS ITEMS
2. Populate that product with items called WIFI, LOAD BALANCER, and CREATE SERVER.
3. Add a single-select system lookup field set to Item on the form used by your service offering.

This would enable you to write a normal lookup filter expression to retrieve the list of items instead of custom lookup values, sidestepping the lack of custom lookup value support.

Note that these items do not need Incident Logging,  Incident Cause, etc. to be enabled for this lookup method to work, and these would thus not have to “clutter” the event logging or close action item lookups. 

Workaround Part 2 – If statements using “UNION ALL” in HQL

To limit the options presented depending on the affected user’s alias, you could use a lookup filter expression that consists of two parts:

• One part that is always included (default options for both end users and technicians).
• A second part with conditional logic baked into the query itself.

This part here is a modified version of an idea presented by ​​@morevalueit as a reply to a question about if statements: 

Example based on your requirement:

First part (default option):

select Item.id 
from item as Item 
where Item.product.shortCode = 'LOOKUPS AS ITEMS' and Item.shortCode = 'WIFI'

Second part (technician-specific options):

select Item.id 
from item as Item 
where Item.product.shortCode = 'LOOKUPS AS ITEMS' 
and (Item.shortCode = 'CREATE SERVER' OR Item.shortCode = 'LOAD BALANCER') 
and @*@$new.affectedUser.userAlias.servDept.shortCode@*@ LIKE 'TECHNICIAN%'

The last part of this second query can be extended by adding more OR Item.shortCode = ... segments inside the parentheses to include additional items for technicians.

Finally, combine the two queries using UNION for the full lookup filter expression:

select Item.id 
from item as Item 
where Item.product.shortCode = 'LOOKUPS AS ITEMS' 
and Item.shortCode = 'WIFI' 
union 
select Item.id 
from item as Item 
where Item.product.shortCode = 'LOOKUPS AS ITEMS' 
and (Item.shortCode = 'CREATE SERVER' OR Item.shortCode = 'LOAD BALANCER') 
and @*@$new.affectedUser.userAlias.servDept.shortCode@*@ LIKE 'TECHNICIAN%'

You could also apply similar logic for end users, but then you’d need to maintain both sides of the query if defaults change later.

These ideas put together you could do something like this:
 

This example was created using assyst 24R2, but it should work just as well in 25R1 and 25R2. I’m not entirely sure if there are any performance implications when using this UNION method. If anyone knows of potential issues, considerations to be made or has suggestions for improvements, I’d be more than happy to be corrected.

Hope this helps point you in the right direction! If you run into any issues or want to explore alternative approaches, feel free to reach out I’d be happy to brainstorm further.

Cheers,
Richard