Expand my Community achievements bar.

Join us LIVE in San Francisco on November 14th for Experience Makers The Skill Exchange. Don't miss out on this free learning event!

Embedding Format into a Value Expression

Avatar

Level 1
I am trying to customize a numeric column to display "N/A" when the value is blank. For this to happen I needed to switch my value format to HTML. Now that it is HTML the numeric values in the file no longer show up at $1,234 but at 1234. Can I embed formatting into my Value Expression? Here is what I currently have. Any Help would be appreciated. displayname=2018 Proposed Tax linkedname=direct namekey=2018 Proposed Tax querysort=DE:2018 Proposed Tax textmode=true valueexpression=IF(ISBLANK({DE:2018 Proposed Tax}),"N/A",{DE:2018 Proposed Tax}) valueformat=HTML Thank you Jodie Otten Dominium Inc.
Topics

Topics help categorize Community content and increase your ability to discover relevant content.

4 Replies

Avatar

Level 6
To my knowledge you cannot insert a different type of valueformatting into the calculation but one thing you can try is: valueexpression=IF(ISBLANK({DE:2018 Proposed Tax}),"N/A",CONCAT("$",{DE:2018 Proposed Tax})) Granted this method will be missing any commas in the currency. Justin Renteria SSFCU

Avatar

Level 1
Hi Justin, Thanks for the response. This is better, I wonder if I change the Value Format back to: valueformat=customNumberAsCurrencyStringRounded Is there is anything different I can do with my Value Expression to insert my Text "N/A" into a numeric field? Thank you Jodie Otten Dominium Inc.

Avatar

Level 3

Hi Jodie, You could try using a Custom Formatting rule that says if the field = null, then display "N/A". Instead of using valueexpression, remove the column and then add the 2018 Proposed Tax column again. This time add a conditional format (see the attached image) Here is the text mode as well:

displayname=2018 Proposed Tax

linkedname=direct

namekey=2018 Proposed Tax

querysort=DE:2018 Proposed Tax

textmode=true

valuefield=2018 Proposed Tax

valueformat=customNumberAsCurrencyStringRounded

case.0.comparison.leftmethod=DE:2018 Proposed Tax

case.0.comparison.lefttext=DE:2018 Proposed Tax

case.0.comparison.righttext=

case.0.comparison.operator=isnull

case.0.comparison.operatortype=double

case.0.comparison.icon=false

case.0.comparison.truetext=N/A

styledef.case.0.comparison.leftmethod=DE:Test

styledef.case.0.comparison.lefttext=DE:Test

styledef.case.0.comparison.righttext=

styledef.case.0.comparison.operator=isnull

styledef.case.0.comparison.operatortype=double

styledef.case.0.comparison.icon=false

styledef.case.0.comparison.truetext=N/A </pre>

Let me know if that helps. Best regards, Dan Dan Perkins Moventus0690z000007ZhWdAAK.png

Avatar

Level 1
Hi Dan, Thank you for your reply. I had tried that but it didn't work. My Co-worker was able to get a work around, keeping this a text column, to put in the commas and the $. Here is what we did if anyone else is interested. She also went a step further and had it insert "No Info" for only MN states if blank. All others are "N/A" displayname=2018 Proposed Tax linkedname=direct namekey=2018 Proposed Tax querysort=DE:2018 Proposed Tax textmode=true valueexpression=IF(CONTAINS("MN",{DE:Property State}),IF(ISBLANK({DE:2018 Proposed Tax}),"No Info",IF({DE:2018 Proposed Tax}>1000000,CONCAT("$",LEFT(ROUND({DE:2018 Proposed Tax},0),LEN(ROUND({DE:2018 Proposed Tax},0))-6),",",RIGHT(LEFT(ROUND({DE:2018 Proposed Tax},0),LEN(ROUND({DE:2018 Proposed Tax},0))-3),3),',',RIGHT(ROUND({DE:2018 Proposed Tax},0),3)),IF({DE:2018 Proposed Tax}>1000,CONCAT("$",LEFT(ROUND({DE:2018 Proposed Tax},0),LEN(ROUND({DE:2018 Proposed Tax},0))-3),',',RIGHT(ROUND({DE:2018 Proposed Tax},0),3)),CONCAT("$",ROUND({DE:2018 Proposed Tax}))))),"N/A") valueformat=HTML Jodie Otten Dominium Inc.