2 Replies Latest reply on May 7, 2014 10:09 AM by jwshelton22

    Only show Tope 10 Expenses in chart

    jwshelton22

      Title

      Only show Tope 10 Expenses in chart

      Post

           We are using FM 12 Advanced.

           I am not sure how to tackle this task.

           A Client has 20 expenses items, each item is a record. I want to show a graph of the 10 highest expense items. How do I set the rank or sort by expense amount and only show the top 10?

           Clients are in a table with expenses being in a related table. Current relationship is Client:::ClientID_kp to Expenses::ClientID_kf.

            

           Thanks,

           Jim

        • 1. Re: Only show Tope 10 Expenses in chart
          philmodjunk

               Do you have a table with one record for each expense item (20 records total in your example) linked to your expense transactions?

               If you had such a table, you could base your layout on that table, sort the records in descending order by total expense and omit the last 20 records from the found set.

               There are also ways to use ExecuteSQL to produce delimited data for charting and there are ways to limit the data returned by ExecuteSQL to just the first 10 rows of data returned by this function.

          • 2. Re: Only show Tope 10 Expenses in chart
            jwshelton22

                 I created 2 new fields

                 Client:::ExpenseRank which I set at 10

                 Expenses::ExpenseRank

                 Created new relationship

                 ClientID_kp=ClientID_kf   and

                 Client::ExpenseRank greater than or equal to Expense::ExpenseRank

                 and then in a script I

                 Get Found set of Expenses

                 Sort by Total$ descending

                 Set Rank

                 This works great and I was already running script when going to this layout.

                  

                 Jim