How to use "transform data" automation action? – Help Center | Contractbook

This article describes using a "transform data" automation action to set different criteria for your data sets. It contains the following topics:

What is "transform data" automation action used for?

Transform data - automation action in the in-app automation builder allows you to change how data sets look according to different criteria.

You can use eight different "transform data" actions in all your automations.

Choose the "transform data" action in your automation steps.
Select a transform type:

Date formatting: format dates using one of the available formats.
Date calculation: calculate a new future or past date based on the input data.
- This type of date transformation can help you set an offset in the future or past based on any of your contract variables with the date type. The new calculated date you have defined can be used in any of the following automation steps.
- You can choose one of the following operators to execute the variable date calculation:
  - Addition
  - Subtraction
  - The last day of the month
  - The first day of the month

Recording_2023-03-10_at_12.43.07.gif

Math formula: Use spreadsheet-style formulas to perform various calculations on the input data.
- You can use basic math operators (+, -, *, /, ^), group terms with parentheses, and use various rounding functions:
  - round(n, p) - will round the number (n) to the precision number (p), e.g. round(3.1415, 2) = 3.14
  - floor(n) - will round the number (n) down towards the nearest whole number, e.g. floor(3.14) = 3.0
  - ceil(n) - will round the number (n) up towards the nearest whole number, e.g. ceil(3.14) = 4.0

Recording_2023-03-10_at_12.46.50.gif

Split by: Split the input data by the specific character or text.
- For example, you can split a list of products defined as variables with a , or any other character:

Screenshot_2023-03-10_at_12.52.48.png

Extract email: Find and extract an email from the input data:
- This transform type can find and extract specific email addresses from variables with additional inputs.

Find and replace: Find text in the input data and replace it with different text:
- This transform type can find a specific value and replace it with another one.
- You can set the toggle "use a regular expression to find" to perform a search with regular expressions listed here.
- In this example, we want to find a representative name matching any of the values ("Jane or Janny") and replace it with "Jenny":

Screenshot_2023-03-10_at_13.05.57.png

Extract number: Find and extract a number from the input data:
- This transform type can find and extract a particular number from other variables with additional inputs
- The expected format of the number is "100" or "1000.00":

Screenshot_2023-03-10_at_13.02.29.png

Lookup table: define lookup tables for any number of variables. If the data matches one of the specified entries, the corresponding value will be used.

What is the "transform data" label used for?

Each variable you want to transform requires a specific label.
This should be a descriptive name for the transformed variable.
The label you define here will be available in the next automation steps, allowing you to use it for additional actions:

Screenshot_2023-03-10_at_12.38.58.png

How to use a lookup table data to automatically create a contract draft when a HubSpot deal stage is changed?

This article will show you how to automatically transform data in lookup tables when a deal stage in HubSpot is changed.

Here is a short video tutorial of the needed for the automation setup steps, and a step-by-step guide will follow right after:

Click on Automations tab located in the top menu and then choose My automations from the left-hand sidebar.
You will see a few predefined automation flows to select and use.
To create your own, click Create new button from the upper right-hand corner.

How can I configure the trigger event?

The first thing you need to do is set the trigger event for the automation to be executed.
In this instance, we will use Deal stage is changed:

Screenshot_2022-03-23_at_12.17.07.png

Note: To select this trigger event in the Automation Builder, you must have an established connection to your HubSpot account by following our Contractbook in-app HubSpot integration guide.
The next thing you need to do is to configure which deal stage will initiate the automation flow - you can choose the one which matches your use case, but for this article, we will select the Contract drafted:

To move on to the next step, just click Save.

How can I delete the trigger in the Automation Builder?

At the very bottom of each trigger configuration window, you have the option to delete only this specific trigger and not the complete automation:

Screenshot_2022-05-23_at_12.46.22.png

How can I configure the action event?

This adjustment step covers the action the automation trigger you have just defined will cause. You can use the search bar to locate the option Transform data:

Screenshot_2022-05-26_at_20.39.24.png

The details in the action step of the automation configuration will be grouped under the Sets of data section.
You can use the fields in this window to define lookup tables for any number of variables, and if the data from your trigger source (HubSpot in our example) matches one of the specific entities, the corresponding value will be used in the consequently created contract draft (this is our chosen action but you can use Transform Data together with any other Automation Builder action event):

Screenshot_2022-05-26_at_20.40.03.png

You can start by selecting which custom variable (property) from your HubSpot deal stage will be used to transform the data in the lookup table.
You can use the search bar to locate any of your deal stage variables:

Screenshot_2022-05-25_at_09.52.43.png

In our example, we will map out a "Subscription" variable:

Screenshot_2022-05-25_at_09.52.52.png

All fields of the lookup table configuration will automatically get displayed, and the next step is to choose a label for the custom variable you have selected.
You can provide a descriptive name for the transformed variable in this field; it will become available in the subsequent automation actions:

Screenshot_2022-05-26_at_20.50.01.png

Note: You cannot leave the label field empty:

Screenshot_2022-05-25_at_10.04.35.png

The Transform Data action window configuration will map out the lookup table data entries.
When filling in these fields, the format is "When the value is, then use."
By default, you will have a single line available to complete, and as you proceed, a new line will be added automatically underneath:

Screenshot_2022-05-25_at_10.10.26.png

Note: A multi-type transformation will be available soon (it will allow you, for example, “apply math function” and “convert date to different format”).
For now, only one type of transformation is supported in the Automation Builder - transform the variable value using a lookup table.
You have the option to insert data from the deal stage is changed trigger (HubSpot deal properties) or type it in manually:

Screenshot_2022-05-25_at_10.07.40.png

The purpose of this configuration is to relate a specific value (When the value is) assigned to the custom variable you chose in the previous automation step and give it a particular corresponding value (Use).
In our example, we will map out by manually typing in two different entries of the lookup table with the following data:

When the value is: "SUB1" - Use "Subscription 10% off"
When the value is: "SUB2" - Use "Subscription 20% off"

Screenshot_2022-05-25_at_10.18.10.png

The last configuration step is providing a default value for when the data does not match any entry in the lookup table.

Note: If you leave the default value field empty, the initial value will be used:

Screenshot_2022-05-25_at_10.04.41.png

In our example, we will give the "Subscription" variable a default value of "Subscription 5% off":

Screenshot_2022-05-26_at_20.41.08.png

Once you have completed these steps, you can then click on Add a set of data, and you will be able to configure an additional set of lookup table data:

To complete the Transform Data action setup, click Save to confirm the configuration.

How can I delete a row in the Lookup table?

Once you have added an entry in the lookup table, you can hover over the line and delete only this specific lookup table row:

Screenshot_2022-05-25_at_10.37.44.png

Now you can add a new action that will follow it by clicking on the small plus icon beneath the Transform Data automation step:

Screenshot_2022-05-26_at_20.56.34.png

You can use the search field to locate the option Create a draft:

Screenshot_2022-05-24_at_21.36.21.png

Then select the template you want to base the new contract draft on - you can choose one of your own, shared, or one of Contractbook's predefined templates. In our example, we will pick "Sales & Purchase Agreement" from the Contractbook templates library saved as our own template:

This means that each time a HubSpot deal is moved to the chosen stage, a new contract draft will be automatically generated with the mapped-out lookup table entries available for a customized distribution in the draft's title, body (based on editable fields), attachments, recipients message, Party or Signee details.

How do I use the Details tab?

The first configuration type is under the Details tab. Under this tab, you can start by naming the draft, then filling out the parties' and signees' details.
In our example, we will name the new contract draft after the template's name combined with the variable we mapped out in the Transform Data configuration (Subscription):

Screenshot_2022-05-25_at_10.29.49.png

You can also use the mapped-out lookup table entries data in the draft's body (Data tab), attachments (Attachments tab), and Recipients' messages (Other tab).
If you need to learn more about using the remaining tabs under Create a draft configuration window, you can find our article here.
Once you have finished the steps above, you can click on Save to confirm the configuration of the automation flow and then Publish in the upper right-hand corner.

How can I delete an action in the Automation Builder?

At the bottom of each action configuration window, you have the option to delete only this specific action and not the complete automation:

Screenshot_2022-05-23_at_09.32.58.png

How can I rename the automation?

You can always rename the automation and its steps as you find fit by clicking on the small pen icon next to the generic title in the upper left-hand corner.
In the example we are working with, we will call it "Transform data automation":

Screenshot_2022-05-26_at_20.58.12.png

How can I make changes to published automation?

If you need to make any changes to the automation configuration at one point, remember to unpublish the automation first, then make the necessary modifications and click again on Publish to put the workflow into use.
You can control that by turning the Status toggle bar on or off:

Screenshot_2022-05-26_at_20.59.12.png

How can I use the Activity feed of my automation?

If you click on the three grey dots next to the status toggle bar, you can open, rename, duplicate, delete, or see the activity feed of the automation.
Remember that the automation must be active (published) for the activities to be available to view. This option allows you full traceability of the automation's usage and flow:

Screenshot_2022-05-26_at_20.59.46.png

In the activity sidebar, you will see all the details of when the automation was used and the steps taken before it was triggered.
Just click on the small arrow next to the date and time to expand the details:

Screenshot_2022-05-26_at_21.25.17.png

In addition, you will always be notified in-app in the Notifications at the top navigation bar as soon as any of your automation flows has been executed:

Screenshot_2022-05-25_at_10.43.32.png

Note: You can adjust your account's email and in-app notifications under Profile Settings.

How can I test Transform data in Lookup tables automation?

To perform the test, you must change your HubSpot deal to the one chosen in the automation trigger stage. In our example, we need to select the Contract drafted stage:

Screenshot_2022-05-25_at_10.47.48.png

And then complete all deal's properties, including the custom property you have created in the Automation Builder. In our case, we will assign the following value to the "Subscription" property in HubSpot:

Screenshot_2022-05-25_at_10.52.54.png

This property value corresponds to the first lookup table row:

Screenshot_2022-05-25_at_10.55.57.png

As soon as the deal has been moved to the correct stage that triggers your Transform Data automation and you have chosen a specific value for the custom HubSpot variable used in the automation configuration, you can then go back to Contractbook and check the Notifications under your account:

The automation status will be listed there as either successful or failed.

How can I fix a failed automation?

If the automation has failed, you can access the detailed view by clicking on the failed notification in the list.
In this view, you can identify the reason for the failure by expanding the failure to run details.
You can then use the information found here to change the automation from the configuration settings.

How can I check the automation runs successfully?

If your automation has run successfully, you can open the new contract draft by clicking on its name in the activity sidebar. In our case, we can identify that the automation has run successfully by looking at the title of the new draft.
It matches the name of the template we used as a base for creating it, followed by the value of the lookup table entry we have configured in the automation builder:

Screenshot_2022-05-25_at_10.43.52.png

The data will be automatically fetched from the lookup table entry associated with the typed-in custom property value:

Screenshot_2022-05-25_at_10.33.23.png

Now that you have tested the automation workflow, you are ready to start using it actively and save time by automatically transforming contract data based on lookup table entries.

You can find more automated workflows you can tailor to your needs with our in-app automation builder here.

If you still need additional information or assistance, reach out to us anytime by contacting our Support Team.