Embedding Format into a Value Expression | Community
Skip to main content
J
JodieOt1
May 24, 2019
Question

Embedding Format into a Value Expression

  • May 24, 2019
  • 4 replies
  • 1067 views
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.
This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

4 replies

JustinRenteria2
JustinRenteria2
Level 6
May 24, 2019
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
J
JodieOt1Author
May 24, 2019
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.
D
DanPe
Level 2
May 28, 2019

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 Moventus

J
JodieOt1Author
May 28, 2019
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.
Terms & ConditionsAccessibility statement

Loading footer...