Hi Knacksters, I’m trying to figure out how to do something and I see lots of posts about similar topics but many are either not answered or the answer doesn’t really match what I’m looking for so I hope the community can help.
I am building a tool to track sales commissions for our sales reps. The rate that gets applied to a sale is based off the the sale date and the reps rate on that date. I have a table with a list of Sales Orders and the date those sales occured and which Rep made the sale. I have another table with a list of Reps and their commission rates. There may be multips entries in this table for each rep as their rates get adjusted over time. So for example Rep A may have 10% for all sales in 2022 but 15% for all sales in 2023. I have a Start Date and End Date for each rate record. I have a third table with invoice records. Each invoice is connected to a sales order. Once an invoice gets marked as paid I need to create a commission payment record that pulls the Rep and Date from the Sales Order data, looks up the appropriate commission rate, and then calculates the owed commission amount. Any ideas on how I might be able to do that?
Hi @John_Prescriptive.Solutions, I’ve definitely come across a similar scenario before.
- Will the commission period be based on sales order creation date, invoice creation date, or invoice paid date?
- Is a commission payment record necessary, or can the commission payment data sit within the related invoice record?
It depends on the above whether it’s be some simple JS/CSS in the background that maps the most recent commission rate connected to the invoice’s salesperson, OR if it requires an API call to find the commission rate in the correct date period.
Either case can be achieved in Knack’s JS, or using Make/Zapier.
Your set up would look something like below:
I’d be happy to have a chat if you’d like to DM me?
Hey Stephen, thanks for the reply. I think what you’re asking is related to the commission payment period and that is based on the invoice paid date. We use a pay on pay method so reps get paid when the company gets paid. I’m not sure that having a payment record is “necessary” but it’s possible that multiple reps are associated with the same invoice and I need to ensure that the reps only see their commissions on a report. My thought was to use the logged in user to filter payment records and didn’t think that would be possible to do if the data for multiple payments to different reps was within the same invoice record.
I’m not a coder at all so JS/CSS or API is not doable without outside consulting. I am using Zapier for a couple of functions already so that might be possible but I want to keep costs to a minimum so trying to do as much as I can within Knack when possible.
@John_Prescriptive.Solutions
If it’s based on the invoice paid date, that makes things a little easier.
However because multiple salespeople may be attached to the same invoice, that makes things tricky, and you would need to use additional commission payment records as you’ve mentioned.
I imagine a workflow like this:
- On invoice being marked as ‘paid’ on a specific form…
- Get all valid commission rate records that intersect the invoice paid date
- For each salesperson logged against the invoice…
- Find a valid commission rate record and create a new commission payment record connected to the invoice
With Zapier it would consume roughly 4 operations (plus 2+ Knack API calls) per salesperson on the invoice.
With Knack’s JS you would use 2+ API calls per salesperson on the invoice.
