Formula to display field format 1

[shannonlp]

We have an application where users enter values and also enter the format that the value is supposed to appear. The purpose of the application is to allow users to enter data that will feed some tables which will then be reported against.

For example:

Table1.kpi_value has a value of -27.10
Table1.kpi_format has a value of ##.# bps

I need to create a formula that will display the value as -27.1 bps

Also, the value entered in the kpi_format field will be unique to that KPI. Specifically, it will not always be ##.# bps. It could be ##,###,### or some other variation.

I’m not even sure if this is possible but any help would be much appreciated.

Thanks,
Shannon

 

[briangriffin]

Assumptions, which are probably not entirely accurate:

1. You don't mind the result being text.

2. If the format has a unit included, such as bps, it will always be separated from the format string with a space - ##.# bps, not ##.#bps. I'm hoping that your app regulates what can and can't be entered as a format string.

Anyway, if those are true then consider something like this:

if instr({@Format}," ") = 0 then totext({@Value},{@Format})

else

totext({Value},left({Format},instr({Format}," ") - 1)) + " "
+ right({Format},len({Format}) - instr({Format}," "))

 

[shannonlp]

Hi,

Thank you for your response. I’m not sure if I gave enough information in my post.

I am going to need to create a formula field that does the following:

If format = ### bps then current value = 123 bps
else
if format = ##,### then current value = 12,3
else
if format = ###.## then current value = 123.00
etc….

There is no issue with it being text.

 

[briangriffin]

Then the formula provided should work for you - have you tried it, and gotten bad results?

Replace the {Format} or {@Format} with your format database field, and replace the {Value} or {@Value} with your value database field.

 

[shannonlp]

Haven't tried it yet but thanks. If I have more questions I'll post again.

 

[shannonlp]

Here is what I entered in the Formula Editor:

if instr({kpi_mgmt_dashbd.kpi_format}," ") = 0 then totext({kpi_mgmt_dashbd_curr.kpi_curr_val},{kpi_mgmt_dashbd.kpi_format})
else
totext({kpi_mgmt_dashbd_curr.kpi_curr_val},left({kpi_mgmt_dashbd.kpi_format},instr({kpi_mgmt_dashbd.kpi_format}," ") - 1)) + " "
+ right({kpi_mgmt_dashbd.kpi_format},len({kpi_mgmt_dashbd.kpi_format}) - instr({kpi_mgmt_dashbd.kpi_format}," "))

No errors appeared when I checked it.

When I place this formula in the details section of the report I get the following error:

‘Bad number format string'

kpi_mgmt_dashbd.kpi_format is a string and kpi_mgmt_dashbd_curr.kpi_curr_val is a number. Not sure if that matters.

Any ideas? Thanks again…

 

[briangriffin]

Try filtering the report to a single record where the format field value is either ##.# or ##.# bps

If that works, then you need to identify the format values that are not working - if the database field for the format is not a valid format string within Crystal then you'll get an error.

Once I see the format values that are not working then perhaps we can tweak the formula to accomodate them. Do you have a listing of all possible format values?

 

[shannonlp]

The values for format are:

###,###,###,###,###
##,###.00
##,# bps
##,##
##,## bps
##.0 bps
##.00 bps
$###,###.00

In the select expert I set the filter so that

{kpi_mgmt_dashbd.kpi_format} in ["###,###,###,###,###", "##,###.00", "##.# bps", "##.##", "##.## bps", "##.0 bps", "##.00 bps", "$###,###.00"]

And now the formula field is working. If this takes care of it then MUCH appreciated. You’ve been a big help.

Thanks again –

Shannon