7 Replies Latest reply on Jul 28, 2012 4:24 PM by BobSchwenkler

    Replace/Delete values in value list help

    BobSchwenkler

      Title

      Replace/Delete values in value list help

      Post

      I've got a global field that's being used as a multi-key match field for a portal. The user clicks on a portal row and that ID number is added or subtracted from the global field. I started out using Substitute(valueList;valueToDelete & ¶;""). Only problem is if I've got eg. 28¶8¶ in my field, performing this function with valueToDelete=8 will return 2¶.

      I'm having a hard time thinking of a way to do this with the stock functions, and wondering if I'll need to create a recursive function to loop through the value list and look for exact matches. Thoughts?

        • 1. Re: Replace/Delete values in value list help
          philmodjunk

          Do it this way:

          Substitute(¶ & valueList & ¶ ;¶ & valueToDelete & ¶;¶)

          or you won't be able to delete the first/last items in your list.

          then use:

          Set Field [YourTable::valueList ; Middle ( YourTable::valueList ; 2; Length ( Yourtable::valueList ) - 2 ) ]

          to strip off the extra ¶ at the beginning and end of the list. (This isn't absolutely necessary, but keeps your list "clean".)

          With a bit of creativity, you can actually do both in a single step. I've kept them separate here so that it's easier to understand.

          Edit note: I've updated this post a half dozen times--had to go back and check a demo file where I have this working--before I got it to this point.

          • 2. Re: Replace/Delete values in value list help
            BobSchwenkler

            Ah, ok. That's clever. Thanks!

            • 3. Re: Replace/Delete values in value list help
              BobSchwenkler

              A check back in. This is mostly working for me, but I'm still having problems with the first value on my list.

               

              If I have 28¶8¶ and try to remove 28, there's no carriage return at the beginning of the list, so my script thinks it's not there. I've spent a bunch of time trying to figure how to smooth this out and nothing graceful is coming to me.

              • 4. Re: Replace/Delete values in value list help
                philmodjunk

                Which is why I added one inside the substitute expression. Note the red pilcrow:

                Substitute( & valueList & ¶ ;¶ & valueToDelete & ¶;¶)

                • 5. Re: Replace/Delete values in value list help
                  BobSchwenkler

                  Yeah, I've got that in there. Here's my actual calculation, it seems the same, right?

                  Substitute(¶ & Order Ingredients__Primary TO__orders::selectedIngredientIDs_kfgn & ¶;¶ & Order Ingredients__View Selected Batch Ingredient Qtys__ingredients::_kpln__ingredientID & ¶;¶)

                  • 6. Re: Replace/Delete values in value list help
                    philmodjunk

                    Hmmm, tested it in the data viewer with this expression:

                    Let ( valueList = "28¶3¶4" ; Substitute(¶ & valueList & ¶ ;¶ & 28 & ¶;¶) )

                    and got:

                    ¶3¶4¶

                    Thus, it works, but leaves an extra return at the beginning and end of the list. This will not effect the function of the list, but is something that could be cleaned up if so desired.

                    And that's the purpose to this step:

                    Set Field [YourTable::valueList ; Middle ( YourTable::valueList ; 2; Length ( Yourtable::valueList ) - 2 ) ]

                    It deletes the first and last characters, the extra returns. You can even set it up like this to do it all in one expression:

                    Let ( Da_list = Let ( valueList = "28¶3¶4" ;
                                                Substitute(¶ & valueList & ¶ ;¶ & 3 & ¶ ; ¶ )
                                              ) ;
                             Middle ( Da_list  ; 2; Length ( Da_list ) - 2 )
                           )

                    • 7. Re: Replace/Delete values in value list help
                      BobSchwenkler

                      Ah. I was messing myself up, I had the PatternCount for the if statement that directed the script to add or delete a value set up incorrectly. It's working great now, thanks so much!