Skip to main content

Set Default Value - Query Worksheet

When creating a new record, if relevant data already exists in the current worksheet or another worksheet, you can query a worksheet based on specific conditions and populate field values from the result.

This method supports setting default values for:

  • Regular fields
  • Relationship fields
  • Subform fields

Configuration Example​

Set Default Value for a Regular Field​

Example:

When an employee submits a new expense reimbursement request, the bank card field should automatically populate with the information used in their last reimbursement.

1) Use a Worksheet Query to Set a Dynamic Default Value for the "Bank Card" Field​

2) Configure Query Method​

  • The system will query all records in the specified worksheet.

  • If multiple records match the filter, you can choose to:

    • Use the first matching record’s value

    • Clear the field value

    • Retain the current field value

  • If no record matches, you can also choose to:

    • Set the field to empty

    • Keep the original field value

Learn more about handling null dynamic values in filters

As a result, when assigning values from the matched record to the current field, three outcomes are possible:

  • Clear value (set to null)

  • Retain original value

  • Update with value from the matched record

2. Set Default Value for a Relationship Field​

Example:

When creating a new order, if the user enters a phone number, the system can query the Contacts worksheet to find the matching contact. If found, the related contact record will be automatically linked to the current order.

1) Use a Worksheet Query to Set the Default Value for the "Contact" Field​

2) Configure the Query Method​

  • For relationship fields, you do not need to specify the worksheet to query.

    You only need to configure the filter conditions.

  • If multiple records are found, you can choose to:

    • Use the first matched record

    • Clear the field value

    • Retain the original field value

  • If no records are found, you can choose to:

    • Clear the field

    • Retain the original value

Learn more about handling null dynamic values in filters

As a result, when assigning the value from the matched record, three outcomes are possible:

  • Clear value (set to null)

  • Retain original value

  • Update with the matched record

3. Set Default Value for a Subform Field​

Example:

During equipment inspections, when creating a new inspection record, once the equipment name is entered, the system can query a inspection details worksheet containing inspection items and methods. The matched results (multiple rows) will then be automatically written into the "Inspection Details" subform of the current record.

1) Set a Dynamic Default Value for a Subform Field​

Note: Subform fields do not support using "Other Field Values" to set default values.

2) Configure Query Conditions​

  • Select the Inspection Details Worksheet as the data source.

  • You can specify how many rows to retrieve (up to 500 rows).

    For card view mode, the limit is 50 rows.

  • If no matching records are found, you can choose to:

    • Clear the field value
    • Retain the original subform content

3) Map Fields to the Subform​

To write the queried template data into the subform, you need to configure field mapping.

  • Left side: fields in the subform
  • Right side: corresponding fields from the template worksheet