Inventory management with a constraint

Basic Inventory Management

Whenever you are faced with a workflow or business problem, QuickBase provides you with building blocks that you can use to solve your problem. In this real-world example, we will look at how you can solve an inventory management problem using a combination of summary and formula fields to calculate inventory transactions and metrics.

Problem:

A customer wants to track what items - we'll call them widgets - were taken from available inventory over the course of each month. The problem is that they only keep track of what was added to inventory each month, along with the total amount on hand at the beginning of each month.  They need to know what amount is available at any given time.

Known - "adds" and "total available at month end"

Unknown - "takes"

On the App Exchange: ABC Inventory Mgt

Please visit the Exchange and download the application ABC Inventory Mgt to your realm for reference.

Solution:

Using two tables - a parent table for each period, in this case each month, and a child table for the transactions that occur as people add, and "take inventory" or count what amount is available at the end of each month, we will be able to back into the calculation and determine what amount of inventory was taken, or used, during the month.

Seeing that QuickBase tables do not allow you to calculate a running, or accumulating, balance record by record, we need to use a parent table in order to use summary fields to calculate the relevant dates and values for each of our transactions.

When you have the relationship created, then you can define a parent record for a given period, in this case let's use "Years" for each period.  We can use any period we want, but for simplicity's sake, let's say that transactions can occur throughout the course of the year.

Since we know that there will be Count transactions, let's first identify the "Last Count Date" so that we can determine the last known total amount on hand for the available inventory.  We can do this by creating a summary field using the operation function of "Maximum" to see the date of greatest, or most recent, value.

We also want to make sure we use the condition where the Adjustment Type is equal to the value of "Count" so we are only evaluating the Count transactions.

To complete this step, we also need to create a Lookup field to lookup to the date value we just identified.  Let's call this "Inventory - Last Count Date."  We will use this in our next summary field to determine the last count amount.

Let's now create the next summary field for getting the Last Count Total Amount.  Be sure to use the function of Total, and you will also need to set the condition so that your date field is equal to the "Inventory - Last Count Date."

The next step is to determine how much has been added to inventory since our last count date.  We do this by creating a summary field "Total Added Since last Count" where the function is Total, and the field to summarize is Amount, and the condition Date field is equal to the "Inventory - Last Count Date."  This field will provide us with a total for all the "Adds" to inventory in this period.

The final step is to create a formula field called "Total On Hand" that evaluates the summary fields we created earlier.  The equation is as follows:

[Last Count Total Amount] + [Total Added Since Last Count]

This amount will help us determine how much was taken from inventory seeing that we haven't been tracking those transactions.

Using the same logic as described above, you can create further summary and formula fields to "back into" the "Takes" from inventory during various periods.

As an exercise, I would highly encourage you to try to recreate this application on your own, from scratch.  This will help you develop your QuickBase building skills, thinking through the problem as well as coming up with the right mix of summary and formula fields.

------------------------------
Sean Padian
------------------------------