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

      • Zoho Analytics in 2024: A look back

        Happy new year to everyone in the Zoho Analytics community! As we welcome 2025, here's a look back of important happenings in 2024.

      • Announcing Zoho Analytics 6.0 Beta!

        We are delighted to open up the next major version, Zoho Analytics 6.0 Beta! The new version comes packed with a wide range of functionalities for all persona, namely business users, data analysts, data engineers, and data scientists. Zoho Analytics team

      • What's New in Zoho Analytics - August 2024

        Hello Users! We are back with the latest updates and enhancements made to Zoho Analytics. Keep reading to learn more about them. Connect to the data hosted in the cloud without allow-listing the IP addresses Utilize Zoho Databridge to connect to the data

      • We are coming to your city! Zoho Analytics Community Meetup

        Hello, business leaders and data enthusiasts! We are delighted to announce that registrations are now open for the ZUG meetups, and we can't wait for you to be a part of them. Our in-house analytics experts are geared up to lead discussions on constructing

      • Zoho Analytics: 2021 Look Back

        As we start a new year in 2022, here's some of our top moments from 2021. Zoho Analytics in 2021

        • Recent Topics

        • Matrix dropdown

          Is there a way to create a matrix question with dropdown answers but 1 row correlates to 1 column? So really I want to combine 4 dropdown questions into 1 matrix but each question has a single, and different, dropdown for answers.

        • Managing Rental Inventory in Zoho

          Can Zoho Inventory manage rental inventory efficiently, or is there another Zoho app that would be better suited for this kind of operation? Looking for advice on the best approach. Thanks in advance.

        • Code API récupération enquêtes et documents

          Bonjour peut-on générer des codes API avec Zoho Survey ou Forms afin de récupérer des questionnaires existants sur nos autres outils ? Comment procéder ? Faut-il disposer d'un abonnement particulier ?

        • Field Updated based off Call Status

          I'm trying to create a Field Update where: When Call Status is Completed Lead/Contact/Account Description field with the information in the "Outcome Of Outgoing Call" Description field. Once our sales team finishes their calls and they add a description

        • Exporting uploaded files in bulk on Zoho Survey?

          Hi, I have a survey that includes an image upload field, I'd like to be able to export the individual responses in bulk, but it appears only the name of the image uploaded, and not the image itself gets exported. Any way to have the images be exported

        • Is there API Doc for Zoho Survey?

          Hi everyone, Is there API doc for Zoho Survey? Currently evaluating a solution - use case to automate survey administration especially for internal use. But after a brief search, I couldn't find API doc for this. So I thought I should ask here. Than

        • Create Quote does not show the "Product Description" entered as part of the Product setup.

          The product description created as part of the product setup page, does not show in the Create Quote module; The module allows for an additional description to be added but without access to the original stored description. By selecting the product from the "Product Name Lookup" pop-up, the "Product Description" part should be populated from the product record where the user is allowed to further modify it.

        • How to convert Item to composite Item

          Dear Team, We need to convert Item to composite Item as Zoho is importing partial information from Shopify.

        • Function #4: Schedule Customer Statements

          Regularly sending statements to customers is an imperative part of many business processes as it helps foster strong customer relationships and provides timely guidance on payments. While you can generate the statement of accounts and have it sent over

        • Why wont Zoho Support Grammarly!! --- PLEASE VOTE FOR THIS to show Zoho we need this

          The spell check and grammar in ZOHO are so buggy and a waste of time. Please support Grammarly! I'm sure I'm not the only one — there are other CRMs that support this. If you're not planning to add this feature, Please let others know before accepting

        • Email Verification on Subdomain

          Hi, The latest guidelines for setting up an email newsletter are to set it up on a subdomain of your main domain so that if you get put in a spam block, it doesn't block all your company email. We have been trying to set this up and managed to get our

        • Phone Number Format Change - Bulk Add Country Code and Remove Dashes

          In the past I would create a new customer in my Zoho Books, and enter their phone number in the format customary for the USA (that's where we are domiciled): Areacode-Prefix-Number 234-456-6789 This scheme is important as many other software packages

        • User Tips: Auto-Create Opportunity/Deal upon Quote Save (PART 1)

          Problem: We use quotes which convert to sales orders but Users / Sales Reps do not create opportunities / deals and go straight to creating a quote. This leads to poor reporting. Implementing this solution improves reporting and makes it easier for users.

        • Tracking Agent Diligence in Updating Records

          Our organization mainly focus on B2B. While we've implemented automations like pushing info from web forms into CRM, we often get info from events and individual networks. The complete/updated info relies in the agents who directly interacts with them,

        • Automating Custom Web Link Messages to Customers via Instant Messaging in Zoho Desk

          Hello, I am looking for assistance with Zoho Desk's Instant Messaging feature. Is there a way to automate the sending of a custom web link to customers every day at 6:30 PM? Any guidance or suggestions on how to achieve this would be greatly appreciated.

        • Categorise Attachments

          We take ID, proof of address, right to work documentation and more.  I can upload a single file in to field, but we often receive multiple files for each category e.g. someone may send a separate file for the front and back of their national ID card.  My team don't have time to manipulate the files in order to upload them as a single file. The options, as far as I can tell, would be to create additional fields on attachments in order to categorise what the file is, or to be able to upload single

        • How to autorespond for inactive users?

          Hi, we have a few inactive users that we would like to set up an autorespond to the sender telling that the email addresses are no longer active. How can we do that?

        • Mass Action Button Script

          Hi all, I've been trying to search a lot of places for a solution on how to do a button placed at the "Mass Action Menu" and thus only updating multiple CHOSEN records at once. After finding no success whatsoever, I decided to just run and test whatever

        • Error 553

          Hello how are you? I'm having trouble sending email. This message appears whenever I try to send an email. I'm receiving emails normally, but I just can't send them

        • Implement Regex in Layout and Validation rules

          Hello all, We are excited to announce that users can now implement Regular Expressions (Regex) in our layout and validation rules. This new functionality allows for more flexible rules to be created when designing and validating forms. What is Regex?

        • Zoho Desk Validation Rule Using Custom Function

          Hi all, I tried to find the way to validate fields using custom function just like in Zoho CRM but to no avail. Is there a way to do this?

        • 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

        • How do we add Google Analytics code to Zoho Bookings scheduling pages or the thank you page?

          We need to track user activity on individual Bookings' pages and/or the thank you pages? How do we do this?

        • Zoho Sheets working offline

          Hi,  I am looking for the ability to work offline in Zoho Sheets, but currently I cannot find the process to complete this.  Does someone have any ideas or steps I might have missed?  Also does Zoho Sheets have the "Format as Tables" function as is currently in MS Excel 2016??  Many thanks. 

        • Repeating Sections in Writer

          I am wondering if it's possible to create repeating sections or text boxes of a document based on a merged subform fields coming from Zoho Forms. We are currently using excel to dynamically create a legal PDF document based on input fields in another

        • Trying to copy Active Leads to a Custom Module called Dead Leads

          Hi, I am trying to code when the "Active Lead Status" field is changed to "Dead", a copy of the record should be created in the "Dead Leads" module while keeping the original lead in "Active Leads." I am using the following code but I keep getting the

        • Important update in Zoho Forms: Enhancements for improved email deliverability

          Hello, form builders! We would like to inform you about some changes we're making in Zoho Forms to ensure the deliverability of your outbound emails. Changes to Gmail policies Gmail has updated its DMARC policy which quarantines emails sent with gmail.com

        • Error 403: Forbidden When Updating Email Signature via API

          Hi Zoho Desk team, First, congratulations again on the excellent Zoho API. But, I’m encountering an issue while attempting to update an email signature via the API. Whenever I make a request to update the signature, the response returns an HTTP 403 Forbidden

        • Directly Edit, Filter, and Sort Subforms on the Details Page

          Hello everyone, As you know, subforms allow you to associate multiple line items with a single record, greatly enhancing your data organization. For example, a sales order subform neatly lists all products, their quantities, amounts, and other relevant

        • Default Reminder for tasks set to Pop-up instead of email

          Try to save a click every time we create a task we want it to default to pop-up instead of email. Also default is to have reminder unchecked anyway to change so reminder is checked and method is pop-up default instead of email? Attached picture. Thanks,

        • Why hasn't Zoho CRM For Everyone been rolled out?

          I don't understand the point of rolling out new features so slowly after a big fanfare launch 8 months ago. I've signed up for 'early access' and also contacted my point of contact, but nothing. Not even an auto reply. Would you say that this is good

        • Canva Integration

          Hello! As many marketing departments are streamlining their teams, many have begun utilizing Canva for all design mockups and approvals prior to its integration into Marketing automation software. While Zoho Social has this integration already accomplished,

        • Can I print a set of record templates as a single pdf?

          I have a record template formatted as a gift certificate. I can email a single gift certificate to each recipient, but I also need to print the whole batch for the organiser, and they won't want 40 separate files. The layout needs to be identical, so

        • Time Zone Sending in Marketing Automation

          Good day...can anyone share if they have been able to get sending via Time Zones to actually work? Our data is 99% perfect, with all the time zone criteria met, yet the results are completely off target when we try to use it. We've had varied results

        • Combine Small Pie Slices in Pie Chart

          Hello, I am trying to make a pie chart showing our sales in each of our product categories. The problem is that we have over 80 product categories, and not that much room from which to look at it. I am wondering if there is a way to combine the smallest

        • Which are the IP addresses to use for 'split delivery' with Office 365? (Zoho mail inbound gateway)

          Hi, I'm trying to set up 'split delivery' (email routing) with Office 365. I'm following the instructions to set up Office 365 as the primary server (https://www.zoho.com/mail/help/adminconsole/coexistence-with-office365.html) One of the prerequisites

        • Zoho Desk Onboarding Assistance - How to do bulk taging

          Hi How to apply a particular tag to multiple tickets in one go.

        • Round robin not processing backlog tickets

          We set up a round-robin for one of our departments which initially worked, but something seems to have broken during the 'tweaking' process as it will no longer assign backlogged tickets of any kind. (I've included images of the settings.) Based on the

        • Merge Tickets Directly from Contact Page in Zoho Desk

          Dear Zoho Desk Support Team, We are writing to request a new feature that would allow users to easily merge tickets directly from the contact page in Zoho Desk. Currently, the only option to merge tickets is from the Tickets list view page, which can

        • Text snippet

          There is a nice feature in Zoho Desk called Text Snippet. It allows you to insert a bit of text anywhere in a reply that you are typing. That would be nice to have that option in Zoho CRM as well when we compose an email.

        • Next Page