7 Replies Latest reply on Apr 1, 2014 8:04 PM by datadesigner

# List Function Formatting?

I've searched this category and cannot find a topic that addresses this.

I need to apply currency formatting to a List function result. I am getting a list of values from a related number field (formatted as currency) and the return separated list shows up properly, but as integers if the List function (number result) field is set to display as entered.

Can I get some help with how to make this list result:

1000000

20000.34

110000

33520.23

display as:

\$100,000.00

\$20,000.34

\$110,000.00

\$33,520.23

Appreciate any help.

• ###### 1. Re: List Function Formatting?

Here's a custom function that will do it for a single value:

Let ( [

Amount = Round ( Abs ( Number ) ; 2 ) ;

A = Int ( Amount ) ;

L = Length ( A ) ;

R = If ( Amount ≠ A ; Left ( Amount - A & "000" ; 3 ) ; ".00" )

] ;

Case ( Number < 0 ; "-" ) &

"\$" &

Case (

L=9 ; Left ( A ; 3 ) & "," & Middle ( A ; 4 ; 3 ) & "," & Right ( A ; 3 ) ;

L=8 ; Left ( A ; 2 ) & "," & Middle ( A ; 3 ; 3 ) & "," & Right ( A ; 3 ) ;

L=7 ; Left ( A ; 1 ) & "," & Middle ( A ; 2 ; 3 ) & "," & Right ( A ; 3 ) ;

L=6 ; Left ( A ; 3 ) & "," & Right ( A ; 3 ) ;

L=5 ; Left ( A ; 2 ) & "," & Right ( A ; 3 ) ;

L=4 ; Left ( A ; 1 ) & "," & Right ( A ; 3 ) ;

A

) //end Case

& R

) //end Let

//  Originally developed by Jeremiah Small, Soliant Consulting

You can use this version to pass it through for a list:

/*

cfAddDollarFormatToList ( numberList ; iteration )

Author: Mike Mitchell, Net Caster Solutions

Purpose: Adds currency format to a return-delimited list of numbers.

Parameters:

numberList: List of numbers

iteration: Iteration through recursive loop (should always be passed as "1" by calling calculation)

Needs cfAddDollarFormat ( ) as a helper function.

*/

Let ( [

listRows = ValueCount ( numberList ) ;

iterationVal = GetValue ( numberList ; iteration ) ;

badVal = IsEmpty ( iterationVal ) ;

currentProcessedResult =

Case (

numberList  ;

LeftValues ( numberList ; iteration - 1 ) & cfAddDollarFormat ( iterationVal ) & "¶" & RightValues ( numberList ; listRows - iteration )

)

] ;

Case (

listRows < iteration ;

numberList ;

cfAddDollarFormatToList ( currentProcessedResult ; iteration + 1 )

)

)

HTH

Mike

• ###### 2. Re: List Function Formatting?

Mike_Mitchell wrote:

Here's a custom function that will do it for a single value:

[…]

You can use this version to pass it through for a list:

[…]

Hi Mike,

if you have a recursive list processor, why not let it work in tandem with a recursive digit processor?

/* CurrencyFormatter ( aNumber )

Purpose: formats a number as currency

Recursive: Yes

uses hard-coded parameters for US currency display: \$/,/.

change here or modify cf to take these values from arguments , e.g. €/./,

Use of local \$var to avoid having to pass a start iterator or using a helper cf

*/

Let ( [

\$i = \$i + 1 ;

a = Abs ( aNumber ) ;

~int = Int ( a ) ;

~len = Length ( ~int ) ;

theSign = Case ( aNumber < 0 and \$i = 1 ; "-" ) ;

theCurrency = Case ( \$i = 1 ; "\$" ) ;

dec = Case ( \$i = 1 ; "." & SerialIncrement ( "00" ; ( a - ~int ) * 100 ) ) ;

sep = "," ;

take = Choose ( Mod ( ~len ; 3 ) ; 3 ; 1 ; 2 ) ;

rest = Middle ( ~int ; take + 1 ; ~len - take ) ;

curRes = Left ( ~int ; take ) & Case ( not IsEmpty ( rest ) ; sep )

] ;

theSign & theCurrency & curRes & Case ( Length ( rest ) ; CurrencyFormatter ( rest ) ; Let ( \$i = 0 ; "" ) ) & dec

)

/* ProcessListAsCurrency ( aList )

Purpose: Processes a list and passes each list item to CurrencyFormatter()

Recursive: Yes (passes ever dwindling list)

Dependency: needs CurrencyFormatter() as a helper function

*/

Let ( [

vc = ValueCount ( aList ) ;

curVal = GetValue ( aList ; 1 ) ;

curRes = Case ( not IsEmpty ( curVal ) ; CurrencyFormatter ( curVal ) ) ;

rest = MiddleValues ( aList ; 2 ; vc - 1 )

] ;

List ( curRes ; Case ( not IsEmpty ( rest ) ; ProcessListAsCurrency ( rest ) ) )

)

• ###### 3. Re: List Function Formatting?

Mike, thanks for your reply. Let me see if I'm understanding your answer. Are you saying that the first part of your answer, the single value part, is the "cfAddDollarFormat" custom function you are referencing later in your reply?

Thanks for taking the time to help with this.

Richard

• ###### 4. Re: List Function Formatting?

This might make things easier:

It is only part of the solution that you are after, but it would at least simplify the custom function.

-Marc

1 of 1 people found this helpful
• ###### 5. Re: List Function Formatting?

Yes. They are two separate functions. The second references the first.

1 of 1 people found this helpful
• ###### 6. Re: List Function Formatting?

Well, I didn't write the original formatting function. But that works too.

• ###### 7. Re: List Function Formatting?

Mike, thanks again for your reply. Just had the chance to apply it in my solution and it works perfectly. It took me a few minutes to take that one apart and see what's going on. It will be useful for a number of reports I have to build.

(And thanks too to Jeremiah Small.)

I appreciate the help.

Richard