3 Replies Latest reply on Jan 14, 2015 8:56 AM by DavidJondreau

    Is there a way to apply a calculation to each List function result?

    datadesigner

      I need to apply a commission percent to related payment records and show the calculated value applied to each payment in a list in a record for reporting purposes. Using the List function I can get the list of each full related payment to appear properly in the Salesman's Order Split record, but cannot find a way to apply that salesman's split percentage to each payment that is appearing in the list function field and then just show that result as a list. I have tried the Extend function ahead of my List function in the calculated field but that doesn't work.

      Anyone have an idea of what I'm missing? Or maybe I'm going at this from the wrong direction(?).

        • 1. Re: Is there a way to apply a calculation to each List function result?
          jbrown

          Good morning.

          I don't think you can use the list function like this, to concatenate two columns together. You can use the list function to get the value of a calculated field that concatenates the paymentand the commission percent.

           

          The other option is to build the list through a script and present that in a text field or by other means.

           

          You'd find all the payments of a sales person. Go to the first record and get the payment  in a variable called $Payment.  Then get the calculated commission percent and set variable $Payment = $Payment & [calculated percent]

           

          Then set a variable called $List as List ($List ; $Payment).This, of course, will add the value of $Payment to the end of the list.

           

          Loop through all the payments of that sales person and then finally set a field to the contents of $List.

          • 2. Re: Is there a way to apply a calculation to each List function result?
            mtwalker

            Is the Salesman's commission rate is stored in the Salesman table? If so you have access to it via the Salesman - Order relationship.

            Order::Commission = Order::Amount * Salesman::commissionRate

             

            You can then use a summary field that is the Sum of Commission to create a report of the commission due.

            • 3. Re: Is there a way to apply a calculation to each List function result?
              DavidJondreau

              I'd say wrong direction. You probably need to do some table restructuring. It depends on whether Payments apply to more than one invoice and can multiple salespeople draw commission from the same order. It's hard to tell what tables you already have and what your requirements are.

               

              But possibly not, what about storing the commission percentage in the Payment table as an auto-enter calculation? Do your commission calculation in Payments and report on that table using Summary fields.