I am liking Manager IO,
I have been doing research (playing manger Io for about 6 months after purchase of cloud, and started using in my company for about 2 months, I can play more and can do some changes up to my requirement, it is quite flexible, and I am liking it.
I like to share my findings to solve problem for exporting to excel and sorting the data.
After setting basic currency in manger io, this shows your basic currency short form, after numbers.
(e.g.= “1000.00 USD”)
This makes me problem, when export to excel and make the data sorting, for the most, the least, the highest, the lowest etc.
Because excel recognize the column “AMOUNT” with Basic currency short form as text, not as numeric.
This problem happens, when export direct from sales invoice, purchase invoice, etc.
But this problem does not happens, when export after creating report, such as Sales Invoice Total by Customers, by Item, by special fields, etc., because those reports doesn’t show currency symbol. So Excel recognize that column as numeric. And can do the data analysis, sorting easily.
After doing some research, testing for a few days, this morning I found the solution.
You can export direct (not from reports), such as sales invoice, sales order etc.
Copy and paste the data to Excel.
you will see the “AMOUNT” column as “Alpha-numeric”,
the number + Basic Currency Short form = e.g. “1000.00 USD”.
High light that column, and go to the function in excel,
Go to DATA=> Click “ Text to Column, “ function
Choose, “Delimited”,
Choose “SPACE’
Choose “General” and finished.
You will get the Current amount and Currency symbol, in 2 separate column and you can start doing your data analysis.
i am on MS2013 too and it works for me.
maybe the difference is because my currency is a symbol and yours is text.
anyway this is not a fault of Manager but of Excel.
Off course it’s not the fault of Manager but it would be better practice to export the currency symbol in a separate column for sorting or, for instance, calculation options.
yes. as @melvin explained in detail it can be easily done in excel.
as for Manager, the currency is part of the amount just like the decimal values, not something separate.
Yes it can be easily done but not everyone has the knowledge. @melvin himself had problems with it so when he found the solution he wanted to share this to help other users.
I understand that the currency is a part of the amount but it shouldn’t be (probably in the database it’s already a separate value) . Exporting to csv is usually done for reporting or to make extra calculations (obviously because it’s accounting software). So it makes no sense to include the currency in the amount. This can also be easily done by Manager before exporting to csv making the program (even) more friendly for the user without the need of knowledge of for instance Excel.
If you are only using one currency then there is no need to set base currency at all, then there are no symbol issues when exporting. Setting base currency is more applicable when you are using more then one currency.
I am not a programmer but it loks like Manager is written as a flat file DB rather than a relational DB. If it was the latter separating currency symbol from amount should be fairly simple (for a DB programmer that is).
I had a similar problem a few weeks ago when exporting a summary of payslip gross, tax, and net payments and wanted to total them. I also found changing the text to “Numbers” did not work as it was obviously recognised as text. I used “Find and Replace”, as advised on this forum. Eg Find AU$ and Replace with nothing and there you have all the AU$ currency symbols gone and numerals only which can easily be added or sorted. But yes, guess it would be good not to need this extra step.
