# Sending Form Submission Data from OE to Celonis
You can manage importing form submission data into Celonis through Form Extractor with different kinds of filtering. Filtering might include form IDs that enable precise data retrieval, or employing the extractor to handle submissions from all form types.
## Prerequisite
To extract the data from OE to Celonis, you have to create a Data Pool in your Celonis account. Celonis uses Data Pools to collect and cluster data information to set up an integration flow. To learn about the details, see the [Celonis Data Pools](https://docs.celonis.com/en/creating-and-managing-data-pools.html) documentation.
## Extracting data from Form submissions
### Creating a data connection
You can create your own data extractor based on an Emporix template. The steps below show how to work with the extractor template and how to use it for your custom configuration.
Start with creating a connection to your Celonis Data Pool that links your OE as the Data Source:
1. In your Celonis account, go to **Data Integration** -> **Your Data Pool** -> **Data Connections** and choose **Add Data Connection**.
2. Choose **Connect to Data Source**.
3. Go to the **Custom** section and choose **Build custom connection** -> **Import from file**.
4. Enter the **Name** for your custom extractor. Optionally, you can also add a **Description** for it.
5. Download the Forms Extractor and then upload it as a JSON file in your Celonis extractor builder:
{% file src="" %}
6. Choose **Save and Continue**.
7. Check the parameters that should be used for the connection and then continue to the next step. The parameters were configured automatically with the uploaded extractor JSON file.
8. Check the authentication method; for Emporix it's the OAuth2 (Client Credentials) method with the following endpoint and type details:
* **Get Token Endpoint**: {Connection.AUTH\_URL}/oauth/token
* **Grant Type**: client\_credentials\
Don't modify this configuration. Continue to the next step to finish the process.
At the end you can see the **Defined Endpoints**.
9. Having configured the data connection, we now need to establish the connection with Emporix system. Go to the **Data Connections** list again and choose **Add Data Connection** -> **Connect to Data Source** -> **Custom**. The data connection you created with the form extractor is visible there, under Custom connections.
10. Choose your connection and check its configuration details to make sure all the authorization details like **Client ID** or **Client Secret** are added. Additionally, provide the form **slug** information to configure the right data filtering. Save your changes.
* You can find the Client ID and Client Secret in Emporix Developer Portal - [Manage API Keys](https://app.gitbook.com/s/bTY7EwZtYYQYC6GOcdTj/getting-started/developer-portal/manage-apikeys).
* The **slug** details can be checked in two ways:
* within the Management Dashboard in the Forms view
* with an API call, for example by calling the [Get a Form Make](https://github.com/emporix/emporix-documentation-portal/blob/master/content/oe-user-guides/value-streams/working-with-digital-processes/oe-make-modules.md#forms-modules) module.
11. When the connection to Emporix is established, you can now configure the responses that you get. In the endpoints configuration, choose the **Form** endpoint to check and customize its configuration.
The **Configure Response** that is visible in the endpoint configuration can be done automatically.
Use the **Configure response using samples from your source system** option to generate the example from the source system.\
Expand the field -> Choose **Use existing Data Source** -> Choose *your data source* -> Choose **Build Response**.\
This autogenerates the response configuration, which you can then see in the JSON response.
12. Similarly as for the Forms endpoint, choose the **Form Submission** endpoint to check and customize its configuration.
The **Configure Response** that is visible in the endpoint configuration can be done automatically.
{% hint style="warning" %}
Make sure the `id` is checked as a **Primary Key**.
{% endhint %}
Use the **Configure response using samples from your source system** option to generate the example from the source system.\
Expand the field -> Choose **Use existing Data Source** -> Choose *your data source* -> Choose **Build Response**.
This autogenerates the response configuration. You can check that the JSON was updated with submission data configuration, for example:
```json
"data" : {
"visiting" : [
0
],
"select" : 0,
"starship" : 0,
"requestPermissionToLand" : false,
"shipCategory" : "",
"doomed" : false,
"_magicLinkCustomAttributes" : {
"planet" : [
{
"name" : "",
"residents" : [
{
"id" : "",
"name" : ""
}
]
}
]
}
}
```
13. Choose **Finish** to save your connection configuration.
## Data jobs
With the forms extractor, you can configure the data jobs to either upload data related to form submissions only, or to form submissions with magic links.
### Data jobs for form submissions
1. To start with the data job configuration, in your **Data Pool** go to **Data Jobs** and choose **Add Data Job**. Add a name for the **Data Job** and choose the **Data Connection** you created for the forms.
For example, a data job named **Forms extraction** and a related **orbit-control extraction** to it.
After creating the data job, you can add the forms extractions.\
2\. Choose **Add** in the **Extractions** section, add a **Name** for the extraction and save it.\
3\. Choose **Add Extraction** in the **Table Configuration**.\
4\. Select **form** from the list of available tables. Save the selection.
As a result, you should see the form table added to the extraction.
5. Go to **Extraction Settings**, choose option B as the load configuration and save it.
6. Go back to the **Data Jobs** view. Choose **Execute Data Job**. This starts a new task and populates a schema that is later visible in the **Transformations**.\
To check logs after a job execution, go to **Logs** tab and select the execution task that you want to view. You can see all the details related to the executed job.
7. Go back to the **Data Jobs** view and choose **Add** in the **Transformations** section. Enter a name for the transformation and save it. The schema is automatically uploaded.
You can now add **Transformation Statements** and execute them. For example: `select * from "form_submission$data$visiting"` or `select * from "form_submission"`.
Whenever a transformation is executed you can see the Output of the job.
### Data jobs for form submissions with magic links
The forms extractor includes magic links extractor, which enhances its functionality. Using the extractor, you can extract form submissions and also track who has been sent a form, regardless of whether they have completed or submitted it. By specifying the form type, the extractor loads relevant magic links, providing visibility into who has received the form, not just the responses they have submitted.
To have magic links data included in the extraction:
1. Choose **Add** in the **Extractions** section, add a **Name** for the extraction and save it.
2. Choose **Add Extraction** in the **Table Configuration**.
3. Select **form** from the list of available tables. Save the selection.
As a result, you should see the form table added to the extraction.\
4\. Go to **Extraction Settings**, choose option B as the load configuration and save it.
5. Go back to the **Data Jobs** view. Choose **Execute Data Job**. This starts a new task and populates a schema that is later visible in the **Transformations**.\
To check logs after a job execution, go to **Logs** tab and select the execution task that you want to view. You can see all the details related to the executed job.
6. Go back to the **Data Jobs** view and choose **Add** in the **Transformations** section. Enter a name for the transformation and save it. The schema is automatically uploaded.
You can now add **Transformation Statement** and execute it:
```json
create table if not exists magic_link_submissions (
magic_link_id varchar2(26),
num_submissions integer,
PRIMARY KEY(magic_link_id)
);
DROP TABLE IF EXISTS magic_link_submissions_new;
create table if not exists magic_link_submissions_new (
magic_link_id varchar2(26),
num_submissions integer,
PRIMARY KEY(magic_link_id)
);
INSERT into magic_link_submissions_new
select ml.id, count(submissions."submittedAt")
from magic_link as ml left outer join "magic_link$submissions" as submissions on submissions."magic_link_id" = ml.id
group by ml.id;
merge into magic_link_submissions
using magic_link_submissions_new
on magic_link_submissions.magic_link_id = magic_link_submissions_new.magic_link_id
WHEN NOT MATCHED THEN
insert (magic_link_id, num_submissions)
VALUES (magic_link_submissions_new.magic_link_id, magic_link_submissions_new.num_submissions);
```
{% hint style="info" %}
To more details on Data Jobs, how to work with extractions and transformations, see the Celonis [Extracting and transforming data](https://docs.celonis.com/en/extracting-and-transforming-data.html) documentation.
{% endhint %}
## Data model
With the forms extractor, you can configure the data model to either upload data related to form submissions only, or to form submissions with magic links.
### Form submissions
Set up the **Data Model** to establish relations between all of the extractor's components.
1. Go to *Your Extractor* -> **Data Model** and choose **Add Data Model**.
2. Select `forms` related items for the table and choose **Next**.
You can skip the activity table configuration.\
3\. Set up all the **Foreign Keys** for your activity table.
For example, set the ID key for the **form**:
* Choose **New foreign key** in the form settings.
* Connect the `id` field from a **Dimension table** to a `form$id` in a **Fact table**.
**The next relations to set up are**:
* Link `form` (Dimension table) with `form_submission` (Fact table) by linking `id` and `form$id`.
* Link `form` (Dimension table) with `form$customAttributes` (Fact table) by linking `id` and `form_id`.
* Link `form$customAttributes` (Dimension) with `form$customAttributes$values` (Fact) by linking `form_id` and `form$customAttributes_form_id`.
To see an example of a configured data model, check the below sample **Data Model**:
4. After setting up the relations, go to **Data loads** tab and choose **Load Data Model**.
When the load is finished you can check the details of the currently loaded data model.
### Form submissions with magic links
Set up the **Data Model** to establish relations between all of the extractor's components including magic links data.
1. Go to *Your Extractor* -> **Data Model** and choose **Add Data Model**.
2. Select the items related to `forms` and `magic links` for the table and choose **Next**.
You can skip the activity table configuration.\
3\. Set up all the **Foreign Keys** for your activity table.
For example, set the ID key for the **form**:
* Choose **New foreign key** in the form settings.
* Connect the `id` field from a **Dimension table** to a `form$id` in a **Fact table**.
**The next relations to set up are**:
* Link `form` (Dimension table) with `form_submission$data$visiting` (Fact table) by linking `id` and `form_submission_id`.
* Link `form` (Dimension table) with `form_submission` (Fact table) by linking `id` and `form$id`.
* Link `magic_link` (Dimension table) with `form_submission` (Fact table) by linking `id` and `magicLinkId`.
* Link `magic_link` (Dimension table) with `magic_link_submissions` (Fact table) by linking `id` and `magic_link_id`.
To see an example of a configured data model, check the below sample **Data Model**:
4. After setting up the relations, go to **Data loads** tab and choose **Load Data Model**.
When the load is finished you can check the details of the currently loaded data model.
{% hint style="info" %}
To learn more about the configuration of Data Models in Celonis, see the [Data Model Denifinition](https://docs.celonis.com/en/data-model-definition.html) documentation.
{% endhint %}
## Executing a data load
Execute a data load based on your form submission configuration and created connection.
1. Go to **Data Pools** -> **Data Jobs** and choose **Execute Data Job**. You can select to execute a Delta or a Full Load. Delta loads only the part of data that changed since the last upload.
2. Choose **Execute Selection**. The job starts and you can already see the process logs.
When the process is finished, you can check the logs details.
## Additional extractor configurations
### Delta loads
To enable delta loads submissions, use the `submittedAtGte` filtering. You can configure the executions to make the `submitted at` column visible and to upload data only from the specified time.
1. Go to **Data Jobs** -> *Your extraction task* -> **Table Configuration**.
2. Enable the creation date filter.
3. Create a new parameter for the modified time - `<%=max_modified_time%>`.
Go to *Your Extraction* -> **Parameters** tab and choose **New Parameter** and provide the following values in the fields:
* **type:** dynamic
* **table**: form\_submission
* **column**: submittedAt
* **operation type**: FIND\_MAX
* **data type**: date
* **default date**: any date from the past
4. Use the Delta filter statement to define the parameter settings.
### Delta loads for magic links
Similar to form submission, you can the **Enable change date filter** to configure your delta loads for magic links. You can set up a date from when the updated data would be fetched with the `updatedAt` filtering.\
You can also configure the executions to make the `updated at` column visible and to upload data only from the specified time.
1. Go to **Data Jobs** -> *Your extraction task* -> **Table Configuration**.
2. Create a new parameter for the modified time - `<%=magic_link_max_modified_time%>`.
Go to *Your Extraction* -> **Parameters** tab and choose **New Parameter** and provide the following values in the fields:
* **type:** dynamic
* **table**: magic\_link
* **column**: updatedAt
* **operation type**: FIND\_MAX
* **data type**: date
* **default date**: any date from the past
### Extracting all submissions of a single form
By default, the forms extractor uploads data from the latest submitted version of a form associated with a given magic link. If users might submit the same form more than once, and you want to upload data covering all of its submitted versions, you can override the default behavior by changing the `latest` parameter settings.
To change the default setting:
1. Go to the **Defined Endpoints** and choose the `Form Submission` endpoint to open its configuration.
2. Go to the **Configure Request** section and expand **Request parameters**.
3. Choose to edit the `latest` parameter and change the value from `true` to `false`. By doing this, you make sure that not only the latest submission of a form is uploaded, but all the versions of it submitted by a specific user using a single magic link.
4. Go to **Data Jobs** and execute a task. Afterwards, you can view the execution logs with the updated setup and the number of loaded records.
{% hint style="info" %}
To learn how to create forms, see the [Forms](https://developer.emporix.io/oe/management-dashboard/forms) documentation.
{% endhint %}
