Not Working - Help: GEL Script to Update Date on Custom Sub-object to Today's Date

[Matthew Guercio]

Hello -
I'll preface with I'm a new learner of GEL Script with no experience. I've attempted to write a simple script that updates a date attribute on a custom (Project) sub-object called Risk Assessment.

The script completes successfully but does not actually update the date attribute to today's date. Can anyone please help? Thanks in advance.

Date Attribute:

ID Attribute:

Database:

GEL Script:
<?xml version="1.0" encoding="UTF-8"?>
<gel:script xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:core="jelly:core"
xmlns:gel="jelly.com.niku.union.gel.GELTagLibrary"
xmlns:file="jelly.com.niku.union.gel.FileTagLibrary"
xmlns:soap=" jelly.com.niku.union.gel.SOAPTagLibrary"
xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:sql="jelly.sql"
xmlns:xog="http://www.niku.com/xog"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi=" http://www.w3.org/2001/XMLSchema-instance">
<!-- Code goes here -->
<gel:setDataSource dbId="niku" var="clarity"/>
<sql:update dataSource="${clarity}" escapeText="0" var="updateCnt">
<![CDATA[
UPDATE odf_ca_wtfc_risk_assess odf
SET odf.wtfc_assessdate = sysdate
WHERE odf.code = ?
]]>
<sql:param value="${gel_objectInstanceId}"/>
</sql:update>
</gel:script>

[Matthew Guercio]

Bump, appreciate any help, thank you.

[Albert Vincentpaul]

Hello Matthew,

There are two corrections needed in your script

1. In the header <gel:script ...> make sure everything is jelly:(colon) instead of jelly.(dot). For example, xmlns:sql="jelly:sql" instead of xmlns:sql="jelly.sql"
2. gel_objectInstanceId equates to id column in the table, not the code. This is database sequence based id, not the one shown on the UI. So, the where condition should be odf.id = ?

Hope this helps.

Thanks,
Albert

[Matthew Guercio]

Hello @Albert Vincentpaul -

Thank you very much for your input - these modifications worked; the custom script updates the date field to today's date.  However, unfortunately the direct database update (SQL) approach does not trigger a second process to start.  I've read guides that indicate XOG Updates will trigger processes to start.

Do you know how I could convert my custom script to utilize XOG instead of a direct database SQL update?  Would greatly appreciate any insight you can share, and thank you in advance for your time/effort.

Matt

[David Morton]

Hi - there are some simple (??) examples in the documents in the Gel.zip file attached to the old FAQ thread ; CA Clarity General Discussion - FAQs

So for your example, I would suggest that the best way to work out what XOG message (i.e. what XML you need) to perform the update is the "XOG Out" your custom object instance (prior to an update), make the update (in the UI or via SQL), then "xog-out" the custom object instance again and look at the differences (it should just be the one custom-attribute that has changed, but doing that should help you understand how XOG "works" I think.

Then your GEL script needs to construct an XML message which looks like* that second-xogged-out message and the GEL required to pass that to the application should be found in the Gel.zip documents.

(* - in practice though the actual XML message needed would be much smaller than this - only a few lines in fact - the XOG out will contain a lot of junk/extra information you don't need. You can experiment with the pure XML message and a XOG-client (so ignoring GEL) as to what the minimum set of fields you would need to provide, its likely just to be the instance identifiers and the one field you want to update but that will depend on how your object is set up (mandatory fields etc))

[Matthew Guercio]

Thanks for your insight David, much appreciated.  This is a lot to take in as I also have not used XOG before, but I'll see what I can learn from the links provided.

Any other input is welcomed!