1 2 Previous Next 15 Replies Latest reply on Mar 25, 2015 1:14 AM by siplus

    Remove N characters from each item n a list using a custom function

    mercator

      Good afternoon,

      I need a custom function that removes the first N characters of every item in a list. How do I iterate over the items of a list and ermove these characters? What I would like as a result is againa list with the the items being the original list items without the first N characters per item.

      Thanks a lot,

      Helmut

        • 1. Re: Remove N characters from each item n a list using a custom function
          user14047
          /*------------------------------------------------------------
          RemoveNCharactersFromList ( text ; numberOfCharacter )
          ------------------------------------------------------------*/
          Let (
          [
          $~i = $~i + 1 ;
          ~valueCount = ValueCount ( text ) ;
          ~value = GetValue ( text ; $~i )
          ] ;
          Case (
          $~i ≤ ~valueCount ;
          Right ( ~value ; Length ( ~value ) - numberOfCharacter )  & ¶ &
          RemoveNCharactersFromList ( text ; numberOfCharacter ) ;
          // else
          Let ( [ $~i = "" ] ; "" )
          )
          )
          • 2. Re: Remove N characters from each item n a list using a custom function
            sporobolus

            On 2015-03-20 6:50 , mercator wrote

            I need a custom function that removes the first N characters of every item in a list. How do I iterate over the items of a list and ermove these characters? What I would like as a result is againa list with the the items being the original list items without the first N characters per item.

             

            i wrote a generic custom function to map any expression onto a list:

             

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

             

            here is an example of using it for removing n characters:

             


            let ([
                n = 2; // number of characters to remove
                expr = "right ( _v ; length ( _v ) - " & n & " )";
                sample_list = list ( "abcdefgh", "1234568", "1", "1243", "12" )
              ];
              mapexpr ( expr , sample_list )
            )

             

            because it evaluates expressions on the fly it will be slower for long lists

            than user14047's more specific example

             

            (edited to fix the indentation; the code tag in email replies used to preserve that)

            • 3. Re: Remove N characters from each item n a list using a custom function

              From my interpretation of your request a custom function is not needed since you are using a Value List from a field in a table.

               

              Therefore the simplest method would be to create a second field that contains the shortened text string.

               

              Create a new caluclation field and make it something like:

               

              Middle (old field used for value list ; xcharacterstoremove ; length ( oldfield used...)  )  verify the actual function as I am using a failing memory.

               

              Using Middle eliminates all of the complext stuff above and uses only one step.

               

              Now create a new value list pointing at this field and you have both the old and the new available.

               

              So simple, yet so easy....

               

              You can fancy this up a bit by using a global field for xCharacters and letting the user determine how many to remove.

              • 4. Re: Remove N characters from each item n a list using a custom function

                Substitute could also be used.

                N = ?

                strA = left(yourstring; N)

                strB = substitute( yourlistline ; strA ; "" )

                • 5. Re: Remove N characters from each item n a list using a custom function
                  sporobolus

                  On 2015-03-21 9:27 , jackrodgers wrote

                  Substitute could also be used.

                  N = ?

                  strA = left(yourstring; N)

                  strB = substitute( yourlistline ; strA ; "" )

                   

                  if strA is repeated, that will be bad news!

                  • 6. Re: Remove N characters from each item n a list using a custom function
                    sporobolus

                    On 2015-03-20 16:23 , jackrodgers wrote

                    Create a new caluclation field and make it something like:

                     

                    Middle (old field used for value list ; xcharacterstoremove ; length ( oldfield used...)  )  verify the actual function as I am using a failing memory.

                     

                    that will remove one too few characters, and only from the first entry in

                    the list

                    • 7. Re: Remove N characters from each item n a list using a custom function

                      Great catch.

                       

                      Everybody, disregard the suggestion!

                      • 8. Re: Remove N characters from each item n a list using a custom function

                        Embarrasing: the Middle function was stated incorrectly. This is the corrected function my neuropathy and tinnitus are raging today. I will not psueocode again. One reason I prefer FileMaker is the point and click.

                         

                        middle ( text ; first character ; number of characters )

                         

                        or Middle ( text ; 1 ; 50 ) Since the request was to chop off the first 50 characters, there is no need to worry about filtering them and the could be any of the many unicode characters. Middle does this easily.

                         

                        Use it for the calc inside the recursive.

                        • 9. Re: Remove N characters from each item n a list using a custom function
                          sporobolus

                          i'm puzzled by this suggestion — it would return the first 50 characters, where OP asked for each element with the first N characters removed

                           

                          also, using Middle () isn't really much simpler than Right (), since one must add 1 to the second term to get the correct offset

                           

                          either of these will work:

                           

                          Middle ( text ; N + 1 ; Length ( text ) )

                          Right ( text ; Length ( text ) - N )

                           

                          one way you could simplify slightly is if you could assume that all elements in the list are shorter than, say, 100 characters; in that case this would work and might be a little faster

                           

                          Middle ( text ; N + 1 ; 100 )

                           

                          if you want to do it in a calc field instead of using a custom function (as your earlier post proposed), here is the gnarly (fun) way:

                           

                          Let ([
                              the_list = List ("abcdefghijkl", "ieurowieuofsjldf" , "239409s809802"); // or reference a field
                              N = 3; // characters to remove
                              max_length = 100;
                              pre = "Middle(\"";
                              post = "\"; " & N + 1 & "; " & max_length & ");"
                            ];
                          evaluate ("List ( " & pre &  Substitute ( the_list ; ¶ ; post & pre ) & post & ")" )
                          )
                          

                           

                          the calc needed when max_length can't be assumed is left as an exercise

                          • 10. Re: Remove N characters from each item n a list using a custom function
                            rrrichie

                            The recursive function will only work if there are 10.000 or less values... You could also try this  (free coded)

                             

                            ExecuteSQL ( " SELECT RIGHT ( fieldName ; LENGTH( fieldName) -2 ) FROM theTable WHERE ... some condition " ; "" ; "" )

                             

                            That will return a list with the first 2 characters gone.   Of course this only works if the list is in a table or you put it there :-)

                            • 11. Re: Remove N characters from each item n a list using a custom function
                              siplus

                              Sorry Helmut but sometimes the perceived need (custom function, N chars) calls for a solution that is more complex than needed. Zooming in on the real problem helps in giving a more efficient solution.

                               

                              - Where does the list come from ? (List(related field), SQL... ) ?

                              - How many elements max will the list have ?

                              - Is N variable, or is it a fixed value ?

                              - What value must be returned for an entry when N is bigger than the list's entry length ?

                              - How often will you call this routine and how fast must be the answer ?

                              - What will you do with the resulting list (helps determining if using a script instead of a CF is possible)

                              • 12. Re: Remove N characters from each item n a list using a custom function
                                sporobolus

                                that's a good idea for working with a table of values, and probably a lot faster with larger datasets; you don't even need a WHERE condition; in most cases when one would be working with larger lists a script- or table-based approach would be feasible and possibly more practical

                                 

                                but note that you can do up to 50,000 calls if you use tail recursion, whereas the non-recursive approach i showed above (to avoid using a custom function) couldn't even handle 1000 items; probably too many arguments for List()

                                 

                                i modified my CF to be tail-recursive as follows:

                                 

                                /*
                                mapexpr_tail ( expr ; l ; "" )
                                apply an expression to every value in a value list; tail recursive to allow up to 50000 recursive calls
                                
                                expr = an expression string suitable for Evaluate() with let variable _v used where each value should be referenced
                                l = a valuelist (return separated text items)
                                so_far = the cumulative result of mapping the expression, initially ""
                                */
                                Let ([
                                    n = ValueCount ( l ) ;
                                    lambda = "Let (_v =" & Quote ( GetValue ( l ; 1 ) ) & "; " & expr & ")"
                                  ];
                                  Case (
                                    n = 0 ; so_far ;
                                    mapexpr_tail ( expr ; RightValues ( l ; n - 1 ) ; so_far & Evaluate ( lambda ) & ¶ )
                                  )
                                )
                                

                                 

                                and was able to process a 1000 item list too quickly to notice a lag, and a 41,000 item list in a little over four minutes; the list was in the field "foo" in the expression below

                                 

                                Let ([
                                    n = 3 ; // number of characters to remove 
                                    expr = "right ( _v ; length ( _v ) - " & n & " )"
                                  ];
                                  mapexpr_tail ( expr ; custom_functions::foo, "")
                                )
                                

                                 

                                 

                                i had expected user14047's suggestion to be a lot faster than my generic mapping approach, but after i built a tail-recursive version if it (below), in my tests it was only about 5% faster

                                 

                                /* 
                                remove_n ( n ; l ; "" )
                                
                                remove the first n characters from each item in a list l, pass "" as third parameter
                                */
                                Let ([
                                    v_count = ValueCount ( l ) ; 
                                    v = GetValue ( l ; 1 )
                                  ];
                                  Case (
                                    v_count = 0 ; so_far ;
                                    remove_n ( n ; RightValues ( l ; v_count - 1 ) ; so_far & Right ( v ; Length ( v ) - n ) & ¶ )
                                  )
                                )
                                
                                • 13. Re: Remove N characters from each item n a list using a custom function
                                  siplus

                                  If all the OP needs is to cut off 30 chars from the head of every item of a list coming from a field in a table, a list extracted via List or SQL, the best thing to do is to define an extra calc field in the original table and list or SQL it. Milliseconds, instead of 4 minutes. Therefore my post.

                                  • 14. Re: Remove N characters from each item n a list using a custom function
                                    mercator

                                    Good evening everybody!

                                     

                                    Wow,I would not have assumed the topic gets that much of an attraction. Here we go:

                                     

                                    - Where does the list come from ? (List(related field), SQL... ) ? >> It comes from a list in an unrelated field (global)

                                    - How many elements max will the list have ? >>> I would assume not more than about 500.

                                    - Is N variable, or is it a fixed value ? >> It is either once or twice the length of a UUID.

                                    - What value must be returned for an entry when N is bigger than the list's entry length ? >> the list is always two or three concatenated UUIDs and I want to cut of once or twice the length of a UUID, so it is never the length of the actual entry

                                    - How often will you call this routine and how fast must be the answer ? >> Once in a while, a couple of seconds would be ok

                                    - What will you do with the resulting list (helps determining if using a script instead of a CF is possible) >> I would like to use it to link a number of related records (i.e. the result is a list of primary keys and I would like to traverse to the related records)

                                     

                                    Hope this helps.

                                     

                                    Cheers,

                                    Helmut

                                    1 2 Previous Next