0

I cannot figure this out for the life of me. We have pretty much all custom objects (most are via a managed package from the exchange app ADvendio). I'm looking to simplify the way we process commissions via reporting because it is far too cumbersome. As of now, I have to use 2 different report types per sales rep, export both reports, add a few formulas, perform a vlookup, add a few more formulas, and then calculate the payouts per invoice eligible that month...

So! This is what I need help figuring out.

Objects in question are:

  • Invoices ADvendio__Invoice__c
  • Invoice items ADvendio__InvoiceItem__c (this is related to Invoices item above, it is a related list under related tab on invoice record)
  • User sales sprint user_sales_sprint__c

I need those 3 custom objects in 1 custom report type. I've tried building a new CRT every which way, and can usually add 2 of the 3 objects I need in layers a and b, however when I get to level c, there are so few objects to select from, and none are the one I need. How can I relate the objects in a way to make this function?

What am I missing? I just took the intro to admin cert class this week and thought I could apply some of the class material via a formula field but that didn't work. If you have ideas about how to accomplish this with a formula field, I'm all ears. If I can get one field from the invoice items object to display as text in the invoices object, that could also work.

The challenge is there can be multiple/different invoice items on one invoice. I need to differentiate exhibits from all other sales as they're paid at a different commission rate. We have an accounting system code that is exclusive to exhibits that I can use, but it only lives as a field on the invoice items object. Exhibits have to be on their own, so if an invoice has one or more invoice items with the exhibit code, it should be marked as an exhibit. If it does not have the exhibit code, then it gets paid at the standard rate. Not sure how to handle but appreciate anything y'all can think of!

Thank you in advance

Improve this question
1
  • 1
    This is a classic example of a XY Problem - a report or a report type is not the solution. You want to create a custom object with (roughly) 3 fields: sales rep, commission, commission period, then create a Flow that calculates the commission for a given period and sticks the results into your custom object. And finally you'll have your (now simple) report. If this is too much, there are a bunch of compensation apps on AppExchange that may be worth it.
    – identigral
    Jul 22 at 3:06

0

Reset to default

You must log in to answer this question.