How to truncate and add records in Zoho CRM and Creator?

How to truncate and add data from other sources to Zoho CRM and Creator?

When working with dynamic source data, such as data from Zoho Analytics to Zoho CRM or Creator, you may need to completely remove existing records in a CRM module or Creator forms before inserting the latest data. Since Zoho CRM and Zoho Creator do not support a direct "Truncate and Add" option, this process can be achieved using a combination of Filter Transform and Ordered exports feature in Zoho DataPrep.

Case 1: Truncating and adding records in Zoho CRM


Consider a pipeline where the data comes from an external source (for example, Zoho Analytics) and needs to be updated in Zoho CRM. The requirement here is to retain only the latest data in Zoho CRM, which means all existing records in the CRM module must be deleted before pushing the new ones.

Since the Delete records from Zoho CRM option is supported only for Zoho CRM -> Zoho CRM exports, you cannot directly delete existing records when importing from other sources. To achieve this, you must first export the data from other sources into Zoho CRM using DataPrep and then follow the steps below to delete the existing records before adding the new ones.

NotesNote: The Delete records from Zoho CRM option is available only when importing data from Zoho CRM and exporting it back to the same Zoho CRM module (CRM -> CRM).


Steps to be followed to truncate and add records in Zoho CRM

1. Delete Existing Records from Zoho CRM using filtering:

  • Import data from Zoho CRM. Here, you might want to import all those records you want to delete into your CRM module(s), either from the beginning or from a custom date. 

  • Use the Filter Transform to filter and delete all existing records.

a) Right-click on a column and select the Filter option.

b) Click the  icon, then choose All Column Filter.

c) From the dropdown, select Data Quality, filter the columns based on Valid Data.

d) Repeat the same process for filtering Invalid Data and Missing Data, and keep the filtered rows. 

2. Insert New records from Zoho Analytics to CRM:

Create another flow from Zoho Analytics to Zoho CRM to insert new data. 

Note: While adding the destination as Zoho CRM, select only add newly imported records.

3. Check Target Matching
 After adding the destination, navigate to the DataPrep Studio page, click the target matching icon  at the top right corner, and choose the Show target option. Make sure the target matching is done to avoid export failure. Click here to know more about target matching.


4. Schedule the Pipeline:

  • You can schedule your pipeline using the Schedule option in the pipeline builder. Select a Repeat method (hourly, daily, weekly, monthly) and set frequency using the Perform every dropdown.


By scheduling the pipeline, the existing data from Zoho CRM will first be imported and deleted. Then, the latest data from Analytics will be pushed into the CRM module. This process will continue based on the specified repeat method and default ordered exports. When the job is completed, you can view the job summary in the Jobs page.


Case 2: Truncating and adding records in Zoho Creator

Consider a pipeline where the data comes from an external source (for example, Zoho Analytics) and needs to be updated in Zoho Creator. The requirement here is to retain only the latest data in Zoho Creator, which means all existing records in the Creator form must be deleted before pushing the new ones.

Since the Delete records from Zoho Creator option is supported only for Zoho Creator -> Zoho Creator exports, you cannot directly delete existing records when importing from other sources. To achieve this, you must first export the data from other sources into Zoho Creator using DataPrep and then follow the steps below to delete the existing records before adding the new ones.

Notes
Note: The Delete records from Zoho Creator option is available only when importing data from Zoho Creator and exporting it back to the same Zoho Creator form (Creator -> Creator).


Steps to be followed to truncate and add records in Zoho Creator:


1. Delete Existing Records from Zoho Creator using filtering:

  • Create a flow from Zoho Creator to Zoho Creator.

  • Use the Filter Transform to filter and delete all existing records.

    Right-click on a column and select the Filter option. Click the  icon, then choose All Column Filter. From the dropdown, select Data Quality, filter the columns based on Valid Data. Repeat the same process for filtering Invalid Data and Missing Data, and keep the filtered rows. 



  • Add Zoho Creator to the destination and choose Update existing records, and enable Delete records from Zoho Creator to delete the existing records in Zoho Creator.




2. Insert New records from Zoho Analytics to Creator:




Create another flow from Zoho Analytics to Zoho Creator to insert new data. 

Note: While adding the destination as Zoho Creator, select Add only new records.


3. Check Target Matching
After adding the destination, navigate to the DataPrep Studio page, click the target matching icon at the top right corner, and choose the Show target option. Make sure the target matching is done to avoid export failure. Click here to know more about target matching.



4. Schedule the Pipeline: 

  • You can schedule your pipeline using the Schedule option in the pipeline builder. Select a Repeat method (hourly, daily, weekly, monthly) and set frequency using Perform every dropdown.



By scheduling the pipeline, the existing data from Zoho CRM will first be imported and deleted. Then, the latest data from Analytics will be pushed into the CRM module. This process will continue based on the specified repeat method and default ordered exports. When the job is completed, you can view the job summary in the Jobs page. 


SEE ALSO