link record to another record in the same table (1 Viewer)

[CJ_London]

Good to finally get some idea of what the business process actually is. Reward seems a bit upside down - those doing most of the work get least reward. And no chance of promotion since you said movements don’t happen

still confused about when someone leaves since when someone joins, the relationship remains frozen at that point in time. So if a level 2 role is vacant - who does the level 3 person have as a level 2 ‘supervisor’?

you might as well have a table

tblagents
Agent id
agent name
Sales code
Level (3,2 or 1)
Level2 recipient (might be blank if agent is level2 or Higher or there isn’t one at the time of joining)
Level1 recipient ( as for level2)
Date joined
Date left (commission not paid for sales dated after this date)

 

[MajP]

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

Are you saying that Tree is set and never changes. Agents are never added, or report to different supervisors? The can only be inactive. If that is the case then that would simplify.

So if you have this organization
qryTree qryTree

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

If C leaves do F and G get assigned a new supervisor for the purposes of royalties? Do they continue to "fall" under C and the portion of royalties from their sales that would go to C now go to Main.

 

[stefanocps]

Good to finally get some idea of what the business process actually is. Reward seems a bit upside down - those doing most of the work get least reward. And no chance of promotion since you said movements don’t happen

still confused about when someone leaves since when someone joins, the relationship remains frozen at that point in time. So if a level 2 role is vacant - who does the level 3 person have as a level 2 ‘supervisor’?

you might as well have a table

tblagents
Agent id
agent name
Sales code
Level (3,2 or 1)
Level2 recipient (might be blank if agent is level2 or Higher or there isn’t one at the time of joining)
Level1 recipient ( as for level2)
Date joined
Date left (commission not paid for sales dated after this date)

the supervisor for everyone is the MAIN AGENT, eventually they all can refer to them..being a little structure with few agents

 

[stefanocps]

Are you saying that Tree is set and never changes. Agents are never added, or report to different supervisors? The can only be inactive. If that is the case then that would simplify.

So if you have this organization
qryTree qryTree

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

If C leaves do F and G get assigned a new supervisor for the purposes of royalties? Do they continue to "fall" under C and the portion of royalties from their sales that would go to C now go to Main.

if F and G have assigned let's say 15%..they will always get 15% even if C leave and what was supposed to go to C go to main..right

 

[MajP]

f F and G have assigned let's say 15%..they will always get 15% even if C leave and what was supposed to go to C go to main..right

That is not exactly the question. The question is after C leaves, do F and G get a new level 2 supervisor. Do future a percentage of their royalties go to a new level 2 supervisor? Of do they still fall under the inactive C and that portion goes to MA?

In most real world problems if C left, F and G would get a new supervisor that would get future royalties instead of future level 2 royalties continuing to go to MA since C left.

If Agents are never added or get new Supervisors, but only made inactive then this can get coded. That means you can build the tree once and only inactivate agents. You can bring in future sales but cannot change your organization in any way except add subordinates. Is that the case?

 

[stefanocps]

That is not exactly the question. The question is after C leaves, do F and G get a new level 2 supervisor. Do future a percentage of their royalties go to a new level 2 supervisor? Of do they still fall under the inactive C and that portion goes to MA?

In most real world problems if C left, F and G would get a new supervisor that would get future royalties instead of future level 2 royalties continuing to go to MA since C left.

If Agents are never added or get new Supervisors, but only made inactive then this can get coded. That means you can build the tree once and only inactivate agents. You can bring in future sales but cannot change your organization in any way except add subordinates. Is that the case?

i said it before to CJ LOndon..they will not get replaced from a new supervisor..i don't know what is real word, this is not my area..this is how i have been told it should be...but i suspect they don't know yet all the possible scenarios as it seems the first time doing this kind of activity..and they wil really think about only once they face the possibilities to have a proper decision
This is why i suggest that when the agent leave can remain the same name.. or renamed to unerstand that there was a change. So the structure remains and could be adjusted for any need in future

 

[CJ_London]

they wil really think about only once they face the possibilities to have a proper decision

Well my suggestion would work as described but changing things later will almost certainly require a complete rewrite of the app.

 

[stefanocps]

Well my suggestion would work as described but changing things later will almost certainly require a complete rewrite of the app i said

As i said...in that case start a new instance of the db...

 

[CJ_London]

Here is my suggestion. It allows for agents leaving but not starting. Assumption is the sales code is not created until the agent starts, so the datestart field is not really required. I have entered a leave date for Agent E that predates the sale date, so you will see they don't get any commission

Also assumes the sales code from the website will always be valid and that the only changes to the organisation is that agents start and leave.

And assumes the rates never change and that a sale made at level 1 (the top level) does not pass commission down to the lower levels, ditto for level 2

There is an alternative structure I could have used using a joining table rather than a union query but thought the attached would be easier to understand and you do not have clear rules

 

[stefanocps]

Here is my suggestion. It allows for agents leaving but not starting. Assumption is the sales code is not created until the agent starts, so the datestart field is not really required. I have entered a leave date for Agent E that predates the sale date, so you will see they don't get any commission

Also assumes the sales code from the website will always be valid and that the only changes to the organisation is that agents start and leave.

And assumes the rates never change and that a sale made at level 1 (the top level) does not pass commission down to the lower levels, ditto for level 2

There is an alternative structure I could have used using a joining table rather than a union query but thought the attached would be easier to understand and you do not have clear rules

just giving a look..at glance i see calculations seems fine

It's not really clear for me, how you determine the tree who is afiliated to who....by the sale code?
Also the agent query..what do i use it for?

the commission can anyway be changed in the table if is needed..right?

 

[MajP]

Handles Inactive with the assumption no reassignments of supervisors or reorganization of the Org.

Assume this organization and c is Inactive on 5/31/2023
qryTree qryTree

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

Assume these sales

subFrmAgentSales subFrmAgentSales

SaleID	Agent	SaleAmount	DateSold
15​	F	$10.00​	5/1/2023​
18​	G	$20.00​	6/1/2023​

one sale before and one after 5/31

Gives you these royalties

SaleID	SellerName	SellerLevel	SaleAmount	Percent	Royalty Recieving Agent Name	Royalty Level	Royalty Amount	Date Sold	Royalty Reciver Inactive
15	F	3	$10.00	40.00%	Main Agency	0	$4.00	5/1/2023	​
15	F	3	$10.00	30.00%	A	1	$3.00	5/1/2023	​
15	F	3	$10.00	20.00%	C	2	$2.00	5/1/2023	5/31/2023​
15	F	3	$10.00	10.00%	F	3	$1.00	5/1/2023	​
18	G	3	$20.00	40.00%	Main Agency	0	$8.00	6/1/2023	​
18	G	3	$20.00	30.00%	A	1	$6.00	6/1/2023	​
18	G	3	$20.00	20.00%	C	2	$4.00	6/1/2023	5/31/2023​
18	G	3	$20.00	10.00%	G	3	$2.00	6/1/2023	​

Once processed

Seller Level	Sale Amount	Date Sold	Royalty Agent	Royalty Agent Level	Royalty Percent	Royalty Amount	Date Processed	RoyaltyNote
3	$10.00	5/1/2023	Main Agency	0	40.00%	$4.00	6/5/2023
3	$10.00	5/1/2023	A	1	30.00%	$3.00	6/5/2023
3	$10.00	5/1/2023	C	2	20.00%	$2.00	6/5/2023
3	$10.00	5/1/2023	F	3	10.00%	$1.00	6/5/2023
3	$20.00	6/1/2023	Main Agency	0	40.00%	$8.00	6/5/2023
3	$20.00	6/1/2023	A	1	30.00%	$6.00	6/5/2023
3	$20.00	6/1/2023	Main Agency	0	20.00%	$4.00	6/5/2023	Royalty from Inactive Agent: C
3	$20.00	6/1/2023	G	3	10.00%	$2.00	6/5/2023

View attachment 108291

 

[stefanocps]

Handles Inactive with the assumption no reassignments of supervisors or reorganization of the Org.

Assume this organization and c is Inactive on 5/31/2023
qryTree qryTree

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

Assume these sales

subFrmAgentSales subFrmAgentSales

SaleID	Agent	SaleAmount	DateSold
15​	F	$10.00​	5/1/2023​
18​	G	$20.00​	6/1/2023​

one sale before and one after 5/31

Gives you these royalties

SaleID	SellerName	SellerLevel	SaleAmount	Percent	Royalty Recieving Agent Name	Royalty Level	Royalty Amount	Date Sold	Royalty Reciver Inactive
15	F	3	$10.00	40.00%	Main Agency	0	$4.00	5/1/2023	​
15	F	3	$10.00	30.00%	A	1	$3.00	5/1/2023	​
15	F	3	$10.00	20.00%	C	2	$2.00	5/1/2023	5/31/2023​
15	F	3	$10.00	10.00%	F	3	$1.00	5/1/2023	​
18	G	3	$20.00	40.00%	Main Agency	0	$8.00	6/1/2023	​
18	G	3	$20.00	30.00%	A	1	$6.00	6/1/2023	​
18	G	3	$20.00	20.00%	C	2	$4.00	6/1/2023	5/31/2023​
18	G	3	$20.00	10.00%	G	3	$2.00	6/1/2023	​

Once processed

Seller Level	Sale Amount	Date Sold	Royalty Agent	Royalty Agent Level	Royalty Percent	Royalty Amount	Date Processed	RoyaltyNote
3	$10.00	5/1/2023	Main Agency	0	40.00%	$4.00	6/5/2023
3	$10.00	5/1/2023	A	1	30.00%	$3.00	6/5/2023
3	$10.00	5/1/2023	C	2	20.00%	$2.00	6/5/2023
3	$10.00	5/1/2023	F	3	10.00%	$1.00	6/5/2023
3	$20.00	6/1/2023	Main Agency	0	40.00%	$8.00	6/5/2023
3	$20.00	6/1/2023	A	1	30.00%	$6.00	6/5/2023
3	$20.00	6/1/2023	Main Agency	0	20.00%	$4.00	6/5/2023	Royalty from Inactive Agent: C
3	$20.00	6/1/2023	G	3	10.00%	$2.00	6/5/2023

View attachment 108291

did a quick test (don't have time now) and the process when an agent is inactive seems working now

one question

Why in the agent list there is iNACTIVE?
do i really need it?

 

[MajP]

Why in the agent list there is iNACTIVE?
do i really need it?

No, not now but I am exploring the possibility of how you do this in the real world. This would support that.
As I mentioned what you suggest is not very realistic. In the real world organizations change.
Agents are added, leave, promoted, demoted, and reassigned. In your structure agents can only become inactive, but the structure has to remain the same.

I think think at most organizations that the royalties are based on the organization at the time of sale. However the database must support a structure to still be able to modify an organization. The only way this works is to know and archive the organization structure as it changes. So you know what it looked like at the time of the sale.

For example if at some date F, G report to C but then get moved under E, most companies would give the percentage to C who was the supervisor at the time of sale not to the new supervisor E.
Or at a later date C become inactive. Currently you can only make C inactive but in real world C's subordinates would get a new supervisor.

If you archive the organizations then to calculate royalties you look at the date of sale and see what organization to use.

So once an agent is inactive they would move under the "Inactive" agent.

 

[CJ_London]

It's not really clear for me, how you determine the tree who is afiliated to who....by the sale code?

No - since according to you, once created the relationships will never change, it is in tblAgents. You never will modify existing records in tblAgents except to add a leaving date when someone leaves.

the commission can anyway be changed in the table if is needed..right?

as designed if you change the commission rates that will impact all reports - past and future. Might be modifiable depending on what changes are required such as whether to be back dated, relate to different types of sale, or different rates for agents at the same level.

It's based on your stated requirement and likely to be thrown away when something changes. As previously advised I don't believe it really reflects the real world, but would seem you aren't able to go there.

I'm now going to drop off this thread as I have more pressing issues to deal with. Good luck with your project.

 

[stefanocps]

No, not now but I am exploring the possibility of how you do this in the real world. This would support that.
As I mentioned what you suggest is not very realistic. In the real world organizations change.
Agents are added, leave, promoted, demoted, and reassigned. In your structure agents can only become inactive, but the structure has to remain the same.

I think think at most organizations that the royalties are based on the organization at the time of sale. However the database must support a structure to still be able to modify an organization. The only way this works is to know and archive the organization structure as it changes. So you know what it looked like at the time of the sale.

For example if at some date F, G report to C but then get moved under E, most companies would give the percentage to C who was the supervisor at the time of sale not to the new supervisor E.
Or at a later date C become inactive. Currently you can only make C inactive but in real world C's subordinates would get a new supervisor.

If you archive the organizations then to calculate royalties you look at the date of sale and see what organization to use.

So once an agent is inactive they would move under the "Inactive" agent.

yes i think the solution is to archive the datas when i would need to make som estructural change to the tree

 

[stefanocps]

yes i think the solution is to archive the datas when i would need to make som estructural change to the tree

i have done some test and it seems all fine so it looks really good
Few thinking

Would be nice to have some kind of report for a single sale to see what are the shares. As it is now, it is in the big list (and can be really big) and could be difficult to find the single sales and related royalties. This is of course somehting that would make it better..but of course it is not a real big need

For same reason would be nice to have a filter "from date to date" to query the report of sales and income...the same, the monthly royalties is going to be a big list after time of use and might not be easy to look for in it

LAst thing, thinking in case of change fo structure..Still thinking that a good solution can be save the data as archive. But we mus be able to easily look at it.. May be a reimport macro (in the same data table or otherone)?

 

[Gasman]

How hard is it to filter a query by dates and filter a report by ID?

 

[MajP]

I have incorporated the ability to modify an organization and archive an organization.
I added table "tblOrganizationArchive" when you modify the organization structure it then Archives the information.

In the current example it has two organization
4/1
qryTree qryTree

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

6/5
qryTree qryTree

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

on 6/5 F,G are put under E and C is moved to Inactive (and dated inactive)

I have the following sales.
The query will use the correct organization for the date of the sale

subFrmAgentSales subFrmAgentSales

SaleID	Agent	SaleAmount	DateSold
15​	F	$10.00​	5/1/2023​
18​	G	$20.00​	7/1/2023​

So Sale 15 should use org 4/1 since that is the organization in place on that date
Sale 18 should use org 6/5

This gives the following
subFrmProcessedRoyalties subFrmProcessedRoyalties

Seller	Seller Level	Sale Amount	Date Sold	Royalty Agent	Royalty Agent Level	Royalty Percent	Royalty Amount	Date Processed	Organization Date	RoyaltyNote
F	3	$10.00	5/1/2023	Main Agency	0	40.00%	$4.00	6/6/2023	4/1/2023​
F	3	$10.00	5/1/2023	A	1	30.00%	$3.00	6/6/2023	4/1/2023​
F	3	$10.00	5/1/2023	Main Agency	0	20.00%	$2.00	6/6/2023	4/1/2023​	Royalty from Inactive Agent: C
F	3	$10.00	5/1/2023	F	3	10.00%	$1.00	6/6/2023	4/1/2023​
G	3	$20.00	7/1/2023	Main Agency	0	40.00%	$8.00	6/6/2023	6/5/2023​
G	3	$20.00	7/1/2023	A	1	30.00%	$6.00	6/6/2023	6/5/2023​
G	3	$20.00	7/1/2023	E	2	20.00%	$4.00	6/6/2023	6/5/2023​
G	3	$20.00	7/1/2023	G	3	10.00%	$2.00	6/6/2023	6/5/2023​

As you can see it is correct. For 15 it uses 4/1 and disperses correctly. Note that since on the date processed C was inactive the royalties went to main.

You will need several reports. I demoed a Detail level report and a way to select a specific sale.

This works and as I said in the beginning, I believe this is the proper design for doing something like this. I will say there are a whole lot of moving pieces to make this come together. So you will have to dig in.
If you spend the time and build some aggregate reports I can demo the filter by time. But you will need lots of reports
Royalties by Agent for example.

 

[stefanocps]

I have incorporated the ability to modify an organization and archive an organization.
I added table "tblOrganizationArchive" when you modify the organization structure it then Archives the information.

In the current example it has two organization
4/1
qryTree qryTree

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

6/5
qryTree qryTree

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

on 6/5 F,G are put under E and C is moved to Inactive (and dated inactive)

I have the following sales.
The query will use the correct organization for the date of the sale

subFrmAgentSales subFrmAgentSales

SaleID	Agent	SaleAmount	DateSold
15​	F	$10.00​	5/1/2023​
18​	G	$20.00​	7/1/2023​

So Sale 15 should use org 4/1 since that is the organization in place on that date
Sale 18 should use org 6/5

This gives the following
subFrmProcessedRoyalties subFrmProcessedRoyalties

Seller	Seller Level	Sale Amount	Date Sold	Royalty Agent	Royalty Agent Level	Royalty Percent	Royalty Amount	Date Processed	Organization Date	RoyaltyNote
F	3	$10.00	5/1/2023	Main Agency	0	40.00%	$4.00	6/6/2023	4/1/2023​
F	3	$10.00	5/1/2023	A	1	30.00%	$3.00	6/6/2023	4/1/2023​
F	3	$10.00	5/1/2023	Main Agency	0	20.00%	$2.00	6/6/2023	4/1/2023​	Royalty from Inactive Agent: C
F	3	$10.00	5/1/2023	F	3	10.00%	$1.00	6/6/2023	4/1/2023​
G	3	$20.00	7/1/2023	Main Agency	0	40.00%	$8.00	6/6/2023	6/5/2023​
G	3	$20.00	7/1/2023	A	1	30.00%	$6.00	6/6/2023	6/5/2023​
G	3	$20.00	7/1/2023	E	2	20.00%	$4.00	6/6/2023	6/5/2023​
G	3	$20.00	7/1/2023	G	3	10.00%	$2.00	6/6/2023	6/5/2023​

As you can see it is correct. For 15 it uses 4/1 and disperses correctly. Note that since on the date processed C was inactive the royalties went to main.

You will need several reports. I demoed a Detail level report and a way to select a specific sale.

View attachment 108306
View attachment 108307
This works and as I said in the beginning, I believe this is the proper design for doing something like this. I will say there are a whole lot of moving pieces to make this come together. So you will have to dig in.
If you spend the time and build some aggregate reports I can demo the filter by time. But you will need lots of reports
Royalties by Agent for example.

it is a real big job MajP, compliments!
Now the problem is for me to understand the whole thing in case i wanto to do some modifucation ...and also i don'k know if i have enough skill to understan!
Anyway..back to your last eit regarding the changing in structure..
Before i start digging in queries and tables, Just as a normale user i don't see anything new in the mask to manage this possibility
That means that everythins is made automatically just looking after the change in the employes list..examle setting inactive date or changing dependancy ?

The new mask i see is to highlight the sales..as i was thinking before..right?

when you say you'll need a lot of report you mean i need to build up many reports to have detailed info on the overall or detailed activities..right?
So it should start from queries right?

 

[MajP]

Before i start digging in queries and tables, Just as a normale user i don't see anything new in the mask to manage this possibility
That means that everythins is made automatically just looking after the change in the employes list..examle setting inactive date or changing dependancy

The structure does not change, but when you change the agent table (add, edit, delete an agent) it archives the organization. So there are now archive tables. This is all done automatically. The user only selects "reports" to and add new agents.
NEVER delete an agent. Move them under inactive

The query to process the royalties is now conceptually pretty complex. It determines the royalties based on the organization at the time of sale. Although complex it appears to work correctly. You will have to verify

Here is another update that allows you to view the archived organizations.

As I said in the beginning, I believe to do this correctly it is complicated. There are few people who can do this. There are a lot of different pieces that have to come together for this to work.
This structure can do the following
1. Allow any number of levels
2. easily assign and reassign Agents
3. handles any royalty scheme
4. handles changes to organization and assigns royalties based on the organization at time of sale.