100%
Home SQL Advanced Inventory Tracking in Zoho Analytics

Advanced Inventory Tracking in Zoho Analytics

If Zoho Doesn't Give You the Functionality, Build It!

If you are an inventory manager, this blog post could change your life. If you are not, it will at least teach you how to build some wicked inventory tracking for your business.

While talking not too long ago with Zoho, we learned that similar inventory reports are in the works. And by “in the works”, they really meant “oh, 1 1/2 – 2 years down the road we’ll get to it.” That’s a long time to wait for this kind of item-level tracking! So, we built our own reports that do the job nicely.

Whether you want to fix erroneous reporting, perform an audit, run analytics on inventory movement or more, the following Query table will help you get there. While reading this post, reference the Item-Level Tracking repository on our Github page.

The problem is that in Zoho Books and Inventory, there is still no universal search by Item Name or SKU. You can’t easily see all the transactions for a particular item. However, in Analytics, if you end up building separate reports for each transaction type, you get something like the following, where you have to add a filter for each report (and that’s just 4!). So, if you use this code, you can build a very clean, effective report, shown at the end.

At approximately 190 lines of SQL code, it looks intimidating! But, once you figure out how to build the SELECT query for one type of transaction, the other 7 are really just variations of the same. Each iteration starts off with a base table of transaction items (invoice items, package items, etc).

Then, there is a series of JOIN’s for each iteration. The most common are to join the Users, Warehouses, and Items tables, on their respective ID’s. For some reports, you will JOIN the Customers or Vendors tables. The trick is to make sure you are matching on the right fields. 

LEFT JOINS ensure that you are not omitting any records, even if the match doesn’t work because there is no data on the “right side”. 

Another feature in every iteration is the column “Original Record”. It is a concatenation of the base URL of every transaction (in Zoho Books) with its unique record ID. See **__insert_link_to_link_analytics_records_to_books_post__**

Also,  we create that simple “Type” column, just adding in our own text, to enable easy filters.

Then, in the last iteration for Transfer Items, note that we JOIN the Warehouses table twice, to pull in the information for both the Source and Destination warehouse.

What if I get an error message?! How am I supposed to know what to fix with 190 lines of code???

 

Yes, who creates a perfect table on their first try? Often, Zoho prompts you with the line number of the error. But what if you get a message like this?

An unknown alias ‘I’ was used? But what if you used that alias in every iteration? You’ll be hard-pressed to find the error.

When I create long queries like this, I like to build one iteration at a time and test it in a separate window. Just copy and paste over one iteration and try that “Execute Query” button. It’s much easier to find an error in 25 lines of code than 190. This way, you can quickly perfect each iteration one by one, then aggregate them all together into the one report and have the confidence it will work.

After the Query Table is Built

Once you have saved and can view the Query, it’s time to build the Item-Tracking Dashboard. Read up on the basics of building summary reports and dashboards with these links. 

Using your newly-built Query table as the base, create a new summary report. As a filter, choose one of the 8 transaction types. Remember, we created that as a text column?

this should be a pic of the filtering by type

Then, drag and drop all the columns you want! I actually like to drop them all in the ‘Group By’ section because it gives a really nice layout and we don’t actually need to summarize anything.

Once you finish one, name and save it. Then, instead of starting from scratch, click ‘Save As’ and name it for your next report. Then, you only have to change the filter and your next report is complete! You may want to add/remove Customer or Vendor depending on which report it is. 

Once you have all 8 reports, throw them all into a single dashboard. Because all these summary reports come from the same base table, when you go to add user filters like SKU or Warehouse, they will filter all 8 reports at once!

should be the inventory lookups pic

With this dashboard, you will be able to:

  • search ALL transactions by SKU, Warehouse, Salesperson, etc.
  • identify where errors arose in inventory counts
  • quickly access the actual transaction records

Wait! There is One More Feature You'll Want to Know About

If you have an inventory manager or other coworker that prefers to work from Zoho Books or Inventory and not from Analytics, you should look into custom buttons and webtabs.

Webtabs allow you to include a view of your Zoho Analytics dashboard inside of another Zoho app. No more switching tabs and apps—see it all in one place!

Custom links are dynamic and enable you to place buttons in different modules that can link back to a pre-filtered Zoho Analytics dashboard. For example, you could place that button in the Items module and when someone clicks on it, it takes them to the Inventory Tracking dashboard you just built, already filtered by that item! Then they can see every transaction for that item, all in one place.

should be the Custom Links picture

With the SQL code already available in GitHub and an explanation written out here, we hope you can learn how to build and even improve these reports!

If it still seems a little too much to tackle on your own, consider looking at our Zoho Analytics course bundle which will teach—and show—you everything you need to know from beginning to end. 

Then, if you have additional questions beyond that or other difficult tasks to be done in your Zoho system, consider reaching out to us here.

See All Courses

Comments