How to Retrieve and Store Subform Values from an Ajax Select Box

Introduction

By default, the Ajax Select Box stores only the identifier (ID) of the selected subform record. In situations where the actual subform field value is required in the main form, additional configuration is necessary. This article describes a method for retrieving subform data based on the selected Ajax Select Box value and storing the required field value in a separate field within the main form by using a Database SQL Query.

How does it work?

1. Create a separate form to act as the subform.
2. In the Settings tab, for Load Data Form, select Database SQL Query to load data into the subform.
   
3. Under Configure SQL Query, use a WHERE clause to filter based on the Ajax Select Box selection in the SQL SELECT Query property.
   

   Select
      * 
   from
      app_fd_appointment
   where
      c_name = ?

   
   
4. Reference the subform filter column in the main form.
   
5. Populate values in the edit form using a Custom HTML element, and insert the HTML code below:
   
   

   <script>
     $(document).ready(function () {
       let ajaxSelectField = FormUtil.getField("ajax_select_box"); // replace with your Ajax Select Box field ID
       let urlParams = new URLSearchParams(window.location.search);
       let isEditMode =
         urlParams.has("_mode") && urlParams.get("_mode") === "edit";
   
       if (ajaxSelectField) {
         $(ajaxSelectField).on("change", function () {
           let selectedValue = $(this).val();
           let actualElField = FormUtil.getField("actual_value");
   
           if (selectedValue) {
             $(actualElField).val(selectedValue);
           } else {
             $(actualElField).val("");
           }
         });
       }
   
       // populate the values in form edit
       let actualElField = FormUtil.getField("actual_value");
   
       if (actualElField && isEditMode && ajaxSelectField) {
         const actualValue = $(actualElField).val();
         if (actualValue) {
           $(ajaxSelectField).val(actualValue);
           $(ajaxSelectField).trigger("change");
         } else if (actualValue === "") {
           $(ajaxSelectField).val("");
         } else if (actualValue === null) {
           $(ajaxSelectField).val("");
         } else if (actualValue === undefined) {
         } else {
           $(ajaxSelectField).val("");
         }
       }
     });
   </script>
   

Expected outcome

Below is an image showing how the final result should look after following the steps above:

• The Ajax Select Box continues to store the selected subform record ID by default.
• The selected subform record ID is used to retrieve related data via a Database SQL Query.
• The required field value from the subform is successfully fetched based on the selected record ID.
• The retrieved subform field value is stored in a separate field within the main form.
• The main form displays the correct subform value without requiring manual user input.

Download sample app