By using this website, you agree to our Terms of Use (click here)
Do you know why some columns have the "General" format when exported from a GI to Excel, and whether there is a way to change the formatting?
When they come out as "General", they cannot be added up. Changing formatting to Number or Currency does not help either. The only way I found is to multiply cells by 1, so that they will be converted to numbers.
I even tried converting the resulting number to CDbl in the GI, as in
=CDbl (IIf([ARAdjust.AdjdDocType]='INV' or [ARAdjust.AdjdDocType]='DRM',1,-1) * [ARAdjust.CuryAdjgAmt])
Thank you.
It looks like your field is a formula. When you do a formula, the formula doesn't control the data type.
What actually controls the data type is the Schema Field column. It doesn't matter what field you select, just make sure to select any field that is a numeric field like this:
Two additional thoughts on exporting to Excel from a Generic Inquiry:
1. Personally, I don't like the extra stuff that gets included above the table. I would like to see it added to a new sheet. If you think so too, you can vote on this:
https://feedback.acumatica.com/ideas/ACU-I-1435
2. If you really want better control, then I would start in Excel and connect back to the Generic Inquiry using OData. Then you can set whatever formatting you want in Excel and it will "stick" during the refresh.
Thank you Tim. It works as expected now.
1 - I have voted for the idea. I think, as an alternative to keeping that information on a different sheet, it can be placed on header of the same sheet. It has pros and cons, but would keep it cleaner.
2 - I will give it a try.
Thanks again.
For some reason it does not work with the invoice reference numbers. It still does not recognize them as numbers, hence, I have to multiply them by 1 in Excel.
What if you try a combination of using CInt in the formula and also setting the Schema Field value?
I have tried it, but it does not work, because, it removes all the leading zeros, hence 000002 becomes 2, which creates another problem if we need to do a VLOOKUP in Excel. In addition, Excel still does not recognize it as a number.
Hmmm, weird. Have you considered using OData and pulling from Excel? That would definitely give you more control over formatting.
I have not tried that yet. Luckily, it is not a report we use everyday. I will wait until test it with 2017 R2, if it still fails, then I will give it a try.
Thank you.