Break out row entry into multiple rows based on multiple select

[Scott]

Hey!

I’m a new user to Seatable with no coding experience, though I am anticipating the solution to my question will be through the use of scripts.

I have built a form where colleagues will enter data for a table.

In this table, there are columns which allow multi-select, to enable users to input projects in one go and capture information that would span over multiple rows in a database.

For example, a user wants to enter a project that will cost £100 a month, for 2 months. Rather than enter 2 transaction rows, one for each month, the multi-select allows the user to enter as one project, selecting all 2 months and entering the total amount of £200.

I want to create a script that will break this entry down into 2 rows of £100 each, one for each month, reflecting one row for each multi-select option, equally divided

E.g.
Form / Row Entry:
Column 1 (Name) = Scott Test
Column 2 (Month - Multi select) = August-2024, September-2024
Column 3 (Amount) = £200

Desired Outcome
Row 1 = Scott Test | August-2024 | £100
Row 2 = Scott Test | September-2024 | £100

Perhaps the use of additional tables to distinguish between entered form / rows & a second table for the breakout database is required?

Appreciate any support and please bare in mind I am very much an amateur regarding scripts / coding.

Thank you!

[daniel.pan]

The code generated by AI from your description seems can meet your need:

import seatable_api
from seatable_api import Base

# Initialize the connection to your SeaTable base
server_url = "https://cloud.seatable.io"
api_token = "your_api_token_here"
base = Base(api_token, server_url)
base.auth()

# Define your table names
form_table = "Form Entries"
breakdown_table = "Breakdown Entries"

# Get all rows from the form table
form_entries = base.list_rows(form_table)

# Process each form entry
for entry in form_entries:
    name = entry['Name']
    months = entry['Month']
    total_amount = float(entry['Amount'].replace('£', ''))
    
    # Calculate the amount per month
    num_months = len(months)
    amount_per_month = round(total_amount / num_months, 2)
    
    # Create a new row in the breakdown table for each month
    for month in months:
        new_row = {
            'Name': name,
            'Month': month,
            'Amount': f'£{amount_per_month}'
        }
        base.append_row(breakdown_table, new_row)

print("Processing complete!")

I think the handling of the “Amount” number field maybe needed according to your specific setup.