Issues with MLM type Formulas/Relationships

  • 0
  • 1
  • Question
  • Updated 6 months ago
  • Answered
Hey guys, I have a question. I am trying to build an app for paying an mlm type payment system. I have it worked out where the payments for each person and their shared commissions pull into a table. I cannot for the life of me figure out how to calculate the total amount of the overrides.
I have four tables set up that I will need for sure with an optional fifth. I have a table for sales team members (IBOs), which is the parent to both the commissions and shared commissions table. I then have both of these tables set up as parents to a payments table which pulls in the payment information to combine under one IBO. I will obviously have multiple payments for each IBO so I have the key field set to being a concatenated version of date+ID number, converted to text which is working great, I just cannot figure how to get the sales numbers to total.
I need it to look at if IBO # (from one record)= Mentor IBO (from another record in the same table) then pull in the total from a number formula field from the record they show as the mentor IBO. 

It seems like it won't look at separate records. It's driving me crazy!
Photo of Amanda Svaleson

Amanda Svaleson

  • 100 Points 100 badge 2x thumb

Posted 6 months ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
Are you aware that a Table can be related to itself? That is how one record in a table can “see” other records in a table.
Photo of Amanda Svaleson

Amanda Svaleson

  • 100 Points 100 badge 2x thumb
I was aware of this, I didn't even think about doing it. I got it to work!
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,522 Points 20k badge 2x thumb
No matter how many tables you use to store your information or how they are related to perform the Multilevel Marketing Commission calculation you are going to have to use either (1) a static hierarchy of known levels with fixed commission rules implemented through native lookup / summary fields or (2) script to dynamically calculate and store the commissions at each level.  This will be true even if you follow Mark's suggestion of relating a table to itself.

Furthermore, you don't describe the commission equations or overrides. I would suggest your look at some of the online MLM commission calculators and relate the one that applies to your commission structure to your existing table structure.
(Edited)