3 Replies Latest reply on Dec 2, 2014 11:06 AM by philmodjunk

    Summary Field - "List of" usage

    miw

      Title

      Summary Field - "List of" usage

      Post

      I have two tables with the following relationship:

      Set----<Items

      Set::__pkSetID = Items::_fkSetID

      So many items can be linked to one set.  My goal is to get each of the ItemIDs for a Set identified in one field.

      I noticed in the latest version of FM Pro there is a summary field calculation for "List of".  I am thus in the Set table trying to get a Summary calculation for a list of the ItemIDs in a Set.  However, only the current active ItemID shows up.  For example, if my active Set has links to two Items where Item IDs are 1 and 22, I want to see my Summary field showing both 1 and 22.  I have tried to show the summary calculation both summarizing repetitions as "all together" or "individually" but get the same single result.  Am I going about this the wrong way?  How can I get a summary of the ItemIDs that are all linked to an individual Set in one field on the SET table?

        • 1. Re: Summary Field - "List of" usage
          philmodjunk

          The "List of" summary field has to be defined in the table where the field you want to "list" is defined so you could define this field in the Items table, but then place it on the Set layout.

          But you can also define a calculation field in set that uses the LIST function to list your items: List ( Items::_fkSetID )  or whatever field in Set that you want to use to produce your list.

          A third option is ExecuteSQL, which can produce such a list, sort it, filter out values from records that don't meet specified criteira, omit duplicates...

          • 2. Re: Summary Field - "List of" usage
            miw

            Is there a way to create another field that will not show the result in a (delimited) list but as a text string?  I tried to use the getastext, and trim functions but they did not work.  Ultimately, what I am trying to get in my example above is a field result that shows the list as a line of text as follows "1 22" (with a space between the first ID and the second ID).

            • 3. Re: Summary Field - "List of" usage
              philmodjunk

              All of the options that I mentioned produce a text string result--but with returns instead of spaces between the values.

              ExecuteSQL can specify the space character as the "record delimitter" to produce what you want.

              Substitute ( List ( Items::Field ) ; ¶ ; " " )

              Substitute ( Items::ListOfSummaryFieldHere ; ¶ ; " " )

              would also produce such a list of values.