---
title: Clarity PPM | Inescapable JEXL and Dependent Lookups
description: Create a dynamic dependent lookup in Clarity PPM (CA PPM), based on a multi-select field, plus more in today's Q&A.
image: https://communities.ca.com/servlet/JiveServlet/downloadImage/102-231174178-1-118217/LWR_SM_Community_header.jpg
---

[![Rego-Logo_Notag-Color-350](https://blog.regoconsulting.com/hs-fs/hubfs/Rego%20Guide%20Images/Rego-Logo_Notag-Color-350.png?width=224&name=Rego-Logo_Notag-Color-350.png)](http://regoconsulting.com)

- [Home](http://regoconsulting.com)

[![](https://communities.ca.com/servlet/JiveServlet/downloadImage/102-231174178-1-118217/LWR_SM_Community_header.jpg)](https://communities.ca.com/servlet/JiveServlet/downloadImage/102-231174178-1-118217/LWR_SM_Community_header.jpg)

# Clarity PPM | Inescapable JEXL and Dependent Lookups

Could you use some advice on how to make your JEXL expressions inescapable? Or would you like to know how to create a dynamic dependent lookup, based on a multi-select field?

We're here to help.  And remember, there's just a few days left to sign up for [Clarity PPM (CA PPM) training at regoUniversity.](https://regouniversity.com/)

## Learn with Rego | Q&A

1. Or (**||**) Statements in GEL for 15.1

2. Dynamic Dependent Lookup Based on a Multi-Select Field

3. Converting in and out of UTC time

Please *feel free to comment on any alternative answers* you've found. At Rego, we always love your input. 

**1. I'm getting an error trying to save GEL scripts with || (OR) statements in them, even when I try to load via XOG. Suggestions?**

In JEXL expressions I typically use **or** instead of **||**.  For example:

 **${true or false}** instead of **${true || false}**

In the same line I also use **and** instead of **&&** or any derivative.

 **${true and false}** instead of **${true && false}**

 Also in this same thread I use…

 **${1 gt 0}** instead of **${1 > 0}**

**${1 ge 0}**

**${1 lt 0}**

**${1 le 0}**

Etc… 

The JEXL operators are cleaner and less likely to be escaped. Also along these same lines…

Even though **attribute=’${variable.method(“stringLiteral”)}’** is allowed (the double and single quotes are inverted), it's safer to construct like this: **attribute=”${variable.method(‘stringLiteral’)}** because the double quotes won’t ever convert to **".**

 

**2. Is it possible to have a dynamic dependent lookup based on a multi-select field? **

**Field A – Multi Lookup**

**Apples**

**Oranges**

**Grapes**

**Field B – Dynamic Dependent lookup, based on the value of Field A**

 Fuji, Pink Lady, Red Delicious

You can change the lookup to look at the **odf\_multi\_valued\_lookups** table and pass the project id instead of the other attribute. The **only drawback** to this method is that the user has to press **save** in between fields, in order for it to work properly.

 

3. 

**When manipulating today’s date (sysdate) the log\_timezone\_diff\_fct function returns the correct offset for the user ID passed in . . .  and can convert in and out of UTC. However, it's not offsetting the Daylight Savings Time conversion. **

> (FYI, the reason I’m having to do it manually is because I’m using an NSQL query’s web service to show dates in an HTML portlet.)

 

Try the function **from\_tz** and the standard UTC time zone from the **cmn\_sec\_users** table.

Example:

SELECT

  CAST(from\_tz(CAST(sysdate-30 AS TIMESTAMP), 'UTC') at time zone u.timezone AS DATE) start\_date

FROM cmn\_sec\_users u

where u.user\_name = 'brimmasch'

If you look at the time with **sysdate – 30** and **sysdate**, the offset is -7 and -6, as it should be. (In my real query **sysdate** will not be **sysdate**.)

**Thank You------------------------------ **

A special thank you to [Ben Rimmasch,](https://www.linkedin.com/in/rimmasch/) [Jenn Rinella,](https://www.linkedin.com/in/rinellajenn/) [Virginia DeCeglia,](https://www.linkedin.com/in/virginiadeceglia/) [Atul Kunkulol,](https://www.linkedin.com/in/atulkunkulol/) [Chris Shaffer,](https://www.linkedin.com/in/clshaffer/) [James Gille,](https://www.linkedin.com/in/james-gille-4897a6a/) [Ram Iyer,](https://www.linkedin.com/in/ramakrishnaniyer/) and the [regoUniversity Conference Team](https://regouniversity.com/) for this Q/A material. 

 By [Camille Pack](https://blog.regoconsulting.com/author/camille-pack)|April 10, 2017

#### Share on Social Media

- [Tweet](https://twitter.com/share)

### About the Author: [Camille Pack](https://blog.regoconsulting.com/author/camille-pack)

![Camille Pack](https://blog.regoconsulting.com/hubfs/profile%20camille200-2.jpg)

Camille Pack has been in marketing for over a decade and started her career as a college composition instructor during graduate school. Technical writing lends itself well to mastery, and in her time at Rego, Camille offered clients product support, configured environments, and served as both a project manager for an internal reporting group and a business analyst for a large external client. Camille holds an MA in Literature and Writing, and a BS in Biology.

### Related Posts

![4-27_LWR_Blog_760x230 (1).jpg](https://blog.regoconsulting.com/hubfs/Blog%20Images/2017%20-%20May/4-27_LWR_Blog_760x230%20(1).jpg)

[Permalink](https://blog.regoconsulting.com/dwh-cost-budget-plans)

#### [Clarity PPM | DWH Cost & Budget Plans](https://blog.regoconsulting.com/dwh-cost-budget-plans)

May 01, 2017 | [0 Comments](https://blog.regoconsulting.com/dwh-cost-budget-plans#comments-listing)

![](https://blog.regoconsulting.com/hubfs/Blog%20Images/2017%20-%20April/Project_access_LWR_Blog_760x230.jpg)

[Permalink](https://blog.regoconsulting.com/how-much-project-access-is-too-much)

#### [Clarity PPM | How Much Project Access is Too Much?](https://blog.regoconsulting.com/how-much-project-access-is-too-much)

April 17, 2017 | [0 Comments](https://blog.regoconsulting.com/how-much-project-access-is-too-much#comments-listing)

![learn with rego orange.jpg](https://blog.regoconsulting.com/hubfs/Blog%20Images/learn%20with%20rego%20orange.jpg)

[Permalink](https://blog.regoconsulting.com/how-to-unlock-the-clarity-role-lookup)

#### [Clarity PPM | How to Unlock the Clarity Role Lookup](https://blog.regoconsulting.com/how-to-unlock-the-clarity-role-lookup)

March 17, 2017 | [0 Comments](https://blog.regoconsulting.com/how-to-unlock-the-clarity-role-lookup#comments-listing)

![Estimate_Allocations_Blog_863x430.jpg](https://blog.regoconsulting.com/hubfs/Blog%20Images/Estimate%20Allocations/Estimate_Allocations_Blog_863x430.jpg)

[Permalink](https://blog.regoconsulting.com/where-did-estimate-from-allocations-go)

#### [Clarity PPM | Where Did ESTIMATE from ALLOCATIONS Go?](https://blog.regoconsulting.com/where-did-estimate-from-allocations-go)

February 24, 2017 | [0 Comments](https://blog.regoconsulting.com/where-did-estimate-from-allocations-go#comments-listing)

![16_08_LWR_05_Blog_863x430.jpg](https://blog.regoconsulting.com/hubfs/Blog%20Images/16_08_LWR_05_Blog_863x430.jpg)

[Permalink](https://blog.regoconsulting.com/early-registration-for-ca-ppm-clarity-training-extended)

#### [Clarity PPM | Early Registration for Training Extended](https://blog.regoconsulting.com/early-registration-for-ca-ppm-clarity-training-extended)

January 13, 2017 | [0 Comments](https://blog.regoconsulting.com/early-registration-for-ca-ppm-clarity-training-extended#comments-listing)

#### [Clarity PPM | Learn with Rego: Display Lookup Values in MSP + More](https://blog.regoconsulting.com/query-links-cmn_custom_scripts-table-to-process-name-find-a-calendar-with-sql-different-project-and-team-actual-hours-apply-a-negative-amount-to-task-actuals-and-display-)

July 15, 2016 | [0 Comments](https://blog.regoconsulting.com/query-links-cmn_custom_scripts-table-to-process-name-find-a-calendar-with-sql-different-project-and-team-actual-hours-apply-a-negative-amount-to-task-actuals-and-display-#comments-listing)

[![Rego Home](https://no-cache.hubspot.com/cta/default/2652075/655865ab-5c17-4a7d-8626-9917913749d3.png)](https://cta-redirect.hubspot.com/cta/redirect/2652075/655865ab-5c17-4a7d-8626-9917913749d3)

[![Contact Us](https://no-cache.hubspot.com/cta/default/2652075/80445cd5-d730-4608-a09c-dcaaeff582b4.png)](https://cta-redirect.hubspot.com/cta/redirect/2652075/80445cd5-d730-4608-a09c-dcaaeff582b4)

### Subscribe to Email Updates

[![Share on Facebook](https://static.hubspot.com/final/img/common/icons/social/facebook-24x24.png)](http://www.facebook.com/share.php?u=https%3A%2F%2Fblog.regoconsulting.com%2Finescapable-jexl-and-dependent-lookups%3Futm_medium%3Dsocial%26utm_source%3Dfacebook) [![Share on LinkedIn](https://static.hubspot.com/final/img/common/icons/social/linkedin-24x24.png)](http://www.linkedin.com/shareArticle?mini=true&url=https%3A%2F%2Fblog.regoconsulting.com%2Finescapable-jexl-and-dependent-lookups%3Futm_medium%3Dsocial%26utm_source%3Dlinkedin) [![Share on Twitter](https://static.hubspot.com/final/img/common/icons/social/twitter-24x24.png)](https://twitter.com/intent/tweet?original_referer=https%3A%2F%2Fblog.regoconsulting.com%2Finescapable-jexl-and-dependent-lookups%3Futm_medium%3Dsocial%26utm_source%3Dtwitter&url=https%3A%2F%2Fblog.regoconsulting.com%2Finescapable-jexl-and-dependent-lookups%3Futm_medium%3Dsocial%26utm_source%3Dtwitter&source=tweetbutton&text=Clarity%20PPM%20%7C%C2%A0Inescapable%20JEXL%20and%20Dependent%20Lookups)

### Recent Rego Articles

### Sign up for our Weekly Newsletter

![rego-logo-white](https://blog.regoconsulting.com/hs-fs/hubfs/rego-logo-white.png?width=205&name=rego-logo-white.png)

As the leading PPM, Work Management, and Agile services provider, we have helped hundreds of organizations achieve a higher return on their software investment.

### CONTACT US

Questions? We can help.  
[info@regoconsulting.com](mailto:info@regoconsulting.com)  
888.813.0444

### Stay up-to-date

Get the latest news in industry best practices, thought leadership, and software updates.

### Solution Capabilities

[Clarity PPM](https://regoconsulting.com/clarity-ppm/)  
[Rally](https://regoconsulting.com/rally/)  
[Apptio](https://regoconsulting.com/apptio/)  
[Sciforma](https://regoconsulting.com/sciforma/)  
[Jira](https://regoconsulting.com/jira/)  
[Microsoft | SharePoint](https://regoconsulting.com/microsoft/)  
[Smartsheet](https://regoconsulting.com/smartsheet/)  
[Meisterplan](https://regoconsulting.com/meisterplan-itdesign/)

### Quick Links

[Our Company](https://regoconsulting.com/our-company/)  
[RegoUniversity](https://www.regouniversity.com/)  
[RegoXchange](http://regoxchange.com/)  
[Careers](https://regoconsulting.com/careers/)

 

Copyright 2022 Rego Consulting Corporation

[Linkedin](https://www.linkedin.com/company/rego-consulting?trk=biz-companies-cym)[Facebook](https://www.facebook.com/RegoConsulting/)[Youtube](https://www.youtube.com/channel/UCdA5xEiDrX--y2nVX2wC1UA?view_as=public)[Twitter](https://twitter.com/regoconsulting)

[Privacy Policy](https://regoconsulting.com/privacy-policy/)

![](https://dc.ads.linkedin.com/collect/?pid=14053&fmt=gif)