Integrate adaptive form with database using AEM workflow submit-forms-to-database-using-forms-portal

Automated Forms Conversion service (AFCS) allows you to convert a non-interactive PDF form, an Acro Form, or an XFA based PDF form into an adaptive form. While initiating the conversion process, you have the option of generating an adaptive form either with or without data bindings.

If you select to generate an adaptive form without data bindings, you can integrate the converted adaptive form with a form data model, XML schema, or a JSON schema after conversion. For form data model, you need to bind adaptive form fields manually with the form data model. However, if you generate an adaptive form with data bindings, the conversion service automatically associates the adaptive form(s) with a JSON schema and creates a data binding between the fields available in the adaptive form and JSON schema. You can then integrate the adaptive form with a database of your choice, fill data in the form, and submit it to the database. Similarly, after successful integration with the database, you can configure fields in the converted adaptive form to retrieve values from the database and prefill adaptive form fields.

The following figure depicts different stages of integrating a converted adaptive form with a database:

This article describes the step-by-step instructions to successfully execute all these integration stages.

Pre-requisites pre-requisites

Sample adaptive form sample-adaptive-form

To execute the use case to integrate converted adaptive forms with database using an AEM workflow, download the following sample PDF file.

You can download the sample Contact Us form using:

Get File

The PDF file serves as the input to the Automated Forms Conversion service (AFCS). The service converts this file to an adaptive form. The following image depicts the sample contact us form in a PDF format.

Install mysql-connector-java-5.1.39-bin.jar file install-mysql-connector-java-file

Perform the following steps, on all the author and publish instances, to install the mysql-connector-java-5.1.39-bin.jar file:

Prepare data for form model prepare-data-for-form-model

AEM Forms Data Integration allows you to configure and connect to disparate data sources. After generating an adaptive form using the conversion process, you can define the form model based on a form data model, XSD, or a JSON schema. You can use a database, Microsoft Dynamics, or any other third-party service to create a form data model.

This tutorial uses the MySQL database as the source to create a form data model. Create a schema in the database and add contactus table to the schema based on the fields that are available in the adaptive form.

You can use the following DDL statement to create the contactus table in database.

CREATE TABLE `contactus` (
   `name` varchar(45) NOT NULL,
   `email` varchar(45) NOT NULL,
   `phonenumber` varchar(10) DEFAULT NULL,
   `issuedesc` varchar(1000) DEFAULT NULL,
   PRIMARY KEY (`email`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8

Configure connection between AEM instance and database configure-connection-between-aem-instance-and-database

Perform the following configuration steps to create a connection between AEM instance and the MYSQL database:

Create form data model create-form-data-model

Once you configure MYSQL as the data source, execute the following steps to create a form data model:

You can download the sample Form Data Model using:

Get File

Generate adaptive forms with JSON binding generate-adaptive-forms-with-json-binding

Use the Automated Forms Conversion service (AFCS) to convert the Contact Us form to an adaptive form with data binding. Ensure that you do not select the Generate adaptive form(s) without data bindings check box while generating the adaptive form.

Select the converted Contact Us form available in the output folder in Forms & Documents and tap Edit. Tap Preview, enter values in the adaptive form fields and tap Submit.

Log on to crx-repository and navigate to /content/forms/fp/admin/submit/data to view the submitted values in JSON format. The following is the sample data in JSON format when you submit the converted Contact Us adaptive form:

{
  "afData": {
    "afUnboundData": {
      "data": {}
    },
    "afBoundData": {
      "data": {
        "name1": "Gloria",
        "email": "abc@xyz.com",
        "phone_number": "2346578965",
        "issue_description": "Test message"
      }
    },
    "afSubmissionInfo": {
      "computedMetaInfo": {},
      "stateOverrides": {},
      "signers": {},
      "afPath": "/content/dam/formsanddocuments/docs_conversion/output/sample_form_json",
      "afSubmissionTime": "20191204014007"
    }
  }
}

You need to create a workflow model now that can process this data and submit it to the MYSQL database using the form data model that you have created in the previous sections.

Create a workflow model to process JSON data create-workflow-model

Execute the following steps to create a workflow model to submit the adaptive form data to the database:

Configure adaptive form submission configure-adaptive-form-submission

Execute the following steps to submit the adaptive form to the workflow model that you have created in the previous section:

Configure adaptive form to prefill values from database

Execute the following steps to configure adaptive form to prefill values from the MYSQL database based on the primary key defined in the table (E-mail in this case):

You can download the sample converted adaptive form using:

Get File