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 January 14, 201610 yr by enquirerfm
make it clearer

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?

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 January 14, 201610 yr by comment

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;"$$$$$"]
)

 

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.

How about simply:

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 January 14, 201610 yr by comment

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 January 14, 201610 yr by enquirerfm
Clarification

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

 

test dollar range.fmp12

Edited January 14, 201610 yr by Markpelleymounter
add detail

Thanks, sorry I'm still using 8.5 !

 

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".

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 $$

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?

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