Calculating with changing values (salary) without retroaction
Hi guys,
I have been searching the forum for an answer, but so far have not been able to find it. Let me try to explain here what I am trying to accomplish.
I have several freelancers working for me, whom I assign different assignments. They obviously get an hourly rate for their assignments. Once in a while though these hourly rates change. I am looking for the best way to calculate their monthly income per assignment based on their current hourly rates.
I now have 2 apps (with a relational field):
- Salary app: Holds their (private) hourly rates, so they can not see each other's rates. Basically just their names, hourly rates, and monthly income is calculated here (summing up all the fees per assignment for that month).
- Assignment app: Holds the assignments. Once the assignment is assigned to one of them, they can fill in their working hours. The app then calculates the fee for the total assignment by getting their hourly rate from the salary app and multiplying this with the total worked hours.
All pretty straightforward.
The problem is that when I negotiate a new hourly rate with a freelancer that already has assignments, I can not change the hourly rate in the salary app, because Podio will recalculate all previous assignments with the new hourly rate. While I just want the new hourly rate calculated for future assignments. What would be the best way to stop Podio from recalculating old assignments?
My best solution would now be to add an extra date field (salary change date) and a "new salary field". That way I would be able to make an if statements that checks if the salary change date is before or after the date of the assignment and then use either the new or the old salary rate. The only problem is that I would need to add extra fields when the salary changes for a 3rd or 4th time, or I would lose the old data again.
Any solutions?? Looking forward to hearing from you. Thanks a lot for your input!
Casper
-
Hi Casper,
My idea is to create a new app behind the two existing
Create 'Hourly Rate App' with three fields
1- Freelancer : Related field to 'Salary App'
2- Rate : Number field for Hourly Rate
3- Status : Category field with two fields, for exemple "ACTIVE RATE" & "ARCHIVE RATE"
** Create a new View with only "Active Rate" status with filtring and saving the view
PS : in the parameters of Hourly Rate on Display Options make sure to display only the "Freelancer field" in Relationship View
Now in your "Assignement App"
First field will be a relationfield to the 'Hourly Rate App' with only "ACTIVE RATE" (The view created)
Change your calculation field to the new fields and that's it.
PS : In the "Salary App" delete de hourly rate field (its just a contact app)
Good Luck
-
Hi,
there'sa way w/o an additional app:
Instead of a number field or a money field for the hourly rate in the Salary App take a single line text field, name it e.g. "Rate History". Enter each rate change in this format: YYYYMMDD:new rate; should then be something like this: 20161101:45;20170301:50
I assume, you've a date field in the Assignment app (maybe a deadline date or a creation date).
In the calculation field in the Assignment app enter:var assDate = moment(@Date field).format("YYYYMMDD");
var assDate = Number(assDate) || 0;
var hrs = @working hours;
var history = @All of History.join().split(";").reverse();
for(var i = 0; i < history.length; i++){
date = history[i].split(":")[0];
date = Number(date) || 0;
rate = history[i].split(":")[1];
rate = Number(rate) || 0;
if(assDate >= date){
break; }
rate;
};
fee = rate*hrs;
feeIf you use Globiflow you can keep the number/money field in Salary. Create a flow which is triggered by a change of the field and let GF append the string ";current date:new rate" in the text field (after existing entry). If you don't need the number/money field enter each change in the text field, add a calc field "Current rate":
var a = @Rate history.split(":").pop();
Number(a) || 0It shows you the actual rate in an own field.
Rainer
rg@delos-consulting.com
Please sign in to leave a comment.
Comments
3 comments