14 Replies Latest reply on Aug 24, 2014 6:14 PM by Hudi

    Format a list

    Hudi

      Hi All,

       

      Does anyone have a custom function to format a list from this:

       

      2100

      3450

      10000

      1200

      4000

       

      To this:

       

      2,100

      3,450

      10,000

      1,200

      4,000

       

       

      Many thanks.

        • 1. Re: Format a list
          BowdenData

          Loop through the list values and apply this custom function to each line.

           

          http://www.fmfunctions.com/fid/90

           

          Regards,

          Doug

          1 of 1 people found this helpful
          • 2. Re: Format a list
            Hudi

            Hi,

            Thanks for the reply. I have that custom function (or a similar variation), where I'm having trouble is the looping part. I'm still on the learning curve for recersive functions and any guidance would be helpful.

             

            Thanks

            • 3. Re: Format a list
              erolst

              Hudi wrote:

              I have that custom function (or a similar variation), where I'm having trouble is the looping part

               

              Until you get the hang of it, maybe try a script to practice the “looping part” … it's the same logic, but possibly easier to implement.

               

              See attached file.

              • 4. Re: Format a list
                sicosys

                Hello erolst!!

                 

                I always enjoy and learn from your answers in this forum.

                 

                Just an observation about your sample file:

                 

                Loop is perfect but the function returns wrong format.  (Hudi needs a simlpe thousands separation function where 100=100 ,1000=1,000, 1234567=1,234,567)

                 

                I know and understand your point with your sample file, but again, this is just a simple and irrelevant observation.

                 

                Felipe

                • 5. Re: Format a list
                  jbante

                  Do you need to insert the text in a return-delimited list that you want to keep as a single block, or are you formatting a series of numbers that will be appearing on a layout? If these are separate numbers you're displaying on a layout, you're better off using FileMaker's own number formatting controls. When you select a number field on a layout, you can go to the Inspector > Data tab > Data Formatting > number formatting and select "Decimal" for the format, then turn "Use thousands separator" on.

                  • 6. Re: Format a list
                    Hudi

                    erolst,

                    Thank you for the reply. The example file is perfect example of transferring the logic of a loop script over to a recursive custom function. Just what I need.

                    Thank you.

                    • 7. Re: Format a list
                      Hudi

                      Thanks for the reply. I need this as block of return delimeted numbers.

                       

                      The list of number is the result of a fSQL query. It's also dollars, making it necessary for me to attach a "$" to the start of each value so I cannot use a number field.

                       

                      I got it this far:

                      $2100

                      $3450

                      $10000

                      $1200

                      $4000

                       

                      But need this:

                       

                      $2,100

                      $3,450

                      $10,000

                      $1,200

                      $4,000

                       

                       

                      ** the dollar sign is attached after with seperate CF. Right now I'm just focused on getting a thousand seperator in there. erolsts' example seems to do the trick.

                      • 8. Re: Format a list
                        Hudi

                        Erolst,

                         

                        The CF in the example file is outputting the number with an additional comma and two zeros. Taking 80000 to 80,000,00 . Sorry to bug you about this but is there something I'm missing or somewhere in the CF I should be lookng to update.

                         

                        Thanks again.

                        • 9. Re: Format a list
                          user19752

                          There is a custom function

                          http://www.briandunning.com/cf/899

                          and standard fm function to insert commas, become

                          ApplyToList ("\"$\"&NumToJText ([n];1;0)" ; vl ; ¶ )

                           

                          I used to wrote expression while not having FMP advanced, like

                          Evaluate ( "NumToJText ( " & Substitute ( vl ; ¶ ; "; 1 ; 0 ) & Char(13) & NumToJText ( " ) & " ; 1 ; 0 )" )

                          This works only for short list, limited by expression lengh 30000.

                          • 10. Re: Format a list
                            erolst

                            Hudi wrote:

                            The CF in the example file is outputting the number with an additional comma and two zeros.

                             

                            Change the value for the numerical precision argument in the call of the NumberFormat() CF.

                             

                            EDIT: But if you only needs a thousands separator, then call NumToJText() directly; no need for the full-blown NumberFormat().

                             

                            See attached; also contains another method to use recursion.

                            • 11. Re: Format a list
                              erolst

                              Hudi wrote:

                              making it necessary for me to attach a "$" to the start of each value so I cannot use a number field.

                               

                              You cannot use a number field because you have a list of values.

                              • 12. Re: Format a list
                                erolst

                                user19752 wrote:

                                I used to wrote expression while not having FMP advanced, like

                                Evaluate ( "NumToJText ( " & Substitute ( vl ; ¶ ; "; 1 ; 0 ) & Char(13) & NumToJText ( " ) & " ; 1 ; 0 )" )

                                This works only for short list, limited by expression lengh 30000.

                                 

                                There's another way to write a recursive function, if you either don't have Advanced, or need a quick'n'dirty, purpose-built function and don't want to fuss about with a CF: create an unstored calc with a $var as increment counter (that you reset on loop exit) and trick FM into accepting its own field name in the recursive call (dummy on creation, then re-open and re-define).

                                 

                                To wit (a simple multiplier, without the usual trap(ping)s and optimizations):

                                 

                                Let ( [ $i = $i + 1 ; res = GetValue ( input ; $i ) * 3 ; more = ValueCount ( input ) > $i ] ; List ( res ; Case ( more ; nameOfThisField ; Let ( $i = "" ; "" ) ) ) )

                                 

                                A bit of a hack (relying on the largely undocumented private life of the common local variable), but still better than using Evaluate().

                                • 13. Re: Format a list
                                  sicosys

                                  Hello Hudi!!

                                   

                                  The attached file is just an option  that does the job and works with positive, zero and negative values as well as decimals.

                                   

                                  I know it is too late for this answer but just in case

                                   

                                  Felipe

                                  1 of 1 people found this helpful
                                  • 14. Re: Format a list
                                    Hudi

                                    Sicosys,

                                     

                                    Thanks that is a good solution as well. I'm not thrilled about the legnth of the script because I'd need to place it in so many different places. I'm partial to custom functions for tasks like this. But thanks anyway.

                                     

                                    Cheers