Studio Text Field with Calculated Formula to use: 'ifelse' AND 'concatenate'

Hello,  not a show stopper here but just something that would be a nicer result on screen for users... and a bit more dynamic.
To save space I am pulling in a few different fields into this one, like a summary, that will be visible in our Case module.

I already have this working:

concat($warrantystatus_c," - ",related($s_serialnumber_cases_1,"status")," "," (Dealer: ",$dealersoldto_c,") ",)

That Field is then like this on the layout:
Warranty Status and Dealer
VALID - Sold (Dealer: name of the Dealer we sold it to)

But, If the dealer is blank we have a lone  ,   and there is another field I would like to pull in IF it is not empty called warranty warnings.
I could just append it to this field as is but I would also like to have maybe the Pipe | or similar to separate each field from the next, but not have | | | | if the fields are blank, like my lone comma.

I think it would be something like this in normal speak, though whatever I try in my formula must be wrong:

If warranty warning is empty, concat these,
else concat these.

OR nesting my 'if'

If warranty warning is empty, concat these,
If dealer sold to is empty & warranty warning is empty concat these.
Else concat these.

If you get my gist?

My latest attempt was this:

ifElse(greaterThan(strlen(related($s_serialnumber_cases_1,"warrantywarnings"))),0),
concat($warrantystatus_c,"-",related($s_serialnumber_cases_1,"status"),"Sold to ",$dealersoldto_c,
concat($warrantystatus_c,"-",related($s_serialnumber_cases_1,"status"),"Sold to ",$dealersoldto_c,"WARRANTY WARNING:",related($s_serialnumber_cases_1,"warrantywarnings"))

  1. So is there any limit with use of Concat ?
  2. Can I nest my if's ??
  3. Do I just have a blunder in my syntax ?
  4. Would Case be an option in a formula ?

Thank you for any guidance which stops my merry-go-round!

  • INFO - a previous similar post and conversation is here and may be a good backrground for anyone looking at using concat:

    sugarclub.sugarcrm.com/.../3454

  • In a performance perspective I would create a custom field which gets value of related field:

    warrantywarnings_c : related($s_serialnumber_cases_1,"warrantywarnings")

    This way you can implement an easier sugarLogic function:

    ifElse(
    not(equal($warrantywarnings_c, "")),
    concat($warrantystatus_c,"-",$warrantywarnings_c,"Sold to ",$dealersoldto_c),
    concat($warrantystatus_c,"-",$warrantywarnings_c,"Sold to ",$dealersoldto_c,"WARRANTY WARNING:",$warrantywarnings_c)
    )

    André Lopes
    Lampada Global
    Skype: andre.lampada
  • I agree with André - but in addition, you may be interested to note that the newest version of Sugar (being released in a few days) finally introduces the long awaited strReplace SugarLogic function. This increases the value of the formula engine many times over - and solves problems like this (as you can run a replacement over the output to remove the lone comma, instead of having to check for whether the field exists).

    FYI too!

  • Once again Andres: thank you  :-)   I actually used the suggested new custom field idea (rather than related) and just added that to the layout with a 'visible if' formula...  and will then build out the ifelse statement as you explained but will be adding a another new field into that - one which will only ever be 3 characters long unlike the warranty warning. 

    So you spurred on some extra features here, thanks again... Luke.

  • Oooo OK, that's a nice new feature Adam (excited emoji needed here!)  Thank you for the heads up.

  • No worries - but credit to our fab product team Slight smile