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.

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}


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




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.



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.



  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------------------------------ 

By Camille Pack|April 10, 2017

About the Author: Camille Pack

Camille Pack

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.

Rego Home
Contact Us

Subscribe to Email Updates


Recent Rego Articles

Sign up for our Weekly Newsletter