Questions Formatting the numbers in a column

Jake,

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.

Jake,

The DiscPercent column in the Terms DAC is an example of a field that is natively defined as a percentage.

This was a lot of help, but I'm still looking to know how to put a percentage sign in the column with the numbers, since I don't believe there are any GIs I'm working with that has this. Would I just have to work that in with the data field, or is there another way?

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:

I tried the same thing, and % symbol only shows up if I leave the schema field empty and let it be 8 decimal places. If not, the entire column goes blank. I'll keep trying, but thanks for all the help!

Jake,

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.

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.

@alisann-crough

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. 

@timrodman

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.

@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)

Thanks for sharing your solution Thomas!

Posted by: @sslavin

a blind squirrel can find a nut from time to time

A blind squirrel who knows how to use Google, that's me!

Just to add to this thread.  When you have a GI that you're using for a business notification the Schema field will be ignored when formatting the field within the email.  Here's a formula that you can use to generate a formatted numeric value.  Be sure to remove any Schema field from this formula since that will remove all formatting of the value.

=iif([APPayment.CuryOrigDocAmt]<0,'-','')

+Replace(Replace(

Left(PadLeft(CStr(Abs([APPayment.CuryOrigDocAmt])),14,'~'),3)+','

+Left(Right(PadLeft(CStr(Abs([APPayment.CuryOrigDocAmt])),14,'~'),11),3)+','

+Left(Right(PadLeft(CStr(Abs([APPayment.CuryOrigDocAmt])),14,'~'),8),3)+'.'

+Left(Right(PadLeft(CStr(Abs([APPayment.CuryOrigDocAmt])),14,'~'),4),2)

,'~,',''),'~','')

The general idea is that I'm turning the number into a string padded on the left with ~ characters. Then I take three-character chunks and separate them with commas and periods (you'll flip that if you write your punctuation backwards 😉  ).  Then I remove any extraneous ~, and ~ strings from the result, leaving me with a formatted number (by going the long way).

If you need longer numbers then you'll need to adjust the "14" accordingly.  And if you need more decimals then you'll need to adjust the Left/Right formula parameters.

Not sure if this may help. But I normally do like this. In this example, I am doing percentage of AP Bill Open Balance over AP Bill Original Amount.

=Concat (
CStr (
CInt (
IIf ( [APInvoice.CuryDocBal] = 0, 0 , ([APInvoice.CuryDocBal]/[APInvoice.CuryOrigDocAmt] ) * 100))) ,
' %'
)

So, I'll get like this.

CInt --> to convert to zero decimal.

CStr --> so that I can concatenate with the % character.