Real Estate Problem: Multiple agents per transaction and commission splits (help me!!)

Question

Hi all! First time poster here. I have been working on this problem for a few days now, plus religiously combing through all these wonderful discussion posts; but still can't find my answer.Here's my problem: I work for a real estate company and am tasked with designing our database. Right now I have two tables, one is Transactions and the other is Agents. Every transaction has at least one agent, but sometimes it has two.

I want to be able to assign each transaction to two different agents, so that when I pull an agent, it will show each transaction they were involved with.&nbsp;

Each agent has a different split commission percentage. This means the total from the transaction is split differently for each agent in the transaction. I only want the split commission to be linked to each respective agent. So if Agent 1 is Jane Doe and Agent 2 is John Deer, I don't want Jane's commission to be linked to John and vice versa.

I got through the first step, but the second one really stumped me. I set up reference fields for each agent and while it would allow me to select two different agents in the ref fields, it wouldn't let me split up the fields per agent. I built a pipeline for it but it didn't work (because I kinda suck at pipelines lol).
Please please help, any advice is welcomed. Thank you so much in advance!------------------------------Karah W.------------------------------

markshnier__you · Answer

This sounds like a classical Many to Many Relationship.
One Agent has Many Transaction,
but also
One Transaction may have Many Agents.

The solution is to have a join table in the middle called, for example, Transaction Agents.

That will then allow you to have an embedded report on the agent table for all the transaction agents they've been involved in. That would list the actual transaction details. And then on each transaction record it would show the list of agents from the transaction agents table. The commission rates could be calculated based on look up fields from the agent, if that is how it works and knowing that there could be multiple agents the system would know how many agents are involved on a particular transaction as they are probably splitting a commission and getting less commission if two people are sharing it.

------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------

karahweber · Answer

Thank you so much Mark!!!------------------------------Karah Weber------------------------------

Forum Discussion

Real Estate Problem: Multiple agents per transaction and commission splits (help me!!)