Create multiple lines from one table and copy to another

1

Hello There,
I am currently struggling with this case:
I want to add a transaction to a table and then create adjacent allocation to expenses table.
Example a bill for 100$ can by for the past two months, as a result I want to allocate 50$ to February and 50$ to March.
The column [Payment allocation] has the number of splits I want to make and as a result the entries on the Expenses table.
I added the [Updated] column to keep the up to date rows separate, so I can use a button to do it for all the new Transactions. I gave it a try, but the functional logic of Coda, still troubles me, coming from more procedural logic.

Thanks in advance!

2

Hi @Giannis_Koutsogiannakopoulos ,
If I have understood, the process involves creating a transaction entry, calculating the allocation amounts based on the number of splits, and then creating corresponding entries in the Expenses table.

I’ll walk you through setting this up step by step. Assume you have two tables: Transactions and Expenses. The Transactions table has the columns Amount, Payment allocation (number of splits), and Updated (a checkbox to indicate whether the transaction has been processed). The Expenses table will receive the split allocations with columns like Date, Amount, and Transaction ID or Description for reference.

Step 1: Adding a Transaction Entry

First, you add an entry in the Transactions table with the following information:

  • Amount: The total amount of the transaction.
  • Payment allocation: The number of periods over which this amount is to be allocated.
  • Updated: Unchecked (False) to indicate this transaction hasn’t been processed yet.

Step 2: Calculate Allocation Amount

You’ll want to calculate the allocation amount per period, which is the Amount divided by the Payment allocation. This can be a column in your Transactions table or a formula directly in the button action (step 3).

Step 3: Create a Button in Transactions Table to Process Allocations

You create a button in the Transactions table that, when pressed, does the following:

  1. Checks if Updated is False (meaning it hasn’t been processed).
  2. Creates the specified number of entries in the Expenses table, dividing the total amount evenly across these entries.
  3. Marks the transaction as Updated by setting the checkbox to True.

Here is a more detailed look at how you might set up the button’s formula, assuming you’re comfortable creating a formula for the button action. Coda’s formula language allows you to run actions conditionally and to iterate over a set number of times (e.g., creating multiple rows in another table).

coda

If(thisRow.[Updated]=False,
  Sequence(1, thisRow.[Payment allocation]).FormulaMap(
    AddRow(
      [Expenses],
      [Expenses].[Amount], thisRow.[Amount] / thisRow.[Payment allocation],
      [Expenses].[Date], Today().AdjustMonths(CurrentValue-1),
      [Expenses].[Transaction ID], thisRow.RowId()
    )
  ) +
  ModifyRows(thisRow, thisRow.[Updated], True),
  ""
)

Explanation:

  • If(thisRow.[Updated]=False, ..., ""): Checks if the transaction hasn’t been processed.
  • Sequence(1, thisRow.[Payment allocation]): Creates a sequence from 1 to the number of allocations.
  • .FormulaMap(...): Iterates over the sequence, for each item (month), it adds a row to Expenses.
  • AddRow([Expenses], ...): Adds a new row to Expenses for each allocation.
    • [Expenses].[Amount]: Sets the amount for each entry as the total amount divided by the number of allocations.
    • [Expenses].[Date]: Sets the date for each entry. Adjust the date based on your requirements.
    • [Expenses].[Transaction ID]: Sets a reference to the original transaction.
  • ModifyRows(thisRow, thisRow.[Updated], True): Marks the transaction as processed.

Please note, the formula might need adjustments based on your exact table and column names, and how you calculate dates for each allocation. If you’re using specific months rather than just adjusting from today’s date, you’ll want to modify the date calculation logic to fit your needs.

1 Like
3

@Tommy_Lambert thank you very much for the rapid response, it is very comprehensive and clear. I implemented and works flawlessly.

However I would like to explore the possibility to have a button outside the Transactions table, that will do the same thing as you described but only for the Updated = 1 rows.

Thank you anyways!