This approach would make use of a new Table where you define your “Rates”. Each time you need to define a new “Rate” for a “Client”, you’d create a new record in this table with the Rate value (dollar amount), a Time created field, and then a linked record field to link it to the Client that Rate is for.
In the Client’s table, you’d need a Rollup field that Rolls up this “Rate” linked field by the MAX({Time created}) – this should ensure that the “Client” record always reflects the most recent rate. You might have to do that Rollup to get the most recent “Created at” time, then pull that back into the “Rates” table with a Lookup, and in a Formula in the “Rates” table, check whether that Looked up MAX({Time created}) matches the {Time created} for the Rate record – and then, once again, use a Lookup in the Client’s table to pull that value in. Oy — it’s been a while since I did this and I’m remembering now how awkward it was to set up.
After all that hoopla :point_up:t2: (which you’ll only have to set up once), you can create new Rates, link them to Clients, and ensure that the Client always reflects the most recent Rate for the Client, while also retaining the historical Rates you’ve used.
Now we need to get that Rate into the To Do’s, and I’d suggest a Scripting Block for this - while JavaScript may not be your proficiency, it will prevent having to pull in any external services.
The Script will just need to grab all of your To Do’s that don’t yet have a “Rate” defined (this can happen by defining a View that only shows To Do’s where “Rate” is blank and querying that View, or else querying all To Do’s and filtering them in the script), and fill in the “Rate” with the value that is currently reflected in the Client’s “most recent” rate.
By doing this with a Rates Table and a Script, we avoid having the Rate field in the To Do’s be a dynamic formula – we don’t want it to be a dynamic formula because we don’t want the rate on past To Do’s to change (and change our record of billing). Formulas are, by their very nature, dynamic, so storing that data in a dynamic field can be dangerous.
With that set up, you just need to click the button to run the script, and it will fill in the Rate for you on any and all To Do’s that have a Client defined, but no Rate defined yet. You still have to intervene and click that button every now and then. Not quite as tedious as looking up rates and manually entering them on a per To Do, per Client basis, though.
I always get through these things and look back and realize just how much set up work I’ve suggested for you. And the cross-lookup thing to get the value of only the most recent Rate is a little complex and difficult to explain. I’m happy to help further and more directly with you – I’ll PM you about that.
