Maximo

Expand all | Collapse all

Can formulas return null?

  • 1.  Can formulas return null?

    Posted Mon December 23, 2019 09:20 AM
    In Maximo 7.6.1.1,

    I have an attribute formula on a persistent field called WORKORDER.X. The field type is decimal, length is 18, and scale is 10.

    The formula is meant to do this:

    If not null, use WOSERVICEADDRESS.LONGITUDEX
    Else, if not null, use ASSET.X
    Else, if not null, use LOCATION.X

    This is the expression I've come up with:
    NVL(SERVICEADDRESS$LongitudeX, NVL(ASSET$X, NVL(LOCATION$X,0) ) )

    Question:
    If all of the search-values end up being null, is there a way to return null, rather than 0?

    I've tried just typing the word null, but get an error:
    BMXAA9399E - This formula is invalid because the following attribute or token is
    invalid: null. Specify a valid attribute and try again.

    I've also tried "", but that returns 0.


  • 2.  RE: Can formulas return null?

    Posted Mon January 06, 2020 07:52 AM
    Edited by Serge Gagnon Mon January 06, 2020 07:52 AM
    Did you try "IF(isnull$WORKORDER.X...." instead of NVL ? You can use a condition as well. If it does not work, sometime it's easier to use an automation skip instead of a formula. Your case is easy to code in a script.

    ------------------------------
    Serge Gagnon
    STI Maintenance
    Jonquiere QC
    ------------------------------



  • 3.  RE: Can formulas return null?

    Posted Mon January 06, 2020 01:32 PM
    Edited by User1971 Mon January 06, 2020 01:34 PM
    Hi Serge. Good idea. Thanks.

    I tried IF instead of NVL, but unfortunately, I still have the same problem. I can't return null:

    IF(1=1,1,null)

    BMXAA9399E - This formula is invalid because the following attribute or token is invalid: null. Specify a valid attribute and try again.

    And returning the value of a field that is known to be null (as a workaround) produces an error too (IF(1=2,2,X)).
    Also, double-quotes returns zero, not null (not what I'm looking for). 


    ------------------------------
    Thanks
    ------------------------------



  • 4.  RE: Can formulas return null?

    Posted Sun April 19, 2020 11:51 PM
    Hi,
    I have the same issue. I also need to return "null" in a similar formula. Does anyone have a solution to this?

    ......................
    Nikolay Stoimenov
    "Dripping water hollows out stone, not through force but through persistence."
    https://nikolay100.com/


    ------------------------------
    Nikolay Stoimenov
    ------------------------------



  • 5.  RE: Can formulas return null?

    Posted Mon April 20, 2020 09:50 AM
    Edited by User1971 Mon April 20, 2020 09:51 AM

    I talked to a developer who works closely with the team who created Maximo formulas.

    He said this:

    "There is no formula today that returns null.  However, we are creating one for the next release.  In the meantime, you can create an automation script that can then be set as a formula and use that (no launch point). The script body would simply be: evalresult=None if the script is called SETNULL then you can go to DB Config, select configure formula functions and create a function as a script type.  You could then use this like if(purchaseprice>5000,SETNULL(),5)."

    Unfortunately, I couldn't get that automation script working -- but it was likely my error, not Maximo's.

    Related:

    https://stackoverflow.com/questions/59888911/maximo-formula-that-uses-a-custom-formula-function-automation-script

    https://stackoverflow.com/questions/59447159/can-maximo-formulas-return-null



    ------------------------------
    Thanks
    ------------------------------



  • 6.  RE: Can formulas return null?

    Posted 6 days ago
    Edited by User1971 6 days ago
    @Nikolay Stoimenov

    From testing, this is what I've found in Maximo 7.6.1.2:

    • Non-persistent fields can handle nulls without issue. I used this to return a null: NULLVALUE( ) 
    • But persistent fields can't seem to handle nulls. I tried using this to return a null: NULLVALUE( )

    Error:

    BMXAA3761E - The event has failed. Ensure that the event is registered correctly. See the log file in the APP HOME directory for more details about the error.

    null

    Word has it that this issue has been fixed in 7.6.1.3 and 8.1.

    Or maybe you're using non-persistent fields, in which case NULLVALUE( ) might help you in your current version.

    Cheers.


  • 7.  RE: Can formulas return null?

    Posted 6 days ago
      |   view attached

    @Nikolay Stoimenov

    Here is a Word doc that shows the testing I did (attached).


    Conclusion:

    Formulas can return null to non-persistent fields.

    But, formulas can't return null to persistent fields.


    I would love to be wrong about this though.