Possible to add text to calculation field?

[Graham Gourlay]

I use Memento for my sales records, and have several calculation fields to work out profits, costs and margins. I would like to be able to display text (mainly £ and % symbols) with the results.
For example:

#{net profit}/#{sale price}*100

This will give me my profit margin, but instead of it displaying "26.2" I want it to display "26.2%". Similarly with other calculations I would like to display a currency sign before the number.

Is this possible?

Thanks!

[JohnO]

There is a uservoice suggestion "Add display format options for some data fields" Which you can comment and vote on.

In the comments section there is a request similar to yours. 

There is another option which I hoped would work, and that is using the concat(,) function to concatenate two strings. It works fine with text fields, but not with literal strings.

For example concat(#{textfield1},#{textfield2}) will concatenate the contents of the two fields, but concat('abc','xyz')  gives the result " invalid double 'xxxyyy' ".

So it performs the operation correctly, but displays an error.

Unfortunately I've used all my votes in uservoice, so I can't suggest this feature.

[JohnO]

Just an update to my last post, I went to uservoice and deleted some of my votes so I could post this suggestion Make the concat function work with literal strings

You can vote for it is you wish.

[Graham Gourlay]

Hi JohnO,

I'd be lying if I said I completely understood everything you've said(!), but what I'm taking from it is "it's not possible to suffix a calculation with a % sign".

I've voted on your link.

Thanks anyway.

[JohnO]

OK, this should do it:

You need a calculate field to hold the result. 

It can all be done in one line, but I'll break it down to make it easier to follow.

1. Your calculation #{net profit}/#{sale price}*100 needs to be converted to a string by using the numToStr() function like so: numToStr(#{net profit}/#{sale price}*100 )

2. This is used as the first entry in the concat(,) function, and the literal string '%' in the second part.

The final calculation looks like this: concat(numToStr(#{net profit}/#{sale price}*100 ),'%')

and set to String result 

Note the comma between the two parts of the concat(,) function, and the single quotes around the literal string '%'

Let me know how it goes.

[Graham Gourlay]

That's great JohnO, you're a genius! It worked perfectly, though it messes up other calculation fields that rely on the output of this one... Which is a pain! That said, as long as no other field replies on it, it works great. Thank you so much :)

Is there any way to reduce the number of decimal points in the string result? Some percentages are coming back as 28.84563256874538%, which is a little long!

No worries if not. And thanks again for helping!

Graham

[Graham Gourlay]

Edit:

I figured it out, using the round() function. It's now rounding the number (which I'm happy with) but it's still putting a decimal point in... e.g. 32.0% 24.0% 62.0%

Thoughts please?

[dovi...@gmail.com]

I am somewhat new to memento. I use access extensively and used datavis smartlist to go and finally moved to a phone.
I don't get the concatination. in my choices of result, I have no string options. I am trying to get lastname + ', ' + firstname but anything that I do even with the concat(,) function without the + doesn't work because I have no string options.

[Bill Crews]

First, starting I think with mobile edition 4.1.0, we started having an Advanced Parameter for Integer fields called Unit of Measure. The value remains an integer, which is useful for subsequent calculation. But the value displays on-screen with the unit of measure, like " in" for inches. However, though there are many categories of units, none appears to have the noble percent sign -- no category for pure magnitude.

Second, I still occasionally use a Calculation field, but it is easy to run out of steam one way or another, and it might (?) not be enhanced much going forward, now that we have JavaScript fields. The concatenation issues you're grappling with aren't there in JavaScript, so you can just say...

((field("net profit") / field("sale price")) * 100).toString() + " %"

[Bill Crews]

> in my choices of result, I have no string options.

When editing a library and adding a Calculation field, returning the result as a string is right there; you can't miss it.

> I am trying to get lastname + ', ' + firstname but anything that I do even with the concat(,) function without the + doesn't work because I have no string options.

I don't understand at all. Keep in mind that you could try a JavaScript field instead.

[Mister O]

Thanks for the JS solution Bill. I've been struggling to make the calculation field dance to my tune when appending text with several failed attempts The JS method scored a triumph on my second attempt..Result

[Logan (DarkComet)]

You are a life saver. Thank you Bill.

To everyone reading in the future... Don't use the "Calculation" field except for pure numbers.

Even then, comparing the two,in my case, when trying to divide two numbers, my normal and sale price, to get a percentage difference:
- Calculation: returns just the number (ex: 49.978765...) takes between 5-10 milliseconds
- JavaScript:Takes about double the time. If you add "%" or any other text it makes it a little longer.

Do keep in mind, this is in milliseconds(ms), so it's going extremely fast either way. My recommendation, use the JavaScript field for everything unless you need to quickly make a number which Calculations provides shortcuts to all of its available functions. Besides that, there are no other benefits from what I can tell.

The nice thing with JavaScript is it is universal and has a large wealth of reference, syntax guides, help and tutorials. It is also forgiving when it comes to " " blank spaces.

My original problem was I wanted to get fancy and add a % symbol to the end of my number and round to the nearest hundredths place. Doing it with Calculations was possible (pain to set up though) and no matter what I tried I could not get it to stop outputting "For Input String:" before my output value because like mentioned by another person you have to convert your number to a string and then add your '%' string to the end of it.

If it helps here is my code for the Calculation field (entering lines are allowed, not spaces though:

'About≈'+
numToStr
(
(
round(
#{normal_original price:}/#{sale price:}
*100
)
/100
)
*100
)
+'%'

Here is my code for JavaScript which produces the same result without having "For Input String:" in the front of it:

"About ≈ " + ((field("Normal/Original Price:") / field("Sale Price:")) * 100).toFixed(2).toString() + "%"

Just looking at the two, you can tell JavaScript is a lot more forgiving. You don't have to jump though hoops to return your value to have two decimal places (that's what the *100/100 is for in the first code). To call the fields you want to import, JavaScript is a lot more natural and easier to remember.

Hopefully this helps and is written in a way that is easy to understand whether you are a basic user or advanced user wanting to use Calculations or Javascript.

~ Logan (aka DarkComet)