link record to another record in the same table (2 Viewers)

[MajP]

but it seems i cannot set the "agent level" field", just cannot wirte in..and it i not even automatically set (as it looks like should be, being impossibile to write in that field

You do not do this manually. It is done via code to ensure it is correct. The code counts the number of supervisors in the chain. This way if any agent is added or removed the levels will automatically update.
In real life with lots of branches and levels it would be near impossible to do this manually. So this is much safer, faster, and more accurate to update in code.

May happen that an agent leave
In this case that part of royalties would go to MAIN AGENCY
How can we mange this?

I did not add a relationship diagram to this database, but I should. You can enforce referential integrity, which means every child must report to another record in the database. Assume you have

-Main
--A
----B
--------C
----D
----E

Assume Employee B leaves. If Referential Integrity is established then you cannot delete B since it has a child C. You need to first reassign C. I would assume C gets reassigned to A becoming a second level not to Main. You would choose a new "reports to" for C and then delete B. I would assume C gets reassigned to A becoming a second level not to Main.

But if you are suggesting that if B leaves C is still considered a 3rd level without a 2nd level supervisor then that cannot be done. This structure does not support where C is considered 3rd level but does not have a 2nd level supervisior.

-Main
--A (lvl 1)
--------C (lvl 3)
----D
----E

In the ADD EMPLOYEE table the agent pk for MAIN AGENCY is 9
In ROYALTIES ASSIGNEMENT the RECEIVING AGENT LEVEL for MAIn AGENCY is 0..

Normally there is no reason to look at a PK. It is only used to relate records. Main is 9 because I added it after all the other records. The levels are calculated in code and has no relation to the PK. When you assign a record A to Main you go to the combo box and pick "Main". In the background it stores the key 9 in A's report to. All you see is reports to "Main", but in fact 9 is stored in the table.

 

[stefanocps]

You do not do this manually. It is done via code to ensure it is correct. The code counts the number of supervisors in the chain. This way if any agent is added or removed the levels will automatically update.
In real life with lots of branches and levels it would be near impossible to do this manually. So this is much safer, faster, and more accurate to update in code.


I did not add a relationship diagram to this database, but I should. You can enforce referential integrity, which means every child must report to another record in the database. Assume you have

-Main
--A
----B
--------C
----D
----E

Assume Employee B leaves. If Referential Integrity is established then you cannot delete B since it has a child C. You need to first reassign C. I would assume C gets reassigned to A becoming a second level not to Main. You would choose a new "reports to" for C and then delete B. I would assume C gets reassigned to A becoming a second level not to Main.

But if you are suggesting that if B leaves C is still considered a 3rd level without a 2nd level supervisor then that cannot be done. This structure does not support where C is considered 3rd level but does not have a 2nd level supervisior.

-Main
--A (lvl 1)
--------C (lvl 3)
----D
----E


Normally there is no reason to look at a PK. It is only used to relate records. Main is 9 because I added it after all the other records. The levels are calculated in code and has no relation to the PK. When you assign a record A to Main you go to the combo box and pick "Main". In the background it stores the key 9 in A's report to. All you see is reports to "Main", but in fact 9 is stored in the table.

so in case agent, of any level, leave what can i do?rewrite the structure for that line to mache the new structure?

 

[MajP]

I modified the recursive code to include a Tree Sort. This allows me to display the Tree in a simple listbox. This makes it a lot easier to comprehend. I got rid of visible PKs since it was not helping.

1. Enforce referential integrity. With RI you cannot delete an Agent if it has subordinates. You must assign ("reports to") an Agent to a viable agent or leave null.

2. Now if I try to delete B. You cannot since B has subordinate G and referential integrity is enforced in the relationship window.

You must first reassign the subordinates. In the below case I have D reporting to B so I reassign D to the other 2nd level C.

After the reassign B has no subordinates and D shows under C.

Now I can delete B

As I add and delete records I get new records. The order of the PKs does not matter.
What is interesting in the tree if I reassign a record that has subordinates it will appear as if the subordinates move to

If I reassign C (lvl 2) to Main he appears to move up along with his subs.

 

[stefanocps]

so this solve the problem of some agent leaving right?
i haven't checked yet, i ll do tomorrow
did you just "tranbsfer" the percentage of the agent leaving to thje MA?
There is no recalculate of royalties...if the agent leaving is on 2 level and he is supposed to earn example 20% in a transaction, all those 20% form now and on will go to MA..right?
Eventually coul also remain the same structure and form the day he leave i know that all the royalties of example JOE go to MA..it is not automatic but gives the result!

 

[Gasman]

@MajP when you complete this database for the O/P it would be worthy of uploading to the Sample Databases section. I am sure this could help others as a starter for a similar project.

 

[CJ_London]

@MajP has mentioned several times that I see this as a trivial problem. In terms of the basic organisation as described
there are just 3 levels so a simple 3 level query with each level joined to sales will do the job. But this assumes the world is perfect and does not change.

I pointed out that the OP had not considered what happens when someone leaves or changes roles within the organisation and urged them to clarify all the business rules. On the other forum this was not really answered. Perhaps it has here.

in my experience managing those changes within the application is where the complexity arises - and clear business rules need to be determined.

my experience with commissions is with large organisations with a variety of reward plans fixed for a period and with occasional strategic short term plans to meet an unforeseen threat or opportunity. Not to mention organisational change. Typical annual spend on rewards would be £20-£30m with around 1000 payees, reward typically being around 50% of total earnings.

Over the last 20 odd years I have saved my clients around £250m annually through clarifying the rules and applying them correctly. I should point out this figure includes payments to external agencies as well as employees. External agencies get paid a far higher percentage of the sale.

As far as this thread is concerned with a quick scan through it seems the rules are still unclear and trying to tackle changes and new requirements on the fly as and when the thought occurs is not the way to go. OP should sit down and consider and document all eventualities and requirements, including reporting before starting on the table/relationship design.

I haven’t read the whole if this thread so perhaps the following has been resolved

The original concept of a code ‘ABA’, ‘ABB’ etc is on the face of it a simple solution, but fraught with issues, typo’s being one. But since these are allocated to an individual they cannot be reused, so there is a limit of 26 individuals per level/group. So make it a role identifier rather than an individual. Then you have the issue of when someone starts to occupy that role, or moves on from it. Or for a short period perhaps two people share the role

is there a training period? Does that impact payments?

What happens when someone leaves? Do they continue to be paid for a period of time?

If someone is paid incorrectly - either too much or too little - how do you resolve that? Make a separate payment? Correct the following month? How does that impact on the historical record (e.g.for reporting)

these are just some of the questions I would be asking.

 

[stefanocps]

@MajP has mentioned several times that I see this as a trivial problem. In terms of the basic organisation as described
there are just 3 levels so a simple 3 level query with each level joined to sales will do the job. But this assumes the world is perfect and does not change.

I pointed out that the OP had not considered what happens when someone leaves or changes roles within the organisation and urged them to clarify all the business rules. On the other forum this was not really answered. Perhaps it has here.

in my experience managing those changes within the application is where the complexity arises - and clear business rules need to be determined.

my experience with commissions is with large organisations with a variety of reward plans fixed for a period and with occasional strategic short term plans to meet an unforeseen threat or opportunity. Not to mention organisational change. Typical annual spend on rewards would be £20-£30m with around 1000 payees, reward typically being around 50% of total earnings.

Over the last 20 odd years I have saved my clients around £250m annually through clarifying the rules and applying them correctly. I should point out this figure includes payments to external agencies as well as employees. External agencies get paid a far higher percentage of the sale.

As far as this thread is concerned with a quick scan through it seems the rules are still unclear and trying to tackle changes and new requirements on the fly as and when the thought occurs is not the way to go. OP should sit down and consider and document all eventualities and requirements, including reporting before starting on the table/relationship design.

I haven’t read the whole if this thread so perhaps the following has been resolved

The original concept of a code ‘ABA’, ‘ABB’ etc is on the face of it a simple solution, but fraught with issues, typo’s being one. But since these are allocated to an individual they cannot be reused, so there is a limit of 26 individuals per level/group. So make it a role identifier rather than an individual. Then you have the issue of when someone starts to occupy that role, or moves on from it. Or for a short period perhaps two people share the role

is there a training period? Does that impact payments?

What happens when someone leaves? Do they continue to be paid for a period of time?

If someone is paid incorrectly - either too much or too little - how do you resolve that? Make a separate payment? Correct the following month? How does that impact on the historical record (e.g.for reporting)

these are just some of the questions I would be asking.

i have answered in the other forum!

Agents will not go up or down

In case one agent will leave will not be replace and his royaltes will be given to the main organization

ANyway in this db there is 1 point of concern

There is no a kind of history
I mean..if i change for example the percentage of royalties..it will affect the whole history of sales
That can be good..but not always
Sometimes i might need to change fromnow and now, leaving all the history like it is...may be just anexport of the data before changing could sort this, in a quick way

 

[stefanocps]

I modified the recursive code to include a Tree Sort. This allows me to display the Tree in a simple listbox. This makes it a lot easier to comprehend. I got rid of visible PKs since it was not helping.

1. Enforce referential integrity. With RI you cannot delete an Agent if it has subordinates. You must assign ("reports to") an Agent to a viable agent or leave null.
View attachment 108264

2. Now if I try to delete B. You cannot since B has subordinate G and referential integrity is enforced in the relationship window.

View attachment 108266

You must first reassign the subordinates. In the below case I have D reporting to B so I reassign D to the other 2nd level C.
View attachment 108267

After the reassign B has no subordinates and D shows under C.
View attachment 108268
Now I can delete B

View attachment 108269


As I add and delete records I get new records. The order of the PKs does not matter.
What is interesting in the tree if I reassign a record that has subordinates it will appear as if the subordinates move to

If I reassign C (lvl 2) to Main he appears to move up along with his subs.
View attachment 108270

i am trying this new function but it seems to do nott work properly
i created only 3 agent a.b and c
so a relate to ma, b to a and c to b...3 levels...so scheme i MA=0,4 - A=0,3 B=0,2 and C=0,1
Now B leaves
scheme don't change, only thing is that the 0,3 of B should go to MA,
SO MA=0,4 - A=0,3 MA=0,2 and C=0,1

It seems that whatever i do in DB, the scheme will change...

p.s.
also in yiur example if you reassign D form B to C you are changing the tree

 

[CJ_London]

Agents will not go up or down

In case one agent will leave will not be replace and his royaltes will be given to the main organization

Ok so you have MA A,B,C. B leaves and is replaced with D? Or perhaps another C? Either way, what is the code required? Was ABC, now what?

another scenario, C leaves and is replaced with D, then D leaves and is replaced with E who in turn leaves. What happens when you reach Z?

 

[stefanocps]

If someone leave is not replaced.his royalties goes to main agent.
For the code itnis something i am not consider for now as i can manage in whatever way, as when i go to db i ll need to insert sale and agent name and this could be enough

 

[stefanocps]

If i can match a code that i use in db and the code cistomer uses when buying in the site can be better....any way of coding would be fine for me anyway

 

[MajP]

FYI,
There is still a very big part I did not demonstrate. Still working on this. However, this royalty query only shows royalties to process and not the solution. These royalties need to be processed by saving them into a permanent table. The query I showed was to demonstrate that the correct values can be obtained from the rules. This query is only valid for a point in time. Once the organization changes (employees added, removed, report to changes) then the query is no longer valid. In the sales table I need a "processed date" to show that the royalties have been saved to a table. You will then have a screen showing unprocessed sales/royalties and processed royalties.

This will work fine unless you plan to process sales after the organization changes. (This sounds unethical even. You want to give Royalties to the MA because you process a sales after an employee leaves?)
If the organization structure that existed when the sale took place is not the same as the current organization then this gets an order of magnitude more complicated. This is beyond just the Agent that last, it includes all agent royalties of subordinates.

It can be done, but this would require that every time the Organization changes (add, delete, or change reports to) then you have to save that structure to a log table. So you would have a history of the organization structure for different dates. This is doable, but may be more time than I would want to invest.
However, in the real world you probably want to do exactly this and save the Organization at every change.

 

[CJ_London]

You need to decide what you are going to do.

No idea what code constraints you have so can’t advise on that.

no idea if your website is going to validate a code when a customer enters it.

I ask questions and you say ‘I hadn’t thought about that’ and ‘any way will do’. It’s your app, you decide. Determine what the business rules actually are and any constraints that exist when interacting with other systems - your website, perhaps finance, maybe marketing, maybe stock control

what if the item ordered is not in stock so cannot be delivered, resulting in a refund - how do you ensure the agent isn’t paid or there is a process in place to clawback what has been paid

what if the customer returns the goods? Is the business rule that they made the sale so are paid regardless?

 

[MajP]

I will summarize where I am.

In the Agents Table added some new fields
tblAgents tblAgents

AgentPK	AgentName	ReportsTo	AgentLevel	TreeSort	InActiveDate
1​	A	9​	1​	1​	​
3​	C	1​	2​	2​	1/31/2023​
4​	D	13​	2​	6​	​
5​	E	1​	2​	4​	​
6​	F	4​	3​	7​	​
7​	G	3​	3​	3​	​
9​	Main Agency	​	0​	0​	​
13​	B	9​	1​	5​	​
14​	Inactive	​	0​	9​	​

Agent Level is calculated in code via a recursive call. This is not man editable. Same as Tree Sort. Agents probably do not get deleted but made inactive. I think this will allow processing of agent records after they leave.

In the sales table I added a field for when the royalties are processed. This is updated in code after the processing
tblAgentSales tblAgentSales

SaleID	AgentID_FK	SaleAmount	DateSold	DateRoyaltiesProcessed
15​	6​	$10.00​	5/1/2023​	​
18​	4​	$20.00​	6/1/2023​	​

I created a log of the processed royalties
tblProcessedRoyalties tblProcessedRoyalties

ID	SaleID_FK	SellerLevel	RoyaltyAgent_ID_FK	RoyaltyAgentLevel	RoyaltyAgentPercent	RecievingAgentActive
38​	15​	3​	9​	0​	0.4​	No​

The royalties once processed are pushed to here.

On the first tab you have

In the Employees table you can add, delete, and change who an employee reports to. Code is run to calculate the Agent level and the sort order so I can build the Tree shown below. That depicts the Organization. The Royalty Assignments are where you set the Pyramid royalty Rules. Example if an Agent is at level 3 and they sell something then They get 10%, Level 2 gets 20%, Level 1 gets 30 % and MA gets 40%. I guess that is why it is called a pyramid scheme.

Through code I create a table that lists all Agents, their level and their subordinates. This is the gist for doing the calculations.
subFrmAgentTree subFrmAgentTree

Agent Name	Agent Level	Child Name	Child Level
Inactive	0​	Inactive	0​
Main Agency	0​	Main Agency	0​
Main Agency	0​	A	1​
Main Agency	0​	B	1​
Main Agency	0​	C	2​
Main Agency	0​	D	2​
Main Agency	0​	E	2​
Main Agency	0​	F	3​
Main Agency	0​	G	3​
A	1​	A	1​
A	1​	C	2​
A	1​	E	2​
A	1​	G	3​
B	1​	B	1​
B	1​	D	2​
B	1​	F	3​
C	2​	C	2​
C	2​	G	3​
D	2​	D	2​
D	2​	F	3​
E	2​	E	2​
F	3​	F	3​
G	3​	G	3​

This way you can percolate up the royalties to superiors.

There is a To process tab that shows the unprocessed sales and royalties. Click the Process button and it saves the royalties to the processed table

The process date is set in the sales table and they no longer appear on this screen.

The Processed records look like this

 

[stefanocps]

You need to decide what you are going to do.

No idea what code constraints you have so can’t advise on that.

no idea if your website is going to validate a code when a customer enters it.

I ask questions and you say ‘I hadn’t thought about that’ and ‘any way will do’. It’s your app, you decide. Determine what the business rules actually are and any constraints that exist when interacting with other systems - your website, perhaps finance, maybe marketing, maybe stock control

what if the item ordered is not in stock so cannot be delivered, resulting in a refund - how do you ensure the agent isn’t paid or there is a process in place to clawback what has been paid

what if the customer returns the goods? Is the business rule that they made the sale so are paid regardless?

you are going far beyond my request..
as i said there is no product to buy/sale
The final customer, let's Call him PAUL YOUNG will buy a subscription
For this the SELLER COMPANY who sell the subscription, give us a commision, and every month there will be a royaltie as long as that PAUL YOUNG is subscribing
This COMPANY at the end of the month will tell us the name of the new subcriber, and how much is the commission for each of them
So for example PAUL YOUNG...100 euro

We know, because PAUL YOUNG as first registered to our site, that he is brought along from agent MIKE
In fact PAUL YOUNG registered to our site, using the code that MIKE gave him, and after that he subscribed to the SELLER COMPANY website through a referal link that belong to MAIN AGENCY
So the SELLER COMPANY only knows the MAIN AGENCY
For them each customer that register with that link, is MAIN AGENCY...they don't know agents, they only know the subscriber name that registered with NAIN AGENCY referal link

So when the seller COMPANY tell us about PAUL YOUNG, we know that PAUL YOUNG has registered with MIKE code and we go to the DB to register the income
MIKe, 100 euro..then mike can be 1st, 2nd or 3rd level..and we know how to share the commission


The code that MIke use..can be really any one...as you see once we go to insert the sale in the DB i already know the agent name..se the associaion AGENT CODE-AGENT NAME is made in our site log.

if one agent in the tree leaves...his part of commission goes to the MAIN AGENCY and he will not be replaced
Eithe no agent can go up or down the scheme
Think it is a "familiar little gorup" doing this

 

[stefanocps]

I will summarize where I am.

In the Agents Table added some new fields
tblAgents tblAgents

AgentPK	AgentName	ReportsTo	AgentLevel	TreeSort	InActiveDate
1​	A	9​	1​	1​	​
3​	C	1​	2​	2​	1/31/2023​
4​	D	13​	2​	6​	​
5​	E	1​	2​	4​	​
6​	F	4​	3​	7​	​
7​	G	3​	3​	3​	​
9​	Main Agency	​	0​	0​	​
13​	B	9​	1​	5​	​
14​	Inactive	​	0​	9​	​

Agent Level is calculated in code via a recursive call. This is not man editable. Same as Tree Sort. Agents probably do not get deleted but made inactive. I think this will allow processing of agent records after they leave.

In the sales table I added a field for when the royalties are processed. This is updated in code after the processing
tblAgentSales tblAgentSales

SaleID	AgentID_FK	SaleAmount	DateSold	DateRoyaltiesProcessed
15​	6​	$10.00​	5/1/2023​	​
18​	4​	$20.00​	6/1/2023​	​

I created a log of the processed royalties
tblProcessedRoyalties tblProcessedRoyalties

ID	SaleID_FK	SellerLevel	RoyaltyAgent_ID_FK	RoyaltyAgentLevel	RoyaltyAgentPercent	RecievingAgentActive
38​	15​	3​	9​	0​	0.4​	No​

The royalties once processed are pushed to here.

On the first tab you have
View attachment 108273

In the Employees table you can add, delete, and change who an employee reports to. Code is run to calculate the Agent level and the sort order so I can build the Tree shown below. That depicts the Organization. The Royalty Assignments are where you set the Pyramid royalty Rules. Example if an Agent is at level 3 and they sell something then They get 10%, Level 2 gets 20%, Level 1 gets 30 % and MA gets 40%. I guess that is why it is called a pyramid scheme.

Through code I create a table that lists all Agents, their level and their subordinates. This is the gist for doing the calculations.
subFrmAgentTree subFrmAgentTree

Agent Name	Agent Level	Child Name	Child Level
Inactive	0​	Inactive	0​
Main Agency	0​	Main Agency	0​
Main Agency	0​	A	1​
Main Agency	0​	B	1​
Main Agency	0​	C	2​
Main Agency	0​	D	2​
Main Agency	0​	E	2​
Main Agency	0​	F	3​
Main Agency	0​	G	3​
A	1​	A	1​
A	1​	C	2​
A	1​	E	2​
A	1​	G	3​
B	1​	B	1​
B	1​	D	2​
B	1​	F	3​
C	2​	C	2​
C	2​	G	3​
D	2​	D	2​
D	2​	F	3​
E	2​	E	2​
F	3​	F	3​
G	3​	G	3​

This way you can percolate up the royalties to superiors.

i am going to test..
but i agree woth your previous post when you say that somehting like permanent table is needed

 

[MajP]

i am going to test..
but i agree woth your previous post when you say that somehting like permanent table is needed

See version 6 I posted in thread 54. You posted while I was typing.

 

[stefanocps]

I will summarize where I am.

In the Agents Table added some new fields
tblAgents tblAgents

AgentPK	AgentName	ReportsTo	AgentLevel	TreeSort	InActiveDate
1​	A	9​	1​	1​	​
3​	C	1​	2​	2​	1/31/2023​
4​	D	13​	2​	6​	​
5​	E	1​	2​	4​	​
6​	F	4​	3​	7​	​
7​	G	3​	3​	3​	​
9​	Main Agency	​	0​	0​	​
13​	B	9​	1​	5​	​
14​	Inactive	​	0​	9​	​

Agent Level is calculated in code via a recursive call. This is not man editable. Same as Tree Sort. Agents probably do not get deleted but made inactive. I think this will allow processing of agent records after they leave.

In the sales table I added a field for when the royalties are processed. This is updated in code after the processing
tblAgentSales tblAgentSales

SaleID	AgentID_FK	SaleAmount	DateSold	DateRoyaltiesProcessed
15​	6​	$10.00​	5/1/2023​	​
18​	4​	$20.00​	6/1/2023​	​

I created a log of the processed royalties
tblProcessedRoyalties tblProcessedRoyalties

ID	SaleID_FK	SellerLevel	RoyaltyAgent_ID_FK	RoyaltyAgentLevel	RoyaltyAgentPercent	RecievingAgentActive
38​	15​	3​	9​	0​	0.4​	No​

The royalties once processed are pushed to here.

On the first tab you have
View attachment 108273

In the Employees table you can add, delete, and change who an employee reports to. Code is run to calculate the Agent level and the sort order so I can build the Tree shown below. That depicts the Organization. The Royalty Assignments are where you set the Pyramid royalty Rules. Example if an Agent is at level 3 and they sell something then They get 10%, Level 2 gets 20%, Level 1 gets 30 % and MA gets 40%. I guess that is why it is called a pyramid scheme.

Through code I create a table that lists all Agents, their level and their subordinates. This is the gist for doing the calculations.
subFrmAgentTree subFrmAgentTree

Agent Name	Agent Level	Child Name	Child Level
Inactive	0​	Inactive	0​
Main Agency	0​	Main Agency	0​
Main Agency	0​	A	1​
Main Agency	0​	B	1​
Main Agency	0​	C	2​
Main Agency	0​	D	2​
Main Agency	0​	E	2​
Main Agency	0​	F	3​
Main Agency	0​	G	3​
A	1​	A	1​
A	1​	C	2​
A	1​	E	2​
A	1​	G	3​
B	1​	B	1​
B	1​	D	2​
B	1​	F	3​
C	2​	C	2​
C	2​	G	3​
D	2​	D	2​
D	2​	F	3​
E	2​	E	2​
F	3​	F	3​
G	3​	G	3​

This way you can percolate up the royalties to superiors.

There is a To process tab that shows the unprocessed sales and royalties. Click the Process button and it saves the royalties to the processed table
View attachment 108274

The process date is set in the sales table and they no longer appear on this screen.

The Processed records look like this

View attachment 108275

i don't know if i do something wrong but to me there is still trouble
I tried a sale from a 3 level tree
If i put inactive an agent level 2, for example, and i have a sale made from agent 3..the royalties scheme change to a different schme while it should not
Also in processed royalties appear INACTIVE as a user


EDIT

ok i think i was doing a mistake, i set the uer that leave like affiliated to INACTIVE
But probably i only need to set the dat eof leaving right?
if i only set the date of leaving nothing change though

 

[MajP]

don't know if i do something wrong but to me there is still trouble

Nothing has been coded yet to handle this because, I have no idea what the rules are to handle this. Further this can get extremely complicated. It is not as simple as handling the inactive Agent, but also all the subordinate agents at the time of the sale. It is not only inactive, but also any promotions/demotions of Agents. It is any change to the organization (except for the addition of new agents).

Example.
So this is where it can get extremely complicated.
Imagine on 1 Jan you have this organization
qryTree qryTree

Agent
Main Agency
----A
--------C
------------F
------------G
--------E
----B
--------D

Then on
31 Jan C leaves. I assume that on 31 Jun that F and G get a new supervisor. I would assume E. The new organization looks like
qryTree qryTree

Agent
Main Agency
----A
--------E
------------F
------------G
----B
--------D

Where C is inactive and F,G report to E.

Lets assume prior to 31 Jan F sold $10 and C sold $20.

Are you planning to process these sales after an organization change.? If so show me how F's $10 is divided and how C's $20 is divided.

If you plan to process sales after an organization changed based on the organization at the time of the sale (which is probably correct), that is going to be very complicated. That requires you to save every organization structure and date every time an organization changes.

I strongly suggest you figure out a way to process outstanding sales prior to any organization change.

IMO seems pretty unethical if C leaves the company they do not get their royalties because the royalties were not processed. But again that is why it is called a "Pyramid Scheme".

 

[stefanocps]

i don't know if i do something wrong but to me there is still trouble
I tried a sale from a 3 level tree
If i put inactive an agent level 2, for example, and i have a sale made from agent 3..the royalties scheme change to a different schme while it should not
Also in processed royalties appear INACTIVE as a user

o

Nothing has been coded yet to handle this because, I have no idea what the rules are to handle this. Further this can get extremely complicated. It is not as simple as handling the inactive Agent, but also all the subordinate agents at the time of the sale. It is not only inactive, but also any promotions/demotions of Agents. It is any change to the organization (except for the addition of new agents).

Example.
So this is where it can get extremely complicated.
Imagine on 1 Jan you have this organization
qryTree qryTree

Agent
Main Agency
----A
--------C
------------F
------------G
--------E
----B
--------D

Then on
31 Jan C leaves. I assume that on 31 Jun that F and G get a new supervisor. I would assume E. The new organization looks like
qryTree qryTree

Agent
Main Agency
----A
--------E
------------F
------------G
----B
--------D

Where C is inactive and F,G report to E.

Lets assume prior to 31 Jan F sold $10 and C sold $20.

Are you planning to process these sales after an organization change.? If so show me how F's $10 is divided and how C's $20 is divided.

If you plan to process sales after an organization changed based on the organization at the time of the sale (which is probably correct), that is going to be very complicated. That requires you to save every organization structure and date every time an organization changes.

I strongly suggest you figure out a way to process outstanding sales prior to any organization change.

IMO seems pretty unethical if C leaves the company they do not get their royalties because the royalties were not processed. But again that is why it is called a "Pyramid

as i said before..IF ANY AGENT LEAVE IT WILL NOT BE REPLACED
ALl his royalties will be transfered to the main agent

If you did not already..check my post 55
I have tried to make a better exaplantion of the system

Look...for what i see there is no even a real need to automate the process of an agent leaving

If agent MIKe leave..wherever is his position..when i see MIKe in any of the DB process i know is MAIN AGENT instead

about the ethic or non ethic question

..i just do web,db,computer it and all these area..so it is not me


for the big organization change..i have a good solution
Start a a new instance of the db and save the older data as archive!