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

    List Function Formatting?

    datadesigner

      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?
          Mike_Mitchell

          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 (

           

          badVal ;

            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?
            erolst

            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?
              datadesigner

              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?
                brsamuel

                This might make things easier:

                 

                http://www.fitchandfitch.com/2012/03/formatting-numbers-as-text-in-filemaker/

                 

                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?
                  Mike_Mitchell

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

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

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

                    • 7. Re: List Function Formatting?
                      datadesigner

                      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