I have a column in database view of type decimal (20,2) and it can take both negative and positive number. Now on downloading excel doc via out-of-the box feature (Export Data Store Entity to Excel), all the number are being quoted inside single quotes but not the positive ones.
Any way to get rid of this?
Discussion posts and replies are publicly visible
Any leads?
Not sure ... could it be that this is just some Excel display "magic"?
Yeah, it seems, it is excel behavior. I found below info on web. Not sure what would be hack here.
Is it possible to have a View and have this particular field as VARCHAR using CONVERT and try? I haven't tried. Just guessing.
It is that way only, in DB view it is a varchar. The problem is the I am expecting bigger value like decimal(20,2) and we know in Appian after 9 digits it starts showing exponent. So, I have used some function and that is making the value as text.
Problem is the moment excel encounter a digit and if it is not in number/decimal data type, it does add apostrophe.Solution would be, we need a way to break down the exponent value by retaining the data type to decimal, which is not possible as of now.
See I am just using the value as coming from DB and I don't see apostrophe.
Dhananjay Kumar said:we know in Appian after 9 digits it starts showing exponent
Sorry, but this is nonsense! In this community, we discussed this so many times. Appian just uses a specific way to format such numbers when it needs to turn the internal numerical value into text to make it visible on screen.
Please, stay with normal decimal types in the database!
Then, your Excel will also be happy, as Excel uses a single apostrophe to indicate that the text in the cell shall be treated as text, but not as numbers.
I agree with you on this part
Appian just uses a specific way to format such numbers when it needs to turn the internal numerical value into text to make it visible on screen.
But it is the ask for business where we really deal with bigger values.
Currently we are manually removing the apostrophe.Why Appian can't change the way it display after 9 digits, just wondering!!!
I am not sure what you mean. In any Appian interface, just use the text() function to format any numerical value the way you like.
And when you say "change it", change to what? What is the "correct" way of displaying a number?
Let me elaborate. Correct me If I am wring at any place.
After 9-digit Appian display digits as exponent with same data type as rule input.
Now I can use text/fixed function to display it full but it does convert the data type to text which I don't want because of excel behavior.
And when you say "change it", change to what? What is the "correct" way of displaying a number? - it means, I am expecting if Appian would have display number as is format not with exponent
To make any number human readable, a computer needs to transform it into a text representation.
In Appian UI, we can use Appian default behaviour, or use the text() function for custom formatting.
Excel is known for trying/failing to be clever. When Appian exports data, it does so, exactly as defined in the database. Then Excel applies its cleverness and does weird stuff. Then you try to outsmart Excel and even more weird stuff happens. I apologize for my annoyed tone.
https://trumpexcel.com/scientific-notation-excel/
No problem, I got your point and I think we both are on same page. It's just I was trying to skip any manual intervention on excel.
Thanks!!