Sales Commission problem

[Erick P]

I have a customer table related to an orders table. I'm trying to make a calculation or write a script to calculate commissions.

The commission will be calc'd on a set of 4 Orders for customers that I've flagged as "commissionable". For new customers it will be the first 4 orders. For old 'Inactive" customers it will be on their most recent 4 orders.

I'm not sure the best way to get only the records that fit the criteria. I've had no luck with a calculated field. I've tried a script but it just loops through all the orders.

Any pointers are much appreicated.

Erick P.

[Fitch]

Not super-elegant, but try this:

 

Let( count = Count( related::orders ) ; 



  Case( type = "inactive" ; 



     Sum( 

       GetNthRecord( orderTotal ; count ) ; 

       GetNthRecord( orderTotal ; count-1 ) ; 

       GetNthRecord( orderTotal ; count-2 ) ; 

       GetNthRecord( orderTotal ; count-3 ) 

       ); 

 

     Sum( 

       GetNthRecord( orderTotal ; 1 ) ; 

       GetNthRecord( orderTotal ; 2 ) ; 

       GetNthRecord( orderTotal ; 3 ) ; 

       GetNthRecord( orderTotal ; 4 ) 

       )

    )

  )

[Søren Dyhr]

Not that it's more elegant, but here is something repeating fields are good at:

 Let(

    tt =

    Case( Extend( type = "inactive" ); Count( Extend( related::orderTotal ) ); 4 );

    Case(

        Get( CalculationRepetitionNumber ) ≠ 5;

        GetNthRecord(

            Extend( Orders::OrderTotal );

            1 + Abs( tt - Get( CalculationRepetitionNumber ) )

        );

        Commission[ 1 ] + Commission[ 2 ] + Commission[ 3 ] + Comission[ 4 ]

    )

)

...where only the last repetition is visible, to deliver the result! The field in itself is a five cells repeater-calc' ...called Commission...

--sd

[comment]

It could also be done this way:

Let ( [

list = List ( Orders::TotalAmount ) ;

part = Case ( Inactive ; LeftValues ( list ; 4 ) ; RightValues ( list ; 4 ) ) ;

formula = Substitute ( part ; ¶ ; "+" ) & "0"

] ;

Evaluate ( formula )

)

[Erick P]

All,

Thanks for the replies. I'm pretty new to filemaker and db's in general, so I'm playing with these options. I don't fully understand all the functions in use, but I'm learning.

Thanks,

Erick.

[Fitch]

Since you have FM Advanced, a great way to learn is to open up the Data Viewer and plug in each piece of my formula, or Michael's, which is clever indeed, and see what you get.

An easy way to do this is to paste two copies of the formula, and surround one copy with:

/* */

The text in between the asterisks is "commented out" and will be ignored by the calculation engine.

[Fitch]

Hmmm. I just reread your OP. Did we all solve the wrong problem? Is your goal to find the 4 records in question and set the "commissionable" flag field?

That's not what these formulas do: these calcs total up the first 4 or last 4 related records based on whether the customer is inactive or not. No flag field needed if you do it this way; but maybe you want the flag field for other reasons?

[Søren Dyhr]

It could also be done this way

I know I was about to suggest it too, but reminded myself about the actual parsing going on with Evaluate( is a snag, according to Filemakers own engineers.

--sd

[Søren Dyhr]

That's not what these formulas do: these calcs total up the first 4 or last 4 related records based on whether the customer is inactive or not. No flag field needed if you do it this way; but maybe you want the flag field for other reasons?

But then make a cartesian portal showing Ugo's method related from a jump further, then do you have both aspects covered.

--sd

[comment]

reminded myself about the actual parsing going on with Evaluate( is a snag

That's not a reason to dismiss Evaluate() altogether. I dare to predict you won't notice a speed difference in this case.

Re the real problem:

I didn't catch if the records flagged as "commissionable" are Customers or Orders. In any case, it seems that the calculation is done in Customers. So if the flag is in Customers, it's just a matter of adding another test. If it's in Orders, it should be simple enough to add a filtered relationship showing only flagged Orders, and point the calc that way.

[Søren Dyhr]

That's not a reason to dismiss Evaluate() altogether.

True indeed! ...it's a change in strategy on my behalf, instead of banning repeaters without nuance, could it make sense to display where they actually work - such as here finite iterations.

--sd