3 Replies Latest reply on Feb 24, 2012 12:07 PM by philmodjunk

    Slurp all fields' values into one variable from found set?

      Title

      Slurp all fields' values into one variable from found set?

      Post

      Is there a simple one-step way to slurp up a field's value for an entire found set into one variable?  Without a loop, that is.

      Say you search your user table for a subset matching a criteria, is there a way to set a variable to each UserID in that found set?  (I want the list of UserIDs so that I can populate records in another table a bit later in the script.)

       

      On a related note, do dynamic value lists require that you use them from a specific context (i.e. one of the tables used in the definition of that list, either as a basis for the layout or some other context involving one or other of the tables)?  I was trying to use an already defined value list from our system using: 

           Set variable [ $List ; Value:ValueListItems (get(filename); ListName)]

      But my variable is coming back empty.

       

      Thanks,

      -J

        • 1. Re: Slurp all fields' values into one variable from found set?
          philmodjunk

          Put just the ID on a new layout based on the same table occurrence. After performing a find, you can switch to this layout and use Copy All Records to copy all the values as a return separated list to your clipboard. Then you can paste this list into a field to get your list of values. This will destroy any data the user has previously copied to the clipboard--which can be a problem, but you didn't want that loop.

          Depending on how you "populate records in another table", there may be alternatives to capturing the values in a list.

          With regards to value lists, what do you mean by "dynamic lists"? If this is a conditional value list where a relationship is used to limit the values listed, then you do need to pay attention to "table occurrence context". In Manage | value lists, there's a table occurrence specified as the "Starting From" table. The relationship between the current record of that table occurrence and the table from which the values are listed determines what will appear in the value list. If you are attempting this from a layout based on a different table occurrence, then you might get no values returned. It depends on the relationships and which layout you are on.

          Table Occurrences:

          These are the "boxes" found in Manage | Database | Relationships. They are what you are selecting in most parts of FileMaker when you are asked to choose a "table". They are what are listed in the Show Records From drop down found in Layout setup... for example. Thus the layout that is current when a script step executes determines how any references to tables and fields evaluate.

          For more on the subject, see: Tutorial: What are Table Occurrences?

          • 2. Re: Slurp all fields' values into one variable from found set?

            In your first method, copying to the clipboard, you mention pasting into a field.  Does this result in all values in the list becoming a multi-valued entry in one field?  I am trying to create new individual records, one for each ID in the Users table (of the found sub-set that is). 

            Yes, 'dynamic list' is a 'conditional list'.  It is using a relationship to restrict the Users to a certain sub-set.  It 'Starts from...USERS' and goes to another USERS TO with the restricted relationship.  (This is the same PROD flag relationship I asked about in another thread.)  It would seem, since the list already specifies both a start and end point, that that list would be usable anywhere and still be valid.

            Thanks for the resource link.

             

            -- J

            • 3. Re: Slurp all fields' values into one variable from found set?
              philmodjunk

              Does this result in all values in the list becoming a multi-valued entry in one field?

              Yes.

              Creating 'new individual records' could also be done with an import records step to import the ID's from your found set. (And import records can be used to move records from one table to another within the same file.)

              It would seem, since the list already specifies both a start and end point, that that list would be usable anywhere and still be valid.

              Those "start and end points" all depend on the current record of the "starting from" table occurrence. If you are on a layout to an unrelated table occurrence, FileMaker has no way to determine the current record and then use the relationship to filter values.