Jump to content

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
Link to comment
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?

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

 

Link to comment
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.

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 by comment
Link to 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
Link to comment
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

 

test dollar range.fmp12

Edited by Markpelleymounter
add detail
Link to comment
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".

Link to comment
Share on other sites

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

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.