Skip to main content

I’m wondering if anyone has had any success using an If statement in the Lookup Filter Expression. 

I’d like to have a dynamic Item B field based on a value of a custom field. 

This is where I’ve got to so far: 

IF($new.W("SSL-CORP-DEVICE").shortCode = "LAPTOPS",select item.id from item as item where item.id in (select item.id from item as item inner join item.itemUsers as itemUsers where itemUsers.user.shortCode = @*@$new.affectedUser.shortCode@*@) and item.id in (select Item.id from item as Item where Item.product.productClass.shortCode = @*@$new.W("SSL-CORP-DEVICE").shortCode@*@ and Item.itemStatus.shortCode !='DISPOSED',select Item.id from item as Item where Item.product.productClass.shortCode = @*@$new.W("SSL-CORP-DEVICE").shortCode@*@ and Item.itemStatus.shortCode !='DISPOSED'))

My aim is to have a custom field with the shortcode of SSL-CORP-DEVICE be a custom lookup of Laptops, Desktops and Kiosk. If Laptops is chosen, to provide a list of the items in the CMDB which are linked to the affected user where the status is not disposed. If Laptops is not chosen, then just provide a list from their product where the status is not disposed. 

The 2 sections of code after the IF statement work as expected, which leads me to one of 2 conclusions. IF statements are not possible in a lookup filter expression or that they are, but not on system fields where driven by custom fields. 

As far as i know, ther is no possibility for IF-statements in the expression. In addition to your code: There are some “)” missing in the first select...

What you can try, is to build a “UNION ALL” SQL, that collects all item.id’s of the two selects together, but is filtered with a join to productClass.shortCode = ‘LAPTOP’ in the first select and productClass.shortCode != ‘LAPTOP’ in the second select.

It should work as a implicit IF-statement.

Reply


Terms & ConditionsCookie settings