Formatting the numbers in a column
On the results tab is a column called Schema Field. This column does several things but in the context of a GI, it provides formatting of the values returned in the specified column. To have the results returned as a percentage, find a column in your GI dataset that is already formatted as a percent and point to that column. If you don't have a percentage column in any of the tables within your GI, you might try adding a table with such a column to your GI. Just don't link it to any other table and only use the one column from the newly added table for defining the formatting for your desired results.
Well. I tried but could not get the system to add " Pct" to the end of the percent values. It returned a blank value. Trying to add " %" causes an error because % is a special character. (BTW, I did all this trying to convert the results of the calculation into a string and concatenating the above characters to the end of the results.
Referencing a value stored as a percentage in Acumatica shows 6 decimal places rather than the 8+ that result for a calculation or the 2 that default from the system settings associated with the reporting Company in 'Companies'.
Anyway, here is how I set things up and results produced:
Based upon what you shared above, it would be a lot of work to just add a % sign at the end of the number but if still interested, think about doing the following:
1) make the calculation and return 8 decimal places,
2) multiply by 100 (to get ready to show as a percentage rather than a decimal),
2) add .00005000 (assuming rounding up to 4 decimal places),
3) convert to a string,
4) truncate the last 4 digits (left(string,len(string)-4)),
5) concatenate ' %' to the end of the resulting string.
You can do all the above in a single formula but I thought it would be clearer if I broke the components down into steps. Good luck and paste whatever you end up doing to share with the community so the next person coming along can benefit from your learning! Cheers.
It worked! Pretty bulky but this is how it turned out,
I've never actually done this, but I think that the formatting for each field gets defined in the Data Access Class (DAC). It would be cool to have a bunch of formatting DAC fields that could be used in this situation. They could be deployed to an environment using a Customization Project.
Even better though would be to have a Format field in Generic Inquiry like we have in Report Designer. Then you could apply a formatting mask using all of the same formatting codes that are available in Report Designer.
I'm trying to format a column with a qty field so that it has nothing after the decimal (5 or 5. but not 5.00) and I can't find a field that is defined the way I need to use in the Schema column of the Results Grid. Is there a list anywhere? Do you know of a field?
FYI - In Companies under Miscellaneous Settings (Shared), I have "Quantity Decimal Places:" set to 2 and I need it to remain that way. Thanks
You should be able to use any Integer field, a decent source of these are the "ID" fields, InventoryID, LocationID, SiteID, etc. You could also use LineNbr, which is convenient for Qty since most of the time those fields are hanging around on a Line or Tran table and you won't need to join in an extra table.
I had been looking around for a DAC attribute that defines things such as the formatting, decimal precision, and the like, but I am not sure what adds those. I wasn't sure if you had dealt with them before? Currently I end up setting the format on the field after adding to the screen, but I am not happy with that solution.
Thank you! I actually tried an ID field but probably wasn't an integer field. Need to figure out how to look that up. Line number (in the Service Order screen) has a format of '0001" which I also didn't want. I guess I gave up to quickly!
I was successfully able to use ApptNumber.
@alisann-crough - Glad to hear that you got it working.
@wyatt-erp - I'm not sure where in the DAC it's stored or even if the formatting is even stored in the DAC for sure. It just seems to me like it makes sense that it would be stored there. That would be a cool Customization Project, a custom DAC with columns that have names that describe the way they are formatted. It could be used for nothing other than formatting fields in Generic Inquiries, similar to this Date and Time technique (click here).
Thanks all for the many ideas. I also needed to format a couple of fields for PositivePay export from a GI.
The first field was the Check number (otherwise known as ExtRefNbr). This field needed to be 10 characters with Zeros padding the left of the check number (0000001234). the formula for the column is
=PadRight([APPayment.ExtRefNbr], 10, '0' )
The second field was the Check Amount (oka OrigDocAmt). By default the output was 5,000,000.00 but it needed to be without any commas. Initially I tried Replace(CStr([APPayment.OrigDocAmt]),',','') but that then returned 5000000.0000. To strip off the last 2 zeros the formula became =Left(Replace(CStr([APPayment.OrigDocAmt]),',',''), Len(Replace(CStr([APPayment.OrigDocAmt]),',',''))-2)