Guide to Subform Scenarios

Introduction

The subform scenario is used to handle one-to-many data entry requirements. For example, in a product category form, it requires entering multiple product information, each product containing multiple fields, such as product name, price, etc. To efficiently manage data, categories and products are typically stored separately in different tables and bound through associations.

When the page is submitted, the following operations need to be performed:

1. Update the master table data
2. Creating, updating, or deleting subtable data
3. Update the relationship between the master table and the subtable

Since it involves multiple database interactions, it is recommended to use cloud functions to implement this logic. The frontend only needs to call the cloud function to complete the data update.

Implementation Approach

The implementation of the subform scenario can be divided into the following steps:

1. Processing Subform Data
   • Fetch newly added subtable data
   • Fetch subtable data that needs to be updated
   • Fetch subtable data that needs to be deleted
   • Update subtable data
2. Update the master table data
   • Update the master table data
   • Update the relationship between the master table and the subtable

Cloud Function Development

Create Cloud Function

Go to the Cloud Functions module, create a new cloud function named subTable-management. After entering the code editor, find Terminal/New Terminal in the top toolbar and execute the following command to initialize the project:

npm init -y

npm install @cloudbase/node-sdk

⚠️ Note: The terminal path must be under the current cloud function directory

Write Code

The cloud function needs to receive the following parameters:

• mainName: master table data model name
• mainData: master table data
• subList: subtable data (array)
  • subCode: The code of the subtable in the master table
  • subName: subtable data model name
  • subData: subtable current data
  • subOldData: subtable source data
• isProd: whether it is the production environment

Core Logic

1. Compare subtable data: Use the diffSubData function to compare the subtable initial data and subtable current data, obtaining the data to be added, updated, and deleted.
2. Execute subtable operations: Perform add, delete, and update operations on subtable data using the executeSubTableOperations method.
3. Update master table data: Update the master table data and its relationship with the subtable.

The code framework is as follows:

const cloudbase = require('@cloudbase/node-sdk');

const app = cloudbase.init({
    env: cloudbase.SYMBOL_CURRENT_ENV,
});
const models = app.models;

/**
 * Handle data relationships between the master table and subtables
 * @param {string} mainName - Master table data model name
 * @param {object} mainData - Master table data
 * @param {array} subList - Subtable list, each element contains subtable information
 * @param {string} subList[].subCode - The code of the subtable in the master table
 * @param {string} subList[].subName - Subtable data model name
 * @param {array} subList[].subData - Subtable current data
 * @param {array} subList[].subOldData - Subtable source data
 * @param {boolean} isProd - whether it is the production environment
 * @returns {Promise<object>} Result
 */
exports.main = async function({
    mainName,
    mainData,
    subList,
    isProd = false,
}) {
    try {
        for (const subItem of subList) {
            const {
                subCode,
                subName,
                subData,
                subOldData
            } = subItem;

            // 1. Compare subtable data to obtain add, delete, and modify operations
            const {
                addData,
                updateData,
                deleteIds
            } = diffSubData(subData, subOldData);

            // 2. Perform add, delete, and modify operations on subtable data
            const subRes = await executeSubTableOperations(subName, addData, updateData, deleteIds);
        }

        // 3. Update the master table data
        // ...

        return {
            success: true,
            message: 'Data processing successful',
            details: {
                subRes,
                res,
            },
        };
    } catch (error) {
        console.error('Data processing failed:', error);
        throw new Error(`Data processing failed: ${error.message}`);
    }
};

Complete code example please refer to: GitHub example

Deploy Cloud Function

Click "Save and Install Dependencies" to complete the deployment of the cloud function.

Page Development

Here we use the "Procurement Management" system as an example to demonstrate how to use the subtable feature.

We take the following relationship as an example:

Create Data Model

Go to Data Model to create the following three data models:

Purchase Order (purchase_order)

Field Name	Type	Description
name	Text	Order name
amount	Number	Total amount
status	ENUM	Order status
order_list	Relationship (one-to-many)	Procurement items list
supplier	Relationship (many-to-one)	Supplier

order_list (Purchase Order) and purchase_item (Procurement Item Details) are of relationship type
supplier (Purchase Order) and purchase_list (Supplier Contact) are of relationship type

Purchase Item Details (order_items)

Field Name	Type	Description
name	string	Product Name
price	number	Product price
quantity	number	Purchase quantity
purchase_item	Relationship (many-to-one)	Parent order

Supplier Contacts (supplier_contacts)

Field Name	Type	Description
name	string	Contact Name
phone	number	Contact number
purchase_list	Relationship (one-to-many)	Order list

Add Application

Create a blank application, quickly generate a "Table and Form Page" using a template, and select the data model Purchase Order (purchase_order)

Edit Page

Enter the edit page. Since the form container's data source binding method cannot retrieve complete sub-table data, you need to modify the form container's data source.

Create Built-in Data Table Query on Page

Add a built-in data table query method on the current page, named getOrderDetail.

1. Configure the data table as: Purchase Order (purchase_order)
2. Triggering method: Manual trigger execution
3. Query condition is: Data identifier equals $w.page.dataset.params._id (_id passed from the url)
4. Associated table query selection (object): Return procurement items list associated table, Return supplier associated table

At this point, click the 'Run' button to see that the splb field in the data returns complete product list data.

Add a Load Event to the Page

Add the following JS code in the page-level page load event to control triggering the query only during editing.

// Fetch data via built-in data table query
({
    event
}) => {
    if ($w.page.dataset.params.formType === 'edit') {
        $w.getOrderDetail.trigger()
    }
}

Update Form Container Data Source

Modify the form container's data source to: expression

When switching, a prompt will appear: "This operation will regenerate/clear the form components. Do you want to regenerate/clear?", then select No.

The expression content is the result of built-in data table query

$w.page.dataset.params.formType === 'edit' ? {
    ...$w.getOrderDetail.data,
    supplier: $w.getOrderDetail.data.supplier?._id
} : {}

tip

supplier is assigned a value because this field is a many-to-one relationship. In the getOrderDetail method, it returns a complete data object, so the _id needs to be extracted to populate the component.

Update Product List Component

The Product List component defaults to a Dropdown. Here we modify it to an Array Nested Form component.

1. Modify the Nested Form Template to Object Array (Table)
2. Change binding field to order_list
3. Add subtable fields:
   • Product Name (name) (Text field)
   • Purchase quantity (quantity) (Number input)
   • Product Price (price) (Number input)

Modify the Commit Event

Next, check the submit event of the Form Container, which calls the data source method to update data.

Now we need to modify it to use the cloud function added in the previous chapter to store the data.

Therefore, we switch from "Call Data Source Method" to "Call Cloud Function Method", and write the following script to complete the data update for the master and child tables. For cloud function parameters, refer to the previous chapter.

async () => {
    await $w.cloud.callFunction({
        name: 'subTable-management',
        data: {
            mainName: 'purchase_order',
            mainData: {
                ...$w.form1.value,
                _id: $w.page.dataset.params._id,
                // Other non-subform association fields need to be processed separately.
                supplier: {
                    _id: $w.form1.value.supplier
                }
            },
            subList: [{
                subCode: 'order_list',
                subName: 'order_items',
                subData: $w.form1.value?.splb || [],
                subOldData: $w.form1.remoteValue?.splb || [],
            }]
        },
    });
};

tip

*mainData needs to include _id, as well as data from other non-subform association fields.

• $w.form1.remoteValue is the initial value of the form1 form container component.

Example Project Link: Procurement Order Management System

Summary

The combination of cloud functions and page development enables efficient data management in sub-form scenarios. Cloud functions handle complex database operations, while the frontend simply calls cloud functions to complete data updates, ensuring clear logic and optimized performance.