When to Use this Feature?
In Equipment Management, display the latest inspection record in a form.
In Inventory Management, display the latest purchase record in a form.
What does it Look Like?
Take retrieving the latest record on October 10 as an example.
After adding data to the Source Table, the query result is shown below:
How is it Designed?
Create a Source Table and add two helper fields to retrieve data from other forms.
Create a Query Form and add a Date Time field to retrieve the latest record based on the specified date.
How to Set up?
In a procurement scenario, the most recent purchase record can be retrieved as follows.
Step 1 Creating the Source Table
1. Building the Form
Create a form. The required data and field types are as follows:
Field Name | Field Type |
Purchase Date | Date&Time |
Purchase Details | Subform |
Product | Subfield - Single Line |
Unit Price | Subfield - Number |
Quantity | Subfield - Number |
Subtotal | Subfield - Number |
Total Amount | Number |
Helper Field - Lookup | Single Line |
Helper Field - Timestamp | Number |
2. Configuring Helper Fields
1) Click the Helper Field - Lookup field and configure the following settings in Field Properties:
Set Initial Value to Custom and set the custom value to 1.
In Field Permissions, uncheck View and Edit.
2) Click the Helper Field - Timestamp field and configure the following settings in Field Properties:
Set Initial Value to Formula Editor and set the formula：Helper Field - Timestamp = Purchase Date
In Field Permissions, uncheck View and Edit.
3. Configuring the Subtotal Field
Click the Subtotal field under Purchase Details. In Field Properties > Initial Value, select Formula Editor and set the formula as follows:
Purchase Details.Purchase Unit Price * Purchase Details.Purchase Quantity
4. Configuring the Total Amount Field
Click the Total Amount field. In Field Properties > Initial Value, select Formula Editor and set the formula as follows:
SUM(Purchase Details.Subtotal)
Step 2 Building the Query Form
1. Creating the Form
Create a Query Form. The required data and field types are as follows:
Field Name | Field Type |
Purchase Date | Date&Time |
Purchase Details | Subform |
Product | Subfield - Single Line |
Unit Price | Subfield - Number |
Quantity | Subfield - Number |
Subtotal | Subfield - Number |
Total Amount | Number |
Helper Field | Single Line |
2. Configuring the MAPX Formula
The MAPX function is used to find the maximum date after converting the timestamp into a number, and then retrieve other related data based on this date.
Click the Purchase Date field. In Field Properties > Initial Value, select Formula Editor and set the formula as follows:
MAPX("max",Helper Field,Helper Field - Cross-Form Lookup,Helper Field - Timestamp)
3. Configuring Data Linkage
Click the Purchase Details field. In Field Properties > Initial Value, select Data Linkage and configure the linkage settings as follows:
Linked Form: Select Source Table.
Filter Condition: The Purchase Date field in the Source Table equals to the Purchase Date field in the current form.
Trigger Linkage: The Purchase Details field in the current form displays the corresponding Purchase Details data from the Source Table.
Note: Configure the Total Amount field with Data Linkage using the same method as configuring the Purchase Details field.
4. Configuring Helper Field
Click the Helper Field field and configure the following settings in Field Properties:
Set Initial Value to Custom and set the custom value to 1.
In Field Permissions, uncheck View and Edit.
