# Replacing values with symbols

This topic is 2356 days old. Please don't post here. Open a new topic instead.

## Recommended Posts

I am disappointed I can't seem to solve such a simple problem. I want to take the value in a field (say, field A), copy it to a new field (field B), check which range of numbers it is in and then convert this number into a symbol. 10-100 = \$; 100 - 250 = \$\$; 250 - 350 = \$\$\$ and so on. Thus if A = \$110. Field B will show \$\$. If A=\$275, B becomes \$\$\$ etc.  I tried copying A into a global temporary field (Field C) and then making this work on that but it doesn't work either. It seems much more complicated than I first imagined. I suppose I have to convert the number to text first too?

Edited by enquirerfm
make it clearer
##### Share on other sites

is your input text that includes the "\$" or just a number formatted to show the currency on the layout?

I don't see where you would need to copy the value from field A... can you post the script that you currently have so we can start from that?

##### Share on other sites

Is there a logic in this conversion, or are the ranges arbitrary? If (as it seems), they are arbitrary, you could calculate the corresponding symbol using the Case() function (where the range boundaries would be hard-coded), or look it up from a table via a relationship.

Edited by comment
##### Share on other sites

Initially I was trying to use this and replace the numbers accordingly:

```Substitute(BOOK dollar signs;
[summary_av_converted_price_USD<100;"\$"];[summary_av_converted_price_USD > 100   ≤  250;"\$\$"];[summary_av_converted_price_USD > 250   ≤ 350;"\$\$\$"];[summary_av_converted_price_USD > 350   ≤  550;"\$\$\$\$"];[summary_av_converted_price_USD > 555;"\$\$\$\$\$"]
)```

##### Share on other sites

I can't make heads or tails of your attempted calculation. Why are there two input fields: 'BOOK dollar signs' and 'summary_av_converted_price_USD'? In any case, I don't think Substitute() is the correct approach to take here.

```Case (
Price < 100 ; "\$" ;
Price ≤ 250 ; "\$\$" ;
Price ≤ 350 ; "\$\$\$" ;
Price ≤ 550 ; "\$\$\$\$" ;
"\$\$\$\$\$"
)```

where 'Price' is the field with the given number (Field A in your original post). I don't see why this couldn't be a simple calculation field (with Text as the result type) and why would there be any need to copy anything, or use a global temporary field, or any other elaborate schemes like that.

Edited by comment
##### Share on other sites

Thanks. Not forgetting this is a SUMMARY field on which this needs to work I didn't want to convert the value in the original field but rather represent it graphically too in another field. Not sure if it changes much but the field on which this needs to work is an summary average. Doing the above in a separate field, say called 'dollar signs' (in other words replacing 'Price' above) doesn't work.

Edited by enquirerfm
Clarification
##### Share on other sites

Comment is correct in that a calc field using a case statement is the most effective and easiest way.  Attached is a very small sample using the case statements to replace a dollar amount, and and summaried (as average) dollar amount as you wanted.

Have a look at the field definitions via Manage

Also, not fogetting that the calc needs to be set to unstorred in storage options.

hth

Edited by Markpelleymounter
##### Share on other sites

Thanks, sorry I'm still using 8.5 !

##### Share on other sites

1 hour ago, enquirerfm said:

Not sure if it changes much but the field on which this needs to work is an summary average.

It shouldn't matter - unless you want to use a sub-summary value.

1 hour ago, enquirerfm said:

Doing the above in a separate field, say called 'dollar signs' (in other words replacing 'Price' above) doesn't work.

Describe "doesn't work".

##### Share on other sites

My report looks like this and it's not picking out the individual average values but I suppose a global average. Even in the detailed report it is the same - it is all \$\$

##### Share on other sites

5 minutes ago, enquirerfm said:

My report looks like this

That doesn't tell much. Could you post a screenshot of the same layout in Layout mode?

##### Share on other sites

Thanks I have managed to get this to work in a related table.

##### Share on other sites

This topic is 2356 days old. Please don't post here. Open a new topic instead.

## Create an account

Register a new account