# Sending Forms Magic Links Data from OE to Celonis (Celonis)
You can manage retrieving data from Emporix OE to track which forms were sent to suppliers or contacts using the Magic Links extractor. The extractor allows for precise data retrieval or comprehensive tracking of all sent forms. This ensures accurate visibility into communication history with your suppliers and contacts.
## 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 Magic Links data
### 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 Magic Link 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 endpoint defined for this extractor - **Get Magic Links**.
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**. Choose the data connection that you created with the magic links extractor. It's visible there, under Custom connections.
10. Choose your connection and check its configuration to make sure all the authorization details like **Client ID** or **Client Secret** are added.
11. When the connection to Emporix is established, you can now configure the responses that you get. In the endpoints configuration, choose the **Get Magic Links** endpoint.
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 view in the JSON below.
You can check that the JSON was updated with magic links configuration, for example:
```json
{
"id" : "",
"name" : "",
"expirationDate" : "",
"stylesheet" : "",
"submissionEventType" : "",
"canBeSubmittedOnlyOnce" : false,
"instanceId" : "",
"scheduleId" : "",
"createdAt" : "",
"updatedAt" : "",
"recipient" : {
"company" : {
"id" : "",
"name" : ""
},
"contacts" : [
{
"id" : "",
"name" : "",
"value" : "",
"language" : ""
}
]
},
"mixins" : {
"topicsNames" : "",
"component" : {
"escalated" : "",
"name" : ""
},
"internalContact" : {
"email" : "",
"name" : "",
"preferredLanguage" : ""
},
"internalContact2" : {
"email" : "",
"name" : "",
"preferredLanguage" : ""
}
},
"forms" : [
{
"id" : ""
}
],
"submissions" : [
{
"submittedAt" : ""
}
],
"customAttributes" : [
{
"name" : "",
"values" : [
""
]
}
]
}
```
{% hint style="warning" %}
Make sure the `id` is checked as a **Primary Key**.
{% endhint %}
12. Choose **Finish** to save your connection configuration.
## Data jobs
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 magic links.
For example, a data job named **Load Magic Links** and a related **Magic Link Extractor** 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 **magic\_link** from the list of available tables. Save the selection.
As a result, you should see the form table added to the extraction.
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 and **Add** two transformations in the **Transformations** section.
* `view the magic links` transformation - with `select * from "magic_link"` as the transformation statement
* `create submission table` transformation - with the following statement:
```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" %}
For 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
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 magic\_link 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 **magic\_link**.
* Choose **New foreign key** in the form settings.
**Set up the relations**:
* Link `magic_link` (Dimension) with `magic_link_submissions` (Fact) by linking `id` and `magic_link_id`.
* Link `magic_link_submissions` (Dimension) with `form_submission` (Fact) by linking `magic_link_id` and `magicLinkId`.
* Link `form_submission` (Dimension) with `form_submission$data$visiting` (Fact) by linking `id` and `form_submission_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.
### Restricting initial loads
Use the **Enable creation date filter** to set up a date from which the data should be loaded. This is used for setting the timestamp for the initial load and late in the delta loads as well.
To enable set up the initial load, use the `createdAtGte` filtering.
1. Go to **Data Jobs** -> *Your extraction task* -> **Table Configuration**.
2. Enable the **Creation date** filter and select the `createdAtGte` filter.
3. Set up the custom date and save your configuration.
### Delta loads
Use the **Enable change date filter** to configure your delta loads. You can set up a date from when the updated data would be fetched with the `updatedAtGte` 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. Enable the **Change 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**: magic\_link
* **column**: updatedAt
* **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 - `updatedAtGte = <%=max_modified_time%>`.
