Clarity PPM1

Expand all | Collapse all

How to create a process to check if a field is null

Jump to Best Answer
  • 1.  How to create a process to check if a field is null

    Posted 10-04-2018 03:04 PM

    I need to create a process that checks if two string fields are null or not. 

     

    If it's null then:

    • the process sends an Action Item letting the requestor know the field(s) is null and  the process will end
    • The requestor will start the same process again once they put data in the fields.

     

    If it's not null, then:

    • the process will continue to the next step.

     

    Since CA does not support a condition if a field is null, I'm not sure how else to do it without having a gel script of some sort.  If the answer if a gel script then please share or point me in the right direction on how to create this gel script.  I don't know how to create gel scripts yet but am willing to give it a try.

     

    Sidenote:  I have the same requirement if there is not a Team assigned to the idea but I don't think this is possible since the process is linked to the Idea object and not the Team object.  Please correct me if I'm wrong.

     

    Thanks,

    Garrett



  • 2.  Re: How to create a process to check if a field is null
    Best Answer

    Posted 10-05-2018 11:06 PM

    I've never user it, but the OOTB example process:

     Issue Review and Escalation - Step Check Project Management Office has a condition of:

    ( project.thisProject.obj_stakeholder2 ==  null )

    as well as

    ( Project.thisProject Project Management Office != null ) 

     

    You may want to review to see if you can use this format. 

     

    Note, you won't be able to 'generate' it using the GUI section of the 'Condition Builder', and you will have to cut/paste required code into the 'Expression' section.

     

    In your case it would be something like:

    ( project.thisProject.attr1 ==  null ) &&  ( project.thisProject.attr2 ==  null ) --> Action Item Step

    else -- Next Step of Process

     

    Note, obj_stakeholder2 is a look-up but hoping that the above will also work on a string attribute.



  • 3.  Re: How to create a process to check if a field is null

    Posted 10-08-2018 12:50 PM

    Thanks for the reply Roland, however the null used in the OOTB examples is not supported by PPM anymore.  According to support, the OOTB processes were grandfathered in, but it will error when I use the null condition in a process I create.

     

    Garrett



  • 4.  Re: How to create a process to check if a field is null

    Posted 10-10-2018 01:24 PM

    Hi Garrett, 

     

    You can use null for some types of attributes (like text) and it will work.  But it won't work and never has worked (that I am aware of) for any type of lookup.

     

    One workaround that I can think of would be to add the following value (or something like it) to your lookup:

    -- Not Selected --

     

    Then you could set that as the default value and use that selection as the criteria for your start or post condition.

     

    I hope this helps.

     

    Sincerely yours,

     

    Jeanne Gaskill

    Senior Support Engineer

    CA Technologies, Inc.

    214-473-1819



  • 5.  Re: How to create a process to check if a field is null

    Posted 10-10-2018 01:31 PM

    Jeanne!

     

    Thanks for your response.  That helps out a lot because the fields I need to check are text fields.  

     

    Do you have any thoughts on how an idea process can check to see if there are any resources assigned to the Team tab?

     

    Garrett



  • 6.  Re: How to create a process to check if a field is null

    Posted 10-10-2018 01:50 PM

    Hi Garrett,

     

    I believe you would need to use a gel script with SQL queries to determine that.

     

    Jeanne



  • 7.  Re: How to create a process to check if a field is null

    Posted 10-10-2018 01:54 PM

    Ok. That was my thought as well.  Any idea or advice on what that gel script might look like to get me pointed in the right direction?

     

    Thanks again!



  • 8.  Re: How to create a process to check if a field is null

    Posted 10-10-2018 04:15 PM

    Hi Garrett,

     

    I don't have any sample gel scripts for you.  Someone else on communities may be able to give you more specific help that way.

     

    However the query to find out how many team members there are should be fairly simple.  The team members are stored in the prteam table just like they are in projects.  

     

    So something like this would tell you how many team members there are for an idea.

     

    select count(1) from prteam where id = 'your_idea_internal_id'

     

    where your_idea_internal id is the 5 million number you find in the url on the properties table or the id in the inv_investments or inv_ideas table.

     

    I hope this helps.

     

    Sincerely yours,

     

    Jeanne Gaskill



  • 9.  Re: How to create a process to check if a field is null

    Posted 10-11-2018 11:04 AM

    select count(*) from prteam where prprojectid = 'your_idea_internal_id'



  • 10.  Re: How to create a process to check if a field is null

    Posted 10-11-2018 11:08 AM

    Thanks for this Nick.  Is this all that I would need to put in the gel script?



  • 11.  Re: How to create a process to check if a field is null

    Posted 10-11-2018 11:16 AM

    That is just a query and not a gel script; just correcting the earlier example that keyed off the wrong (and non-existing) field.

     

    A thing about the GEL scripts in the process is that the work they do are generally unseen by the rest of the process (what happens in GEL scripts, stays in GEL scripts).  Meaning, if your process has step transition conditions or other system actions, they cannot use the calculations determined within the GEL script as those variables and results are only visible to other GEL scripts on the process.

     

    So if you go that route, you most likely end up replacing your process flow with one that is created in the script(s) instead, including for sending the action items or for whether to execute any further scripts or not.

     

    There are many examples of GEL scripts running queries if you search the communities for 'sql:query' (the tag that runs them in the script) to get an idea of how they are created and handled, but there's no simple undertaking like creating a condition expression outside of the script.



  • 12.  Re: How to create a process to check if a field is null

    Posted 10-11-2018 11:55 AM

    Let me make sure I understand what you're saying. 

     

    I cannot have an idea process that does the following:

     

    Step 1 = gel script checks if a Team is added to the idea

     

    Post-Condition 1 = If No Team then go to Step 2a

    Post-Condition 2 = If Team added then go to Step 2b

     

    Step 2a:  Send AI to requestor there is no Team

    Step 2b:  Send AI to Idea Review Team for approval

     

     

    If the above statement is true, then could I create a gel script that:

    1. Checks if there are team members

    2. Sets a field to "Yes" or "No" depending on if there are team members

    Then I can have Post conditions based off if the field is "Yes" or "No"



  • 13.  Re: How to create a process to check if a field is null

    Posted 10-11-2018 12:14 PM

    Hi Garrett,

     

    Please accept my apologies.  I meant to put in prprojectid and got sidetracked.

     

    In order to accomplish that, you would probably have to create a boolean attribute or a lookup that would indicate whether or not there were team members.  Once you run the query you would have to use xog to set that attribute to checked or unchecked or yes or no depending on how you decide to do this.  Then you would be able to use the above logic.

     

    Jeanne



  • 14.  Re: How to create a process to check if a field is null

    Posted 10-11-2018 12:23 PM

    Thanks for the clarification.

     

    Can I have a xog within an SQL query or do the two need to be separate?  I'm assuming separate because the query is sql and xog is xml.  If so, then how would I have the query and xog work with eachother.  



  • 15.  Re: How to create a process to check if a field is null

    Posted 10-11-2018 12:27 PM

    Actually, I think I just answered my own question.  I just looked at a sample gel script in the communities and it has sql and xog within it.  So the answer is yes the sql and xog can live in the same gel script. Right?



  • 16.  Re: How to create a process to check if a field is null

    Posted 10-11-2018 12:35 PM

    Yes, definitely, and a very typical use case.



  • 17.  Re: How to create a process to check if a field is null

    Posted 10-11-2018 01:22 PM

    Thanks!



  • 18.  Re: How to create a process to check if a field is null

     
    Posted 10-16-2018 07:41 AM

    yes..you can write a xog and as per result set you can form a xog in same script with necessary for loop if needed to iterate in sql output



  • 19.  Re: How to create a process to check if a field is null

    Posted 10-25-2018 11:18 AM

    Thanks Alekhya.



  • 20.  Re: How to create a process to check if a field is null

    Posted 10-11-2018 11:43 AM

    Consider LetzNav - it does many things regarding training, instruction, creation of related materials.  But one other thing it does is allow one to build validation rules for fields and present help/error messages to the user in real-time, at the point of entry.

     

    We're looking at this app - looking to get it approved for 2019 budget.



  • 21.  Re: How to create a process to check if a field is null

    Posted 10-11-2018 01:27 PM

    Thanks Dale.  This looks like a similar idea to CAPA but can do more.



  • 22.  Re: How to create a process to check if a field is null

    Posted 10-11-2018 02:17 PM

    Yes – very similar – but cost is much lower as it doesn’t depend on any expensive content from Oracle.  Also, one can use LetzNav on any web-based app, whether one has Clarity or not.

     

    Another difference is that the license structure is app based – one license per app, unlimited users.  Want to use it on another app?  Fine – buy another license.  This might get expensive if one has many apps that one wants to apply this too – but maybe one can negotiate a volume deal?  I haven’t asked.

     

    Dale