Variables of type form data model are generally used to insert rows in the underlying data source of the form data model. This video explains the steps needed to insert a row in the database using AEM workflow.
Hi, in this video, we will take a look at using a variable of type form data model in AEM workflow. The use case that we are going to accomplish is when a user submits this form, it should create a row in the database. So for example, this is a form, and when I click on the submit button, it should create a row in my underlying database here. So this is my database, and when I refresh, I should have a row. So for subsequent videos, we will take a look how to accomplish this particular use case. So the first thing I did was to create a database in MySQL database. So I have created a schema called employee, and under that schema, I have couple of tables. The table that we are going to populate is called new hire, and it has these columns; Employee ID, first name, last name, all the way up to the state. So once you have your database table and column specified, the next thing you need to do is to create a data source from the AEM config manager console. So I’ve logged into my AEM config manager, and search for Apache Sling Connection Pooled Data Sources. And here you can create a new data source by clicking on the plus icon here. So I have already created the data source here. Let me open up this particular data source here. So the data source name is called forums. You can give any name that you want. Keep the data source service property name as is, do not change this, and the JDBC driver class. Since my database is my SQL database, I’ve used the appropriate driver class here, it’s MySQL driver class here, then the JDBC connection URL, so that’s the syntax.
So make sure your database is running, and the port and the schema name. So my schema name is employee and it’s running on localhost 3306, and then the database servers, username and password. You can leave the other settings as is, accept them as the default settings and save your configuration here.
And you can save your configuration. So once the configuration or the data source is created, the next thing to do is to create a form data model based on the data source that was just created.
So after the data source is configured, the next step is to create a form data model. So to create a form data model, I’ve logged into AEM, navigate to the forms and then go into the data integrations menu. And here you can create your new form data model.
Provide a meaningful name at the title. They can leave the data source configuration as is. Click on next. And here, you need to select the data source that will be the basis for your form data model. So the data source that we are going to select is called forums, that was created in the earlier steps. So let’s look for that data source here.
So that’s the forums, and it is backed by the RDBMS database, that’s why you see there’s an icon here of a database. Click on create, And then open it in the edit mode.
Expand the forums note. And you’ll see here the various tables within the MySQL database. So the schema that we are interested in is employee, and then we select the appropriate table, new hire, and click on add selected to add the new hire entity, and you can see all its columns are listed here. And we also need to make sure that we insert the insert service into our form data model here, so that service gets inserted. Then we need to configure the insert service here.
Open up its properties. And the input model object for our insert service is going to be the new hire. So the new hire is the entity which represents a table in our underlying data source, and we’re going to insert rows into that underlying database using this form data model, and then click on done here, and you can save the settings here. So you can even do a test operation of your service by clicking on this, and there’s an employee ID 77 auto generated, and there’s some default data, so click on test. If everything was successful, you’ll get a true message here. And you can even go to the underlying database to check if the values were inserted here. So there is employee ID number 77, and that’s the dummy values that were inserted from our test service of our form data model. So we have created a data source and the form data model. The next thing we need to do is to create a workflow, which inserts rows into the underlying database.
The next step is to create a workflow, which would insert a row in the database using the form data models in work service operation. So to do that, I have created a workflow called insert employee. Let’s take a look at the workflow’s properties.
So the workflow has two steps, one is called a set variable and the other one is called invoke form data model service. And this workflow also has two variables defined. The first one is called submitted data and it is of type form data model, and it is associated with the form data model that we had created earlier called add new employee.
The second variable that we have defined in the workflow is to hold the response of the invoke operation of the form data model service. So in our workflow we have two components as we are set, the first one is called a set variable, which allows us to initialize the submitted data variable here. So the way we initialize it, by going to the mapping tab, select the variable, select the mapping mode as relative to payload, and specify a value here where I’ve called it Data.JSON. So this means that they will be a file called Data.JSON relative to the payload in my CRX repository, and whatever value is in this file, Data.JSON will be stored in the variable submitted data.
And then the next step we have is the invoke form data model service. So this calls the insert service operation of our form data model. So we have associated this form data model component with our add new employee form data model here.
And select the appropriate service, in this case, insert service. And we need to provide the input parameters for our input service. So there are these two options here. We are going to select provide input data using a variable or a JSON file. So the easiest option is to select as a variable, and you select the appropriate variable, which is of type form data model called submitted data. And then store the response of the invoke service in a variable called response here, and then save your workflow and click on the sync button.
So we have created the workflow, which would get triggered when the adaptive form is submitted, and this workflow will insert a row in our underlying database. So the next step that we need to do is to create a form based on the form data model, and configure the form to invoke this particular workflow.
The next thing to do is to create adaptive form based on the form data model that we had created earlier. So to do that, I have logged into AEM forms, create a new adaptive form.
Base it on a template. In this case, I’m going to base it on a blank template. Click next. Provide a title here. I’m going to call it insert employee. And associate the form data model that we had created earlier with this form. So here you can select the form data model called add new employee and click on create to create your adaptive form.
Open the form in the edit mode.
And you see here, this is the data sources tab, it lists you the entities in the underlying data source. So to include all the fields in the table here, you simply have to select the new hire entity here and drag and drop it into the form. And we don’t need the employee ID because that will be auto-generated, so we’ll remove that field from this form. And we also add a submit button to the root panel here.
Right, so we have added a submit button. Now, the next thing we need to do is to configure the submission options of this particular form, so click on form container.
Go to the submission tab here, and you select the invoke action as invoke and AEM workflow.
Select a name, that was insert employee was our workflow name, and the data file path here is going to be Data.JSON. So if you remember, in our workflow we have specified the same name when we were initializing the variable, that name was Data.JSON. So here we need to make sure we use the same name, and that is what we have done here, Data.JSON, and then save your settings. So one thing we can do, which is very nice. If you want to arrange these fields in a tabular format, where you have two columns. So you can select this particular layer here. You need to select the layout layer, and select the any field that you want and click on the parent here, and go to the new hire panel. And here you can specify the number of columns you want. So I think you can specify up to four or six columns, but usually you would specify two columns. And then these things are arranged side by side, so this makes it easier to read. And then you can go back to our edit layer here, and we can change the, if you want, you can change the team of the adaptive form. So I like the Ultramarine here, I can select that.
So that’s my form team. Now, if I want to preview this form and submit.
Preview this form and enter some values in here, like Henry Lacosste, that’s address. And when I click on the submit, the workflow will get triggered and the workflow will insert a new row in my database. So let’s try that when I hit the submit button. And if I go into my database here, there are four rows, and if I were to refresh the database here. So there it is, Henry Lacosste. Employee ID was auto-generated, and a role is created in my database. So this is how you can easily insert values into your database using form data model data type in your workflow. -