Kaizen #206 - Answering your Questions | Displaying Related Purchase Orders from Zoho Books in CRM Deals using Queries

Kaizen #206 - Answering your Questions | Displaying Related Purchase Orders from Zoho Books in CRM Deals using Queries


Hello everyone!
We're back with another post in the Kaizen series.
We're grateful for the feedback we received from all of you!

One of the questions we received was "I would like to see the list of Purchase Orders in Zoho Books for a Deal in CRM."

We will discuss how to achieve this using the Queries feature in Zoho CRM.

Scenario

In many organizations, a single Deal in Zoho CRM may correspond to one or more Purchase Orders(POs) in Zoho Books. To get a real-time view of all POs associated with a Deal in CRM, we can leverage the Queries feature under Setup > Developer Hub > Queries, and custom related lists.

1. CRM and Zoho Books Integration

Zoho CRM and Books integration allows you to
  • View POs, invoices, and payments for deals directly in CRM
  • Synchronize financial data between CRM deals and Books POs
  • Automate the mapping of deals to POs via custom fields
To create this connection,
  1. Go to Setup > Developer Hub > Connections
  2. Create a connection to Books(OAuth) with the scopes ZohoBooks.contacts.All and ZohoBooks.purchaseorders.All.

2. Prerequisites

Before creating the query and automation, you must have the following ready:
  1. Zoho Books organization ID – needed for REST API calls.
  2. Custom field in Books PO module:
    Field Name: CRM Deal ID
    Type: Single-line
    Purpose: Store the CRM deal ID that each PO relates to.
    Why do we need this custom field?
    By default, Books POs are not directly linked to CRM Deals. To bridge this gap, we create a custom single-line field(CRM Deal ID) in Books POs to store the CRM Deal’s record ID. This makes it possible to filter POs in queries(cf_crm_deal_id == {{deal_ID}}) and reliably display only the related POs in CRM.

    You can store the record ID of the deal in CRM in this field either manually or via a custom function. When you use a custom function, use the Update a Purchase Order API to update the CRM Deal ID field in Books with the record ID of the deal in CRM. You can then associate this function to a button or automation to constantly update the PO in Books every time a deal in CRM is created or updated in CRM. You can also use Zoho Flow to achieve this instead of a function.
  3. Trusted domain and source:
    1. Ensure you have added Books API domain to trusted domain for API calls.

    2. Add Zoho Books as the source of the query to get the PO details from Books. You can add the Books' organization ID as a parameter either here or while creating the query.

3. Single Deal, Multiple POs

Scenario

  • A CRM deal requires multiple POs from different vendors.
  • Each PO in Books stores the same Deal ID in the CRM Deal ID field.
  • This allows a related list in CRM to show all POs associated with the deal.

Why a Single-line Field Works?

  • Each PO references one deal.
  • Queries in CRM can filter using cf_crm_deal_id == {{deal_ID}}. cf_crm_deal_id is the API name of the custom field in PO of Books. You can find it under Setup > Purchases > Purchase Orders > {the custom field} > Edit.
  • Works reliably for related list display.

4. Creating a REST API Query in CRM

Since we will use Books API to get the list of POs, we will create a REST API type query.

  1. Go to Setup > Developer Hub > Queries > Create Query.
  2. Enter the details as shown in the following image:

  3. Choose the Books source that you added recently.
  4. Give a name for the query.
  5. The API name will be auto-populated, and the URL will be populated from the source.
  6. Enter the endpoint to access the resource from Books. In our case, purchaseorders.
  7. To filter based on the value in the CRM Deal ID field in Books, enter the parameter as cf_crm_deal_id and its value as a variable. This variable takes the Deal's record ID as the value during run time. This ensures that the query takes the deal residing in the CRM Deal ID(which is also the record ID of the deal in CRM) field in Books and filters only those POs related to this Deal.
  8. If you have not given org_id as a parameter while creating the source, include it here.
  9. Click Next. You will get a pop-up asking for the value to the variable cf_crm_deal_id. Give a value to see the query in action.
  10. The query returns the list of POs from Books that are related to this particular deal in CRM.
  11. Save the query.

5. Serializing(to return a table for the related list)

Serializer allows you to manipulate the response of the query using Javascript.
In our example, the raw response of the query will contain the POs from Books for a deal in CRM as an array with many fields.
For example, the deal with CRM record ID 3652397000017912047 has two POs in Books viz, PO-00001 and PO-00003. The raw response before serialization is as follows.


But, to associate this response with a related list, we don't need all the fields. We can use the serializer to modify this response and use only the required fields.
The following image shows the serialized response.


Click Save and configure the Schema.


Click Save to save the query.

6. Adding the Query to a Related List

Now that we have the query ready, let's associate this with a related list in the Deals module in CRM to contextually view the POs of the Deal in Books.
  1. Navigate to the Deals module in CRM.
  2. Click the three-dot icon on the right corner of the record, and click Add Related List. Alternatively, you can also click the Add Related List option on the left in the Related List pane.

  3. From the pop-up, select Queries.

  4. From the list of available queries, select the Get Purchase Orders query you created and click Associate.

  5. Give a name for the related list.
  6. Under Variable Mapping, type #(to view merge fields) to get the list of modules available. Choose Deals and the Deal Id field. This means that the record ID of the deal will be passed as the value of the cf_crm_deal_id variable at runtime. The query will then fetch the list of POs for this Deal ID from Books, and display it in this related list.

  7. Click Save and you will see the related list under the Related Lists section of the record.

  8. Reload the page to see the query in action.

Automation Options

You can automatically update the CRM Deal ID in Books POs when a deal is created or a PO is raised.

1. Using Function

  • Function triggers on deal creation or a button click in CRM.
  • Fetches the PO in Books and updates the CRM Deal ID field using the Books connection.

2. Using Zoho Flow

  • Event: Deal created or updated in CRM.
  • Action: Update the corresponding PO(s) in Books.
  • Works without writing Deluge code.
Both methods require the Books connection and organization ID.

Summary/Best Practices

  • Single Deal, Multiple POs: Use single-line CRM Deal ID field in Books.
  • Queries and serializer: Filter POs based on cf_crm_deal_id == {{deal_ID}}.
  • Automation: Use functions or Zoho Flow for real-time updates.
  • Multi-Deal, Single PO: Not supported for related lists; use custom reporting instead.

This setup ensures that sales and finance teams can see all POs for a deal directly in CRM without manual reconciliation.


We hope this post answered your question and you found this useful.
Let us know what you think in the comments or write to us at support@zohocrm.com.


Cheers!


--------------------------------------------------------------------------------------------------------------------------










    • Recent Topics

    • Adding anchor links in Zoho CRM email templates

      I know you can add anchor link in Campaigns, but I dont see the option to that in the CRM email template. Am I missing something?

    • All new Address Field in Zoho CRM: maintain structured and accurate address inputs

      The address field will be available exclusively for IN DC users. We'll keep you updated on the DC-specific rollout soon. It's currently available for all new sign-ups and for existing Zoho CRM orgs which are in the Professional edition. Managing addresses

    • Enhanced duplicate check for Leads in CRM

      Hello Everyone, We are excited to announce that you can now check for duplicate entries in leads by comparing them with similar records in the Contacts Module. Previously, when you added a lead, only the converted leads were checked for duplicates. This

    • Support “Never End” Option for Recurring Meetings in Zoho Meeting

      Hello Zoho Meeting Team, Hope you are doing well. We would like to request support for creating recurring meetings with no end date in Zoho Meeting. Currently, when scheduling a recurring meeting, Zoho Meeting requires us to select a specific end date.

    • openUrl in blueprints

      My customer wants to open a URL at the end of a blueprint transition. Seems this isn't possible right now but it would be very useful. In this thread, https://help.zoho.com/portal/en/community/topic/openurl-not-working the Zoho agent said that it's logically

    • Is it possible to pull the Zoho desk data into Zoho analytics in real time

      Hi - I am looking to add more dashboards and reports of Zoho Desk in analytics. I see there is a schedule to pull the data into analytics, but I'm wondering if there is an option to pull the data in real time instead of a specific interval?

    • Item Details Field - New Barcode / Document option?

      Is this a new feature??? its in both books and inventory.

    • CRM x WorkDrive: File storage for new CRM signups is now powered by WorkDrive

      Availability Editions: All DCs: All Release plan: Released for new signups in all DCs. It will be enabled for existing users in a phased manner in the upcoming months. Help documentation: Documents in Zoho CRM Manage folders in Documents tab Manage files

    • Introducing the revamped What's New page

      Hello everyone! We're happy to announce that Zoho Campaigns' What's New page has undergone a complete revamp. We've bid the old page adieu after a long time and have introduced a new, sleeker-looking page. Without further ado, let's dive into the main

    • Ability to Change Visibility of Published YouTube Videos

      Hi Zoho Social Team, How are you? We would like to request an enhancement in Zoho Social regarding the management of already published youtube videos. Currently, after publishing a youtube video through Zoho Social, there is no option to change its visibility

    • Ability to Add YouTube Video to Playlist During Publishing

      Hi Zoho Social Team, How are you? While publishing YouTube videos through Zoho Social, we noticed that the platform currently does not allow selecting a playlist at the time of publishing. Instead, we can only add the video to a playlist after it has

    • Nouveauté dans Zoho Writer - Création de documents et de modèles assistée par l'IA

      Zia, l’assistant IA de Zoho Writer, soutient les utilisateurs dans l’amélioration de leur écriture et la création de contenus : correction orthographique et grammaticale en temps réel, suggestions adaptées au contexte, détection du plagiat, révisions

    • Canvas View in Zoho Recruit

      Is it possible or would it be possible to have the new 'Canvas View' in Zoho Recruit?

    • Sharing Form Ownership Among Multiple Users

      I would really like the ability to share form ownership among multiple users. It's frustrating to me that if a co-worker wants to make an edit to a form, I have to transfer ownership to them. It would be great if forms could act like google forms, where multiple people can edit a form and view responses. 

    • How to go to the next open ticket in the queue when agents closes ticket

      Zoho Desk When agent closes a ticket - eg when they choose 'Send and Close" - where is the setting that automatically redirects them to the next open ticket in the queue?

    • How to Automate Email Sequence

      I'm having trouble trying to set up a workflow to automate an email sequence. Once a group of emails in a Task has been tagged by a certain tag, I want an instant email template to be sent. After 7 days with no response, another email template would be

    • Marketer’s Space - Ace Your Spooky-Season Marketing with Pre-designed Templates in Zoho Campaigns

      Hello marketers, Welcome back to another post in Marketer’s Space! We’re in Q4, which means that you have endless opportunities to connect with your audience, starting with Halloween campaigns! In this post, we’ll show you how to design the perfect Halloween

    • Turning off the new UI

      Tried the new 'enhanced' UI and actively dislike it. Anyone know how to revert back?

    • Zia expands to China with native features and DeepSeek-powered generative AI features

      Hello everyone, We are glad to support Zia native features and Zia generative AI features for our customers in China. From hereon, all AI-features in Desk will be accessible in China data center with the integration of DeepSeek generative AI model. DeepSeek

    • Email in each module

      We have a contact ,module which then has a link to customer assets which in turn the asset has a multiple link to service visits. When we link assets to customers we choose by name and it brings over the associate email via the lookup. Great feature.

    • Create custom rollup summary fields in Zoho CRM

      Hello everyone, In Zoho CRM, rollup summary fields have been essential tools for summarizing data across related records and enabling users to gain quick insights without having to jump across modules. Previously, only predefined summary functions were

    • Global Search placement in the new UI

      Having a hard time with the global search placement in the UI redesign. Surely I can't be the only one. Previously global search placement was perfect. A bar at the top/center of the page. Exactly where you would expect it to be. Since the new UI has

    • Introducing Skill-Based Ticket Assignment

      The goal of every support team is to provide great support, and to do so as fast as they can. To make this possible, it is important that agents spend their time judiciously, especially when they're dealing with a large number of tickets of varying urgency

    • Empowered 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 individuals

    • Kaizen #213 - Workflow APIs - Part 1

      Welcome to another week of Kaizen! If you have ever managed complex business processes, you know that Workflows are the quiet backbone of any well-run business process. They keep things moving; assigning owners, sending alerts, keeping deals on track,

    • Browser and address bar hide

      Hi, How i can do hide the address bar with browser headline when i am working on the sheet, because i am using (freeze panes) which i want visible for full work. For your reference here i am attached the screen shot and marked yellow lines which really

    • Cells Border

      Hi I am using Zoho Sheet on S Tab , is there any option to make all border of any cell at once. I think this is very basic which we are missing. This is available in mobile but not in tab or suggest if i am missing this function. And for Tab can you give

    • Credit Management: #2 Configuring Right Payment Terms for Credit Control

      Think about the last time you ordered something online and saw that little note at the checkout, "Pay on Delivery" or "Pay later". It's simple, but it actually sets the tone. As a business owner, you know exactly when payment is expected. Now, imagine

    • Zobot and Sales IQ

      What will happen to the Zoho Sales IQ being integrated to the website after creating the Zobot on the website too

    • Workflow workdrive rollout

      Hi! When will workflow be rolled out to all users? Thanks.

    • Task Due Date greater than 10 years.

      We use recurring tasks in Projects where every week, month, year etc Some of our projects are greater than 10 years and we are unable to set a new due date because the difference between start date and due date is greater than 10 years. As an example

    • Zoho Analytics - Make text clickable in underlying data

      Hi Community, I have a simple sales report based on a Invoice query table. I have included a link on to each invoice on the table and sent the Invoice number URL to the link. This works find in the query table, but when I click underlying data on the

    • Custom Modules - Where are Comments??

      In the standard ticket module and in the tasks module, we have an interactive comment box that we can post important details/notes and can tag others if needed. Where is this functionality for Custom Modules? Ideally, custom modules would have very similar,

    • Mapping a new Ticket in Zoho Desk to an Account or Deal in Zoho CRM manually

      Is there any way for me to map an existing ticket in Zoho desk to an account or Deal within Zoho CRM? Sometimes people use different email to put in a ticket than the one that we have in the CRM, but it's still the same person. We would like to be able

    • ZOHO TEAM INBOX Calendar Integration

      The Problem: Clients send meeting invitations to our TeamInbox address. TeamInbox receives these invites, but we cannot accept them. We do not use individual inboxes for transparency purposes. Ideal Solution: A way to accept calendar invites sent to our

    • mail admin not loading

      i am trying to login to mailadmin ... gears keeps rotating forever... its not a password issue whats so ever ... not cookies issues whatsoever from android app i can login but there so few things to do from there .. i changed ip address the same... i

    • Help Center and SEO: Any Benefit to My Domain-Mapped Website Ranking?

      First of, I love the Help Center which I've just decided to integrate into my website to replace its old-fashioned FAQs. So much more to achieve there now! Lots of new benefits to the site visitors and to me in terms of organizing and delivering all the

    • Blueprint status change

      I want to change the Blueprint status of a workflow. The status is completed automatically when I added the record through API call. But I want to make it active, I don't see any options for this. Even if I draw transition from any stage , it is not visible

    • Support french language options

      Greetings, I want to use Zoho with the french language portal, however the supplied translation is not very good (google translate). There are many basic mistakes on the main most important sections (my requests, submit a request). Is there a way for

    • Automation #7 - Auto-update Email Content to a Ticket

      This is a monthly series where we pick some common use cases that have been either discussed or most asked about in our community and explain how they can be achieved using one of the automation capabilities in Zoho Desk. Email is one of the most commonly

    • Next Page