13 Replies Latest reply on May 10, 2011 10:54 AM by philmodjunk

    remove values from text string

    BruceCochrane

      Title

      remove values from text string

      Post

      i have a list of values in two seperate fields (A and B)  and i wish to create a third field (C) with the values from A that are not in B - Both A and B do not have a set number of values

       

      i would like this to be a calculation ;

        • 1. Re: remove values from text string
          philmodjunk

          Be nice if there was a built in function in FileMaker for this, but there isn't. Any calculation that does this would need to loop through the list of values in Field A or B order to generate the list you want in field C. This can be done with recursion--either in a custom function (Requires FileMaker Advanced to add to your file) or in a recursive calculation field.

          Do you have FileMaker Advanced?

          And you may be able to do a web search for a custom function where this has already been designed as I seem to remember references to such a function.

          • 2. Re: remove values from text string
            BruceCochrane

            yes i am using advanced ; but not in a very advanced way

            i'll try a web search

             

            • 3. Re: remove values from text string
              philmodjunk

              If you can't find it and another user doesn't post a link to such a source, let me know here and I'll take a swing at crafting such a custom function. It shouldn't be too difficult to create.

              • 4. Re: remove values from text string
                BruceCochrane

                found two wich will remove a single value

                both on briandunning.com

                AddRemoveListItem and RemoveValue

                as yet i haven't a clue how to go the next step

                • 5. Re: remove values from text string
                  BruceCochrane

                  ok my brain can't cope with recursive functions yet

                  and i just need it to work for now

                  so i stole the substitute line from David Heads function(coppied bellow)

                  // AddRemoveListItem (theList; value)
                  // Author: David Head, uLearnIT
                  // theList: standard return separated list
                  // value: text
                  // this function will add an item to a list if it does not already exist; otherwise the item is removed from the list

                  Let ([
                  novalue = IsEmpty ( FilterValues ( theList ; value ) );
                  listminusvalue = Substitute( "¶¶" & theList & "¶¶"; [¶ & value & ¶ ; ¶ ] ; ["¶¶¶"; ""] ; ["¶¶"; ""] ) ;
                  listplusvalue = List ( theList; value )
                  ];
                  If ( novalue ; listplusvalue ; listminusvalue )
                  )

                  i have just used it in a simple calculation - which will work so long as the absent list (see below) does not exceed 20 (5 would be unlikely)

                  not elegant or clever - i would be interested to know if it can be done properly

                  Substitute (
                   "¶¶" & calc_list_ON_plus_EXTRA &  "¶¶" ;
                  [¶ & GetValue ( calc_list_ABSENT ; 1 ) & ¶  ;¶ ];
                  [¶ & GetValue ( calc_list_ABSENT ; 2 ) & ¶ ; ¶ ];
                  [¶ & GetValue ( calc_list_ABSENT ; 3) & ¶ ; ¶ ];
                  [¶ & GetValue ( calc_list_ABSENT ; 4 ) & ¶ ; ¶ ];
                  [¶ & GetValue ( calc_list_ABSENT ; 5 ) & ¶ ; ¶ ];
                  [¶ & GetValue ( calc_list_ABSENT ; 6 ) & ¶ ; ¶ ];
                  [¶ & GetValue ( calc_list_ABSENT ; 7) & ¶ ; ¶ ];
                  [¶ & GetValue ( calc_list_ABSENT ; 8) & ¶ ; ¶ ];
                  [¶ & GetValue ( calc_list_ABSENT ; 9 ) & ¶ ; ¶ ];
                  [¶ & GetValue ( calc_list_ABSENT ; 10 ) & ¶ ; ¶ ];
                  [¶ & GetValue ( calc_list_ABSENT ; 11 ) & ¶ ; ¶ ];
                  [¶ & GetValue ( calc_list_ABSENT ; 12 ) & ¶ ; ¶ ];
                  [¶ & GetValue ( calc_list_ABSENT ; 13 ) & ¶ ; ¶ ];
                  [¶ & GetValue ( calc_list_ABSENT ; 14 ) & ¶ ;¶ ];
                  [¶ & GetValue ( calc_list_ABSENT ; 15 ) & ¶ ; ¶ ];
                  [¶ & GetValue ( calc_list_ABSENT ; 16 ) & ¶ ; ¶ ];
                  [¶ & GetValue ( calc_list_ABSENT ;17 ) & ¶ ; ¶ ];
                  [¶ & GetValue ( calc_list_ABSENT ; 18 ) & ¶ ; ¶ ];
                  [¶ & GetValue ( calc_list_ABSENT ; 19 ) & ¶ ; ¶ ];
                  [¶ & GetValue ( calc_list_ABSENT ;20) & ¶ ; ¶ ] ; 
                  ["¶¶¶"; ""] ; ["¶¶"; ""]
                  )

                  • 6. Re: remove values from text string
                    philmodjunk

                    This custom function works for me:

                    //  RemoveSubList ( SourceList ; SubList )
                    //
                    // Return a list of all values in SourceList that are not also listed in SubList
                    // SourceList: A return separated list of values
                    // SubList: a Return separated list of values to be removed from SourceList

                     Let ( [ ListItem = GetValue ( SubList ; 1 ) ;
                               SubListRemanent = RightValues ( SubList ; ValueCount ( SubList ) - 1 ) ;
                               FilteredSourceLista = Substitute ( ¶ & SourceList & ¶ ; ¶ & ListItem & ¶ ; ¶ ) ;
                               FilteredSourceListB = Middle ( FilteredSourceLista ; 2 ; Length ( FilteredSourceLista ) -2 )
                              ] ;
                            Case ( IsEmpty ( SubList ) ; SourceList ;
                                       RemoveSubList ( FilteredSourceListB ; SubListRemanent )
                                     ) // case
                             ) // Let

                    • 7. Re: remove values from text string
                      RestaurantCharlie

                      @ Phil, can you point me in the direction of some good reading on recursive calc fields? They sound very usefull...

                      • 8. Re: remove values from text string
                        BruceCochrane

                        busy morning just had time to put it in - it works

                        if i sound amazed it's 'cos i am

                        40min later

                        broke it down - got it - but doing one from scratch 'll be a wee while

                        i'm with resteraunt charlie - need to do some reading - and thinking

                        and really many thanks - solved the problem - and several good lessons at the same time

                         

                        • 9. Re: remove values from text string
                          philmodjunk

                          Recursive expressions can really have a "smoke and mirrors feel" to them. I can still remember how strained my brain felt trying to understand the concept when I first saw a college instructor post one on the chalk board (yeah those dark colored rectangles where they wrote on them with hard white sticks called "chalk" back before we had whiteboards and other stuff. ;-) )

                          I wasn't "comfortable" with them, until I sat down with pencil and paper and "paper executed" one through a few recursive loops and then was able to write my own recursive function calls.

                          It may help to keep in the back of your mind that recursion works by breaking downt he execution into a series of steps where the value at each step is not computed, but rather is "stacked" in memory until the point is reached where the answer is trivial. The trivial answer is returned and then each "stacked" step is unstacked and evaluated one step at a time until the final answer is reached.

                          This "stacking of recursive calls", is in fact the biggest limitation of recursive calculations. Theres a maximum limit to how many such steps can be stacked up before you run out of memory. That's usually not a major issue with FileMaker Pro, but FileMaker Go users are finding that some recursive functions fail pretty quickly on an iPad or iPhone.

                          • 10. Re: remove values from text string
                            RestaurantCharlie

                            So they can behave like a script that goes through records and makes a return separated list of values? I've been trying to figure out a way to have a function that behaves like Select Distinct in SQL, where I could have a function return a list of distinct values in a related table.

                            When you say limits in the stack, are we talking about 10 steps? 20? 100? 1000?

                            I'm trying to break down the function you posted. I follow it through the let, but when you get to the case, I get lost. What does the RemoveSubList function do? Or what am I missing?

                            • 11. Re: remove values from text string
                              philmodjunk

                              Note that RemoveSubList is the name of this function. It "calls itself", but passes on simplified values to this next call in the parameters. Eventually, the parameters passed get so simple that there's an obvious answer. In this case, the parameters are simplified by removing the left (first) value of Sublist from SourceList, then the modified SourceList and all remaining values of Sublist get passed on to the new function call. When there are no more values to remove from the sourceList, the sublist is empty and recursion starts to "unstack" back to the original call--returning a copy of the source list that has all items from SubList removed From SourceList.

                              This won't work for a UniqueItems query as it removes all instances from the list.

                              This is a type of query that can be much easier to do in SQL than in FileMaker. It can be done however. The best approach depends on what you want to do with this query. Are you trying to find a set of records with duplicates filtered out or are you trying to produce a list of values from the table where duplicates have been filtered out?

                              Let me know what you want and I'll explain further. (And you might consider starting a new thread for this if you want to increase the chance that other contributers will chime in with their suggestions.)

                              • 12. Re: remove values from text string
                                RestaurantCharlie

                                Ahhhh.

                                So the way to loop the calculation is to call the calculation within the calculation, and the equivelent of the "exit loop if" in a script happens when the result has reached it's most simplified form?

                                Any ideas on the limits of how many layers it can stack? FMP and FM Go numbers would really help.

                                THANKS!

                                *I added another thread for the Select Distinct question: http://forums.filemaker.com/posts/8b9da309b4

                                • 13. Re: remove values from text string
                                  philmodjunk

                                  I really have no idea as to the limits and it can vary from system to system depending on the complexity of the function (Are you "stacking" large complex parameters each time?) and your available RAM. I only know that you can hit this limit much more quickly in Go due to the hardware limitations.