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 – 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.
    • Invitation-Based User Access in Zoho Analytics

      Hello everyone, We’re rolling out an important update on how users are added to your Zoho Analytics Organization and Workspaces. Previously, when admins added users, they were automatically added to the organization. Moving forward, to improve security
    • Zoholics Europe 2025: Your Ultimate Data Analysis (Zoho Analytics) Workshop Experience

      Why should you attend? This year, Zoholics Europe 2025 is putting data analysis centre stage. With a dedicated workshop designed to answer all your data-related questions, you’ll gain practical skills, real-time solutions, and expert insights that you
    • [Webinar] Evolving BI & Analytics in the Age of AI

      Artificial intelligence is redefining how data is collected, analyzed, and leveraged across industries. As businesses strive to become more agile and insight-driven, traditional BI and analytics must transform to meet new demands. AI-first organizations
    • Recent Topics

    • Adding a developer for editing the client application with a single user license

      Hi, I want to know that I as a developer I developed one application and handed over to the customer who is using the application on a single user license. Now after6 months customer came back to me and needs some changes in the application. Can a customer
    • Deals by Stages Funnel not showing in correct order

      Using the Stage-Probability Mapping for the Deals module we have created a steps our deals will pass through, RFQ, Closed/Lost, Declined/No-Go, Pricing, Submitted, Negotiations, Won. However when I view the Deal By Stages Funnel it does not show in the
    • Turning off the new UI

      Tried the new 'enhanced' UI and actively dislike it. Anyone know how to revert back?
    • Message "...does not support more than 100 distinct values..." WHY????

      I get this message on one of my reports: Sorry, Zoho Reports currently does not support more than 100 distinct values in columns. 'Account Name * Sum(Amount),Count(Amount Tier)' contains more than 100 distinct values.  Possibly, you can apply filter to reduce the number of distinct values in 'Account Name' or drop the 'Account Name' field in Rows. I want to list all ACCOUNT NAMES (about 500) with SALES BY ACCOUNT.  What is blocking this?
    • Attention API Users: Upcoming Support for Renaming System Fields

      Hello all! We are excited to announce an upcoming enhancement in Zoho CRM: support for renaming system-defined fields! Current Behavior Currently, system-defined fields returned by the GET - Fields Metadata API have display_label and field_label properties
    • Confirmation prompt before a custom button action is triggered

      Have you ever created a custom button and just hoped that you/your users are prompted first to confirm the action? Well, Zoho knows this concept. For example, in blueprint, whenever we want to advance to the next state by clicking the transition, it is
    • GST Slabs Redefined: Stay Compliant Using Zoho Books!

      Hello Everyone! The Government of India is rolling out new GST rates, a major reform aimed at simplifying the current tax structure starting 22 September 2025. GST will move from four slabs (5%, 12%, 18%, 28%) to two main slabs (5% and 18%), plus a special
    • Cost of good field

      Is there a way we can have cost of good sold as a field added to the back end of the invoicing procedure and available in reports?
    • How to create auto populate field based on custom module in Zoho CRM?

      Hello, i'm still new to Zoho CRM and work as administrator in my company. Currently, I'm configuring layout for Quotes Module. So, the idea is, I've created a read-only field in Quotes called "Spec". I want this field automatically filled with Specification
    • Rich Text For Notes in Zoho CRM

      Hello everyone, As you know, notes are essential for recording information and ensuring smooth communication across your records. With our latest update, you can now use Rich Text formatting to organize and structure your notes more efficiently. By using
    • 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
    • Elevate your CX delivery using CommandCenter 2.0: Simplified builder; seamless orchestration

      Most businesses want to create memorable customer experiences—but they often find it hard to keep them smooth, especially as they grow. To achieve a state of flow across their processes, teams often stitch together a series of automations using Workflow
    • Office 365 and CRM mail integration: permission required

      Has anyone run into this weird problem? My email server is Office 365. When I try to configure Zoho CRM to use this server, a Microsoft popup window opens requesting user and password. After entering that, I get a message in the Microsoft window saying
    • Time Entry Notifications

      Hi All - I have support staff who place notes of their work in the time entry section of Zoho Desk. Is there a specific workflow or setting I need to enable to have the ticket holder updated via email when an entry is saved?
    • Tables improvement ideas / features

      Heya, I've been using Zoho Tables for a few months now and wanted to post some features that I think will be greatly beneficial for the tool: 1. Ability to delete a record in automation or move a record in automation. - Usecase: I move a record from one
    • Deluge - Can't get phone number SalesIQ

      Hey folks, I’m building a custom plug for SalesIQ that’s supposed to register leads into Zoho CRM. The SalesIQ chat is being implemented on WhatsApp, and in my plug I’m using this line: mobile_clean = session.get("phone").get("value"); From what I understand,
    • Critical Issue: Tickets Opened for Zoho Support via the Zoho Help Portal Were Not Processed

      Hi everyone, We want to bring to your attention a serious issue we’ve experienced with the Zoho support Help Portal. For more than a week, tickets submitted directly via the Help Portal were not being handled at all. At the same time no alert was posted
    • Zoho CRM button to download images from image upload field

      Hello, I am trying to create a button in Zoho CRM that I can place in my record details view for each record and use it to download all images in the image upload fields. I tried deluge, client scripts and even with a widget, but feel lost, could not
    • how to differentiate if whatsapp comes from certain landing page?

      I create a Zobot in SalesIQ to create a Whatsapp bot to capture the lead. I have 2 landing pages, one is SEO optimized and the other want is optimized for leads comes from Google Ads. I want to know from which landing page this lead came through WhatsApp
    • Zoho Desk - Community

      As a regular user of Zoho Cares Community I would really love to see the publish date of articles. For example, when I look at Announcements, it would be very beneficial to see which ones were posted recently, over those which have just and a recent comment.
    • access to quartz for my customers

      Hi how can I have access to the application quartz you use for us to send you screen rocording, this feature would be immensely useful for our customers support https://quartz.zoho.com/
    • Restricting contact creation

      Hi all! I am looking to use Zoho Desk in a part of the business that takes end user enquiries. These are generally single interactions, and not linked to an account name. As Desk is Account centric, has anyone designed a way to manage these incoming emails
    • Issue with Inline Images in Email Reply via Zoho Desk API

      Hi, I am attempting to send inline images in an email reply using the Zoho Desk API, but the images are not being displayed inline for the recipient. I have followed this documentation: https://desk.zoho.com/DeskAPIDocument#Uploads https://desk.zoho.com/DeskAPIDocument#Threads#Threads_SendEmailReply
    • How to search a value stored in a subform?

      Hello, We store serial numbers in subforms but now we would like to be able to search the values to be able to easily find the record with the serial number. I saw that it's not possible to search such values through global search but is it possible to do it an other way? Thank you,
    • Field Dependency Not Working on Detail Page in Zoho Desk

      Hi Support Team, I’ve created field dependencies between two fields in Zoho Desk, and they are working correctly on the Create and Edit layouts. However, on the Detail page, the fields are not displaying according to the dependencies I’ve set — they appear
    • How do the keyword critera work?

      Hi, I'm working on automated assignment of tickets based on keywords. How does this feature work? Where does this criteria look for keywords - email address, subject, email body? Can you please clarify this as I want to avoid overlapping with criteria
    • Error: Unsupported content type: text/html;charset=UTF-8 after tryeing to get the token for n8n automation

      I am working on ZOHO Desk automation and need to get the ZOHO auth token for n8n I have created the app in ZOHO Desk API, got client id and client secret. Added all data required to get a token in n8n. After I sign in with my ZOHO credentials in ZOHO
    • Improving Collaboration Features in Zoho Portal

      Hello Zoho Community, I’ve recently started exploring Zoho services and I’m really impressed with the wide range of features. However, I feel there is still room for improvement in the collaboration area. For example, it would be really helpful if we
    • Automated Shopify Emails Not Being Delivered

      I have an ecommerce store with Shopify. I recently set up my email to be served through Zoho. Since doing this, customers are not receiving some of our automated emails from Shopify itself. Our initial email that confirms their purchase goes through but our Shipping Notification that is automatically sent out upon fulfillment is not going through. Sometimes we get a notice that it's been classified as spam, sometimes nothing. I can send/receive email via Outlook on my desktop and I can send/receive
    • Clear Tag & Linking Between Quotes and Sales Orders

      Hi Zoho Team, In Zoho Books, when a quote is converted into a sales order, it would be extremely useful to have: A clear tag/indicator on the quote showing that it has been converted into a sales order. A direct link in the sales order back to the originating
    • I can't log in to my account on Thunderbird

      I've just had to rebuild my PC (calamitous mess from Microsoft with Win10/Win 11 'upgrade' - they confirmed I had to start with a new build). I have used Zoho mail for years via Mozilla Thunderbird, but now I've had to download the latest version of TBird,
    • New in Cadences: Option to Resume or Restart follow-ups when re-enrolling records into a Cadence, and specify custom un-enrollment criteria

      Managing follow-ups effectively involves understanding the appropriate timing for reaching out, as well as knowing when to take a break and resume later, or deciding if it's necessary to start the follow-up process anew. With two significant enhancements
    • Send a campaign to one recipient.

      Very often I speak to a customer and they say they didn't see my email (maybe it went in Junk, maybe they deleted it). Anyway, I just want to go into the Campaign and send it to one person. You already have a feature very close to this - when sending a Test. While developing a campaign, I can send tests to anyone. Why can't we have this AFTER  the campaign has been sent? I know, there's a caveat, and that's in the use of merge tags. Most of the time I only use FNAME, but maybe you could check if
    • Feature Request - Insert URL Links in Folders

      I would love to see the ability to create simple URL links with titles in WorkDrive. or perhaps a WorkDrive extension to allow it. Example use case: A team is working on a project and there is project folder in WordDrive. The team uses LucidChart to create
    • Try FSM again for our business

      We already have our customers individual equipment in CRM with serial numbers, install dates, warranty length and importantly next service which is generally 2 years. a month before the service date is due we get get a report and send out service reminders.
    • Use Zoho Books to bill for work done in Zoho Desk??

      I'm trying to see if something is possible (and if yes, how). We use Zoho One to manage our business. We have a lot of clients that will put in a ticket (via portal) to have work done. Out techs will pick up the ticket, do the work, and then log the time
    • Zoho Creator as LMS and Membership Solution

      My client is interested in using Zoho One apps to deploy their membership academy offer. Zoho Creator was an option that came up in my research: Here are the components of the program/offer: 1. Membership portal - individual login credentials for each
    • Get Cliq Meetings in my O365 calendar

      Hi, we are currently evaluating to replace the Teams Messaging and Meetings with Cliq. We currently still have all our email and calendars in O365. What i want to achieve is, to create a (ZOHO) meeting from Cliq and have this meeting added to my Outlook/O365
    • Issue with Zoho Help Portal – Tickets Missing or Not Answered

      Hi, How are you? I think there may be an issue with the Zoho Help Portal. I opened a few tickets directly in the help portal a some time ago but never received any response I also opened ticket 148356451 by email. I did receive a reply to it, but the
    • No bank feeds from First National Bank South Africa since 12 September

      I do not know how Zoho Books expects its customers to run a business like this. I have contacted Zoho books numerous times about this and the say it is solved - on email NO ONE ANSWERS THE SOUTH AFRICAN HELP LINE Come on Zoho Books, you cannot expect
    • Next Page