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.
Responses