This case introduces how to use the MAPX function to retrieve and apply data across forms when the value to be calculated corresponds to multiple conditions.
When to Use this Feature?
In Course Management, when counting the number of enrolled students for the same class and the same course, the lookup conditions are Class + Course.
In Order Management, when retrieving the latest order number of the same store and the same salesperson, the lookup conditions are Store Name + Salesperson.
In Sales Management, when calculating the total sales amount of the same salesperson in the same month, the lookup conditions are Salesperson Name + Month.
How does it Work?
Add a helper field to both the current form and the lookup form.
Use the CONCATENATE function to combine multiple lookup conditions into the helper field.
Finally, configure the MAPX function and use the helper field as the lookup value and lookup range.
What does it Look Like?
Take the sales management scenario as an example, where the total monthly sales amount and commission of sales representatives are calculated:
1. Record the daily sales data in the Daily Sales Deprot form.
2. At the end of each month, finance staff only need to enter the salesperson's name and the month in the Sales Commission Form. The system will automatically retrieve sales data and calculate the total sales amount and corresponding commission.
How to Set up?
Step 1 Building the Form
1) Create the Daily Sales Report form and configure the fields as follows:
Field Name | Field Type | Field Configuration |
Salesperson | Single Line |
|
Date | Date&Time | Default Value: Current Time |
Sales Details | Subform |
|
Product Name | Subfield - Single Select |
|
Quantity | Subfield - Number |
|
Unit Price | Subfield - Number |
|
Subtotal | Subfield - Number | Formula: Sales Details.Quantity * Sales Details.Unit Price |
Total Sales Amount | Number | Formula: SUM(Sales Details.Subtotal) |
Name + Year-Month | Single Line |
|
2) Create the Sales Commission form and configure the fields as follows:
Field Name | Field Type | Field Configuration |
Salesperson | Single Line |
|
Month | Date&Time | Type: Year-Month Default Value: Current Time |
Total Sales Amount | Number |
|
Commission Calculation | Number |
|
Name + Year-Month | Single Line |
|
Step 2 Setting the Formula
1. Configuring Helper Fields
1) Go to the Daily Sales Data form. Select the helper field Name + Year-Month. In Field Properties > Initial Value, select Formula and click Edit below.
2) In the Formula Editor, set the formula for the Name + Year-Month field as follows:
CONCATENATE(Salesperson,TEXT(DATE(Date),'yyyyMM'))
3) Similarly, set the formula for the Name + Year-Month field in the Commission Form form as follows:
CONCATENATE(Salesperson,TEXT(DATE(Month),'yyyyMM'))
2. Configuring the MAPX Function
Go to the Commission form and set the formula for the Total Sales Amount field as follows:
MAPX('SUM',Name+Year-Month,Name+Year-Month,Total Sales Amount)1) The first parameter, Name + Year-Month, comes from the current form.
In Available Variables, select Current Form, and then add the Name + Year-Month field.
2) The second and third parameters, Name + Year-Month and Total Sales Amount, come from the Daily Sales Data form. In Available Variables, switch to the Daily Sales Report form and add the corresponding fields.
3. Configuring Sales Commission
Finally, set the formula for the Commission Calculation field in the Commission form as follows:
IF(Total Sales Amount<10000,Total Sales Amount*0.1,Total Sales Amount*0.2)
It means the commission rate is 0.1 when the total sales amount is less than 10,000, and 0.2 when it is greater than or equal to 10,000.
