Dear Community Members,

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.  

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 that have || (OR) statements in them, even when I try to load via XOG. Suggestions?

Answer

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

Answer

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

Answer

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, Jenn Rinella, Virginia DeCeglia, Atul Kunkulol, Chris Shaffer, James Gille, Ram Iyer, and the regoUniversity Conference Team for this Q/A material.