Clarity PPM1

Expand all | Collapse all

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

Jump to Best Answer
  • 1.  Not Working - Help: GEL Script to Update Date on Custom Sub-object to Today's Date

    Posted 08-28-2019 01:57 PM
    Edited by Matt Guercio 08-28-2019 01:57 PM
    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>


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

    Posted 09-09-2019 11:37 AM
    Bump, appreciate any help, thank you.


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

    Posted 09-10-2019 07:51 AM
    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



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

    Posted 09-10-2019 01:10 PM
    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


  • 5.  RE: Not Working - Help: GEL Script to Update Date on Custom Sub-object to Today's Date
    Best Answer

    Posted 09-11-2019 10:06 AM
    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))


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

    Posted 09-11-2019 01:57 PM
    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!