6 Replies Latest reply on Mar 8, 2012 4:23 PM by TobyWimberley

    Combining field contents



      Combining field contents


      I have a logic question.  Say I have a layout with a portal that displays an individual finished product. The main form has all of the attributes pertaining to the finished product and the portal contains the individual ingredients and their percentage that make up the finished product.  I need a calc field that collects these ingredients separated by ", " in descending order.  What would be the easiest way to achieve this? 



      Toby Wimberley

        • 1. Re: Combining field contents

          Take either your existing relationship on which your portal is based or make a new occurrence of the portal's table and related it in the same way. Either way, specify a sorted order for the relationship that matches the descending order you want for your list of ingredients. If this sort order is acceptable for your portal, use the original relationship. If that would mess up your list of ingredients in the portal, use the duplicated table occurrence so that your portal's order is unchanged (or specify a sort order for the portal in portal setup...).

          Then this calculation will return your sorted list of ingredients:

          Substitute ( List ( SortedTableOccurrence::IngredientField ) ; ¶ ; ", " )

          To specify a sorted order for a relationship:

          Open Manage | Database | Relationships and find the relationship line for the relationship.

          Double Click It.

          Click the sort check box for the table you want to have a sorted order and specify your sort order.


          A sorted relationship will affect:

          1. The order of records in an unsorted portal
          2. The order of records pulled up by a go to related records step
          3. The order of values returned by the List function
          4. Which record is returned by the Last function.
          5. Which record returns a value to a direct reference to the related table such as RelatedTable::Field
          • 2. Re: Combining field contents

            That worked perfectly except for sorting..  I'v attached a screenshot of the relationship graph and the sorting window. The trouble field is IngredientStatement, which imports the field contents from Ingredient_Description_en and hope to get it sorted by percent.

            Im sure its something simple and that i'm just not seeing it...


            Thanks for the help,


            • 3. Re: Combining field contents

              If you want the Ingredient_Product records listed in descending order by percentage that should work--provided that Percentage is either a number field or a calculation field with "number" specified as the return type. I'm assuming you want to define the List function as a calculation field in Product or in a script step that is performing with a Layout based on Product as the current layout.

              I suspect though that you tried to list records from Ingredients. I don't know if the sort order as specified here would work in that case or not--haven't tested that variation. If you are trying to list a name field from Ingredients, try this and see if it works: Define a calculation field in Ingredients_Products defined as: Ingredients::Name_en. Then refer to this field in your list function.

              • 4. Re: Combining field contents

                Actually I'm wanting the Description_en from INGREDIENTS sorted by percentage in INGREDIENTS_Products and store it in PRODUCTS... 

                • 5. Re: Combining field contents

                  I suggest following my suggestion for a calculaiton field, but use Description_en instead of Name_en in the calculation.

                  • 6. Re: Combining field contents

                    AWESOME!!!!  You Rock!!  Works like a champ!


                    Thanks again!