Post

10 followers Follow
7
Avatar

Access the currency of a money field (in calculation)

A money field has a currency and a amount. The currency can be fixed when the template is defined, or the user chooses it when entering the amount. When the field is displayed in a view, it appears with currency and amount, eg "GBP 123". When the field is accessed in a calculation, it appears as just the amount, eg "123". How can a calculation access the currency?

Ben Rubinstein

Official comment

Avatar

You can actually add a currency but it requires a script:

In order for this to work you must prioritise the type of the summing you want to perform, using the '(' and ')' symbols.

When you use the "$" + Value1 the expression is evaluated to a string and afterwards this expression is concatenated as a string to Value2 and so on with Value3.

A simple solution would be to use the following expression:
"$" + ( Value1 + Value2 + Value3)
The first addition is a simple string concatenation while what is between the '(' and ')' symbols is a mathematical one.

Does this help?

Sara Høeg Højlund-Rasmussen

Please sign in to leave a comment.

16 comments

2
Avatar

I've the same problem, can somebody answer this please ?

Would allow us to have a 'hack' for currency conversion.

David Alvarez 2 votes
0
Avatar

Any news on this? Did someone figure it out? It should be possible to access it separately, since it gets exported separately to Excel...

Reinhardt Smit 0 votes
0
Avatar

This would be crucial to do currency conversions in Podio (although of course even better would be if Podio could convert currencies and sum them correctly automatically).

Kees van Bochove 0 votes
0
Avatar

I would need that answer, too. I know Globiflow can do it. Why can"t i find a way in Podio?

Marten Rauschenberg 0 votes
0
Avatar

Hi Sara,

Thanks for responding, but it doesn't help.  I'm not trying to add a currency symbol onto a string to display; I'm trying to do a calculation based on a currency field.

For example I have an app with a currency field which allows users to enter a sum which may be in USD, GBP or EUR. 

I can do calculations on this field - because we can access the numeric value - but they are misleading.  For example if there are four items in the app, which might be $100 + £200 + $70 + €20, I can get a sum = 370.  But 370 of what? It's a completely useless value.

If we could access the currency that the user has selected, as well as the number they've entered, then we could either use an exchange rate to produce a usable sum, or an accurate total of ($170 + £200 + €20). But as far as I can tell, there is no way for a script to find out what currency the user has selected in this field on each item.

 

Ben Rubinstein 0 votes
1
Avatar

This is actually a very valid and important point. Right now the currency has only aesthetic value. I have the same issue with about 20 apps in various workspaces using currency fields, and I'm having to use Excel exports to get stuff done with them, simply because you can't access the currency of the field. Seems like something that's rather essential if you have a field you're actually calling "Money".

Reinhardt Smit 1 vote
0
Avatar

We use a workaround for that: Instead of a money field we've a number field plus a category field for allowed currencies. This way we can calculate values in different currencies. 

But the better way would be to have the money field only and to have access to the currency in the calculation field.

Rainer

Rainer Grabowski 0 votes
1
Avatar

Reinhardt, you wrote "Right now the currency has only aesthetic value".

Actually it's worse than that - if (when creating the app) you only permit one currency, it does indeed have merely aesthetic value. But if you permit choices, that destroys the ability to do any meaningful calculation on the field - so just like Rainer, we have to instead use a category field for the currency, followed by a number field - making the nice currency field redundant.

Even if you're not attempting to sum the value from multiple items (which you can't because you don't know what units they represent) but merely attempting to concatenate fields to produce a readable description, for example, this is impossible because you don't know what currency symbol to include in the concatenation.

Ideally there would be some way to access both the numeric value entered, and the currency chosen. But failing that, if Podio just made the value of a currency field be a string, the printed expression of the currency+value, then the 'concatenation' example above would just work, and it would be possible for a calculation to break apart the string and recover the numeric value.  (For partial compatibility, the current behaviour could be retained where the field only permits a single currency.)

Ben Rubinstein 1 vote
1
Avatar

Hi Sara - Ben is correct. This is an issue for us as well. Is this being looked at, or does Podio understand that this is an issue in the first place?

Blair Sutherland 1 vote
0
Avatar

It is currently not in the roadmap. You can add the currency value to any calculation as seen in "official comment". Are you looking to access the currency exchange rate as well?

//Sara - Podio

Sara Høeg Højlund-Rasmussen 0 votes
2
Avatar

Hi Sara - I'm sorry, but the "official comment" is completely missing the point of this issue.

Essentially, a field with currency options cannot be used in any way other than display - it's not possible to do any form of useful summing or calculation on it, because the true value is unavailable.

Ben Rubinstein 2 votes
0
Avatar

Hi Sara, this is still a problem for us. I like Podio a lot, but this is a real oversight for any company doing business internationally. The problem is that I can't use the Podio reporting functionality, or even the scripting, to get an accurate total for sales leads that have multiple currencies. So we have to resort to all kinds of workarounds like exporting it to Excel and calculating this manually.

If you need more explanation I'm happy to give it!

Kees van Bochove 0 votes
0
Avatar

@Kees 
I agree that it would be a great advantage to have access to the currency in the calculation field. 
But there are 2 options to avoid with "all kinds of workarounds like exporting it to Excel and calculating this manually". Everything is better than  wasting time with Excel-export/-import. I've created such solutions several times for my clients.

Option 1 is using Globiflow. It's the more flexible option. For you as a user it has the same effect as converting it directly in a calculation field. 

Option 2: If the currency in the item is ALWAYS depend on the lead's country or if it is possible in your use case to have an option in the Leads-App where the currency for this lead can be selected, you can convert  every currency directly in a calculation field (w/o Globiflow). That's also a solution I've implemented for some clients.

Rainer
rg@delos-consulting.com

Rainer Grabowski 0 votes
0
Avatar

Any updates?

 

Very important feature. I use multiple currencies as well and can't use the reports unless there is a way to determine what is the selected currency. Please advise. 

 

Thanks.

Oren 0 votes