I need advice re: creation of a script to sum fields in selected records from a single table.
The data model is a difficult to explain, so I have attached a diagram (see attached 'ConsultHeirarchyExport.jpg')
I am using a table named Consultants.dbf to model an organization chart. The OrgChart is a typical chart with one to many relationships going down the chains and a one to one relationship going up the chains.
There are 4 fields in the Consultants table that I use to model the OrgChart: num1upline, level, placesponsor and consultnum.
num1upline is the topmost box (consultant) in the OrgChart; level is the level of the row in the OrgChart, placesponsor is the box directly above the current consultant box (placesponsor defines the 1:1 relationship going up any specific chain); consultnum is the unique key for each box/consultant.
So...
If we order the consultants table by num1upline + invert(level) + placesponsor
we can process each record one by one and perform various functions like summing a specific field value for all records in a particular chain etc.
The model is working to this point and I have created a number of scripts that calculate summary values.
HERE IS MY PROBLEM
It's complicated and I am not sure exactly how to describe it... but... referring to the attached diagram:
I cannot produce a script that works for a functionality Known as 'Diamond Director Profit Sharing' (DDPS)...
I want to sum a specific field (PGV) in the consultants table. The sum is for each chain and for a concept named DM Generation (District Manager Generation)
I will try to explain what DD Generation means:
Each box represents a Consultant
unique key = Consultnum 9,0Each consultant on a particular level
is linked to a consultant above using
a field named 'placesponsor'Diamond Director Profit Sharing (DD PS):
"DDs earn bonuses on the Personal Group Volumes (PGVs) of all of the District Managers in their downline chain, to a depth of 6 generations".
Explanation of 'Generations':
A is the placesponsor for B,C,D,E,F. B C D E & F are the 1st generation DMs for A
B is the placesponsor for G, H, and I.
G ,H and I are the 1st generation DMs for B... and the second generation DM's for A
Z6,Z7 and Z8 are the 1st generation DMs for Z2, the 2nd generation DMs for Z, the 3d generation DMs for P, the 4th generation DMs for F and the 5th generation DMs for A
Z11, Z12 and Z13 do not contribute to the PS bonus of A... because they are too far down the chain (7th gen for A)... but they do contribute to the others in the chain (at Gen 1 through 6) The PGV values for each consultant have been calculated and are stored in the Consultants table.
We must sum all of the PGVs for each consultant for each of their generations.
eg A has 5 1st generation DMs. Each DM has a record with a PGV field. We need to sum all of the PGV values and place them in a field
in the Consultants table or in a new table that records these sums for each consultant... similarly for each generation (down to a max of 6)When this has been accomplished we must multiply each Generation sum by a percentage to obtain the bonus value...
The percentages are recorded in a table, so this part is trivial, we just do a lookup and multiply the gensum by the percentage.
I have spent a LOT of time with this but I just cannot see a clear way to create the script that I need.
Any advice would be greatly appreciated.
The data model is a difficult to explain, so I have attached a diagram (see attached 'ConsultHeirarchyExport.jpg')
I am using a table named Consultants.dbf to model an organization chart. The OrgChart is a typical chart with one to many relationships going down the chains and a one to one relationship going up the chains.
There are 4 fields in the Consultants table that I use to model the OrgChart: num1upline, level, placesponsor and consultnum.
num1upline is the topmost box (consultant) in the OrgChart; level is the level of the row in the OrgChart, placesponsor is the box directly above the current consultant box (placesponsor defines the 1:1 relationship going up any specific chain); consultnum is the unique key for each box/consultant.
So...
If we order the consultants table by num1upline + invert(level) + placesponsor
we can process each record one by one and perform various functions like summing a specific field value for all records in a particular chain etc.
The model is working to this point and I have created a number of scripts that calculate summary values.
HERE IS MY PROBLEM
It's complicated and I am not sure exactly how to describe it... but... referring to the attached diagram:
I cannot produce a script that works for a functionality Known as 'Diamond Director Profit Sharing' (DDPS)...
I want to sum a specific field (PGV) in the consultants table. The sum is for each chain and for a concept named DM Generation (District Manager Generation)
I will try to explain what DD Generation means:
Each box represents a Consultant
unique key = Consultnum 9,0Each consultant on a particular level
is linked to a consultant above using
a field named 'placesponsor'Diamond Director Profit Sharing (DD PS):
"DDs earn bonuses on the Personal Group Volumes (PGVs) of all of the District Managers in their downline chain, to a depth of 6 generations".
Explanation of 'Generations':
A is the placesponsor for B,C,D,E,F. B C D E & F are the 1st generation DMs for A
B is the placesponsor for G, H, and I.
G ,H and I are the 1st generation DMs for B... and the second generation DM's for A
Z6,Z7 and Z8 are the 1st generation DMs for Z2, the 2nd generation DMs for Z, the 3d generation DMs for P, the 4th generation DMs for F and the 5th generation DMs for A
Z11, Z12 and Z13 do not contribute to the PS bonus of A... because they are too far down the chain (7th gen for A)... but they do contribute to the others in the chain (at Gen 1 through 6) The PGV values for each consultant have been calculated and are stored in the Consultants table.
We must sum all of the PGVs for each consultant for each of their generations.
eg A has 5 1st generation DMs. Each DM has a record with a PGV field. We need to sum all of the PGV values and place them in a field
in the Consultants table or in a new table that records these sums for each consultant... similarly for each generation (down to a max of 6)When this has been accomplished we must multiply each Generation sum by a percentage to obtain the bonus value...
The percentages are recorded in a table, so this part is trivial, we just do a lookup and multiply the gensum by the percentage.
I have spent a LOT of time with this but I just cannot see a clear way to create the script that I need.
Any advice would be greatly appreciated.
Comment