Keeping SWITCH formula number frozen for old invoices

[Jon_Swerens]

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?

[AirBenderMarcus]

Could you please share your formula? How are rates currently being calculated?

You could add a conditional to your SWITCH formula that only updates if certain conditions are met depending on how you set up your data.

[pressGO_design · Accepted solution]

The quick and dirty way that saves things kind of like analogue paystubs:

1. Create a new table called Closed Payments that doesn't link to anything.
2. 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"
3. Create a view that filters for Closed.
4. 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.
5. 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.

[Jon_Swerens]

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:

CONCATENATE({First name}, " ", {Last name}, 
IF(
  {Current rate} = BLANK(), " " & DATETIME_FORMAT(CREATED_TIME(), 'MMM YYYY')
))

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.

[Jon_Swerens]

Oh, the Payment formula looks like this:

SWITCH({Type}, "Feature", Feature, "Pick", Pick, "Column", Column, "Album Review", {CD Review}, "Local Album", {Local CD}, "Movie Review", {Movie Review}, "Book Review", {Book Review}, "Blurb", Blurb, "Theater Review",  {Theater Review}, "Act Profile", {Act Profile}, "Project", Project, "Quick Hit", {Quick Hit}, "CN Route", {CN Route}, "DT Route", {DT Route}, "NW Route", {NW Route})

This is calling on a bunch of lookup fields. 

[AirBenderMarcus]

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.

[pressGO_design]

Are all of those lookup fields the amounts that the people get paid for the different things they write?

[Jon_Swerens]

The lookup fields are all of the kinds of articles that our freelancers could possibly write.

I'm going to read through and consider your solution above. I have to think through how this is going to work! Thanks!

[Jon_Swerens]

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.

Thanks for the help!

[pressGO_design]

Yay!

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.