This guide demonstrates how to implement Lookup and Aggregation features to establish data relationships between customers, contacts, and sales orders. By doing so, you can enable seamless data communication and cross-form collaboration in a customer management scenario.
Design Approach
1. Use Lookup fields to connect the Customer form with the Contact and Order forms.
2. Once the relationships are established, the Customer form will automatically add two Related Tabs for managing related contact and order data.
3. Add an Aggregation field to the Customer form to summarize related sales data, including total and average purchase amounts, making it quite clear.
Note:
Related tabs are automatically generated when a lookup relationship is created. You can rename, hide, or reorder them as needed. For details, refer to [].
What Does It Look Like?
Seamless Data Integration
When creating a new contact, you can reference existing customer data via a lookup field. After saving, you can view related customer data directly from the contact form through the related tab, and vice versa.
The same bidirectional linkage also applies to the relationship between Customer and Order.
Real-Time Aggregation
When the first order worth $50,000 is recorded under a customer, the customer’s "Total Purchase Amount" and "Average Purchase Amount" fields (aggregation field) will automatically update from 0 to 50,000.
How to Configure It?
Step 1 Creating the Forms
Three forms are needed in this case:
Customer: Stores basic customer information.
Contact: Captures contact details for each customer.
Order: Records orders placed by customers.
Step 2 Configuring the Lookup Field
For the Contact form:
On the design page, add a Lookup field and set the Customer form to relate to.
In Field Configuration, select display fields: Customer No., Customer Name, Source, and Status.
Click 
and go to the settings page. In the Data Sorting Rule, set Updated Time (Descending). Tick the option Add records to the master form and then close the settings page.
In Data Filling, select Customer No. and Customer Name to fill them into the current form.
For the Order form:
On the design page, add a Lookup field and set the Customer form to relate to.
In Field Configuration, select display fields: Customer No., Customer Name, Source, and Status.
Click and go to the settings page. In the Data Sorting Rule, set Updated Time (Descending). Tick the option Add records to the master form and then close the settings page.
In Data Filling, select Customer No. and Customer Name to fill them into the current form.
Step 3 Configuring Aggregation Fields
Open the Customer form. Add an Aggregation field and rename it to Total Purchase Amount.
Click Settings under Formula and set:
Aggregate Variable: Order.Total Price
Aggregate Method: SUM
Aggregate Filter: Approval Status equals Approve
Add another Aggregation field, named Average Purchase Amount.
Enter the formula settings page. Use the formula:
SUM(Order.Total Price,Filter)/COUNT (Order.Records amount,Filter)
And both the Aggregate Filter is "Approval Status equals Approve".
More to Know
1. You can rename, hide, or reorder related tabs in the Customer form.
2. To ensure visibility of the related tab of Contacts to members, grant them view permission to this form.
3. To enable users to add new customer data directly from the Contact form, assign them "Add and View All Data" permission in Publish. The same applies to the Order form.
