Sometimes, it is necessary to quickly retrieve detailed information from one or more ledgers that may contain hundreds of thousands to many millions of records. The query object is the perfect tool for such data selection as it is totally scalable and can retrieve selected fields from multiple tables at once. The following example (using Cronus data) will show the aggregated quantity per bin of lot-tracked items in stock. This query can be presented to a user by means of either a report or a page:
- We will define the logic we need to follow and the data that's required to support that logic, and then we will develop the query. It is necessary to know what inventory is in stock, which also contains a lot number. This is accomplished using the Item Ledger Entry table.
- However, the Item Ledger Entry record does not contain any bin information. This information is stored in the Warehouse Ledger Entry table.
- The Location Code, Item No., and Lot No. columns are...