Multi-condition Application of MAPX Function
Introduction
Case Introduction
By this case, you can understand how to retrieve and use data across forms through MAPX function when there are multiple conditions corresponding with the value that the form collects.
Application Scenarios
- In the case of course management, if you want to count the number of students enrolled in the same class of the same course, the criteria are the students' class and course.
- In the case of order management, if you want to retrieve the latest order ID from the same store and salesperson, the criteria are names of store and salesperson.
- In the case of sales management, if you want to calculate the sum of the same salesperson's sales in the same month, the criteria are names of the salesperson and month.
How to Design
1. Add an auxiliary field respectively to the current form and the form you retrieve.
2. Integrate the multiple conditional texts to be retrieved into the auxiliary field through CONCATENATE function.
3. Set MAPX function and use auxiliary field as the retrieval value and retrieval range.
Preview
Take the example of gathering the total sales and commission of salesperson in a certain month.
1. Enter the daily sales data of salesperson in the form Daily Sales Data.
2. Finance staff could fill in Commission Form at the end of the month, entering the names of salesperson and the current month, then the sales data would be automatically retrieved to calculate total sales and corresponding commission.
How to Set
Building the Form
1. Create the form Daily Sales Data, the fields settings are as follows:
Field Name | Field Type | Field Setting |
Salesperson | Single Line | / |
Date | Date&Time | Initial Value: Current Time |
Sales Details | Subform | / |
Product Name | Subfield - Single Select | / |
Amount | Subfield - Number | / |
Price | Subfield - Number | / |
Sum | Subfield - Number | Formula: Sales Details.Amount*Sales Details.Price |
Total Sales | Number | Total Sales: SUM(Sales Details.Sum) |
Name & Year & Month | Single Line | / |
2. Create the Commission Form, the fields settings are as follows:
Field Name | Field Type | Field Setting |
Salesperson | Single Line | / |
Month | Date&Time | Type: YYYY-MM Initial Value: Current Time |
Total Sales | Number | / |
Commission | Number | / |
Name & Year & Month | Single Line | / |
Setting Formula
1. Set an auxiliary field.
a. In the Daily Sales Data form, select the auxiliary field Name & Year & Month, set Initial Value as Formula at Field Properties > Initial Value, and click Edit.
b. On the Formula page, set the formula in Name & Year & Month as CONCATENATE(Salesperson,TEXT(DATE(Date),'yyyyMM')). This action combines the entries from the Salesperson and Date fields into the Name & Year & Month field, displaying the salesperson's name followed by the corresponding year and month of the date entered.
c. Likewise, set the formula at Name & Year & Month in Commission Form as CONCATENATE(Salesperson, TEXT(DATE(Month),'yyyyMM')), also displaying the salesperson's name followed by the corresponding year and month of the date entered.
2. Set the MAPX function.
In the Commission Form, set the formula at Total Sales as MAPX('SUM',Name & Year & Month,Name & Year & Month,Total Sales).
a. In the formula, the first parameter Name & Year & Month is from the current form, therefore choose Commission Form at the bottom left column, then add Name & Year & Month.
b. The second and third parameters Name & Year & Month and Total Sales are from Daily Sales Data, therefore choose Change at the bottom left column, then add the corresponding fields.
3. Set sales commission.
Finally, set Commission in Commission Form as IF(Total Sales<10000,Total Sales*0.1,Total Sales* 0.2), meaning if total sales are less than 10,000, the commission proportion would be 10%, if total sales are greater than 10,000, the commission proportion would be 20%.
