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
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
SES
Parvathy K
Kurt Adair
Janani Thakshayani
Reinoud Visser
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, expandedAnnouncing 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 securityZoholics 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
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
How to list emails in a folder, e.g. Inbox, on multiple pages when using Zoho mail webpage?
Something as shown in the figure. There are totally 50 emails in Sent folder. If "Mail per page" equals 20, then the Sent folder is split into 3 pages. When I wander through Sent folder, I can just select a specific page to jump to. BTW, it seems thatHow to add image to items list in Invoice or Estimate?
Hello! I have just started using Zoho Invoice to create estimates and, possibly to switch from our current CRM/ERP Vendor to Zoho. I have a small company that is installing CCTV systems and Alarm systems. My question is, can I add images of my "items" to item list in Zoho Invoice and Estimates and their description? I would like to show my clients the image of items in our estimates so they can decide if they like these items. And I tell you, often they choose more expensive products just becauseZoho Calendar soft bounce on @hotmail.com and @yahoo.com email addresses
Hello, our Zoho calendar recently does not send the calendar invites to emails with hotmail and yahoo domains and comes back with a "soft bounce". other domains like Gmail works fine. Also sending "email" to the same emails to the above domains work wellERROR CODE :512 - 5.4.4 DNS error:NXDOMAIN.
Suddenly we cant send mail, we are getting this error for all outbound mail to multiple domains.Can Zoho Flows repeat Actions more than once?
I'm attempting to make an intentional Zoho Flow loop using the below layout. However, when "WithinLimit" condition is met, the program fails to execute the action "Get & Add Request Co..." again. Is this by design? Is Zoho Flows unable to repeat actionsUnveiling Cadences: Redefining CRM interactions with automated sequential follow-ups
Last modified on 01/04/2024: Cadences is now available for all Zoho CRM users in all data centres (DCs). Note that it was previously an early access feature, available only upon request, and was also known as Cadences Studio. As of April 1, 2024, it'scustomer data security
We are exploring ways to enhance our within Zoho CRM. Our Goal: We want to fully integrate RingCentral with Zoho CRM to enable click-to-call functionality for our sales team. However, to comply with data privacy regulations and protect customer contactZoho Cliq not working on airplanes
Hi, My team and I have been having this constant issue of cliq not working when connected to an airplane's wifi. Is there a reason for this? We have tried on different Airlines and it doesn't work on any of them. We need assistance here since we are constantlySync CRM inventory data with Zoho Books
I just switched everything over to ZoHo books, but I am trying to find out why the CRM Estimates, Invoices, and Sales Orders created in ZoHo CRM are not then duplicated in ZoHo Books? I had Quickbooks before, and had to do everything twice, I thoughtmask Customer phone number and agents cant see customer phone number
Is there any way we can integrate Zoom Phone with Zoho CRM while ensuring that customer phone numbers remain masked? We need a solution where agents can make outbound calls but cannot see customer phone numbers. Please let us know if there is any solutionEmail Reminders on Shared Calendars
How do we turn off the setting that emails reminders to everyone who has accepted or declined a calendar invite? If 8 of us have been invited to the same meeting, we receive 8 notifications for every step of the process, from invitation to decision.Default Sorting on Related Lists
Is it possible to set the default sorting options on the related lists. For example on the Contact Details view I have related lists for activities, emails, products cases, notes etc... currently: Activities 'created date' newest first Emails - 'createdWebDAV / FTP / SFTP protocols for syncing
I believe the Zoho for Desktop app is built using a proprietary protocol. For the growing number of people using services such as odrive to sync multiple accounts from various providers (Google, Dropbox, Box, OneDrive, etc.) it would be really helpful if you implemented standard protocols such as WebDAV / FTP / SFTP so that alternative inc clients can be used.What's New in Zoho Inventory | Q2 2025
Hello Customers, The second quarter have been exciting months for Zoho Inventory! We’ve introduced impactful new features and enhancements to help you manage inventory operations with even greater precision and control. While we have many more excitingHow to refresh a ticket view ?
I am doing a widget where I send a rest api call to make a new draft to the ticket I am viewing. The issue is sometimes it refresh a ticket view and I can see inserted draft right away, but sometimes I do not see it even if it is inserted correctly andUgh! - Text Box (Single Line) Not Enough - Text Box (Multi-line) Unavailable in PDF!
I provide services, I do not sell items. In each estimate I send I provide a customized job description. A two or three sentence summary of the job to be performed. I need to be able to include this job description on each estimate I send as it's a criticalMerge Items
Is there a work around for merging items? We currently have three names for one item, all have had a transaction associated so there is no deleting (just deactivating, which doesn't really help. It still appears so people are continuing to use it). I also can't assign inventory tracking to items used in past transactions, which I don't understand, this is an important feature moving forward.. It would be nice to merge into one item and be able to track inventory. Let me know if this is possible.Supervisor Rules - Zoho Desk
Hi, I have set up a Supervisor Rule in Zoho Desk to send an email alert when a ticket has been on hold for 48 hours. Is there a way to change it so that the alert only sends once and not on an hourly basis? Thank you LauraResponseCode 421, 4.7.0 [TSS04] Messages from 136.143.188.51 temporarily deferred due to user complaints
Had email bounce. Let me know if you can fix this. Thanks. MichaelZoho Canvas - Custom templates for related lists
Hi, I see that the example pages load always one of our related lists in a custom template, but I dont know how to work with that: 1) How can i make my own custom templates for related lists? 2) Where and how can i check out existing custom templates?Automation #15: Automatically Adding Static Secondary Contacts
Rockel is a top-tier client of Zylker traders. Marcus handles communications with Rockel and would like to add Terence, the CTO of Zylker traders to the email conversations. In this case, the emails coming from user address rockel.com should have TerenceNew Zoho triggers Google Dangerous flag due toabnormal charcters
Just signed up and doing my first email test. I sent it to my google email account but it got flagged as Dangerous" due abnormal characters. My DNS setup looks ok. Page snips attached Help Please Thanks, Rick DC PowerWorldIs there a API to fetch tasks in a Board/Section
I am writing a scheduled function that retrieves all the tasks and send an reminder on cliq. I cannot seem to find a API to fetch tasks (by user / board / section) What are the way to fetch tasks?Having trouble fetching contents of Zoho Connect Feeds using the API, requesting alternative API documentation.
I'm trying to retrieve feed/post data from Zoho Connect using the API but facing challenges with the current documentation. What I've tried: OAuth authentication is working correctly (getting 200 OK responses) Tested multiple endpoints: /pulse/nativeapi/v2/feeds,Adding an Account Name to Tasks/Reminders
Does anyone know how to add the related account name to a task? When we look at the list of activities and when the reminders pop up, there is no way of quickly seeing who the account is.Triggering Zoho Flow on Workdrive File Label
Right now Im trying to have a zoho flow trigger on the labeling/classification of a file in a folder. Looking at the trigger options they arent great for something like this. File event occurred is probably the most applicable, but the events it has arentSendMail to multiple recipients
Hi, I'm trying to send an email to a list of recipients. Right now the "to" field is directed to a string variable. (List variables won't work here). In the string variable, how can I make it work? trying "user@app.com;user2@app.com" or "user@app.com; user2@app.com" just failed to send the emails. RavidPopulate drop down field from another form's subform
Hello, I found how to do that, but not in case of a subform. I have a Product form that has a subform for unit and prices. A product might have more than one unit. For example, the product "Brocoli" can be sold in unit at 3$ or in box of 10 at 25 $. BothUsar o Inventory ou módulo customizado no CRM para Gestão de Estoque ?
Minha maior dor hoje em usar o zoho é a gestão do meu estoque. Sou uma empresa de varejo e essa gestão é fundamental pra mim. Obviamente preciso que esse estoque seja visível no CRM, Inicialmente fiz através de módulos personalizados no próprio Zoho CRM,Signup forms behaviour : Same email & multiple submissions
My use case is that I have a signup form (FormA) that I use in several places on my website, with a hidden field so I can see where the contact has been made from. I also have a couple of other signup forms (FormB and FormC) that slight differences. Allgetting error in project users api
Hello, I'm getting a "Given URL is wrong" error when trying to use the Zoho Projects V3 API endpoint for adding users to a project. The URL I'm using is https://projectsapi.zoho.com/api/v3/portal/{portalid}/projects/{projectid}/projectusers/ and it'sChange total display format in weekly time logs
Hi! Would it be possible to display the total of the value entered in the weekly time log in the same format that the user input? This could be an option in the general settings -> display daily timesheet total in XX.XX format or XX:XX.Different Company Name for billing & shipping address
We are using Zoho Books & Inventory for our Logistics and started to realize soon, that Zoho is not offering a dedicated field for a shipping address company name .. when we are creating carrier shipping labels, the Billing Address company name gets alwaysHow to display historical ticket information of the total time spent in each status
Hi All, Hoping someone can help me, as I am new to Zoho Analytics, and I am a little stuck. I am looking to create a bar chart that looks back over tickets raised in the previous month and displays how much time was spent in each status (With Customer,Zoho Projects iOS app update: Global Web Tabs support
Hello everyone! In the latest version(v3.10.10) of the Zoho Projects app update, we have brought in support for Global Web Tabs. You can now access the web tabs across all the projects from the Home module of the app. Please update the app to the latestZoho Community Weekend Maintenance: 13–15 Sep 2025
Hi everyone, We wanted to give you a heads-up that Zoho Community will undergo scheduled maintenance this weekend. During this period, some community features will be temporarily unavailable, while others will be in read-only mode. Maintenance Window:Agent Performance Report
From data to decisions: A deep dive into ticketing system reports An agent performance report in a ticketing system provides a comprehensive view of how support agents manage customer tickets. It measures efficiency and quality by tracking key performanceShow both Vendor and Customers in contact statement
Dear Sir, some companies like us working with companies as Vendor and Customers too !!! it mean we send invoice and also receive bill from them , so we need our all amount in one place , but in contact statement , is separate it as Vendor and Customer,URL validation
We use an internal intranet site which has a short DNS name which Zoho CRM will not accept. When attempting to update the field it says "Please enter a valid URL". The URL I am trying to set is http://intranet/pm/ Our intranet is not currently setup with a full DNS name and given the amount of links using the shortname probably isn't a feasible change for us.Pourquoi dans zohobooks version gratuite on ne peut ajouter notre stock d'ouverture??
Pourquoi dans zohobooks version gratuite on ne peut ajouter notre stock d'ouverture ??Next Page