Kaizen #104 - Bulk Write API using PHP SDK (v5) - Part II
Welcome to another exciting week of Kaizen! This week, we will continue our discussion on Bulk Write API. In last week's post, we covered the features of Bulk Write API, and explained in detail the first two steps involved in using Bulk Write API. Today, we'll discuss how to create a bulk write job, fetch the job status, and download the results.
1. Prepare the CSV file
2. Upload your zip file
3. Create a Bulk Write job
After uploading the file and obtaining the file ID, the next step is to create a bulk write job. For this, you will require the file ID, callback URL, and the field API names to perform one of the three operations: insert, update, or upsert. Upon a successful API call, the response will provide a job ID, with which you can check the job status in the next step.
The following are the keys you need to set.
- callback (JSON object, optional) : The CallBack object defines the callback URL and method for receiving notifications or updates about the job's status.
- character_encoding (string, optional) : Specify the encoding to be used for the data being written.
- operation (string, mandatory) : Specify the type of operation to be performed in the bulk write job. Possible values are insert, update and upsert.
- ignore_empty (Boolean, optional) : If you have a few empty fields while updating the record and you want the system to ignore it, input the value as TRUE.
- resource (JSON array, optional): Initiate an object of Resource class that contains the details of the module, file, and field mappings associated with the bulk write job. The resource array contains the file_id, module, find_by, field_mappings and type keys. For more details, refer to our help document here.
Mapping fields in bulk write jobs
To specify which value in the CSV file should be mapped to which field in the CRM module, you can use the field_mappings key in the resource array. This is an optional key, and if you omit it, the system will use the headers of the CSV file to map the fields. When the field_mappings is skipped, the CSV headers must be the field API names and, all the columns of your CSV file will be used for import. In this case, you should remove columns that you do not want to import into your CRM account.
Please note that the CSV headers will be ignored if field_mappings is used while creating the bulk write job.
When to use the field_mappings key?
You should use the field_mappings key if:
- The column headers in your CSV file are different from the field API names in the CRM module.
- You want to ignore certain columns in the CSV file.
- You want to specify a default value for a field in the CRM module.
Rules for using the field_mappings key
- When you map a field by specifying the field_mappings key, it is mandatory to specify either the index or the default_value key, or the system will throw an error.
- The index is the position of the field in the CSV file, starting from 0.
- The default_value key is used when you want to provide a default value for a field. By specifying a default value, you ensure that if a cell in the CSV file is empty, the system will populate it with the default value.
- Use the ignore_empty key when you want the system to skip updating fields that are empty in the source file. Please note that for insert operation, ignore_empty will be set as false by default.
Index | Default Value | Ignore Empty | Empty Cell | Action |
YES | YES/NO | TRUE/FALSE | NO | CSV Value |
YES | YES | TRUE/FALSE | YES | Default Value |
YES | NO | TRUE | YES | No Update |
YES | NO | FALSE | YES | Empty |
| NO | YES | TRUE/FALSE | YES/NO | Default Value |
- If you specify the index and there are no empty cells in the source file, the system will use the values from the CSV file for the corresponding fields. The default_value will be ignored in this case.
- For update and insert operations, when the index is specified and there are a few cells for which the value is empty in the source file, then for the corresponding records with empty value:
- If default_value is specified, the field will be updated with the default_value.
- If default_value is not specified, the system will handle the corresponding fields differently depending on the value of the ignore_empty key.
- The field will be left untouched if ignore_empty is set to TRUE.
- The field will be updated to an empty value if ignore_empty is FALSE.
- When the index is not specified, but a default_value is given, the system will update the corresponding fields in the CRM module with the specified default value.
Bulk Insert with lookup and user lookup data
Here is an example of a simple bulk write job request:
<?php namespace bulkwrite; use com\zoho\crm\api\bulkwrite\BulkWriteOperations; use com\zoho\crm\api\bulkwrite\RequestWrapper; use com\zoho\crm\api\bulkwrite\CallBack; use com\zoho\crm\api\util\Choice; use com\zoho\crm\api\bulkwrite\Resource; use com\zoho\crm\api\bulkwrite\FieldMapping; use com\zoho\crm\api\modules\MinifiedModule; use com\zoho\crm\api\bulkwrite\DefaultValue; require_once "vendor/autoload.php"; class CreateBulkWriteJob { public static function initialize() { // Add init code here. // Refer to this post for more details } public static function createBulkWriteJob(string $moduleAPIName, string $fileId) { $bulkWriteOperations = new BulkWriteOperations(); $requestWrapper = new RequestWrapper(); // Set the callback URL, and method $callback = new CallBack(); $callback->setUrl("add_your_callback_url_here"); $callback->setMethod(new Choice("post")); $requestWrapper->setCallback($callback); $requestWrapper->setCharacterEncoding("UTF-8"); //character encoding $requestWrapper->setOperation(new Choice("insert")); //specify the type of operation // module, file and field mapping details $resourceIns = new Resource(); // To set the type of module that you want to import. The value is data. $resourceIns->setType(new Choice("data")); $module = new MinifiedModule(); $module->setAPIName($moduleAPIName); $resourceIns->setModule($module); $resourceIns->setFileId($fileId); $resourceIns->setIgnoreEmpty(true); $fieldMapping1 = new FieldMapping(); $fieldMapping1->setAPIName("Account_Name"); $fieldMapping1->setIndex(0); $fieldMapping2 = new FieldMapping(); $fieldMapping2->setAPIName("Phone"); $fieldMapping2->setIndex(1); $fieldMapping3 = new FieldMapping(); $fieldMapping3->setAPIName("Parent_Account"); $fieldMapping3->setIndex(2); $fieldMapping3->setFindBy("id"); $fieldMapping4 = new FieldMapping(); $fieldMapping4->setAPIName("Referred_User"); $fieldMapping4->setIndex(3); $fieldMapping4->setFindBy("id"); $defaultValue = new DefaultValue(); $defaultValue->setValue("4876876327565"); $fieldMapping4->setDefaultValue($defaultValue); $resourceIns->setFieldMappings([$fieldMapping1, $fieldMapping2, $fieldMapping3, $fieldMapping4]); $requestWrapper->setResource([$resourceIns]); //Call createBulkWriteJob method that takes RequestWrapper instance as parameter $response = $bulkWriteOperations->createBulkWriteJob($requestWrapper); // Add your code to handle the response received in the $response. } } CreateBulkWriteJob::initialize(); $moduleAPIName = "Accounts"; //The module to which you want to insert the records $fileId = "48768764054001"; // add your file_id received in the response of Upload File CreateBulkWriteJob::createBulkWriteJob($moduleAPIName, $fileId); |
In the above code, Referred_User is a user lookup field, and Parent_Account is a lookup field in the Accounts module. The CSV file contains the unique ID of the corresponding records in the lookup modules.
Here is a sample CSV file used for the above Bulk Insert job. Note that the unique record IDs of the lookup (Parent_Account) and user lookup(Referred_User ) fields. If you choose to schedule the job without using field mappings, ensure that you provide headers for these fields in the format field_api_name.id with the record IDs in the corresponding columns.
Updating or Upserting records by unique key:
During an update operation, the find_by field acts as a unique key that identifies the record to be updated. You can use the unique record id, or any other unique field for this. For example, if the Phone field is a unique field in the Accounts module, specifying setFindBy("Phone") allows updates based on the values present in the Phone field. Similarly, for an upsert operation, if there is no existing record matching the provided Phone number, a new record will be inserted. Conversely, if a matching record is found, it will be updated.
Here is the code snippet for the field_mapping for an update operation using the Phone field as the unique key.
//You can use setFindBy method to update/upsert records based on a unique field $resourceIns->setFindBy("Phone"); //fieldMapping for the unique field $fieldMapping1 = new FieldMapping(); $fieldMapping1->setAPIName("Phone"); $fieldMapping1->setIndex(1); $fieldMapping2 = new FieldMapping(); $fieldMapping2->setAPIName("Description"); $fieldMapping2->setIndex(8); |
Importing Subform Data
The Bulk Write API allows you to import and update records with subforms. To insert or update records with subforms, you must import the parent records first with Bulk Write API. For this bulk write job, $moduleAPIName has to be set with parent module with proper $fieldMapping. Refer to the first sample for more details.
Once you have imported the parent records, get the parent record ids, prepare the CSV file with the subform data, and create a Bulk Write job for the subform module. You can get the parent record ids from the result zip file in Step 5 (Download the result). Refer to our previous post to know how to prepare the CSV file with the Subform data.
Consider a subform named Contact_details in the Accounts module, with the fields Contact_Name, Contact_Email, and Contact_Phone. Here is the code snippet for the field_mapping to import the subform data.
//field_mapping for the Parent_Id $fieldMapping1 = new FieldMapping(); $fieldMapping1->setAPIName("Parent_Id"); $fieldMapping1->setIndex(0); $fieldMapping1->setFindBy("id"); //add proper field_mapping for the subform fields with the subform field API names |
Note that in this case, the $moduleAPIName is the api name of the subform (Contact_details) and the operation type should be insert. The subform data is added to the corresponding record specified by Parent_Id field in the subform CSV.
Multiselect Lookup Fields in Bulk Write API
To upload records with multiselect lookup fields, you can follow the same steps as for subforms. First, import the parent records using a Bulk Write job. Then, import the multiselect lookup field data using another Bulk Write job. For the multiselect lookup field data, the CSV file should contain both the parent record ID and the multiselect lookup field data. Please note that this method does not work for line items.
Consider a multiselect lookup field named Clients in the Accounts module, which is linked to the Contacts module. In your CSV file, ensure that you include both the Parent Record ID and the linked record IDs. For multiselect lookup data, the $moduleAPIName should be the API name of the linked module, and in the field_mappings, the API names used should correspond to the field API names from the lookup module. Check the following sample code for more details.
//field_mapping for module 1 (Contacts) $fieldMapping1 = new FieldMapping(); $fieldMapping1->setAPIName("Client_Account"); //field API name in the linked module $fieldMapping1->setIndex(0); $fieldMapping1->setFindBy("id"); // field_mapping for module 2 (Accounts) $fieldMapping2 = new FieldMapping(); $fieldMapping2->setAPIName("Account"); // field API name in the linked module $fieldMapping2->setIndex(1); $fieldMapping2->setFindBy("id"); |
4. Check job status
The Bulk Write API offers two options for monitoring the status of a job: polling and callback. If you prefer not to poll for job status, you can wait for the system to notify you of job completion by using the provided callback URL in the Create Bulk Write Job request. Alternatively, you can choose to poll the API using the job ID obtained from the previous step to check the status of the scheduled bulk write job. The job status can be one of the following: ADDED, INPROGRESS, or COMPLETED. Only completed jobs will include the download-URL key in the response.
Here is a code snippet to check the job status and get the download URL from the response.
public static function getBulkWriteJobDetails(string $jobId) { $bulkWriteOperations = new BulkWriteOperations(); //Call getBulkWriteJobDetails method that takes jobId as parameter $response = $bulkWriteOperations->getBulkWriteJobDetails($jobId); if ($response != null) { $responseWrapper = $response->getObject(); if ($responseWrapper instanceof BulkWriteResponse) { $result = $responseWrapper->getResult(); if ($result != null) echo ("Bulkwrite DownloadUrl: " . $result->getDownloadUrl() . "\n"); } } } |
5. Download the result
In this step, you can obtain the result of the bulk write job in a ZIP file that includes a CSV file containing the details of the job. Please note that you can get the details of only one bulk write job in a single API call.
To download the result, you need to use the download_url provided in the response. The CSV file will consist of the first three mapped columns from the uploaded file, as well as three additional columns: STATUS, RECORD_ID, and ERRORS.
{ public static function initialize() { // Add init code here. // Refer to this post for more details } public static function downloadBulkWriteResult(string $downloadUrl, string $destinationFolder) { $bulkWriteOperations = new BulkWriteOperations(); $response = $bulkWriteOperations->downloadBulkWriteResult($downloadUrl); // Add your code to handle the response received in the $response. } } DownloadBulkWriteResult::initialize(); $downloadUrl = "paste_your_download_url_here"; $destinationFolder = "/Documents"; DownloadBulkWriteResult::downloadBulkWriteResult( $downloadUrl,$destinationFolder); |
The image shows the result of an Upsert operation. As you can see, few of the records were skipped because the unique field had a duplicate value.
We hope you found this post useful. If you have any further questions, reach out to us at support@zohocrm.com or let us know in the comments section. We are looking forward to hearing from you!
Stay tuned for more useful posts in the series. See you all next week!
Access your files securely from anywhere
New to Zoho Recruit?
Zoho Developer Community
New to Zoho LandingPage?
Zoho LandingPage Resources
New to Zoho Survey?
New to Zoho CRM Plus?
Deliver unforgettable customer experiences
New to Zoho CRM Plus?
Deliver unforgettable customer experiences
New to Zoho Marketing Plus?
Everything you need to run your marketing
New to Zoho Marketing Plus?
Everything you need to run your marketing
New to Bigin?
Topic Participants
Anu Abraham
Sticky Posts
Kaizen #152 - Client Script Support for the new Canvas Record Forms
Hello everyone! Have you ever wanted to trigger actions on click of a canvas button, icon, or text mandatory forms in Create/Edit and Clone Pages? Have you ever wanted to control how elements behave on the new Canvas Record Forms? This can be achievedKaizen #142: How to Navigate to Another Page in Zoho CRM using Client Script
Hello everyone! Welcome back to another exciting Kaizen post. In this post, let us see how you can you navigate to different Pages using Client Script. In this Kaizen post, Need to Navigate to different Pages Client Script ZDKs related to navigation A.Kaizen #210 - Answering your Questions | Event Management System using ZDK CLI
Hello Everyone, Welcome back to yet another post in the Kaizen Series! As you already may know, for the Kaizen #200 milestone, we asked for your feedback and many of you suggested topics for us to discuss. We have been writing on these topics over theKaizen #197: Frequently Asked Questions on GraphQL APIs
🎊 Nearing 200th Kaizen Post – We want to hear from you! Do you have any questions, suggestions, or topics you would like us to cover in future posts? Your insights and suggestions help us shape future content and make this series better for everyone.Kaizen #198: Using Client Script for Custom Validation in Blueprint
Nearing 200th Kaizen Post – 1 More to the Big Two-Oh-Oh! Do you have any questions, suggestions, or topics you would like us to cover in future posts? Your insights and suggestions help us shape future content and make this series better for everyone.
New to Zoho TeamInbox?
Zoho TeamInbox Resources
Zoho CRM Plus Resources
Zoho Books Resources
Zoho Subscriptions Resources
Zoho Projects Resources
Zoho Sprints Resources
Qntrl Resources
Zoho Creator Resources
Zoho CRM Resources
Design. Discuss. Deliver.
Zoho Show Resources
Get Started. Write Away!
Writer is a powerful online word processor, designed for collaborative work.
Zoho CRM コンテンツ
-
オンラインヘルプ
-
Webセミナー
-
機能活用動画
-
よくある質問
-
Ebook
-
-
Zoho Campaigns
- Zoho サービスのWebセミナー
その他のサービス コンテンツ
Nederlandse Hulpbronnen
ご検討中の方
Recent Topics
Python - code studio
Hi, I see the code studio is "coming soon". We have some files that will require some more complex transformation, is this feature far off? It appears to have been released in Zoho Analytics alreadyGood news! Calendar in Zoho CRM gets a face lift
Dear Customers, We are delighted to unveil the revamped calendar UI in Zoho CRM. With a complete visual overhaul aligned with CRM for Everyone, the calendar now offers a more intuitive and flexible scheduling experience. What’s new? Distinguish activitiesReplacing email ID,
In zoho sheets If I am sending it as an email attachments can I replace sender email ID from notifications to my email ID.Move orders scan ISBN
Hi We have ISBN setup to be searched in items zoho but move orders dissent recognize the ISBN is there q missing configuration? regards, JSViewID and Zoho Desk API
I'm looking at the documentation for Listing contacts and it looks like you can filter by ViewID. I assume this is views created in a department or all departments. Is this correct? And if so, how do I find the ViewID for that view? EDIT: I see the viewAbility to Link Reported Issues from Zoho Desk to Specific Tasks or Subtasks in Zoho Projects
Hi Zoho Desk Team, Hope you're doing well. When reporting a bug from Zoho Desk to Zoho Projects, we’ve noticed that it’s currently not possible to select an existing task or subtask to associate the issue with. However, when working directly inside ZohoAutomatically Update Ticket Status in Zoho Desk Based on Actions in Zoho Projects
Hi Zoho Desk Team, Hope you’re doing well. We’re using the Zoho Desk–Zoho Projects integration to manage tasks related to customer tickets, and it works well for linking and tracking progress. However, there are a few important automation capabilitiesPrint Tickets
We have field engineers who visit customers. We would like the option to print a job sheet with full details of the job and account/contact details.Zoho Desk integration with Power BI
Hi, I want to be able to create a Power BI report which has live updates of ticket data from zoho desk, is this possile at all? Thanks JackAbility to Attach Images When Reporting Issues to Zoho Projects from Zoho Desk
Hi Zoho Desk Team, Hope you’re doing well. We’re using the Zoho Desk–Zoho Projects integration to report bugs directly from support tickets into the Zoho Projects issue tracker. This integration is extremely useful and helps us maintain smooth coordinationAbility to Choose Task List and Add Subtasks When Creating Tasks from Zoho Desk
Hi Zoho Desk Team, Hope you’re doing well. We’re using the Zoho Desk–Zoho Projects integration to seamlessly connect customer tickets with project tasks. While the integration works great overall, we noticed two important limitations that affect our workflowSync Task Status from Zoho Projects to Zoho Desk
Hi Zoho Desk Team, Hope you’re doing well. We’re actively using the Zoho Desk–Zoho Projects integration, which helps our support and project teams stay aligned. However, we noticed that when we change a task’s status in Zoho Projects, the change is notDefault/Private Departments in Zoho Desk
1) How does one configure a department to be private? 2) Also, how does one change the default department? 1) On the list of my company's Zoho Departments, I see that we have a default department, but I am unable to choose which department should be default. 2) From the Zoho documentation I see that in order to create a private department, one should uncheck "Display in customer portal" on the Add Department screen. However, is there a way to change this setting after the department has been created?Retainer invoice in Zoho Finance modlue
Hello, Is there a way of creating retainer invoices in the Zoho Finance module? If not can I request this is considered for future updates please.Limited layout rules in a module
There is a limit of 10 layout rules per module. Is there a way to get that functionality through different customization or workflow + custom function (easily accessible), etc. Having just 10 is limiting especially if module contains a lot of data. AreCan we do Image swatches for color variants?
We want to do something like the attached screenshot on our new zoho store. We need image swatches instead of normal text selection. We want to user to select an image as color option. Is this doable? I don't see any option on zoho backend. Please hHow Zoho Desk contributes to the art of savings
Remember the first time your grandmother gave you cash for a birthday or New Year's gift, Christmas gift, or any special day? You probably tucked that money safely into a piggy bank, waiting for the day you could buy something precious or something youZoho CRM IP Addresses to Whitelist
We were told to whitelist IP addresses from Zoho CRM. (CRM, not Zoho Mail.) What is the current list of IP Addresses to whitelist for outbound mail? Is there a website where these IP addresses are published and updated? Everything I could find is overColor of Text Box Changes
Sometimes I find the color of text boxes changed to a different color. This seems to happen when I reopen the same slide deck later. In the image that I am attaching, you see that the colors of the whole "virus," the "irology" part of "virology," andThe difference between Zoho Marketing Automation and Zoho Campaigns
Greetings Marketers! This post aims to differentiate between Zoho Marketing Automation and Zoho Campaigns. By the time you get to the end of the post, you will be able to choose a product that objectively suits you. What is Zoho Marketing Automation?When moments in customer support get "spooky"
It’s Halloween again! Halloween is celebrated with spooky symbols and meanings based on history and traditions, with each region adding its own special touch. While we were kids, we would dress up in costumes along with friends, attend parties, and enjoyHow to use Rollup Summary in a Formula Field?
I created a Rollup Summary (Decimal) field in my module, and it shows values correctly. When I try to reference it in a Formula Field (e.g. ${Deals.Partners_Requested} - ${Deals.Partners_Paid}), I get the error that the field can’t be found. Is it possibleZoho Mail Android app update - View emails shared via Permalink on the app.
Hello everyone! In the latest version(v2.8.2) of the Zoho Mail Android app update, we have brought in support to access the emails shared via permalink within the app. Earlier, when you click the permalink of an email, you'll be redirected to a mobileLet us view and export the full price books data from CRM
I quote out of CRM, some of my clients have specialised pricing for specific products - therefore we use Price Books to manage these special prices. I can only see the breakdown of the products listed in the price book and the specialised pricing forWeekly Tips: Manage External Images in Zoho Mail
When you receive emails every day, whether from clients, newsletters, or services, many of them contain external images that automatically load when you open the message. While this can make emails look more engaging, it can also impact your privacy andEmpowered Custom Views: Cross-Module Criteria Now Supported in Zoho CRM
Hello everyone, We’re excited to introduce cross-module criteria support in custom views! Custom views provide personalized perspectives on your data and that you can save for future use. You can share these views with all users or specific individualsHow to display Motivator components in Zoho CRM home page ?
Hello, I created KPI's, games and so but I want to be able to see my KPI's and my tasks at the same time. Is this possible to display Motivator components in Zoho CRM home page ? Has someone any idea ? Thanks for your help.Introducing Record Summary: smarter insights at your fingertips
Hello everyone, We’re excited to introduce the Record Summary feature. This powerful addition makes use of Zia to simplify how you interact with your CRM data, providing a seamless, consolidated view of critical record information. Scrolling through theAccount in Quick View Filter
I have a report that I often run against a specific Account. Every time, I have to go into the edit menu and change the Advanced Filter. I would prefer to use the Quick View Filter, but it does not allow me to use the one and only field that makes anyInsert Cookie Policy in Zoho Sites
Hello, i need to insert a banner on my site because i'm in Italy so i have to respect EU laws for Cookie Policy and Privacy Policy. I see that i need to insert a code in <head> section of my site to show a banner/popup with cookie info. How i can do this? Thank you LucaCliq iOS can't see shared screen
Hello, I had this morning a video call with a colleague. She is using Cliq Desktop MacOS and wanted to share her screen with me. I'm on iPad. I noticed, while she shared her screen, I could only see her video, but not the shared screen... Does Cliq iOS is able to display shared screen, or is it somewhere else to be found ? RegardsUnable to confirm Super Admin assignment — confirmation button not working
I’m trying to change the roles within my organization. I am currently a super admin and would like to add another user as a super admin. When I attempt to confirm the action, a screen appears asking for my password to verify my identity. However, whenDelegates should be able to delete expenses
I understand the data integrity of this request. It would be nice if there was a toggle switch in the Policy setting that would allow a delegate to delete expenses from their managers account. Some managers here never touch their expense reports, andLet's Talk Recruit: Meet Zia, your all-in-one AI assistant (Part-2)
Welcome back to Let’s Talk Recruit series. In Part 1, we introduced Zia and how AI is reshaping the way recruiters work. This time, we’re taking a closer look at how far Zia has come and how each update continues to simplify your everyday tasks. WhenFunction #9: Copy attachments of Sales Order to Purchase Order on conversion
This week, we have written a custom function that automatically copies the attachments uploaded for a sales order to the corresponding purchase order after you convert it. Here's how to configure it in your Zoho Books organization. Custom Function: Hitstock
bom/bse : stock details or price =STOCK(C14;"price") not showing issue is #N/A! kindly resolve this problemKaizen #8 - Handling Recurrence and Participants in the Events Module via API
Hello everyone! We are back this week with an exciting post—Handling recurrence and participants in the Events module through API. First things first—What is the Events module? "Events" is a part of the Activities module in Zoho CRM. An event is an activity that happens at a given place and time. You can find Events on the user's Zoho CRM's home page, Activities home page, Calendar, and in other related records. What are the types of Events? Events are of two types—Recurring and non-recurring events.Marketer’s Space - Get Holiday-Ready with Zoho Campaigns
Hello marketers, Welcome back to another post in Marketer’s Space! Q4 is packed with opportunities to connect with your audience - from Halloween, Black Friday, and Cyber Monday, to Thanksgiving, Christmas, and New Year. In this post, we’ll look at howPersonalized demo
can I know more about the personalized demo we are construction company andUser Filter not selecting All Items
We are encountering 2 issues when using the user filter. When users are trying to search using the filter option, the OK button is grayed out. Users have to unselect or make a change before it filters properly. 2. When filtering and the OK button works,Next Page