Skip to content
How it works
There are several intermediate steps to the Settle Up calculations, visible in the hidden page .
First, we generate a table listing each unique person mentioned as a payer or beneficiary in the Expenses table (we could also include someone who only appears in but I haven’t done that yet). The code that does that is triggered in the “Reset Money Owed” button (after it clears previously generated rows):
ListCombine(Expenses.Payer, Expenses.Beneficiaries)
.Unique()
.Filter(CurrentValue.IsNotBlank())
.FormulaMap(
AddRow(
[Money Owed],
[Money Owed].[Who?], CurrentValue
)
)
From there, the columns “Money Spent” and “Money Received” in have formulas to sum across all and , and the difference between them is the total owed by the individuals to the group (and vice-versa). We put that in the ”Money Owed” column.
To go from that vague state of general “owing/owed”, we need to calculate specific suggested payment one-by-one. To track our progress we keep a “Remaining Owed” column on that is not defined by a formula, it’s only updated as we generate payments. The “Reset Money Owed” button sets its initial values to simply be the “Money Owed” column from the above calculation:
FormulaMap(
[Money Owed],
ModifyRows(
CurrentValue,
[Remaining Owed], Owed
)
)
The “Generate next payment” button is what creates a suggested payment. Each payment suggestion is made by having the most-in-debt person pay the person who is owed the most:
RunActions(
AddRow(
[Suggested Settleups],
[Suggested Settleups].From, [Most in debt].[Who?],
[Suggested Settleups].To, [Most owed].[Who?],
[Suggested Settleups].Amount, [Next Payment Amount]
),
ModifyRows(
[Most in debt],
[Money Owed].[Remaining Owed],
[Most in debt].[Remaining Owed] + [Suggested Settleups].Last().Amount
),
ModifyRows(
[Most owed],
[Money Owed].[Remaining Owed],
[Most owed].[Remaining Owed] - [Suggested Settleups].Last().Amount
)
)
The payment is added by the AddRow action, and then the 2 participants get their “Remaining Owed” column in updated to reflect the payment that was just suggested.
This logic heavily relies on the separate formulas that calculate the participants “Most in debt” and “Most owed”, and the “Next Payment Amount” that prevents an overpayment (in cases when the in-debt participant owes more to the group than the group owes the most-owed participant).
The final touch is populating by having the “Generate next payment” button executed repeatedly (once per participant) by the “Suggest Some Settle Ups” button:
Sequence(1, [Money Owed].Count())
.FormulaMap(RunActions([Generate next payment]))
This magic button doesn’t even need to be clicked because it gets executed by the automations that listen for any changes in the and tables.
Want to print your doc?
This is not the way.
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
CtrlP
) instead.