Table lookup
This tool allows you to overwrite the incoming data from the streaming app.
Imagine that you receives an order from a store with the “Paid” status . You are glad and want to change the status of this order in your CRM system. However, when trying to transfer the “status” field from online store to CRM, an error occurs. This is because CRM expects a numeric code in the “status” field, but receives the text - “Paid”. To work around this error, it is necessary to change data format.
We will consider an example with AliExpress and Bitrix24 integration.
First, we create an automation that responds to order status changes in AliExpress and automatically change statuses in Bitrix24. Our task is to track the cancellation of orders by customers.
Aliexpress order status:
PLACE_ORDER_SUCCESS – Awaiting payment from customer
PAYMENT_PROCESSING – Payment for the order is being processed
RISK_CONTROL – Payment successful, order under risk control
WAIT_SELLER_SEND_GOODS – Waiting for the seller to ship the goods
SELLER_PART_SEND_GOODS – The seller has partially shipped the goods
WAIT_BUYER_ACCEPT_GOODS – Seller has shipped all goods, awaiting order confirmation by buyer
IN_CANCEL – Waiting to reach an agreement between the seller and the buyer
FINISH – Complete order closing
ARQUIVO – The order has been sent to the archive
And there are the steps of the Bitrix24 CRM system funnel:
new new
PREPARATION - Preparation of documents
PREPAYMENT_INVOICE – Prepaid Invoice
1 – Delivery in Ya. Taxi
2 – Send track number
RUNNING - In progress
FINAL_INVOICE – final score
WON – Successful business
LOSE – Trading Failed
APOLOGY - Analysis of the reason for failure
As you can see, they are not the same. You can easily solve this problem through table replacement.
Open the Automations Builder section and click the + button. Select the “Tools” option and, in the window that opens, pick the Table lookup tool.
Fill in each of the fields with the respective information:
Fill in each of the fields with the respective information:
Name - set a name for your new variable. You will use this name later.
Value - select the value that the system will check for compliance with the condition that you will configure later. In the example, this is the Aliexpress “Order Status” value.
Add condition - add the conditions for the tool to work. You can configure one more condition as the number of customizable conditions is not limited.
Otherwise - specify the value that will be passed if Albato cannot find a value that matches the above condition. This feature works according to the If... Else... scenario.
Here is an example we are using:
Name - We call our new variable “Transformed Order Status”.
Value - We chose Aliexpress “Order Status” variable as the value.
Condition 1 - we establish a condition according to which the following scenario is performed: if the data of the variable “Value” is equal to the hard-coded text “INCANCEL”, then we change this value to the hard-coded text “LOSE”_ .
Otherwise - we configure the field so that if none of the conditions are met, the data of the variable "Order Status" in Aliexpress is automatically replaced with the value "RUNNING" - this is the system value of the funnel "In Progress" status in Bitrix24.
Observation: You can find the system values for funnel or order statuses in the “Apps” section. Find the service you created a connection to and look for the value in the directories. Below we show you how you can find values for Bitrix24.
The tool is configured! All that's left is to use the value in the next step.
Let's use the duplicate search when creating a new deal to find an existing one. This example assumes that during the initial creation of offers, the AliExpress order ID was transferred to the “Name” of the created offer. In order for the “Bitrix24: New Deal” step to work, updating the status of an existing transaction, it is also necessary to transfer the AliExpress order ID to the “Name” field.
Then go to the duplicate processing settings in the "Bitrix24: New Deal" step.
Specify the processing rule “Update record if a duplicate is found” and select “Deal name” as the field for checking compliance.
All fields will contain “Current Value”, which means that the field will save the already existing data.
To update the data, you will need to specify a new value. Position the cursor in the “Stage” field, delete “Current value” as normal text and place the tool variable “Replace in table”. To do this, in the list that appears, find the “Other values” section and, in it, the resource in question.
We select a variable with the name created earlier, in our case it is “Transformed Order Status”.
Click “Save”. Ready! You have now learned how to customize Albato's Table lookup tool.
Here are some cases that you can solve using this tool.
Case #1
Task: Consider a scenario where a user needs to receive notifications on Telegram when the status of a transaction is changed in amoCRM. In this case, it is not enough to show the name of the status the transaction is moved to, but we also need a numerical representation. How can we do this using the Table lookup tool?
Solution: Since the connection has remained active, we will be able to demonstrate an updated version of the message with the correct display of the step names.
You need to fill in the fields as follows:
Name - indicate the name of our entity, what we are changing and why.
Value - target a field whose values will change according to conditions. In our case, this is the “Status” field. Attention: amoCRM passes an integer value to the status field.
Condition - indicate the status start value and end value. To better understand the mechanics of table replacement, just rephrase the construct and bring it to the following form: if the value in the X field is equal to the Y value, then pass the Z value further down the link. In our case: if the value of the “Status” field is equal to the value “142”, then later in the link pass the value “Successfully implemented”. And so for each status.
Otherwise - note the value that will be passed by the string if none of the substitution conditions work. At the end, just save and test the format of the message received on Telegram
At the end, just save and test the format of the message received on Telegram.
Case #2
Task: Consider a connection between JivoSite and amoCRM. It is necessary to create tasks for the same users in amoCRM who are operators in JivoSite.
Solution: Add the “Table lookup” tool to the existing automation. When making the settings, keep in mind that:
the responsible in amoCRM is transmitted as a unique identifier;
JivoSite replacement can be performed either by operator ID or by name.
Now you need to collect the following logic: if the parameter “Operator name” is equal to the value “Ann”, then transfer the value “872567332” to the next step of the automation. Otherwise, transfer the value “98638475”.
After saving the block with table replacement, configure the correct replacement of this value in the next step.
Now, the same operator who communicated with the user in JivoSite will receive a task automatically in amoCRM.
Imagine that you receives an order from a store with the “Paid” status . You are glad and want to change the status of this order in your CRM system. However, when trying to transfer the “status” field from online store to CRM, an error occurs. This is because CRM expects a numeric code in the “status” field, but receives the text - “Paid”. To work around this error, it is necessary to change data format.
We will consider an example with AliExpress and Bitrix24 integration.
First, we create an automation that responds to order status changes in AliExpress and automatically change statuses in Bitrix24. Our task is to track the cancellation of orders by customers.
Aliexpress order status:
PLACE_ORDER_SUCCESS – Awaiting payment from customer
PAYMENT_PROCESSING – Payment for the order is being processed
RISK_CONTROL – Payment successful, order under risk control
WAIT_SELLER_SEND_GOODS – Waiting for the seller to ship the goods
SELLER_PART_SEND_GOODS – The seller has partially shipped the goods
WAIT_BUYER_ACCEPT_GOODS – Seller has shipped all goods, awaiting order confirmation by buyer
IN_CANCEL – Waiting to reach an agreement between the seller and the buyer
FINISH – Complete order closing
ARQUIVO – The order has been sent to the archive
And there are the steps of the Bitrix24 CRM system funnel:
new new
PREPARATION - Preparation of documents
PREPAYMENT_INVOICE – Prepaid Invoice
1 – Delivery in Ya. Taxi
2 – Send track number
RUNNING - In progress
FINAL_INVOICE – final score
WON – Successful business
LOSE – Trading Failed
APOLOGY - Analysis of the reason for failure
As you can see, they are not the same. You can easily solve this problem through table replacement.
Step 1. Adding a tool
Open the Automations Builder section and click the + button. Select the “Tools” option and, in the window that opens, pick the Table lookup tool.
Step 2. Set up a tool
Fill in each of the fields with the respective information:
Fill in each of the fields with the respective information:
Name - set a name for your new variable. You will use this name later.
Value - select the value that the system will check for compliance with the condition that you will configure later. In the example, this is the Aliexpress “Order Status” value.
Add condition - add the conditions for the tool to work. You can configure one more condition as the number of customizable conditions is not limited.
Otherwise - specify the value that will be passed if Albato cannot find a value that matches the above condition. This feature works according to the If... Else... scenario.
Here is an example we are using:
Name - We call our new variable “Transformed Order Status”.
Value - We chose Aliexpress “Order Status” variable as the value.
Condition 1 - we establish a condition according to which the following scenario is performed: if the data of the variable “Value” is equal to the hard-coded text “INCANCEL”, then we change this value to the hard-coded text “LOSE”_ .
Otherwise - we configure the field so that if none of the conditions are met, the data of the variable "Order Status" in Aliexpress is automatically replaced with the value "RUNNING" - this is the system value of the funnel "In Progress" status in Bitrix24.
Observation: You can find the system values for funnel or order statuses in the “Apps” section. Find the service you created a connection to and look for the value in the directories. Below we show you how you can find values for Bitrix24.
The tool is configured! All that's left is to use the value in the next step.
Step 3. Enabling the Duplicate Search setting
Let's use the duplicate search when creating a new deal to find an existing one. This example assumes that during the initial creation of offers, the AliExpress order ID was transferred to the “Name” of the created offer. In order for the “Bitrix24: New Deal” step to work, updating the status of an existing transaction, it is also necessary to transfer the AliExpress order ID to the “Name” field.
Then go to the duplicate processing settings in the "Bitrix24: New Deal" step.
Specify the processing rule “Update record if a duplicate is found” and select “Deal name” as the field for checking compliance.
All fields will contain “Current Value”, which means that the field will save the already existing data.
To update the data, you will need to specify a new value. Position the cursor in the “Stage” field, delete “Current value” as normal text and place the tool variable “Replace in table”. To do this, in the list that appears, find the “Other values” section and, in it, the resource in question.
We select a variable with the name created earlier, in our case it is “Transformed Order Status”.
Click “Save”. Ready! You have now learned how to customize Albato's Table lookup tool.
Cases about using the “Table lookup” tool
Here are some cases that you can solve using this tool.
Case #1
Task: Consider a scenario where a user needs to receive notifications on Telegram when the status of a transaction is changed in amoCRM. In this case, it is not enough to show the name of the status the transaction is moved to, but we also need a numerical representation. How can we do this using the Table lookup tool?
Solution: Since the connection has remained active, we will be able to demonstrate an updated version of the message with the correct display of the step names.
You need to fill in the fields as follows:
Name - indicate the name of our entity, what we are changing and why.
Value - target a field whose values will change according to conditions. In our case, this is the “Status” field. Attention: amoCRM passes an integer value to the status field.
Condition - indicate the status start value and end value. To better understand the mechanics of table replacement, just rephrase the construct and bring it to the following form: if the value in the X field is equal to the Y value, then pass the Z value further down the link. In our case: if the value of the “Status” field is equal to the value “142”, then later in the link pass the value “Successfully implemented”. And so for each status.
Otherwise - note the value that will be passed by the string if none of the substitution conditions work. At the end, just save and test the format of the message received on Telegram
At the end, just save and test the format of the message received on Telegram.
Case #2
Task: Consider a connection between JivoSite and amoCRM. It is necessary to create tasks for the same users in amoCRM who are operators in JivoSite.
Solution: Add the “Table lookup” tool to the existing automation. When making the settings, keep in mind that:
the responsible in amoCRM is transmitted as a unique identifier;
JivoSite replacement can be performed either by operator ID or by name.
Now you need to collect the following logic: if the parameter “Operator name” is equal to the value “Ann”, then transfer the value “872567332” to the next step of the automation. Otherwise, transfer the value “98638475”.
After saving the block with table replacement, configure the correct replacement of this value in the next step.
Now, the same operator who communicated with the user in JivoSite will receive a task automatically in amoCRM.
Updated on: 04/05/2023
Thank you!