Sandler
12-02-2020, 09:38 AM
Hi Everyone - Please Help
I am trying to automatically limit my Details by Profit Center based on the original Balance amount. In my example profit center 100 works out fine for me because I have an initial balance of 100,000 and then in the 5 agreements in my Accruals table the total balance is also 100,000.
For Profit Center 105 I have an initial balance of 50,000. The 5 agreements total to 95,000. So in my details column I need to pick up agreement 1 of 30,000, Agreement 2 of 10,000 and Agreement 3 of only 10,000 to get to my original 50,000. (So agreement 3 amount has to be modified from the initial amount to get it up to only 50,000 and Agreement 4 and Agreement 5 should return nothing because I am already at the total max of 50,000.
Accruals
Details
Profit Center
Balance
Profit Center
Agreement
Balance
Profit Center
Agreement
Balance
100
100,000.00
100
1
20,000.00
100
1
20,000.00
105
50,000.00
100
2
30,000.00
100
2
30,000.00
100
3
10,000.00
100
3
10,000.00
100
4
10,000.00
100
4
10,000.00
100
5
30,000.00
100
5
30,000.00
105
1
30,000.00
105
1
30,000.00
105
2
10,000.00
105
2
10,000.00
105
3
15,000.00
105
3
10,000.00
105
4
20,000.00
105
4
105
5
20,000.00
105
5
I am trying to automatically limit my Details by Profit Center based on the original Balance amount. In my example profit center 100 works out fine for me because I have an initial balance of 100,000 and then in the 5 agreements in my Accruals table the total balance is also 100,000.
For Profit Center 105 I have an initial balance of 50,000. The 5 agreements total to 95,000. So in my details column I need to pick up agreement 1 of 30,000, Agreement 2 of 10,000 and Agreement 3 of only 10,000 to get to my original 50,000. (So agreement 3 amount has to be modified from the initial amount to get it up to only 50,000 and Agreement 4 and Agreement 5 should return nothing because I am already at the total max of 50,000.
Accruals
Details
Profit Center
Balance
Profit Center
Agreement
Balance
Profit Center
Agreement
Balance
100
100,000.00
100
1
20,000.00
100
1
20,000.00
105
50,000.00
100
2
30,000.00
100
2
30,000.00
100
3
10,000.00
100
3
10,000.00
100
4
10,000.00
100
4
10,000.00
100
5
30,000.00
100
5
30,000.00
105
1
30,000.00
105
1
30,000.00
105
2
10,000.00
105
2
10,000.00
105
3
15,000.00
105
3
10,000.00
105
4
20,000.00
105
4
105
5
20,000.00
105
5