Query in Analytics for tracking Physical Available For Sale from Books

Query in Analytics for tracking Physical Available For Sale from Books

Need: a query in Analytics on which we can build reports related to Physical Stock from Books items. 

In Books, each item has an Accounting Stock and a Physical Stock. In the settings you can toggle the 'Mode of Stock tracking' option and we have selected Physical Stock. 

However, there is a note that says "Irrespective of your preferred mode of stock tracking in Zoho Inventory, the integrated Zoho Books organization will always track stock based on Bills and Invoices." 

In Zoho Analytics, it is relatively easy to build reports based on the Accounting stock. Because no matter which 'Mode' you choose for tracking, the report structure is based on Accounting stock. 

We have a need to track by Physical stock. We have developed a query that works 90% or more of the time. We have contacted Zoho about this and requested them to either review our query and provide solution, or to just start over and provide a new query that they build. We have been waiting so far 30 days for a meaningful response or delivery of a query, including the past 6 days with no response at all. 

One challenge is the Stock In Flow table has 4 Quantity fields and the Stock Out Flow table has 2 Quantity fields. It's not clear how these fields work, when they are used or not used, how the interact with each other. We have not been able to find any documentation explaining it. A simple pivot view shows there is some logic to how these fields are used, but it is not consistent enough for us to understand without any other information. For example, what is the different between 'Quantity In' and 'Quantity Physically Tracked'? Why is one used sometimes and not other times? Why is one used for a certain type of entry, but not all entries of the same type? 

I have included the queries that we have created below. A specific case where this query failed was due to one item which had the quantity tracked in the 'Quantity In' column instead of 'Quantity Physically Tracked'. This entry was also of the type 'Credit Note'. However not all entries of 'Credit Note' used 'Quantity In' some of them used it and some used 'Quantity Physically Tracked'. 

The first query is a summary query which is used to build reports on. The second query is the detail query which underlies the summary query. This is the one that actually merges all the different tables and attempts to create a current snapshot of the Physically Available stock for each item. 

Any help is greatly appreciated!

--Summary Query--
SELECT
StockLevelDetail."Product ID" AS 'Product ID',
Items."Item Name" AS 'Item Name',
StockLevelDetail."Quantity" AS 'Stock on Hand',
StockLevelDetail."Direction" AS 'Direction',
'Item' AS 'SourceEntity'
FROM  "Stock Level For Estimates Detail Query" AS  StockLevelDetail
LEFT JOIN "Items" AS  Items ON Items."Item ID"  = StockLevelDetail."Product ID"  
WHERE StockLevelDetail."Product ID"  NOT IN
(
  SELECT CompositeItem."Item ID"
FROM  "Composite Item" AS  CompositeItem 
)
 AND LOWER(Items."Item Name")  NOT LIKE '%digital%'
 AND LOWER(Items."Item Name")  NOT LIKE '%online%'


--Detail Query--
 SELECT
  'In Stock' AS "Direction",
  'StockInFlowTable' AS 'Table',
  StockInFlowTable."Stock In Flow ID" AS "Key",
  StockInFlowTable."Product ID" AS "Product ID",
  StockInFlowTable."Transaction Date" AS "Transaction Date",
  StockInFlowTable."Quantity Physically Tracked" AS "Quantity",
  /*IF(StockInFlowTable."Quantity Physically Tracked">0,StockInFlowTable."Quantity Physically Tracked",StockInFlowTable."Quantity In") AS "Quantity",*/ StockInFlowTable."Total (BCY)" AS "Total"
 FROM  "Stock In Flow Table" AS  StockInFlowTable 
 UNION ALL
  SELECT
  'Out Stock' AS "Direction",
  'StockOutFlowTable' AS 'Table',
  StockOutFlowTable."Stock Out Flow ID",
  StockOutFlowTable."Product ID",
  StockOutFlowTable."Transaction Date",
  (-1 * StockOutFlowTable."Quantity Physically Tracked") AS "Quantity",
  (-1 * SUM(FIFOMappingTable."Total (BCY)"))
 FROM  "Stock Out Flow Table" AS  StockOutFlowTable
 LEFT JOIN "FIFO Mapping Table" AS  FIFOMappingTable ON FIFOMappingTable."Stock Out Flow ID"  = StockOutFlowTable."Stock Out Flow ID"  
 GROUP BY 1,
  2,
  3,
  4,
  5,
    6 
 UNION ALL
  SELECT
  'In PO' AS "Direction",
  'PurchaseOrderItems' AS 'Table',
  PurchaseOrderItems."Item ID",
  PurchaseOrderItems."Product ID",
  PurchaseOrders."Date",
  (PurchaseOrderItems."Quantity Received") AS 'Quantity',
  SUM(PurchaseOrderItems."Total (BCY)")
 FROM  "Purchase Order Items" AS  PurchaseOrderItems
 LEFT JOIN "Purchase Orders" AS  PurchaseOrders ON PurchaseOrders."Purchase Order ID"  = PurchaseOrderItems."Purchase Order ID"  
 GROUP BY 1,
  2,
  3,
  4,
  5,
    6 
 UNION ALL
  SELECT
  'Out SO' as "Direction",
  'SalesOrderItems' AS 'Table',
  SalesOrderItems."Item ID",
  SalesOrderItems."Product ID",
  SalesOrders."Order Date",
  (-1 * SalesOrderItems."Quantity"),
  SUM(SalesOrderItems."Total (BCY)")
 FROM  "Sales Order Items" AS  SalesOrderItems
 LEFT JOIN "Sales Orders" AS  SalesOrders ON SalesOrders."Sales order ID"  = SalesOrderItems."Sales order ID"  
 GROUP BY 1,
  2,
  3,
  4,
  5,
    6 
  
  
  

    • Sticky Posts

    • What's New in Zoho Analytics - January 2026

      Hello Users! We are starting the year with a strong lineup of updates, marking the beginning of many improvements planned to enhance your analytics experience. Explore the latest improvements built to boost performance, simplify analysis, and help you

    • What's New in Zoho Analytics - November 2025

      We're thrilled to announce a significant update focused on expanding your data connectivity, enhancing visualization capabilities, and delivering a more powerful, intuitive, and performant analytics experience. Here’s a look at what’s new. Explore What's

    • What's New in Zoho Analytics - October 2025

      Hello Users! We're are back with a fresh set of updates and enhancements to make data analysis faster and more insightful. Take a quick look at what’s new and see how these updates can power up your reports and dashboards. Explore What's New! Extreme

    • What’s New in Zoho Analytics – September 2025

      Hello Users!! In this month’s update, we’re raising the bar across multiple touchpoints, from how you bring in data, plan and track projects to how you design and brand your dashboards. We’ve added the all-new Gantt chart for project visualization, expanded

    • Announcing Agentic AI - Ask Zia!

      We are delighted to roll out the new agentic AI capabilities in Ask Zia, where every stage of the BI workflow is assisted by AI. With a human-in-the-loop approach, Ask Zia ensures that you’re in command of the decision, while AI handles the complexity.

    • Recent Topics

    • Zoho CRM for Everyone's NextGen UI Gets an Upgrade

      Hello Everyone We've made improvements to Zoho CRM for Everyone's Nextgen UI. These changes are the result of valuable feedback from you where we’ve focused on improving usability, providing wider screen space, and making navigation smoother so everything

    • Add Custom Reports To Dashboard or Home Tab

      Hi there, I think it would be great to be able to add our custom reports to the Home Tab or Dashboards. Thanks! Chad Announcement: The feature is being rolled out across DC's and Edition in phases. To know more refer to the announcement post here.

    • Hiding Pre-defined Views

      You can enhance Zoho with custom views - but you cannot hide the pre-defined views. Most users focus on 4 or 5 views. Right now for EVERY user EVERY time they want to move to one of their 4 or 5 views - they have to scroll down past a long list of pre-defined

    • why can't agent see accounts & contacts

      My new user, with 'Agent' privileges, cannot see records that don't belong to them. How can I give them access? Why isn't this the default configuration in Zoho?

    • Sender Email Configuration Error.

      Hello Team, Hope you are all doing well. We are in the process of creating the Zoho FSM environment in the UAE. When we try to add the sender email address “techsupportuae@stryker.com”, we receive the error message: “Error occurred while sending mail

    • 【Zoho CRM】フィルター機能のアップデート：ルックアップ項目を使ったデータフィルタリング

      ユーザーの皆さま、こんにちは。コミュニティチームの藤澤です。 今回は「Zoho CRM アップデート情報」の中からフィルター機能のアップデートをご紹介します。 ルックアップ項目を使ったデータフィルタリングがより詳細に行えるようになりました。 この機能は詳細フィルターとカスタムビューで利用でき、必要な情報を正確に取得できます。 これにより、タブ間を移動することなく、より深く正確な方法でデータを絞り込むことが可能になります。 ◉できること 詳細フィルターとカスタムビューで、ルックアップ先タブの項目が選択可能

    • This will be long, Please bear with me - Next Gen Layout - Search

      In general, I think that Zoho are going in the right direction with the Next Gen UI. The latest update brings some nice improvements and all-in-all from a user's perspective I think the improvements are generally very good. However, there are some areas

    • Zoho Books | Product updates | July 2025

      Hello users, We’ve rolled out new features and enhancements in Zoho Books. From plan-based trials to the option to mark PDF templates as inactive, explore the updates designed to enhance your bookkeeping experience. Introducing Plan Based Trials in Zoho

    • Zoho Books | Product updates | August 2025

      Hello users, We’ve rolled out new features and enhancements in Zoho Books. From the right sidebar where you can manage all your widgets, to integrating Zoho Payments feeds in Zoho Books, explore the updates designed to enhance your bookkeeping experience.

    • Quotes Approval

      Hey all, Could you please help in the following: When creating quotes, how to configure it in a way, that its approval would work according to the quoted items description, not according to quote information. In my case, the quote should be sent to approval

    • Change Last Name to not required in Leads

      I would like to upload 500 target companies as leads but I don't yet have contact people for them. Can you enable the option for me to turn this requirement off to need a Second Name? Moderation update (10-Jun-23): As we explore potential solutions for

    • Important Update: Facebook Pages API Deprecation

      Dear Zoho Analytics users, As of January 26, 2026, Facebook has officially deprecated Facebook Pages API version 18. This update involves the removal of several metrics that were previously supported by the API. As a result, these changes will affect

    • Adding a Deal to and Existing Contact

      I want to easily add a Deal to an existing Contact. If I click on New Deal on the Contact page this currently this is what happens: All of the mandatory field (and other field) information exists within the Contact. Is there a simple way for it to automatically

    • Email was sent out without our permission

      Hi there, One customer just reached out to us about this email that we were not aware was being sent to our customers. Can you please check on your end?

    • Flexible Partial-Use Coupons (Stored Value Credits)

      Subject: Feature Request: Ability for users to apply partial coupon balances per transaction Problem Statement Currently, our coupons are "one-and-done." If a user has a $50 coupon but only spends $30, they either lose the remaining $20 or are forced

    • Prevent accidental duplicate entry of Customer Ordersome

      Zoho Support has confirmed that Zoho currently does not have any method (using Deluge, flow or any other method) to alert a user when a sales order has been entered twice using the same customer reference number (i.e. a duplicate). Most ERP platforms

    • Customizing Helpcenter texts

      I’m customizing the Zoho Desk Help Center and I’d like to change the wording of the standard widgets – for example, the text in the “Submit Ticket” banner that appears in the footer, or other built-in widget labels and messages. So far, I haven’t found

    • Migrating Brevo Automation Logic to Zoho Campaigns

      Hello Zoho Campaigns Support Team, I am in the process of migrating my email marketing from Brevo to Zoho Campaigns. I have around 10,000 contacts, which I have already successfully exported from Brevo and imported into Zoho Campaigns. I now need guidance

    • Is there a way to delete workspaces?

      I want to remove one of the workspaces in my Campaigns account. I don't see any obvious way to do this. Am I missing something?

    • Boost your Zoho Desk's performance by archiving tickets!

      The longer your help desk operations are, the more likely it is to accumulate tickets that are no longer relevant. For example, ticket records from a year ago are typically less relevant than currently open tickets. Such old tickets may eventually lead

    • Collapsing and expanding of lists and paragraphs

      hello Would you ever implement Collapsing and expanding of lists and paragraphs in zoho writer ? Best regards

    • New Custom View -Sorting the Custom fields

      While creating a New Custom View in invoice , Customers, Bills ,expense etc , the sorting of custom fields are not available , a query function "order by / sort by  " may also be included in  Define new criteria module  which will be more beneficial to

    • Make CAMPAIGNS email look as simple as possible

      Hi there I'm trying to make my Campaigns email look as much like a normal email as possible. I'm a bit stuck with the "justification" of the email email block. Can I LEFT JUSTIFY the "whole email" to make it look "normal"? (Please see screenshot attached)

    • [Webinar] Top 10 Most Used Zoho Analytics Features in 2025

      Zoho Analytics has evolved significantly over the past year. Discover the most widely adopted features in Zoho Analytics in 2025, based on real customer usage patterns, best practices, and high-impact use cases. Learn how leading teams are turning data

    • Sorry! we encountered some problems while sending your campaign. It will be sent automatically once we are ready. We apologize for the delay caused.

      Hello. Lately we are having problems with some campaigns, which show us this error message. Sorry! we encountered some problems while sending your campaign. It will be sent automatically once we are ready. We apologize for the delay caused. We can't find

    • Can I remove or divert certain contacts from an active Campaigns workflow?

      I have created a workflow in Zoho Campaigns, which sends different emails, once contacts have been added to a mailing list. To choose which email to send to the contacts, there are conditions, which divert contacts based on their company type and their company size. There was a subsection of this workflow, where company size wasn't selected correctly, and some contacts have been sent down the wrong path and received the wrong email. The workflow contains a reminder loop and a further series of emails.

    • File Upload field automatically replaces spaces with underscores – support experience

      Hi everyone, I want to share my recent experience regarding the File Upload field behavior in Zoho Creator and my interaction with the Zoho support team. When a user uploads a file, the system automatically renames the document by replacing spaces in

    • How to map fields from Zoho Recruit to Zoho People

      I've got these fields from my Job Offer that I'm trying to map to the Work information fields in Zoho People, but they arent showing up. For example, how do I get the department name field (in the job post) to map to the work information field in Zoho

    • UTM in zoho campaigns

      Helloo everybody!!! Someone know how IF ZOHO CAMPAIGNS has UTM for tracking the url of any campaigns. thank u

    • Full Context of Zoho CRM Records for Zia in Zoho Desk for efficient AI Usage

      Hello everyone, I have a question regarding the use of Zia in Zoho Desk in combination with CRM data. Is it possible to automatically feed the complete context of a CRM record into Zia, so that it can generate automated and highly accurate responses for

    • Knowledge base printing

      I saw a posting about printing the knowledge base as I was looking for the answer, but we would like the ability to print out the entire knowledge base with a click, keeping the same organization format.   Bonus would include an index of keywords and

    • Search not working!

      I have items in my notebook tagged but when I search for a tag nothing comes up! Any fix for this?

    • Zoho Books | Product updates | January 2026

      Hello users, We’ve rolled out new features and enhancements in Zoho Books. From e-filing Form 1099 directly with the IRS to corporation tax support, explore the updates designed to enhance your bookkeeping experience. E-File Form 1099 Directly With the

    • Updates for Zoho Campaigns: Merge tag, footer, and autoresponder migration

      Hello everyone, We'd like to inform you of some upcoming changes with regard to Zoho Campaigns. We understand that change can be difficult, but we're dedicated to ensuring a smooth transition while keeping you all informed and engaged throughout the process.

    • File Upload field not showing in workflow

      Hi, I have added a field on Zoho CRM. I want to use it in a workflow where that particular field is updated based on another field, however it is not showing up in the field list to select it in the workflow. Why is this please?

    • Drag 'n' Drop Fields to a Sub-Form and "Move Field To" Option

      Hi, I would like to be able to move fields from the Main Page to a Sub-Form or from a Sub-Form to either the Main Page or another Sub-Form. Today if you change the design you have to delete and recreate every field, not just move them. Would be nice to

    • Sharing my portal URL with clients outside the project

      Hi I need help making my project public for anyone to check on my task. I'm a freelance artist and I use trello to keep track on my client's projects however I wanted to do an upgrade. Went on here and so far I'm loving it. However, I'm having an issue sharing my url to those to see progress. They said they needed an account to access my project. How do I fix this? Without them needing an account.

    • How do i integrate google analytics to Zoho Campaigns?

      Looking to track Zoho Traffic from email Current topic is outdated

    • How do teams manage meeting follow-ups across Zoho tools?

      We’re using Zoho tools for collaboration and tracking, but managing meeting notes, action items, and follow-ups across teams is still challenging. Curious how others are handling this within Zoho workflows. Are there best practices or integrations that

    • Zoho Sprint Backlog View, filter by item status

      Hello, In Zoho Sprints, it would be great to be able filter out specific items in the Backlog based on their status. We would like to track items that were Removed from our backlog without seeing them constantly in the Backlog view, as this view should

    • Next Page