Generate Repeated Related Details

Let’s say you have a loan and want to split it into several monthly payments. Instead of manually calculating each monthly amount, determining the number of payments, and creating records for every payment, you can automate the process. Just click a button — sounds great, doesn’t it?

In this guide, we’ll walk through step-by-step instructions on how to create a custom button that generates payment records based on loan details. Each generated payment will be linked to the corresponding loan record.

Step 1: Create the Required Tables

You’ll need three tables:

• Loans
• Payments
• Indexes (a technical table used for generating sequential values)

Indexes Table

This table will contain a single numeric column named “Index”. Each record represents a sequential number starting from 1. The number of records in this table determines the maximum number of payments that can be generated for any one loan.

Loans Table should include the following columns: Name, Amount, Number of Payments, Start Date.

Finally, in Payments Table create the following columns: Amount, Date, Index (type: number), and Loan reference column (with lookup fields such as Loan Amount, Loan Name, Loan Number of Payments, and Loan Start Date)

Step 2: Set Up Relationships

In the Loans table, create a many-to-many relationship with the Indexes table. No match conditions are needed at this stage.

Next, under this relationship, create a recordset column and apply the following filter:

[Index]<=Related[Number Of Payments]

This ensures that only the necessary number of index records are related to each loan, based on the specified number of payments.

Step 3: Create a Custom Button

In the Loans table, create a Preview Page Custom Button named “Generate Payment Records”. As a next step, add a record create workflow action. This action called “Create Payment Records” will be generating the payment child records from the loan record. Add two assignments to this action: [Index] will be indicating the sequence number of a specific payment record within the batch (e.g. payment 2 out of 5), and Parentkey() will indicate the related Loan record.

Step 4: Automate Payment Field Calculations

To streamline the payment creation process, configure default values for the Amount and Date fields in the Payments table:

Amount Calculation: Automatically calculate the amount for each payment using this formula as a Default/Calculate in the column settings:

[Loan Amount]/[Loan Number Of Payments]

Date Calculation: Additionally, we will automatically set the payment date to the first day of the month following the loan’s start date. Use this formula as a Default/Calculate in the column settings:

AdjustMonth(FirstDayOfMonth([Loan Start Date]),[Index])

This formula ensures that each payment date is spaced one month apart, starting from the month after the loan begins.

Final Result

You can now create a Loan record, specify the start date, total amount, and number of payments. Then, simply click the custom button to automatically generate payment records — each with a calculated amount and scheduled date. Check the settings for this in our database templates library.

And you’re done! Finally, to take it a step further, consider adding reminders or notifications to help ensure payments are made on time.