Hello! I run a weekly publication and we have a number of freelancers who get paid different amounts for stories and columns. I have an Airtable table with their names and rates.
So in my payments table, I have a Single Select field which chooses the type of writing. Then I have a SWITCH formula that uses the name and type of story to access the right rate. Works perfectly.
Until I give someone a raise. When that happens, all of the rates in the old issues from previous weeks get updated, too.
I'm trying to figure out how to either "freeze" the numbers in old weeks ... or something. Anyone help?
Best answer by pressGO_design
The quick and dirty way that saves things kind of like analogue paystubs:
Create a new table called Closed Payments that doesn't link to anything.
Create a single select field in your payment table with "Pending" "Paid" "Closed" (or whatever stages you want for your payment stuff), or just a checkbox field called "Closed"
Create a view that filters for Closed.
Create an automation that has "when a record enters a view" as its trigger and create record for its action. When you close a payment, it enters the view and creates a new record in the Closed Payments table. You can also use the "when a record is updated" and use the checkbox field as the field to watch for updates if that's your jam.
At some point, delete all the Closed Payments from your Active Payments table. They remain in your Closed Payments table.
The good part: you have a static, stand-alone table that stores closed payments with the correct rates. The bad part: you have a static, stand-alone table with data that you might want to use. Is that a solvable problem? Yes.
The quick and dirty way that saves things kind of like analogue paystubs:
Create a new table called Closed Payments that doesn't link to anything.
Create a single select field in your payment table with "Pending" "Paid" "Closed" (or whatever stages you want for your payment stuff), or just a checkbox field called "Closed"
Create a view that filters for Closed.
Create an automation that has "when a record enters a view" as its trigger and create record for its action. When you close a payment, it enters the view and creates a new record in the Closed Payments table. You can also use the "when a record is updated" and use the checkbox field as the field to watch for updates if that's your jam.
At some point, delete all the Closed Payments from your Active Payments table. They remain in your Closed Payments table.
The good part: you have a static, stand-alone table that stores closed payments with the correct rates. The bad part: you have a static, stand-alone table with data that you might want to use. Is that a solvable problem? Yes.
I sure can share the formula, but right now I am working on this solution.
Since I should track what I used to pay freelancers anyway, what I will do is create a "new" freelancer when I give a raise. I'll mark one instance as the "current rate" so I can create a view with all of the correct rates going forward.
The old instance of the freelancer gets renamed in the formula so it says Bob Smith Jan 2022:
Now, when I send a PDF to the accounting department every other week, the rate will be correct. AND the previous rates will stay in the system.
All I need to work on now is figuring out how to create reports that pull together all of the rates from, say, a quarter. It's going to involve some truncating and stuff.
I sure can share the formula, but right now I am working on this solution.
Since I should track what I used to pay freelancers anyway, what I will do is create a "new" freelancer when I give a raise. I'll mark one instance as the "current rate" so I can create a view with all of the correct rates going forward.
The old instance of the freelancer gets renamed in the formula so it says Bob Smith Jan 2022:
Now, when I send a PDF to the accounting department every other week, the rate will be correct. AND the previous rates will stay in the system.
All I need to work on now is figuring out how to create reports that pull together all of the rates from, say, a quarter. It's going to involve some truncating and stuff.
That's a great solution if you don't mind having people in there twice. At that point, they become invoice records vs freelancer/people records which may or may not be what you're going for. The need for archiving your records seems like a must though and @pressGO_design solution would be a great way to do that.
If you're not already, you should be tracking project dates in your base so that you can easily break out rates based on quarter and even filter them automatically with Views. The other benefit of tracking project dates is that you'll also be able to automate marking records as Active, etc.
The quick and dirty way that saves things kind of like analogue paystubs:
Create a new table called Closed Payments that doesn't link to anything.
Create a single select field in your payment table with "Pending" "Paid" "Closed" (or whatever stages you want for your payment stuff), or just a checkbox field called "Closed"
Create a view that filters for Closed.
Create an automation that has "when a record enters a view" as its trigger and create record for its action. When you close a payment, it enters the view and creates a new record in the Closed Payments table. You can also use the "when a record is updated" and use the checkbox field as the field to watch for updates if that's your jam.
At some point, delete all the Closed Payments from your Active Payments table. They remain in your Closed Payments table.
The good part: you have a static, stand-alone table that stores closed payments with the correct rates. The bad part: you have a static, stand-alone table with data that you might want to use. Is that a solvable problem? Yes.
All right! This is the way. Worked perfectly. I'm able to copy and thus archive everything into my Closed Payments table once I click a new field called "Closed" in my payments table.
Feeling inspired, I also added a view that looks at the past 14 days — our payment period — and then created a Batch Update extension that checks everything in that view with the click of one button.
I don't really want to delete any records at this point, but now I can filter out "Closed" records as needed.
If you're not ready to delete the closed payment records and if you share the payments table with anyone, you might want to add a note in the payments table description that says something along the lines of "all of the payment information in the Closed records is wrong, don't rely on it for anything, if you want to see what the actual data is for those records, you can see them in the Closed Payments table" and/or a conditional color to further notate the fact that these payment records are wrong.
