5 Replies Latest reply on May 8, 2012 2:25 PM by suite48

    adding field values to selected records



      adding field values to selected records


      I work with a field that has a bunch of multi-choice pre-defined value options (I call the field "tags").  i am looking for an easy way to automatically add values to this field in the set found as a result of ad hoc queries done in other fields.  

      "Command =" doesn't work, as it replaces the current values.

      I prefer not to change the "tag" field to a calculation because most often I will manually add field values. When done automatically, it should accomodate not-predefined ad hoc queries that could be different each time.

      Anybody any suggestions?

        • 1. Re: adding field values to selected records

          Set Field [YourTable::YourField ; List ( YourTable::YourField ; "newValueHere" ) ]

          Will append the new value to the end of the other values already present in the field.

          List ( YourTable::YourField ; "newValueHere" ) can also be used with Replace Field Contents to do a batch update of your found set if you select the calculation option.

          • 2. Re: adding field values to selected records

            perfect, thanks!

            • 3. Re: adding field values to selected records

              one more related question, which is basically the opposite scenario: is there also something to the equivalent of "remove from list"?  Here the scenario is the opposite: for a found set of records I'd like to remove a specific value from a field that already contains multiple values.

              • 4. Re: adding field values to selected records

                With care, Substitute can be used to remove specified list items.

                Let ( List1 = Substitute ( ¶ & YourTable::YourField & ¶ ; "¶newValueHere¶" ; ¶ ) ;
                        Middle ( List1 ; 2 ; Length ( List1 ) - 2 )

                This approach makes sure that deleting "Apple" from the list doesn't change "Applesauce" into "sauce". ¶ stands for a carriage return and there's a button to add it to your calculations in the specify calculation dialog box. This expression puts an extra ¶ at the beginning and end of the text, long enough for substitute to remove the specified item, then removes them.

                Substitute is case senstive so watch your capitalization.

                BTW, there's a pretty cool trick you can use with these calcuations. you can set up a portal of items with the fields in the portal formatted as buttons that perform a script that inserts the item if it's not in the list and deletes it if it is. Conditional formatting can be used to show which items are currently in the list. This produces what is essentially a check box group, but one that can have a scroll bar if there are more items than there is space to list them all at the same time...

                • 5. Re: adding field values to selected records

                  Fabulous!  Got to the remove stuff to work.  I'll look into the portal solution you mentioned to fancify it...