Details about this thread
  • State Not Answered
  • Replies 5 replies
  • Subscribers 310 subscribers
  • Views 2115 views
  • Users 0 members are here
Related content from around SugarClub

Calculated Field if value empty

Cheryl Alderson

I'm trying to create a calculated date field that will calculate differently based on if the target field is empty or not, so:

if the $build_commencement_date_c date field is empty, 

addDays(build_stage_revised_start_c,$build_stage_days_slab_)

if $build_commencement_date_c date field is not empty

addDays($build_commencement_date_c,$build_stage_days_slab_c)

After reviewing responses to previous posts, I have pieced together:

ifElse(equal($build_commencement_date_c,""),addDays(build_stage_revised_start_c,$build_stage_days_slab_c),

ifElse(greaterThan($build_commencement_date_c,0),addDays($build_commencement_date_c,$build_stage_days_slab_c),0))

but it's not working, and is possibly completely off track!

Can anyone help??

  • 0

    Hi

    Perhaps you can update the formula this way:

    ifElse(equal($build_commencement_date_c,""),addDays(build_stage_revised_start_c,$build_stage_days_slab_c),

    ifElse(not(equal($build_commencement_date_c,"")),addDays($build_commencement_date_c,$build_stage_days_slab_c),0))

    André Lopes
    Lampada Global
    Skype: andre.lampada
  • 0 in reply to André Lopes

    Hi Andre,

    Thanks for your guidance!

    Updating the formula to your suggested version returns the error:

      Invalid Formula: The formula must be of typedate

    The formula is contained in a date field.

    The fields in the formula are Data Type:

    • Date ($build_commencement_date_c)
    • Date (build_stage_revised_start_c)
    • Integer ($build_stage_days_slab_c)

    Do you now why this error is occuring?

  • 0 in reply to Cheryl Alderson

    You are right ! Because of that you can not implement a regular sugarLogic but instead a Dependency SetValue. Inside a sugarLogic you need to return the field type according to the field being calculated, but inside a Dependency SetValue you can set the triggers which will calculate, so you can only calculate if the appropriate fields are set.

    Regards

    André Lopes
    Lampada Global
    Skype: andre.lampada
  • 0

    Hi , if the field is not empty, we should be careful in using addDays on top of the same field, since it might keep incrementing the number after every save.

    Here is a simple formula which sets your build_commencement_date based on whether it's empty or not. If it's empty, it will addDays to build_stage_revised_start, if not, will just leave the value as-is:

    ifElse(equal($build_commencement_date_c,""),addDays($build_stage_revised_start_c,$build_stage_days_slab_c),$build_commencement_date_c)

    To explain this a little better:

    ifElse(
      equal($build_commencement_date_c,""), -- Criteria
      addDays($build_stage_revised_start_c,$build_stage_days_slab_c), -- Date is empty
      $build_commencement_date_c -- Date is not empty, don't alter it
    )

    Date value cannot be "zero", so you cannot set that as a value.

  • 0 in reply to Neeraja P

    Hi ,

    Thanks for the reply.  Your suggested formula returns only one calculated value, however I needed a further calculation when date is not empty.

    Sugar support provided the following formula, which works perfectly.

    ifElse(equal($sent_to_site_c,""),
    addDays($build_stage_revised_sent_c,$build_stage_days_start_c),
    addDays($sent_to_site_c,$build_stage_days_start_c))

    However your explanation assisted with another formula with was causing me grief - so thanks :-)