3 Replies Latest reply on Aug 22, 2012 9:18 AM by comment

    sort a calculated list


      In our MRP system I had to create a textfield called composition containing the ingredients of each formula.

      A formula is a "recipe"and contains the ingredients which are in the "products"file to produce each item.


      we have PRODUCTS

      we have FORMULAS

      and we have FORMULA_ITEMS, which contains the percentage.


      For example FORMULA of shampoo XX contains

      90% WATER

      3% Fragrance X

      7% LSS


      On every cosmetic you have to write the composition on the back. For this product this would read


      Aqua, sodium laureth sulfate ,rosmarinus officinalis, sodium laureth sulfate / glycol distearate / cocamide mea / peg 10 cetyl alcohol


      I use this calculation to create this text in the COMPOSITION field.


      Substitute( List ( fo_itens_PRODUTOS::prod_INCI_name ); ¶;", ")


      How can I achieve that the List itemsin the COMPOSITION field get written sorted by their percentage. The list I get now is sorted by creation order in the Products file, however I would like to have it created by the percentage each product appears in the formula.

      Thank you for you help.


        • 1. Re: sort a calculated list

          The List() function returns items sorted in the sort order defined for the underlying relationship.


          In this case, it would be probably best to have an unstored calculation field in the percentages table to pull the product's name, and use the List() function on this field.


          Message was edited by: Michael Horak

          • 2. Re: sort a calculated list


            thank you for you input.

            Where can I set the "sort order" of a underlying relationship.

            My fo_items table is a link file between a many to many relationship between FORMULAS and PRODUCTS.

            You call this flie "percentage table"..OK. Now... how could I make it, that the list, which now is sorted by the creation order of the PRODUCTS is sorted by the "percentage" value?


            Complicated? Hehe. Thank you anyway for your great help.



            • 3. Re: sort a calculated list

              The complication here is that the field you want to sort by is not in the table you want sorted. Therefore, you need to add a calculation field to the join table (which you called FORMULA_ITEMS and I called Percentages). The calculation is just this =






              pfroelicher wrote:


              Where can I set the "sort order" of a underlying relationship.


              In the Edit Relationship window, there is a 'Sort records' checkbox on both sides of the relationship. If this is left unchecked, the related records are sorted by the default (creation) order.