Can you create rows that retain the information pulled in at the time of creation?

1

If I have columns that calculate information based on the balance in another table. Which subsequently gets paid down. Will those columns automatically update reflecting the new information, or is there a way to freeze them so that it retains the information collected at the time it was created in order to better reflect the history?

2

Dear @Bianca_Adams,

If I am understanding your question well, the below post might contain the solution:

Row Modification History

Best regards,
Jean Pierre

3

Thanks, but from what I can tell, those formulas aren’t available at this time. Someone was just making a suggestion. That looks like about what I need though.

4

Yes those formulas don’t exist yet though it’s definitely on our list to add them. Feel free to upvote that post!

In the mean time, there are a few ways you could do this.

  1. A ledger model - in that case, you store every update to the “balance” at it’s own row (like a ledger). Then you get the current balance by finding the last entry in that table.

  2. A history table - Set up a button that logs the current balance to a “history table”. You can either click that when you make changes, or set up an automation to do it at some frequency (i.e. “if balance has been modified since the last log entry, then click the button to add a log entry”). This example is similar to this idea: Capture `History` of changes / Create Log of changes to important value of record

Happy to help build out examples for either case.

shishir

5

Thank you. The workaround I have right now is to use an automation to calculate balances and modify the row with that data and update status to Paid In Full. I am assuming that since that is only triggered when I trigger that automation, those values should stay static unless I re-trigger it for that row. Is that correct?

Sometimes a payment will cover several invoices, and sometimes there will still be a balance remaining. So I have each invoice row with a column pmt 1, date 1, pmt 2, date 2, pmt 3, date 3. I was attempting to make an automation so that when I select which invoice will still have that remaining balance, I want it to check to see if pmt 1 is blank, and if so enter the amount paid towards that invoice in that row. If it’s not blank, put it in pmt 2. If that is also not blank, put it in pmt 3.
But… everything I try to determine if it is blank is giving me a false result.

Here is a sample I was using in the if section of the automation:

Invoices.[Invoice #].matches([Client Payments].[Invoice With Remaining Balance]) and Invoices.[Payment 1 Amount].IsBlank()

Am I using IsBlank() incorrectly?

If it is easier, would it be possible to just list all payment amounts in one column and when the automation is triggered, that value gets put in the current list of payments, like this.

  • 10,000
  • 5,000
  • 2,000

And then I could have a formula add those amounts and subtract from the invoice amount to determine the new remaining balance.

6

Sorry Bianca,

I should have double checked as I intented to refer to the 2nd point in Shishir’s post.

Kind regards,
//JP

7

No problem, happy for any help.