Commission for employees based on order tags

V5 Question
1

EDIT: This has now been solved by @emre. Scroll down to see the final report I used.

Sometimes a customer will buy several things at once. Different staff might have sold each thing, therefore my commissions are assigned at the ORDER level instead of at the TICKET level.

I have an Order Tag Group called commissions, which can be assigned to items that have been sold. Sometimes, multiple members of the team can be involved in the sale of just one item (and they all should get some commission), therefore it is possible to select multiple people.

image.png1268×189 9.16 KB

Using these tags I have been able to produce the following report:

image.png1275×563 35.3 KB

You’ll notice that the “commisionable amount” is sometimes less than the base price of the item sold. This is because I remove any item discounts applied and I remove a proportion of the total ticket discount, before finally multiplying the commision-able amount by the employees particular commission rate.

My question

I need to make two small tweaks to this report.

  1. Need to add commission totals per person (where indicated by $#/## in the screenshot above). These values are the result of a lot of calculations, so I wasn’t sure if and how I can use some .sum commands to provide a total.
  2. Need to divide the “commissionable amount” by the number of people involved in the sale. You can see in the ‘staff’ column we have a list of the names of people involved in the sale. I couldn’t work out how to convert this to a number.

This is the current report


[Commissions:.8,1.5,1,1,1,1.5,1.5,1,1,1,1,1,1]
@Caro,Kim,Nati,Matt
>>Ticket#|Date|Total|Discount|Customer|Staff|Item|Item price|Item Disc|Ticket disc|Commissionable|Commission Rate|Commission
> $1
{REPORT ORDER DETAILS:T.TicketNumber.asc,T.Date,T.TotalAmount,CA.Discount,EN.Customers,OT.Commission,O.MenuItemName,O.Price,OTP.Discounts,=[CA.Discount]*(([O.Price]+[OTP.Discounts])/([T.TotalAmount]-[CA.Discount])),=([O.Price]+[OTP.Discounts])+[CA.Discount]*(([O.Price]+[OTP.Discounts])/([T.TotalAmount]-[CA.Discount])):(OT.Commission=$1):{0}|{1}|{2}|{3}|{4}|{5}|{6}|{7}|{8}|{9}|{10}|{REPORT ENTITY DETAILS:EC.DiveCommission:(ET=Employees) AND (EN=$1)}|[=F({10}*{REPORT ENTITY DETAILS:EC.DiveCommission:(ET=Employees) AND (EN=$1)})]}
> |||||||||||Total for $1| $#.##
1 Like
2

To calculate summaries you need to have a separate R.O.Details tag that does not have values like Ticket#,Date, Customer, etc
 and use summary expressions to calculate totals.

We currently support these summary expressions.

[T.Field1.Sum]*[T.Field2.Sum]                   Multiplies Field1.Sum by Field2.Sum. 
([T.Field1]*[T.Field2]).Sum                     Sum of Field1 * Field2.
[([T.Field1]*[T.Field2]).Sum]/[T.Field2.Sum]    Divides sum of Field1 * Field2 by sum of field 2.
2 Likes
3

Sorry @emre, I’m not sure I understand, I am having difficulties making this work for the proportion of ticket discount*

To focus on the problem, I have created two tables which show the problem I am having:

Here is the report:

[table1:1,2]
@Kim,Nati
>>Item price|Proportion of ticket discount
> $1
{REPORT ORDER DETAILS:O.Price,=[CA.Discount]*(([O.Price]+[OTP.Discounts])/([T.TotalAmount]-[CA.Discount])):(OT.Commission=$1)}

[table2:1,2]
@Kim,Nati
>>Item price|Proportion of ticket discount
> $1
{REPORT ORDER DETAILS:O.Price.sum,=([CA.Discount]*(([O.Price]+[OTP.Discounts])/([T.TotalAmount]-[CA.Discount]))).sum:(OT.Commission=$1)}

What am I doing wrong?

By the way, the idea of “proportion of ticket discount” is that imagine this ticket


Pizza $10
 - Sold by Caro
Wine $100
 - Sold by Kim

Subtotal: $110
Happy face discount: -$20
Payment total: $90

if Caro sells a $10 pizza and Kim sells a $100 botttle of wine, but then we give a $20 discount on the entire ticket, then you can expect


  • $1.82 of the $20 discount to come off Caro’s commisionable amount and
  • $18.18 of the discount to come off Kim’s commissionable amount. Does that make sense?
4

Sorry to bump this up. I have been going back and forth on this so many times, but I don’t seem to be able to get the right results. @emre mentioned using a separate report but I couldn’t get the right results using that.

Any ideas how I could generate the totals I need, or turn the ‘Staff’ column which lists tags (Matt,Nati) in to a count of the number of tags added (2)?

5

Honestly I didn’t tested it with that much nested parenthesis. I’ll check that.

btw. what you want to have looks like just O.ExactTotal field. Did you tried using that?

Edit: Seems like I made a typo in expression generation. I fixed that and now the expression syntax is correct but I won’t be able to test if it generates a valid report as I don’t have your data. I’ll be glad if you check that and let me know after I release the next update.

6

Thanks @emre :slight_smile

Yes - it is quite a lot of ()'s :slight_smile:

The reason for such a lot of calculation is that for each type of diving we have there are several different modifiers which affect the price (but these modifiers are not eligible for commission) . This is the reason for using the Price and not the ExactTotal.

Think of it as being a $10 Pizza


Pizza                            $10
 - Frequent customer discount   -$2
 - Oaxaca cheese                 $1
======
Total:                           $9

Commissionable should be = $8

Pizza                            $10
 - Oaxaca cheese                 $1
 - Spicy sauce                   $1
 - Jalapenos                     $1
 - Other Mexican cliché          $1
======
Total:                           $14

Commissionable should be = $10

Here is an example of my order tags:

image.png785×352 15.4 KB

7

Perfect - Thanks @emre, I will look out for the next release.

Any idea how I can turn the list of tags applied in to a .count result? (The column that currently says “Matt,Nati” I would like to say “2” because 2 tags have been selected).

8

I added OTC.<tag name> to read order tag counts. For next update.

1 Like
9

Awesome, thanks so much :slight_smile:

10

Hi @emre

Sorry, another question

Can you please allow O.ItemTag as a condition, so we don’t have to duplicate the item tag as a custom tag

My staff get different commission rates depending on if they sell diving (services) or equipment (products).

I currently have items set up using the “Tag” to differentiate between diving and equipment.

The group code is then used to sub-divide the large equipment menu so it is easier to navigate


image.png1179×272 17.8 KB

I have custom Entity fields called DivingCommission and EquipmentCommission which hold the different rates for each different member of staff.

In my custom report for commissions I would like to be able to first run through all Diving items and then all Equipment items, but I cannot use AND (O.ItemTag=$2) as a condition, for example in the following simplified report:

[Tag issue:.5,1,1,1,1,2,.5,.5]
@{REPORT ORDER DETAILS:OT.Commission.asc::,}
@Diving,Equipment
>>Ticket#|Total|Customer|Staff|Item type|Item|Item price|Commission Rate
> $1
{REPORT ORDER DETAILS:
	T.TicketNumber,T.TotalAmount,EN.Customers,OT.Commission,O.ItemTag,O.MenuItemName,O.Price	
	:(OT.Commission=$1) AND (O.ItemTag=$2)
	:{0}|{1}|{2}|{3}|{4}|{5}|{6}|{REPORT ENTITY DETAILS:EC.$2Commission:(ET=Employees) AND (EN=$1)}
}

The only options that seem to be available are:

  • MG (Menu Group) - The problem is that I have many different Menu groups
  • MT (Custom product tag) - This does work, but it seems silly to add a new custom tag to all products which is the same as the product tag.

To prove the concept of the report, I created a new custom product tag called CommissionType and manually assigned it the same value as the actual item tag


Using these tags I was able to make this report work correctly


[Tag issue:.5,1,1,1,1,2,.8,.8]
@{REPORT ORDER DETAILS:OT.Commission.asc::,}
@Diving,Equipment
>>Ticket#|Total|Customer|Staff|Item tag|Item|Price|Comm Rate
> $1
{REPORT ORDER DETAILS:
	T.TicketNumber,
	T.TotalAmount,
	EN.Customers,
	OT.Commission,
	O.ItemTag,
	O.MenuItemName,
	O.Price	
	:(OT.Commission=$1) AND (MT.CommissionType=$2)
	:{0}|{1}|{2}|{3}|{4}|{5}|{6}|{REPORT ENTITY DETAILS:EC.$2Commission:(ET=Employees) AND (EN=$1)}
}

Output (as you can see the commission rate is different per item type)


image.png722×434 22.2 KB

The question


Can you please allow O.ItemTag as a condition, so we don’t have to duplicate the item tag as a custom tag

11

You can use Custom Tags instead of Tags. We unnecessarily have duplicate tagging features but custom tags implemented after Tag feature and I left it for backwards compatibility as people uses it for printing Foreign Language names of products. I’ll remove tag field on a major version update.

12

OK, so I will use Custom Tags. Thanks @emre

1 Like
13

OK
 All looks good. The numerous ()'s and .sum's all seem to be working perfectly now. I thought I would dump a bunch of my learning and explain what I’ve done here, just in case it is helpful to anyone else.

Just to recap, this is how we have commissions set-up


  • each employee can have different commission rates for different type of products
  • more than one employee can sell each order item
    when this happens, the commisionable amount is split between the number of employees who sold the item
  • many different order items can be sold by many different employees on the same ticket
  • each product can have discounts applied via order tags
    if we discount the item, then we reduce the commission available
  • each product can have modifications which add to the price
    these additional price modifications are not commissionable
  • we can give discounts across the whole ticket
    if we do this, then the discount is split proportionally among all items on the ticket, reducing the commissionable amount of each item

Some specific SambaPOS stuff for this:

  • Requires SambaPOS 5.1.61 (20/02/2017 16:55) or higher
  • Create Employee entities with custom fields to store personalized commission percentages
    For example, many employees have DivingCommission=0.10 and EquipmentCommission=0.06
  • Create a custom product tag called CommissionType to store values to differentiate which commission rate should be applied to each product
  • Create an Order Tag group called commissions which has the names of employees as options tags
  • Create the custom Commmissions report (my example is below).

To show how this all works, I have created an example report based on the following 2 tickets:

TICKET #1

1 Playa del Carmen ($70)          $70
  * Sold by Caro & Kim
  * Frequent customer discount   -$7
  * Equipment rental             +$15

1 Cozumel ($140)                  $140
  * Sold by Caro & Kim
  * Frequent customer discount   -$14
  * Equipment rental             +$15

1 Cenotes ($115)                  $115
  * Sold by Spyro
  * Frequent customer discount   -$11.50
  * Equipment rental             +$15

3 Marks - Visor Negro ($95)       $285
  * Sold by Kim & Nati

3 PADI Books ($45)                $135
  * No commission on books
==============================================
Subtotal:                         $757.50
5% discount:                      $37.50
Payment:                          $720.00

TICKET #2

4 Cenote Extreme ($160)           $640
  * Sold by Caro
  * Equipment rental             +$60

4 Playa del Carmen ($70)          $280
  * Sold by Matt
  * Equipment rental             +$60

1 Leonardo Dive computer ($350)   $350
  * Sold by Spyro & Matt
==============================================
Subtotal:                         $1390.00
10% discount:                      $139.00
Payment:                          $1251.00

The resulting report looks like this:
As you can see, Caro gets 3% for diving sales. Everyone else gets 10% and everyone gets 6% for equipment sales

image.png1373×808 66 KB

The custom report is this:

[Commissions:.5,1,1,1,1.5,1.5,0.5,2,1,1,1,1,1,1]
@{REPORT ORDER DETAILS:OT.Commission.asc::,}
@Diving,Equipment
>>Ticket#|Date|Total|Discount|Customer|Staff|Qty|Item|Line price|Line Disc|Ticket disc|Commissionable|Commission Rate|Commission
> $1
{REPORT ORDER DETAILS:
	T.TicketNumber,
	T.Date,
	T.TotalAmount,
	CA.Discount,
	EN.Customers,
	OT.Commission,
	O.Quantity,
	O.MenuItemName,
	=[O.Quantity]*[O.Price],
	OTP.Discounts,
	=[CA.Discount]*((([O.Quantity]*[O.Price])+[OTP.Discounts])/([T.TotalAmount]-[CA.Discount])),
	=((([O.Quantity]*[O.Price])+[OTP.Discounts])+([CA.Discount]*((([O.Quantity]*[O.Price])+[OTP.Discounts])/([T.TotalAmount]-[CA.Discount]))))/[OTC.Commission]
	:(OT.Commission=$1) AND (MT.CommissionType=$2)
	:{0}|{1}|{2}|{3}|{4}|{5}|{6}|{7}|{8}|{9}|{10}|{11}|{REPORT ENTITY DETAILS:EC.$2Commission:(ET=Employees) AND (EN=$1)}|[=F({11}*{REPORT ENTITY DETAILS:EC.$2Commission:(ET=Employees) AND (EN=$1)})]
}
> {REPORT ORDER DETAILS:=([O.Quantity]*[O.Price]).sum,=([O.Quantity]*[OTP.Discounts]).sum,=([CA.Discount]*(([O.Quantity]*([O.Price]+[OTP.Discounts]))/([T.TotalAmount]-[CA.Discount]))).sum,=(((([O.Quantity]*([O.Price]+[OTP.Discounts])))+[CA.Discount]*(([O.Quantity]*([O.Price]+[OTP.Discounts]))/([T.TotalAmount]-[CA.Discount])))/[OTC.Commission]).sum:(OT.Commission=$1) AND (MT.CommissionType=$2):|||||||$2 Totals\:|{0}|{1}|{2}|{3}|{REPORT ENTITY DETAILS:EC.$2Commission:(ET=Employees) AND (EN=$1)}|[=F({3}*{REPORT ENTITY DETAILS:EC.$2Commission:(ET=Employees) AND (EN=$1)})]}

@emre - I would have liked to break down the second report tag (the totals one) line-by-line to make it easier to read, but when I did this I couldn’t make it appear correctly in bold. It would work OK without using bold, but it seems I was adding the > character in the wrong place


3 Likes
14

Love seeing people use SambaPOS in unconventional ways it really shows just how powerful it is.

2 Likes
15

Cheers KenDash - I am really pleased each time you guys are able to guide me to a new solution. I believe the only worthwhile POS is one that works perfectly for the business, and the customisation options within SambaPOS means that with time and patience pretty much anything is possible.

A really big thank-you must go to @emre because a few things I’ve wanted to do have only been possible by him implemented some new features and tweaks to the core of SambaPOS.

Hopefully this thread will be useful for others in spreading tips and commissions amongst the teams you have in your restaurants.

4 Likes
16

Hi Please how do create custom Entity fields

17

Go to Manage > Settings > Entities > Entity Types > Your Entity Type > Custom Fields

image
image.png1920×1080 100 KB

1 Like
18

How do i add 1 .5 % as the rate and how do i link them with the items tag

19

See my response in your other thread I gave you the correct syntax finally. sorry.

20

In the end, I used a lot of these calculation formulae, but instead of displaying the values in a report, I actually added the values to the staff’s “customer account” each time a ticket was processed.

I am crazy busy right now, but I will try to share more details on this when I get a moment